LINQ is a new feature released with
.Netframework 3.5. LINQ stands for LanguageIntegrated Query. It is a
data querying methodology that provides querying capabilities to .Net languages
which have similar syntax like SQL query. It has a set of querying operators
that can be used to query in memory object collections, Sql database, XML, etc.
The LINQ processing engine will then convert the LINQ query to native query
specific to the database on execution. Since, the querying feature is integrated
with the language; one can build an efficient query with the language of their
choice. To support LINQ data operations, there is a new datasource control added
to the existing datasource control set called LINQDataSource control.

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.

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.

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.

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.

Custom Paging through Stored Procedure in
LINQDataSource Control

ALTER PROCEDURE
dbo.GetEmployees

(

@PageIndex INT,

@PageSize INT

)

AS

BEGIN

SELECT EmpId, EmpName, Age,
DeptID

FROM (

SELECT EmpId, EmpName, Age,
DeptID, ROW_NUMBER () OVER (

ORDER BY
EmpId

) AS RowNumber

FROM Employee

) AS Results

WHERE RowNumber BETWEEN @PageIndex
+ 1 AND @PageIndex + @PageSize

SELECT COUNT(*) FROM Employee

END

Now, drag and drop the stored procedure
to the right pane of your dbml designer from the server explorer.

Again, the method signature should be
changed to return IMultipleResults(Refer the previous section).

As a better practise, you can move the
stored procedure implementation into a separate partial class
DataClassesDataContext. This is because, whenever we do any change on
dbml the LINQ to SQL classes are recreated, which means the changes we done will
always be reverted back which will make us to repeat the code change again and
again for the SP’s(For example, changing return type to IMultipleResults).
Download the source attached in this article to understand it better.

The introduction of LINQ gave us one
another method for data accessing that simplified the database operation
using.net language syntax. Also, the new LINQDataSource control further
simplified the data operations and which also bought the flexibility to use SP’s
at times when needed. Download the source attached with this article and see it
in action.

Hey that procedure's variable name should be RowIndex instead of PageIndex. I wasted a lot of time realizing that :)

Commented By mayas
on
8/8/2011 @ 3:10 AM

Superb!

The paging solution should solve the error I was getting:

" the query results cannot be enumerated more than once"

Thanks for sharing this!!!

Commented By Maximus
on
5/31/2011 @ 12:48 PM

mine requires table name

It works for me without setting the tablename property in LINQDatasource control ...

Commented By Joe
on
3/19/2010 @ 5:42 AM

mine requires table name

As you do above, I left out the TableName property from my LinqDataSource but received the error message "The TableName property of LinqDataSource '[source name]' must specify a table property or field on the data context type."