Geek Answers Handbook

How to use LINQ to SQL with stored procedures returning multiple result sets without ORM?

http://blogs.msdn.com/b/dditweb/archive/2008/05/06/linq-to-sql-and-multiple-result-sets-in-stored-procedures.aspx

Like this link, however, the project I'm working on does not use the ORM LINQ to SQL component (we use it more to quickly create the ADO.Net interface for db).

Currently, we are following as follows:

var result = myDataContext.GetAllCustomersAndOrders();

And the stored procedure is as follows:

enter image description here

Are there any additional steps I need to take? Do I need to expand the generated dbml file or data file as data?

Hope this makes sense ... This is a bit hard to explain, and all the examples I found use the ORM part for dbml (dragging tables onto the surface of the dbml designer).

+4
source share
1 answer

Yes, you can do this, but I will need to create a partial class for your dbml.

So, if your dbml file is MyLinqToSQL.dbml

  • open dbml constructor
  • drag SP to the design surface.
  • save
  • and then press F7.

This will create a partial class MyLinqToSQL.cs.

Now...

  • open the automatically generated file MyLinqToSQL.designer.cs
  • copy the access method with the same name as SP (the one from which you want to return multiple sets from.)
  • Insert this into the body of the partial class in the MyLinqToSQL.cs that you just created.
  • change the method name to something at least slightly different
  • change the return type from ISingleResult to IMultipleResult.
  • decorate your new class with System.Data.Linq.Mapping.ResultType attributes to determine the types of the returned set.

All results will be returned in one IQueryable collection.

You can then use reflection to determine the type of each item in the collection, and then use them for use.

See http://kishor-naik-dotnet.blogspot.com/2011/12/linq-multiple-result-set-of-procedure.html

+5
source

More articles:


All Articles