LINQ and multiple datasets from Visual Studio for Web 2010

Introduction

Returning multiple sets of data from a database using LINQ in ASP.NET.

I have used LINQ in the past, but I had a requirement to return multiple 'datasets' from a single stored procedure call.

Background

When we use Visual Studio to create a .dbml file we can browse the databases available to us. We can then drag a table from the database onto the working surface and Visual Studio will create all the classes as types as needed. Similarly if we drag a stored procedure onto the working surface, Visual Studio will examine the procedure and create a class from the result set. This class will then be used as the 'type' of object that calling the stored procedure will return.

A problem with this approach is that when you drag a stored procedure onto the surface visual studio will create a class that matches a single return type. In the past, the developer would create a dataset from the data returned from the stored procedure and map it into separate data-tables in the dataset. The developer can rename these tables, if required, and can access the data in them separately by calling datasetname.firstdatatable.columnname and datasetname.seconddatatable.columnname.

The way to achieve this using Visual Studio is not straightforward. You will only get a single dataset because, by default, Visual Studio creates a class that creates a function that has a return type ISingleResult, e.g.,

public ISingleResult<s_GetFDProductsToReviewResult>
s_GetFDProductsToReview(
   [global::System.Data.Linq.Mapping.ParameterAttribute(Name="Enabled",
DbType="Bit")] System.Nullable<bool> enabled,
[global::System.Data.Linq.Mapping.ParameterAttribute(Name="StockCode",
DbType="VarChar(30)")] string StockCode,
[global::System.Data.Linq.Mapping.ParameterAttribute(Name="AgeRange",
DbType="VarChar(2)")] string ageRange,
[global::System.Data.Linq.Mapping.ParameterAttribute(Name="Gender",
DbType="Char(1)")] System.Nullable<char> gender,
[global::System.Data.Linq.Mapping.ParameterAttribute(Name="Reviewed",
DbType="Bit")] System.Nullable<bool> reviewed)
{
IExecuteResult result = this.ExecuteMethodCall(this,
 ((MethodInfo)(MethodInfo.GetCurrentMethod())), enabled, StockCode, ageRange, gender, reviewed);
return ((ISingleResult<s_GetFDProductsToReviewResult>)(result.ReturnValue));
}

OK, I'll admit this looks complicated but it isn't.

The function is public, its return type is 'ISingleResult' of type s_GetFDProductsToReviewResult.

Remember s_GetFDProductsToReviewResult is the class that Visual Studio created from the data being returned from the stored procedure (in this case its the name of the actual stored procedure as well!)

This function takes a number of parameters.

The first parameter is:

[global::System.Data.Linq.Mapping.ParameterAttribute(Name="Enabled", DbType="Bit")] System.Nullable<bool> enabled

The second parameter is:

[global::System.Data.Linq.Mapping.ParameterAttribute(Name="StockCode", DbType="VarChar(30)")] string StockCode

The third parameter is:

global::System.Data.Linq.Mapping.ParameterAttribute(Name="AgeRange", DbType="VarChar(2)")] string ageRange

etc.

Inside the function body an internal method is called (ExecuteMethodCall) passing it the parameters described previously.

The function returns data from the executed stored procedure casting it to type:

return ((IsingleResult<s_GetFDProductsToReviewResult>)(result.ReturnValue));

Bingo! we have our data.

Only problem is that this function returns the first dataset it finds.

To capture all datasets returned from the stored procedure we have to create a method that uses IMultipleResults as the return type. We also have to create objects that will map to the datasets that the stored procedure returns. Now my stored procedure returns two datasets, the first being a count of all the records that are in the database table that I am interested in. The second dataset will contain only 20 of the total number of all the records that are in the database table that I am interested in. This is so I can show a label to the user that says 'Showing 20 of 40 records'. As I update the records in my database table I want to be able to say 'Showing 20 of 39 records' and so on until I get less than 20 records remaining.

So how to get hold of both datasets ?

The way to do this is to create 2 classes that map to the datasets returned.

I could do this manually by adding a partial class to my project and laboriously creating all the required properties etc.

The easiest way to do this is to go to the stored procedure and comment out the first select statement that will become the first dataset.

Once that is done I can delete the stored procedure in the dbml file and then save the DBML file, before dragging the stored procedure onto my dbml file work surface.

