I
am using Northwind Customers database table for query the data. As the first
step we need to create a stored procedure in SQL. The stored procedure should
contain some techniques to work with page size and start index. Following is
the stored procedure used:
Create PROCEDURE spGetAllCustomersForGrid
(
@startIndex int,
@pageSize int,
@sortBy
nvarchar(30)
)
AS
SET NOCOUNT ON
DECLARE
@sqlStatement nvarchar(max),
@upperBound int
IF
@startIndex < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET
@upperBound = @startIndex + @pageSize
SET
@sqlStatement = '
SELECT C.CustomerID,
C.CompanyName,
C.ContactName,
C.ContactTitle,
C.Address,
C.City,
C.Region,
C.PostalCode,
C.Country,
C.Phone,
C.Fax
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY '
+ @sortBy + ') AS rowNumber, *
FROM Customers
)
AS C
WHERE rowNumber >= '
+ CONVERT(varchar(9), @startIndex) + ' AND
rowNumber < '
+ CONVERT(varchar(9), @upperBound)
exec (@sqlStatement)
This
stored procedure uses start index, page size and sorting expression as input
parameters. Start index is the starting item of the selected page. Page size is
the no.of records to displayed in the grid. Sorting expression sort the data
set async.
We
have another stored procedure to get the total no.of records. The following is
sp will return the total no.of records:
CREATE PROCEDURE TotalCustomers
AS
BEGIN
SET NOCOUNT ON
SELECT COUNT(*) FROM Customers
END
Now
open Visual Studio and create a new ASP.NET application. Add a WebDataGrid to
the aspx page. Following is the aspx page source:
<div>
<asp:ScriptManager runat="server"></asp:ScriptManager>
<ig:WebDataGrid ID="WebDataGrid1" runat="server" DataKeyFields="CustomerID" Width="100%"
Height="467px" DefaultColumnWidth="90px" OnCustomDataBinding="WebDataGrid1_CustomDataBinding">
<Behaviors>
<ig:Paging PageSize="5" />
</Behaviors>
</ig:WebDataGrid>
</div>
We
are creating a entity class to map the customer object with all the properties.
public class CustomerEntity
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string ContactTitle { get; set; }
public string Address { get; set; }
public string City { get; set; }
public string Region { get; set; }
public string PostalCode { get; set; }
public string Country { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
}
Open
the code behind of the aspx page and create a method to query the records from
database. Use the following method to query the database:
public List<CustomerEntity> GetCustomersInRange(int startIndex, int count)
{
CustomerEntity customer;
List<CustomerEntity> customerList = new List<CustomerEntity>();
using (SqlConnection conn = new SqlConnection("your connection
string"))
{
SqlDataReader rdr = null;
SqlCommand cmdSelect = new SqlCommand();
conn.Open();
cmdSelect.CommandText = "spGetAllCustomersForGrid";
cmdSelect.CommandType = CommandType.StoredProcedure;
cmdSelect.Connection = conn;
int rangeCount = count;
int repositoryCount = DataCount();
if (startIndex + count > repositoryCount)
rangeCount =
repositoryCount - startIndex;
cmdSelect.Parameters.AddWithValue("@startIndex", startIndex);
cmdSelect.Parameters.AddWithValue("@PageSize", rangeCount);
cmdSelect.Parameters.AddWithValue("@sortBy", "CustomerId");
rdr =
cmdSelect.ExecuteReader();
while (rdr.Read())
{
customer = new CustomerEntity();
customer.CustomerID = rdr["CustomerID"].ToString();
customer.CompanyName = rdr["CompanyName"].ToString();
customer.ContactName = rdr["ContactName"].ToString();
customer.ContactTitle =
rdr["ContactTitle"].ToString();
customer.Address = rdr["Address"].ToString();
customer.City = rdr["City"].ToString();
customer.Region = rdr["Region"].ToString();
customer.PostalCode = rdr["PostalCode"].ToString();
customer.Country = rdr["Country"].ToString();
customer.Phone = rdr["Phone"].ToString();
customer.Fax = rdr["Fax"].ToString();
customerList.Add(customer);
}
return customerList;
}
Then
create a method to get the total count of records.
public int DataCount()
{
//Add
code to access TotalCustomer SP
}
Now
you are about to complete the last few steps. Define the data source at the
page load event and at the custom data binding event call for our method
developed for query.
protected void Page_Load(object sender, EventArgs e)
{
WebDataGrid1.DataSource = new List<CustomerEntity>();
}
protected void WebDataGrid1_CustomDataBinding(object sender, DataBindingEventArgs e)
{
WebDataGrid grid = sender as WebDataGrid;
grid.ScrollTop = 0;
int currentPage = grid.Behaviors.Paging.PageIndex;
int pageSize = grid.Behaviors.Paging.PageSize;
int startIndex = currentPage * pageSize;
e.Cancel = true;
e.DataSource =
GetCustomersInRange(startIndex, pageSize);
e.SelectArguments.TotalRowCount =
DataCount();
}
Run
the application and browse your aspx page, you will get the result.
You
may test how the query works. You can use SQL Server Profiler to check the
query execution. Since the page size is 5, the db returns only 5 records at a
page click. It will not return all the records as the normal procedure.
