Most of the times we need to use GridView control to show tabular data in our asp.net application. We simply write procedure to search the records and bind them with GridView and show 10 to 20 records per page. Have you ever thought that why you are fetching all the records and show only 10 to 20 records, why cannot fetch only those records which we have to show on the page, means 20 records if page size is 20 or 50 if page size is 50.
Suppose we have millions of records in our table and we are fetching all records and showing only 20 records, just imagine the page speed, here we are going to discuss how to get only page size records which will work faster, never matter how many records we have in our table.
Here is the page we are going to create with paginated data, searching, sorting on columns and paging functionality, we will use GridView control and ObjectDataSource with Ajax update panel.
Let’s start with stored procedures; we will need two procedures one for page records and one for number of records count.
If we don't want sorting functionality on columns then our procedure will be as follows (We will see complete procedure with sorting functionality latter)
CREATE PROC [dbo].[Customer_Search]
@City VARCHAR(50)
, @PinCode VARCHAR(6)
, @StartRowIndex INT
, @MaximumRows INT
, @SortExpression VARCHAR(100)
AS
WITH Cust AS
(
SELECT ROW_NUMBER() OVER (ORDER BY FirstName) AS [RowNo]
, CustomerID
FROM Customers
WHERE City Like '%' + @City + '%'
AND Zip Like '%' + @PinCode + '%'
)
SELECT Cust.[CustomerID]
, [FirstName]
, [MidleName]
, [LastName]
, [Address1]
, [Address2]
, [City]
, [State]
, [Country]
, [Zip]
FROM Customers
INNER JOIN Cust ON Cust.CustomerID = Customers.CustomerID
Where Cust.RowNo Between @startRowIndex + 1 AND @startRowIndex +@MaximumRows
ORDER BY Cust.RowNo
See we used WITH Cust AS to get row number and customer Id which will always be fast and further we used it as a table to join and only get page data with the help of row number see the second part of procedure. Copy the same procedure and create a count one to get the total number of records, we are not going to change the parameters here even we are not using then in count procedure see this:
CREATE PROC [dbo].[Customer_SearchCount]
@City VARCHAR(50)
, @PinCode VARCHAR(6)
, @StartRowIndex INT
, @MaximumRows INT
, @SortExpression VARCHAR(100)
AS
SELECT COUNT(CustomerID)
FROM Customers
WHERE City Like '%' + @City + '%'
AND Zip Like '%' + @PinCode + '%'
We completed our procedure part, now let's create methods to get data from database, I wrote two methods in my CustomerRepository class, here they are:
// Method to get page data
public static DataTable Customer_Search(String City, String Zip
, Int32 StartRowIndex, Int32 MaximumRows, string SortExpression)
{
using (SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["appConnection"].ToString()))
{
SqlCommand CMD = new SqlCommand("Customer_Search", conn);
CMD.CommandType = CommandType.StoredProcedure;
CMD.Parameters.AddWithValue("@City", City ?? "");
CMD.Parameters.AddWithValue("@PinCode", Zip ?? "");
CMD.Parameters.AddWithValue("@StartRowIndex", StartRowIndex);
CMD.Parameters.AddWithValue("@MaximumRows", MaximumRows);
CMD.Parameters.AddWithValue("@SortExpression", SortExpression);
if (conn.State == ConnectionState.Closed) conn.Open();
SqlDataReader dr = CMD.ExecuteReader(CommandBehavior.CloseConnection);
DataTable dtab = new DataTable("CR Report");
dtab.Load(dr);
return dtab;
}
}
// Method to get count of records
public static Int32 Customer_SearchCount(String City, String Zip
, Int32 StartRowIndex, Int32 MaximumRows, string SortExpression)
{
using (SqlConnection conn = new
SqlConnection(ConfigurationManager.ConnectionStrings["appConnection"].ToString()))
{
SqlCommand CMD = new SqlCommand("Customer_SearchCount", conn);
CMD.CommandType = CommandType.StoredProcedure;
CMD.Parameters.AddWithValue("@City", City ?? "");
CMD.Parameters.AddWithValue("@PinCode", Zip ?? "");
CMD.Parameters.AddWithValue("@StartRowIndex", StartRowIndex);
CMD.Parameters.AddWithValue("@MaximumRows", MaximumRows);
CMD.Parameters.AddWithValue("@SortExpression", SortExpression);
if (conn.State == ConnectionState.Closed) conn.Open();
var recordCount = CMD.ExecuteScalar();
return Convert.ToInt32(recordCount);
}
}
Our coding part is done, time to manage page and GridView control's HTML
<table border="0" cellpadding="0" cellspacing="0" width="100%">
<tr>
<td><b>City</b></td>
<td><asp:TextBox ID="txtCity" runat="server" /></td>
<td><b>Zip/Pin Code</b></td>
<td><asp:TextBox ID="txtZip" runat="server" /></td>
<td><asp:Button ID="btnSearch" runat="server" Text="Search"
onclick="btnSearch_Click" /></td>
</tr>
</table>
<br />
<asp:GridView ID="gvCustomer" runat="server" AutoGenerateColumns="false"
Width="100%"
PageSize="10"
AllowPaging="true"
AllowSorting="true"
OnSorting="gvCustomer_Sorting">
<Columns>
<asp:BoundField DataField="CustomerID" HeaderText="Customer ID"
SortExpression="CustomerID" />
<asp:BoundField DataField="FirstName" HeaderText="First Name"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="Last Name"
SortExpression="LastName" />
<asp:TemplateField HeaderText="Address">
<ItemTemplate>
<%# String.Format("{0}, {1}", Eval("Address1"), Eval("Address2") ) %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="City" HeaderText="City" SortExpression="City" />
<asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
<asp:BoundField DataField="Country" HeaderText="Country"
SortExpression="Country" />
<asp:BoundField DataField="Zip" HeaderText="Zip" SortExpression="Zip" />
</Columns>
</asp:GridView>
Now come to code behind and adjust different events:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ViewState["SortDirection"] = "ASC";
ViewState["SortExpression"] = "FirstName";
BindGridView(0);
}
}
Method to bind the asp.net GridView, we used here ObjectDataSource from code behind which you can use it from HTML as well
private void BindGridView(Int32 currentPage)
{
ObjectDataSource odsCustomer = new ObjectDataSource();
odsCustomer.TypeName = "WebApplication1.CustomerRepository";
odsCustomer.SelectMethod = "Customer_Search";
odsCustomer.SelectCountMethod = "Customer_SearchCount";
odsCustomer.EnablePaging = true;
odsCustomer.SelectParameters.Add("City", DbType.String, txtCity.Text.Trim());
odsCustomer.SelectParameters.Add("Zip", DbType.String, txtZip.Text.Trim());
odsCustomer.SelectParameters.Add("StartRowIndex", DbType.Int32,
gvCustomer.PageIndex.ToString());
odsCustomer.SelectParameters.Add("MaximumRows", DbType.Int32,
gvCustomer.PageSize.ToString());
odsCustomer.SelectParameters.Add("SortExpression",
DbType.String, String.Format("{0} {1}",
ViewState["SortExpression"], ViewState["SortDirection"]));
gvCustomer.DataSource = odsCustomer;
gvCustomer.DataBind();
}
Now add the button click event:
protected void btnSearch_Click(object sender, EventArgs e)
{
BindGridView(0);
}
Time to adjust sorting functionality of grid view, we already used sort expression in our gridview HTML
protected void gvCustomer_Sorting(object sender, GridViewSortEventArgs e)
{
if(ViewState["SortDirection"] == null)
ViewState["SortDirection"] = "ASC";
else
ViewState["SortDirection"] =
Convert.ToString(ViewState["SortDirection"]) == "ASC"? "DESC": "ASC";
ViewState["SortExpression"] = e.SortExpression;
BindGridView(gvCustomer.PageIndex);
}
In our procedure we removed the sorting functionality in beginning so let's complete our procedure now to sort records by columns, we will make change only in WITH Cust AS part, see this:
CREATE PROC [dbo].[Customer_Search]
@City VARCHAR(50)
, @PinCode VARCHAR(6)
, @StartRowIndex INT
, @MaximumRows INT
, @SortExpression VARCHAR(100)
AS
WITH Cust AS
(
SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN @SortExpression = 'CustomerID ASC' THEN CustomerID END ASC,
CASE WHEN @SortExpression = 'CustomerID DESC' THEN CustomerID END DESC,
CASE WHEN @SortExpression = 'FirstName ASC' THEN FirstName END ASC,
CASE WHEN @SortExpression = 'FirstName DESC' THEN FirstName END DESC,
CASE WHEN @SortExpression = 'MidleName ASC' THEN MidleName END ASC,
CASE WHEN @SortExpression = 'MidleName DESC' THEN MidleName END DESC,
CASE WHEN @SortExpression = 'LastName ASC' THEN FirstName END ASC,
CASE WHEN @SortExpression = 'LastName DESC' THEN FirstName END DESC,
CASE WHEN @SortExpression = 'Address1 ASC' THEN FirstName END ASC,
CASE WHEN @SortExpression = 'Address1 DESC' THEN FirstName END DESC,
CASE WHEN @SortExpression = 'City ASC' THEN City END ASC,
CASE WHEN @SortExpression = 'City DESC' THEN City END DESC,
CASE WHEN @SortExpression = 'State ASC' THEN State END ASC,
CASE WHEN @SortExpression = 'State DESC' THEN State END DESC,
CASE WHEN @SortExpression = 'Country ASC' THEN Country END ASC,
CASE WHEN @SortExpression = 'Country DESC' THEN Country END DESC,
CASE WHEN @SortExpression = 'Zip ASC' THEN Zip END ASC,
CASE WHEN @SortExpression = 'Zip DESC' THEN Zip END DESC
) AS [RowNo]
, CustomerID
FROM Customers
WHERE City Like '%' + @City + '%'
AND Zip Like '%' + @PinCode + '%'
)
SELECT Cust.[CustomerID]
, [FirstName]
, [MidleName]
, [LastName]
, [Address1]
, [Address2]
, [City]
, [State]
, [Country]
, [Zip]
FROM Customers
INNER JOIN Cust ON Cust.CustomerID = Customers.CustomerID
Where Cust.RowNo Between @startRowIndex + 1 AND @startRowIndex +@MaximumRows
ORDER BY Cust.RowNo
It's complete now, I know it became long but I try to provide every thing which we need for paginated data. It is really helpful for table where we have tons of records and want to show only 20 t0 50 or 100 records on a page, if we will use our normal procedure and pagination really those page will take lot's of time to render if internet speed is slow because entire records need to travel from database to client machine.
|
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
| By Ali Adravi On 16 Feb, 13 Viewed: 8,387 |
In many cases we need to use read only text box so user cannot edit the value but readonly textbox will lost its value after postback. Let’s say you have a read only text box to set date by using ajax calendar so after post back date will be lost, other case might be you are setting some value in... By Ali Adravi On 24 Apr 2013 Viewed: 4,219
There are ways to call a web service method JavaScript, for more detail you can see [how to retrieve data from database using JavaScript in asp.net][1], but is there any way to call a normal method from JavaScript? And the answer is No; show how we can call a code behind method from JavaScript,... By Jonathan King On 08 Apr 2013 Viewed: 12,445
A nice feature with ASP.Net is the model binding, it reduced our code and effort to bind our well know controls like GridView, DataList, Repeater etc. So let’s see how we can bind our old controls in new and easy way. Let’s see how we bind the our grid before 4.5 1. We write the code to get... By Mike .Net On 17 Jan 2013 Viewed: 3,200
In asp.net there is not control to select multiple files and upload them once with progress bar, so we will use a small third party DLL to achieve this functionality. We will use Flajaxian FileUploader, you can download it from [http://www.flajaxian.com][1] We will create three different images... By Hamden On 12 Jul 2012 Viewed: 6,546
I was searching for select/deselect all checkboxes code into a gridview. I found most of them only provide to select and deselet all the checkboxes if any of the checkbox is unselected into grid the main checkbox is not affecting. I further try to search some useful code but could not found any... By Ali Adravi On 10 Jul 2012 Viewed: 5,376