If you go to the designer.cs file you will see the partial class that Visual Studio created. In my case this was the record count. Copy the method off to somewhere safe as you will need it later. Then delete the stored procedure from the DBML file and save the DBML file.

Next return to the stored procedure and uncomment out the first select statement before commenting out the second select statement.

Repeat the above. Save the stored procedure go to Visual Studio and refresh the database explorer.

Go to the DBML file and drag the stored procedure onto the working surface. Examine the designer.cs file and you will see a different class that maps to the second dataset created by the second select statement in the stored procedure.

Now we need to copy back the code for the first class that we saved off somewhere safe.

The designer.cs file will now look something like:

Some private info here including constructors for the class and some connection strings ... followed by:

//[global::System.Data.Linq.Mapping.FunctionAttribute(Name="Tesco.s_GetFDProductsToReview")]
//public ISingleResult<s_GetFDProductsToReviewResult> s_GetFDProductsToReview(
 //[global::System.Data.Linq.Mapping.ParameterAttribute(Name="Enabled", 
 //        DbType="Bit")] //System.Nullable<bool> enabled,
//[global::System.Data.Linq.Mapping.ParameterAttribute(Name="SysproStockCode",
   //DbType="VarChar(30)")] string sysproStockCode,
 //[global::System.Data.Linq.Mapping.ParameterAttribute(Name="AgeRange",
 //   DbType="VarChar(2)")] //string ageRange, 
//[global::System.Data.Linq.Mapping.ParameterAttribute(Name="Gender", 
//       DbType="Char(1)")] //System.Nullable<char> gender, 
 //[global::System.Data.Linq.Mapping.ParameterAttribute(
 //     Name="Reviewed", DbType="Bit")] //System.Nullable<bool> reviewed)
//{
//    IExecuteResult result = this.ExecuteMethodCall(this, 
//        ((MethodInfo)(MethodInfo.GetCurrentMethod())), //enabled, sysproStockCode, ageRange, gender, reviewed);
//    return ((ISingleResult<s_GetFDProductsToReviewResult>)(result.ReturnValue));
//}

The code above has been commented out in Visual Studio because it is the default function created when I did the first call to the database and I need to replace this.

This is the first class, i.e. it maps to the first dataset which simply contains a record count.

public partial class s_GetFDProductsToReviewResult__RecordsCount
{

private System.Nullable<int> _RecordsCount;

public s_GetFDProductsToReviewResult__RecordsCount()
{
}

[global::System.Data.Linq.Mapping.ColumnAttribute(
  Storage="_RecordsCount", DbType="Int")]
public System.Nullable<int> RecordsCount
{
    get
    {
        return this._RecordsCount;
    }
    set
    {
        if ((this._RecordsCount != value))
        {
            this._RecordsCount = value;
        }
    }
}
}

This is the second class that maps to the actual rows in the database that I want the user to review.

public partial class s_GetFDProductsToReviewResult
    {
        private string _PSE_SysProStockCode;
        private string _Name;
        private string _ImageUrl;
        private System.Nullable<bool> _PSE_Enabled;
        private int _PSE_DisableReason;
        private System.Nullable<System.DateTime> _PSE_EditedDate;
        private string _PSE_EditedBy;
        private System.Nullable<bool> _Reviewed;
        
        public s_GetFDProductsToReviewResult()
        {
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_PSE_SysProStockCode", 
               DbType = "NVarChar(30) NOT NULL", CanBeNull = false)]
        public string PSE_SysProStockCode
        {
            get
            {
                return this._PSE_SysProStockCode;
            }
            set
            {
                if ((this._PSE_SysProStockCode != value))
                {
                    this._PSE_SysProStockCode = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_Name", DbType = "VarChar(100)")]
        public string Name
        {
            get
            {
                return this._Name;
            }
            set
            {
                if ((this._Name != value))
                {
                    this._Name = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_ImageUrl", DbType = "NVarChar(258)")]
        public string ImageUrl
        {
            get
            {
                return this._ImageUrl;
            }
            set
            {
                if ((this._ImageUrl != value))
                {
                    this._ImageUrl = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_PSE_Enabled", DbType = "Bit")]
        public System.Nullable<bool> PSE_Enabled
        {
            get
            {
                return this._PSE_Enabled;
            }
            set
            {
                if ((this._PSE_Enabled != value))
                {
                    this._PSE_Enabled = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_PSE_DisableReason", DbType = "Int NOT NULL")]
        public int PSE_DisableReason
        {
            get
            {
                return this._PSE_DisableReason;
            }
            set
            {
                if ((this._PSE_DisableReason != value))
                {
                    this._PSE_DisableReason = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_PSE_EditedDate", DbType = "DateTime")]
        public System.Nullable<System.DateTime> PSE_EditedDate
        {
            get
            {
                return this._PSE_EditedDate;
            }
            set
            {
                if ((this._PSE_EditedDate != value))
                {
                    this._PSE_EditedDate = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_PSE_EditedBy", DbType = "VarChar(50)")]
        public string PSE_EditedBy
        {
            get
            {
                return this._PSE_EditedBy;
            }
            set
            {
                if ((this._PSE_EditedBy != value))
                {
                    this._PSE_EditedBy = value;
                }
            }
        }

        [global::System.Data.Linq.Mapping.ColumnAttribute(
          Storage = "_Reviewed", DbType = "Bit")]
        public System.Nullable<bool> Reviewed
        {
            get
            {
                return this._Reviewed;
            }
            set
            {
                if ((this._Reviewed != value))
                {
                    this._Reviewed = value;
                }
            }
        }
    }
}

A lot of typing avoided !

What we need to do now is modify the function that actually calls the stored procedure, so that it now returns a type of IMultipleResults instead of type ISingleResult.

If you look back to the code above you can see what Visual studio generates by default when you drag a stored procedure onto the DBML file work area. We need to modify this code so that it will return two datasets that we can get hold of.

To do this we need to add some decorations to the method and modify the code to the following:

//This maps the name of the stored procedure. 
global::System.Data.Linq.Mapping.FunctionAttribute(Name="myDB.s_GetFDProductsToReview")]  
//The code below shows the attributes we need to decorate the method with: 
[ResultType(typeof(s_GetFDProductsToReviewResult__RecordsCount))] 
[ResultType(typeof(s_GetFDProductsToReviewResult))]   

//The code below is the function declaration including all the parameters it needs 
//notice it will return IMultipleResults ...
public IMultipleResults GetMultipleResultSet(
[global::System.Data.Linq.Mapping.ParameterAttribute(Name =
"Enabled", DbType = "Bit")] System.Nullable<bool>
enabled, 
[global::System.Data.Linq.Mapping.ParameterAttribute(Name =
"SysproStockCode", DbType = "VarChar(30)")]
string sysproStockCode,
[global::System.Data.Linq.Mapping.ParameterAttribute(Name =
"AgeRange", DbType = "VarChar(2)")] string ageRange, 
[global::System.Data.Linq.Mapping.ParameterAttribute(Name =
"Gender", DbType = "Char(1)")]System.Nullable<char> gender,

[global::System.Data.Linq.Mapping.ParameterAttribute(Name =
"Reviewed", DbType = "Bit")]System.Nullable<bool> reviewed) 
{
    try{
    IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), enabled, sysproStockCode, ageRange, gender, reviewed);
    return ((IMultipleResults)(result.ReturnValue));
    }
 catch (Exception ex){throw new Exception(ex.Message);
}
}

That’s it, all we need to do now is to call the method found in the designer.cs page in our main application.

Something like:

MyDataContext tdc = new MyDataContext();
protected void BindData(){ 

reviewed = getReviewed();gender = getGender();
enabled = getShow();
agerange = getAgeRange();

var products = tdc.GetMultipleResultSet(enabled,sysstockcode, agerange, gender,reviewed);
            
IList<s_GetFDProductsToReviewResult__RecordsCount>cnt =
products.GetResult<s_GetFDProductsToReviewResult__RecordsCount>().ToList();

List<s_GetFDProductsToReviewResult> records =
products.GetResult<s_GetFDProductsToReviewResult>().ToList();

lbl_RecordCount.Text= "Viewing "+records.Count+" of " + cnt[0].RecordsCount +"records" ;  
GridView1.DataSource = records;
GridView1.DataBind();

This article started out as a quick 'how to' for my own personal use. I hope it saves someone some time and or pain.