|
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();
}
}
|