CODEDIGEST
Home » Articles
Search
 

Technologies
 

Sponsored links
 

CodeDigest Navigation
 

Technology News
No News Feeds available at this time.
 

Community News
No News Feeds available at this time.
 
Custom Paging in GridView Using LINQ

By Satheesh Babu
Posted On Jun 11,2009
Article Rating:
Average Rating: 5
No of Ratings: 1
No of Comments: 13
Category: ASP.Net
Print this article.

Custom Paging in GridView Using LINQ

 

What is LINQ?

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.

 

What is LINQ to SQL classes?

LINQ to SQL is a new technique where we can manage the relational database object as a managed .net object. Read Introduction to LINQ to SQL – A Beginners Guide if you are new to Linq to SQL.

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).

The final code will look like,

 

<asp:GridView ID="gvEmployee" DataSourceID="ObjectDataSource1" runat="server" AllowPaging="True"

           PageSize="3">

</asp:GridView>

<asp:ObjectDataSource ID="ObjectDataSource1" EnablePaging="true"

        runat="server" SelectCountMethod="GetEmployeeCount"

        SelectMethod="BindEmployees" TypeName="EmployeeDAO" >       

</asp:ObjectDataSource>

 

Note

The property StartRowIndexParameterName and MaximumRowsParameterName is not required if you use the parameter name as startRowIndex and maximumRows in SelectMethod.

 

Execute the page and you can see the custom paging in GridView control in action. Refer the below figure,

F:\Articles\Dot Net 3.5\LINQ to SQL\GV.png

 

Download the source attached and see it in action.

 

Downloads

Download Source 

Conclusion

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.

Happy Coding!!

Similar Articles
You can contribute to CodeDiget.Com:
Donate to CodeDigest.com
Article Feedback
Comments
good tutorial
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
nice
thanks for sharing this. http://www.kenntrix.info
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
quetions on BindEmployees method
if i have more than two parameters for some where conditions
How should i configure the ObjectDataSource control
Thank you:)
LINQ Quality
Very use full code and provide complete and very fast result.
thanks...
LINQ to SQL designer.
I am using Visual web developer edition, which I don't think have got 'LINQ to SQL designer'?



Column Sorting
In your example how would you handle gridview column sorting?
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..
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.
autocomplete?oauth_consumer_key=YourConsumerKey&term=Casper
autocomplete?oauth_consumer_key=YourConsumerKey&term=Casper
Column Sorting
How do you accomplish Gridview column sorting?
AllowSorting="true" in Gridview produces a run time error!
good
very use full
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..)