LINQ Exchange

LINQ to SQL provides a lot of features to make it easier and faster for us to connect to a database and call stored procedures. Unfortunately, there is still a lack of built-in support for retrieving multiple recordsets. I am going to show you a way to force LINQ to SQL to return multiple recordsets.

Caveat: There is one caveat to this technique – every time you make a change in the LINQ to SQL designer, you will need to perform the actions below, again. This is because whenever the designer is updated/changed, the underlying class is recreated.

For this example, we will be using the following stored procedure, which returns two recordsets:

Example Stored Procedure:

CREATE PROCEDURE sproc_Books_Get_List
AS
    — first recordset – overview of the list
    SELECT count(*) as totalbooks,
    sum(pages) as totalpages,
    sum(price) as totalprice
    from book_list

    — second recordset – the book list
    select id, title, author, pages, price, published_date
    from book_list
GO

First, create a new DBML file (Add > New Item > LINQ to SQL Classes). I will call this file "MyDB.dbml". Once the designer loads, drag and drop the stored procedure onto the right-most column of the designer. 

Every time you drag a new stored procedure onto the designer, Visual Studio creates two things. First, it creates the function you call to execute the stored procedure. Second, it creates a class to hold the results of the stored procedure call (usually named with the stored procedure's name, suffixed with "Results"). If you look at the MyDB.designer.cs file, you will see the function "sproc_Books_Get_List" and the class "sproc_Books_Get_ListResults".

We need to create a new class that will hold the results of both recordsets. Create a new class and call it "clsMultiRecords". Next, reopen MyDB.designer.cs and cut-and-paste the partial class "sproc_Books_Get_ListResults" into our new class:

public class clsMultiRecords

{

    public partial class sproc_Books_Get_ListResults

    {

        ……

     }

 }

Now, create a second partial class (as a child of clsMultiRecords), and call it sproc_Books_Get_ListResults2 (I'm using "2" to identify it as the second recordset). Use the structure of the first partial class to assist you. The second partial class might look like the following:

public partial class sproc_Books_Get_ListResults2
{
    private int _id;
    private string _title;
    private string _author;
    private int _pages;
    private decimal _price;
    private DateTime _published_date;

    public sproc_Books_Get_ListResults2()
    {
    }

    [Column(Storage = "_id", DbType = "Int NOT NULL")]
    public int id
    {
        get
        {
            return this._id;
        }
        set
        {
            if ((this._id != value))
            {
                this._id = value;
            }
        }
    }

   ……

 }

 

Quick Tip: To quickly create the secondrecordset partial class, create a dummy stored procedure and put the single select statement into the stored procedure, drag it to the DBML, open the .designer.cs. Cut/paste the auto-generated class for that select statement and put it in your new data class. When you are done, delete the dummy stored procedure from the DBML and the database

Re-open MyDB.designer.cs. Find the "sproc_Books_Get_List" function, and change it's return type to be your new class (clsMultiRecords). The steps for this are below:

1. Replace the As ISingleResult(Of <whateverclass) with IMultipleResults
2. Change the return type from ISingleResult to IMultipleResults
3. Add the ResultType attributes above the function declaration
4. Copy the entire function to your new class (clsMultiRecords), and comment out the function (for safety)

Before

[Function(Name = "sproc_Books_Get_List")]
public ISingleResult<sproc_Books_Get_ListResult> sproc_Books_Get_List()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((IMultiResults<ISingleResult>)(result.ReturnValue));
}

After

[Function(Name = "sproc_Books_Get_List"),
ResultType(typeof(clsMultiRecords.sproc_Books_Get_ListResults)),
ResultType(typeof(clsMultiRecords.sproc_Books_Get_ListResults2))]
public IMultipleResults sproc_Books_Get_List()
{
    IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())));
    return ((IMultipleResults)(result.ReturnValue));
}

Now that we have everything set up, we just need to call it:

System.Data.Linq.IMultipleResults results = new ALQDataContext().sproc_Books_Get_List();
List<clsMultiRecords.sproc_Books_Get_ListResults> Result1 = results.GetResult<clsMultiRecords.sproc_Books_Get_ListResults>().ToList();
List<clsMultiRecords.sproc_Books_Get_ListResults2> Result2 = results.GetResult<clsMultiRecords.sproc_Books_Get_ListResults2>().ToList();

NOTE: Whenever you make a change to the DBML designer, the whole designer’s cs file gets regenerated. The IMultiple result function will ALSO disappear to be replaced by a single result one (and its data class). That’s where the copied Imultiple function in your own data class comes in handy. Just copy and replace and all will be well again.

 

If you have any questions, comments, or suggestions, please feel to post them in the LINQ Exchange Forum

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.