LINQ stands for Language Integrated
Query. LINQ is a data querying methodology that provides querying capabilities
to .Net languages which have similar syntax like SQL query. LINQ has a set of
querying operators that can be used to query in memory object collection, Sql
database, XML, etc. The LINQ processing engine will convert the LINQ query to
native query specific to the database on execution. Since,
querying feature is integrated with the language; one can build an efficient
query with the language of their choice. To support LINQ development, Visual Studio provides
intellisense support and with language support, we have type safety and
compile-time error checks.

Moving forward, we will build custom
paging for GridView control using LINQ to SQL classes.

With these information’s, we will move
to our subject matter with these introductions.

To understand the custom paging
implementation, we will bind the GridView with employee data. The sample will
contain 2 tables, Employee and Department in APP_Data folder. We will bind the
GridView with the data fetched from the database using LINQ to SQL
classes.

Note

There are lots of talks that LINQ to
SQL is no more and it should not be preferred. The talks are that LINQ to entity
should be preferred instead of LINQ to SQL. But, Microsoft still supports it and
has plans to improve it further. Read more here.

Binding GridView and Providing Custom
Paging

We will bind the GridView control using
ObjectDataSource control and will use the inbuilt pager in GridView control to
provide the paging. The ObjectDataSource control in turn will use LINQ to
interact with database.

Steps

1.Open Visual Studio 2008.

2.Create a new Asp.Net website. Drag a GridView control and ObjectDataSource control into our Default.aspx page.

Designing the LINQ to SQL
classes

I assume you have already created a
SqlExpress database in APP_Data folder with Employee and Department
table.

Open Server Explorer, Expand the
database tables.

Drag Employee and Department into LINQ
to SQL designer. The LINQ to SQL Objects will be created automatically. Refer
the below figure.

Thus, our LINQ to SQL class is ready.
Next, we will configure our ObjectDataSource and GridView control to display the
data with custom paging.

Configuring ObjectDataSource control
and GridView control

In order to make ObjectDataSource
control to work we need to set the following properties.

EnablePaging

This property accepts a Boolean to
enable paging with ObjectDataSource control. Set it to true.

SelectCountMethod

We need to specify the name of the
method that can fetch the total number of records available in the
database.

SelectMethod

This property will accept the name of
the method that fetches actual database record.

TypeName

This property needs to be configured
with the class or type name that has the implementation of SelectCountMethod and
SelectMethod.

StartRowIndexParameterName

This property will accept the start row
index of the record to fetch from database.

MaximumRowsParameterName

This property will accept the maximum
number of rows that can be fetched at one time. This will be equivalent to page
size.

The data access class will use LINQ to
fetch the data from the database. Refer the below code,

public class EmployeeDAO

{

public EmployeeDAO()

{

//

// TODO: Add constructor
logic here

//

}

public IQueryable BindEmployees(int
startRowIndex, int maximumRows)

{

EmployeeInfoDataContext dbEmp =
new EmployeeInfoDataContext();

var query = from emp in
dbEmp.Employees

join dept in
dbEmp.Departments

on emp.DeptID
equals dept.DeptID

select new

{

EmpID =
emp.EmpID,

EmpName =
emp.EmpName,

Age =
emp.Age,

Address =
emp.Address,

DeptName =
dept.DepartmentName

};

return
query.Skip(startRowIndex).Take(maximumRows);

}

public int
GetEmployeeCount()

{

EmployeeInfoDataContext dbEmp =
new EmployeeInfoDataContext();

return (from emp in
dbEmp.Employees

select emp).Count();

}

}

In the above code, the LINQ query that
fetches the employee record uses join operator to get the department name from
the Department table(Refer BindEmployees method). We use Skip and Take operator
to fetch the records that belongs to the current page.

The GetEmployeeCount() method will get
the number of employees available in the table using LINQ query to construct the
page numbers.

Now, set the TypeName of
ObjectDataSource control to EmployeeDAO and configure the other required
properties. Next, set the GridView control DataSourceID property to the
ObjectDataSource ID, enable the AllowPaging to true and set the PageSize
property (i have set it as 3).

Thus, we have implemented an easy way
of providing custom paging using the inbuilt pager in GridView control. The main
disadvantage of this approach is the pager links are not a hyperlink which makes
it not search engine friendly and hence it should be used for public facing
sites. But, it is one of the good approaches if you look to provide for an
asp.net intranet and business applications. I will show how to make search
engine friendly paging for GridView control using LINQ in my next article.

thanks for the tutorial,ive been playing around with linq to sql, simply love it.just that your example takes it in the layers approach where the data layer is not in the same page as presentation, also i was not familiar thats we could do all that via obj data source.

seems when i 'select emp' in my linq instead of 'select new..'db is faster, altho logically 'select new' with fewer fields should be faster... anyways thanks

Commented By farid
on
3/18/2010 @ 8:42 AM

nice

thanks for sharing this. http://www.kenntrix.info

Commented By kenntrix
on
2/19/2010 @ 1:57 AM

RE:quetions on BindEmployees method

Ofcourse, you can do that. Use SelectParameter collection of objectdatasource control.Read this,codedigest.com/Articles/ASPNET/180_Custom_GridView_Paging_with_ObjectDataSource_Control_with_ASPNet_20.aspx

Commented By Satheesh
on
12/18/2009 @ 9:30 AM

quetions on BindEmployees method

if i have more than two parameters for some where conditionsHow should i configure the ObjectDataSource controlThank you:)

Commented By libai come from china
on
12/18/2009 @ 1:38 AM

LINQ Quality

Very use full code and provide complete and very fast result.thanks...

Commented By Brajendra
on
11/13/2009 @ 5:18 AM

LINQ to SQL designer.

I am using Visual web developer edition, which I don't think have got 'LINQ to SQL designer'?

Commented By Gaurav Gaind
on
9/18/2009 @ 5:56 PM

Column Sorting

In your example how would you handle gridview column sorting?

Commented By Adam
on
9/16/2009 @ 6:23 PM

RE:count of records

Hi Stephan,The query returns only the required records from the db. As Joe pointed out, you check this through SQL profiler..

Commented By Satheesh
on
9/10/2009 @ 5:13 AM

count of records

As far as I know, at the end, it produces an SQL query wich will be run against the database. But, you can easily check it with the SQL Profiler.

How do you accomplish Gridview column sorting?AllowSorting="true" in Gridview produces a run time error!

Commented By Nikos
on
9/9/2009 @ 8:01 PM

good

very use full

Commented By seenu
on
9/8/2009 @ 11:49 PM

count of records

return query.Skip(startRowIndex).Take(maximumRows -->does the database return all rows & the class fetches only the required ones, or does LINQ fetches only the required ones? Maybe important if the database is on a differrent machine (lots of traffic on every call..)