Archive

Archive for the ‘LINQ’ Category

The delights of SqlMetal: Multiple ResultSets from a stored procedure

May 17, 2011 5 comments

Ever wanted to return multiple result-sets from a stored procedure using Linq to SQL? Well I have and, like you if you’re reading this, I found that the default settings in Visual Studio generated code did not allow it. The problem was that the code generated only returned the first result set from the stored procedure. I.e:

SELECT * FROM USERS
SELECT * FROM ROLES

Gave me an IEnumerable return value. Now this just wasn’t good enough; so without further ado here’s the simple code to create a database entities ‘later’ using SqlMetal (a Microsoft authored tool supplied with both Visual Studio 2008 and 2010). First of all, the easiest way to start this tool (as with all Visual Studio tools) is to open the command window within the Tools sub-menu in the Visual Studio section of the Start menu.

Once in try this:

SqlMetal /server:. /database:MyDb /code:”DalEntities.cs” /sprocs /namespace:DalEntities /pluralize /serialization:Unidirectional

…and you’ll find that magically the usual dbml style generated code is produced and placed in DalEntities.cs – in this particular case with all the Stored Procedures included also.

What’s more, as previously said, the tool supports multiple result-sets to be returned from stored procedures. As shown below:

[global::System.Data.Linq.Mapping.FunctionAttribute(Name="dbo.TestStoredProcedure1")]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(TestStoredProcedure1Result1))]
[global::System.Data.Linq.Mapping.ResultTypeAttribute(typeof(TestStoredProcedure1Result2))]
public IMultipleResults TestStoredProcedure1()
{
	IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
	return ((IMultipleResults)(result.ReturnValue));
}

It’s like magic, I tell ye!  Have fun!

%d bloggers like this: