|
Like any other datasource control, this
control can be declaratively configured for all the database operations like
select, insert, update and delete. LINQDataSource control will inturn works with
LINQ to SQL or LINQ Entity class (O/R mapping) to complete the required
operations. At times, it may be required to use stored procedures for the data
operations for certain conditions with LINQDataSource control. It is possible to
configure the LINQDataSource to use stored procedures instead of the default
LINQ to do the database interactions through the LINQ to SQL classes. Moving
forward, we will use stored procedures with LINQDataSource control in 3
different scenarios,
1. Using a simple stored procedure with LINQDataSource
control.
2. Using stored procedure that returns multiple resultset.
3. Custom paging through stored procedure in LINQDataSource
Control.
To understand the topic, we will create
a SQL express database in App_Data folder with Employee and Dept
table.
Steps
1. Open Visual Studio 2008.
2. Click New > Website > Select ASP.Net Website.
3. I have selected C# as the language.
4. Next, create a new SQL express database with Employee and Dept
table.
5. You can add database by right clicking App_Data folder in the
solution and clicking Add New Item. This will bring a dialog box where you need
to select “Sql Server Database” and click Add. Then, create a table called
Employee and Dept with the necessary columns using the “Server Explorer”. Just
right click the added database and click “Open” to open your database using
Server Explorer on the left pane of your Visual Studio 2008.
As we all know, in order to work with
LINQDataSource control, we need to first design our LINQ to SQL or LINQ Entity
classes.
Designing the LINQ to SQL
classes
I assume you have already created a
SqlExpress database in App_Data folder with Employee and Department
table.
1. Open Server Explorer, Expand the database tables.
2. Drag Employee and Department into LINQ to SQL designer. The LINQ to
SQL Objects will be created automatically. Click Save.
Refer the
below figure.
Using Stored procedures with
LINQDataSource control
In this section, we will create a
simple stored procedure that returns all employees from Employee table and
consume it through LINQDataSource control.
To add a new Stored Procedure in
SqlExpress database, right click the “Stored Procedure” folder in Server
Explorer. Create the SP and save it.
Select the list of employees Using
SP
CREATE PROCEDURE
dbo.GetAllEmployees
AS
SELECT * FROM Employee
RETURN
Consuming the SP
From the Server explorer, drag and drop
the stored procedure to the right pane of your dbml designer.
This will create the below method in
the designer file,
[Function(Name =
"dbo.GetAllEmployees")]
public
ISingleResult<GetAllEmployeesResult> GetAllEmployees()
{
IExecuteResult result =
this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return
((ISingleResult<GetAllEmployeesResult>)(result.ReturnValue));
}
How to call from LINQDataSource
control?
The LINQDataSource control exposes many
events before and after databinding which gives us the flexibility to do some
business logic or other required operations during the databinding. The
OnSelecting event is raised before every database operation which can be used in
our case to call the SP and populate the result.
Hence, we need to call the above method
from OnSelecting event of LINQDataSource control.
Refer the code below,
ASPX
<asp:GridView ID="GridView1"
runat="server" DataSourceID="LinqDataSource1">
</asp:GridView>
<asp:LinqDataSource
ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext"
onselecting="LinqDataSource1_Selecting">
</asp:LinqDataSource>
CodeBehind
protected void
LinqDataSource1_Selecting(object sender, LinqDataSourceSelectEventArgs
e)
{
DataClassesDataContext context =
new DataClassesDataContext();
e.Result =
context.GetAllEmployees();
}
Execute the page and you can see the
employee information populated on the GridView control.
There are chances where the SP may
return multiple result sets. Next section, will help you to handle
that.
Using Stored Procedure that returns
multiple resultset
Consider the below SP that returns 2
table, Employee and Dept.
SP’s returning multiple
Table/Results
ALTER PROCEDURE
dbo.GetAllEmployeeWithDepts
AS
SELECT * FROM Employee
SELECT * FROM
Dept
RETURN
From the Server explorer, drag and drop
the stored procedure to the right pane of your dbml designer.
This will create the below method in
the designer file similar to the previous section (Single resultset),
[Function(Name="dbo.GetAllEmployeeWithDepts")]
public
ISingleResult<GetAllEmployeeWithDeptsResult>
GetAllEmployeeWithDepts()
{
IExecuteResult result =
this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return
((ISingleResult<GetAllEmployeeWithDeptsResult>)(result.ReturnValue));
}
To make the above method to return both
the table, we need to change the return type from
ISingleResult<GetAllEmployeeWithDeptsResult> to IMultipleResults in
System.Data.Linq namespace and ResultType attributes to specify the type of
returned result sets.
The above method can be rewritten
as,
[Function(Name =
"dbo.GetAllEmployeeWithDepts")]
[ResultType(typeof(Employee))]
[ResultType(typeof(Dept))]
public IMultipleResults
GetAllEmployeeWithDepts()
{
IExecuteResult result =
this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())));
return
((IMultipleResults)(result.ReturnValue));
}
To call this stored
procedure,
ASPX
<asp:GridView ID="GridView2"
runat="server" DataSourceID="LinqDataSource2">
</asp:GridView>
<asp:LinqDataSource
ID="LinqDataSource2" runat="server"
onselecting="LinqDataSource2_Selecting">
</asp:LinqDataSource>
<asp:GridView ID="GridView3"
runat="server">
</asp:GridView>
CodeBehind
protected void
LinqDataSource2_Selecting(object sender, LinqDataSourceSelectEventArgs
e)
{
DataClassesDataContext context =
new DataClassesDataContext();
IMultipleResults results =
context.GetAllEmployeeWithDepts();
e.Result =
results.GetResult<Employee>().ToList();
GridView3.DataSource =
results.GetResult<Dept>();
GridView3.DataBind();
}
Execute the page and you can see both
the GridView populate with employee and department information.
With this knowledge, we will move
forward and built a custom paging implementation using stored procedure with the
LINQDataSource control, a practical usage of multiple resultset with
LINQDataSource control.
|