Home > C#, Computers and Internet, LINQ, SQL Server > The delights of SqlMetal: Multiple ResultSets from a stored procedure

The delights of SqlMetal: Multiple ResultSets from a stored procedure

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!

Advertisements
  1. eugenedebb
    May 19, 2011 at 10:29 pm

    Great article !!

    A quick question though, Is there a way to generate the code for only one stored proc. I have about 100 sprocs and only need to use 1 for my project.

    Thanks again

    E

  2. May 20, 2011 at 8:27 am

    Funnily enough I looked for a method of passing in a list of tables/sprocs that I wanted access to with no luck. The only way I could figure to do this was to create a new DB user with permission to see only the SPROCS tables you required and then use SQLMetal on that ‘secured’ database.

    I should think it will work (as the system sprocs were ignored) but let me know how you get on – as I didn’t try it myself!

  3. tc
    March 31, 2017 at 9:52 pm

    Is there a limit on how many “(StoredProcName)Result(X)” Class’s, “SqlMetal” will generate? It seems to be only generating them for the first 3 result sets. It’s not the problem with additional result sets with the same signature not being generated that has been reported and closed with no plan to fix (see: Microsoft Connect issue ID 759599 at “https://connect.microsoft.com/VisualStudio/feedback/details/759599/sqlmetal-omits-result-sets-from-stored-procedures-when-column-signatures-are-the-same”) as my missing result sets are of different signatures from any that were generated.

    • March 31, 2017 at 11:10 pm

      Hi Tc, That rings a vague bell (although this was a long time ago now – and I’ve since played with Dapper which I find to be much more flexible and, without the need to tune, much faster than EF – although the code is not generated and SqlMetal won’t really help!). As I understand it: as long as you have not changed FMTONLY and there’s no conditionally returned result sets you should be fine – if there are more than three (and this is definitely an honest bug/drawback to Sqlmetal – I no longer have it installed so can’t double check right now for you) I guess you might need to run SqlMetal twice over two halves of the a copy of the SPROC: this doesn’t really make it a very useful code generation tool in this instance – but it might get a once off hand-generated version of the VB/C# source together… Sorry I can’t suggest anything more! Good luck…

    • March 31, 2017 at 11:14 pm

      Tc – one other thing: thanks for writing to me – I’d forgotten all about this blog… Am relieved to have found it! Must get back into keeping it up to date! What have I been doing all this time!?!?!?!

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: