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:
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:
global::System.Data.Linq.Mapping.FunctionAttribute(Name="myDB.s_GetFDProductsToReview")]
[ResultType(typeof(s_GetFDProductsToReviewResult__RecordsCount))]
[ResultType(typeof(s_GetFDProductsToReviewResult))]
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.
