.NET Assembly,
Asp.net,
ASP.Net 2.0,
asp.net 4.0,
C#,
Gridview
.Net GridView Paging and sorting data in a GridView that comes from a SqlDataSource is a breeze with ASP.NET 2.0. As in the demos before, start by adding a SqlDataSource that accesses the desired data. Next, add a GridView. The GridView's Smart Tag contains two checkboxes: Enable Paging and Enable Sorting. To turn on sorting and/or paging, simply check the appropriate checkboxes. It's that simple!
The SqlDataSource has a DataSourceMode property that you can set to specify if the SqlDataSource returns a DataReader or DataSet, with DataSet being the default. When creating a pageable GridView you must return a DataSet. The GridView's sorting capabilities can only be utilized with either a DataSet or a DataReader, but when sorting by returning a DataReader you must retrieve your data from a stored procedure. Furthermore, this stored procedure must accept a parameter indicating the sort expression; specify the name of this input parameter through the SqlDataSource's SortParameterName property.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Example Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="ItemDataSource" Runat="server"
SelectCommand="SELECT [ItemName], [ItemUnitPrice],
[ItemUnitsInStock], [ItemQuantityPerUnit] FROM [ItemProducts]"
ConnectionString=
"<%$ ConnectionStrings:MyConnectionString %>">
</asp:SqlDataSource>
<asp:GridView ID=" ExampleGridView" Runat="server"
DataSourceID="ItemDataSource" AutoGenerateColumns="False"
AllowSorting="True" BorderWidth="2px" BackColor="White"
GridLines="None" CellPadding="3"
CellSpacing="1" BorderStyle="Ridge" BorderColor="White"
AllowPaging="True">
<FooterStyle ForeColor="Black"
BackColor="#C6C3C6"></FooterStyle>
<PagerStyle ForeColor="Black" HorizontalAlign="Right"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ItemName" SortExpression="ItemName">
</asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="ItemUnitPrice" SortExpression="ItemUnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="ItemUnitsInStock"
SortExpression="ItemUnitsInStock"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity Per Unit"
DataField="ItemQuantityPerUnit"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White" Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=ExampleGridView.PageIndex + 1%>
of
<%=ExampleGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>
In order to have the data paged appropriately in the ASP.NET page there are a couple of settings on the ObjectDataSource we must set in order to have paging work correctly:
A.EnablePaging—set this to True.
B.MaximumRowsParameterName—this property provides the name of the first integer parameter to the method that pages the results. It defaults to maximumRows. If you use a different parameter name in your methods, you'll need to specify that parameter name in this property.
C.StartRowIndexParameterName—similar to MaximumRowsParameterName, in that this property value specifies the second integer parameter name for paging. It defaults to startRowIndex, so you only need to set this property explicitly if you use a different parameter name in your code.
D.SelectCountMethod—the name of the method that returns the total number of records to be paged through.
And one property we must set in order to have sorting work properly:
1.SortParameterName—the name of the string input parameter specifying how to sort the data.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Grid Example Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="GridDataSource"
Runat="server" TypeName="GridProductDAL"
SortParameterName="SortExpression"
SelectMethod="GridGetProducts" EnablePaging="True"
SelectCountMethod="GridTotalNumberOfProducts">
</asp:ObjectDataSource>
<asp:GridView ID="ItemproductsGridView" AllowPaging="True"
BorderColor="White" BorderStyle="Ridge"
CellSpacing="1" CellPadding="3" GridLines="None"
BackColor="White" BorderWidth="2px"
AutoGenerateColumns="False"
DataSourceID="GridDataSource"
Runat="server" AllowSorting="True">
<FooterStyle ForeColor="Black" BackColor="#C6C3C6"></FooterStyle>
<PagerStyle ForeColor="Black" HorizontalAlign="Right"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ItemProductName"
SortExpression="ItemProductName"></asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="ItemUnitPrice" SortExpression="ItemUnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="ItemUnitsInStock"
SortExpression="ItemUnitsInStock"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity Per Unit"
DataField="ItemQuantityPerUnit"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White"
Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=ItemproductsGridView.PageIndex + 1%>
of
<%=ItemproductsGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>
The SqlDataSource has a DataSourceMode property that you can set to specify if the SqlDataSource returns a DataReader or DataSet, with DataSet being the default. When creating a pageable GridView you must return a DataSet. The GridView's sorting capabilities can only be utilized with either a DataSet or a DataReader, but when sorting by returning a DataReader you must retrieve your data from a stored procedure. Furthermore, this stored procedure must accept a parameter indicating the sort expression; specify the name of this input parameter through the SqlDataSource's SortParameterName property.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Example Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="ItemDataSource" Runat="server"
SelectCommand="SELECT [ItemName], [ItemUnitPrice],
[ItemUnitsInStock], [ItemQuantityPerUnit] FROM [ItemProducts]"
ConnectionString=
"<%$ ConnectionStrings:MyConnectionString %>">
</asp:SqlDataSource>
<asp:GridView ID=" ExampleGridView" Runat="server"
DataSourceID="ItemDataSource" AutoGenerateColumns="False"
AllowSorting="True" BorderWidth="2px" BackColor="White"
GridLines="None" CellPadding="3"
CellSpacing="1" BorderStyle="Ridge" BorderColor="White"
AllowPaging="True">
<FooterStyle ForeColor="Black"
BackColor="#C6C3C6"></FooterStyle>
<PagerStyle ForeColor="Black" HorizontalAlign="Right"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ItemName" SortExpression="ItemName">
</asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="ItemUnitPrice" SortExpression="ItemUnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="ItemUnitsInStock"
SortExpression="ItemUnitsInStock"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity Per Unit"
DataField="ItemQuantityPerUnit"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White" Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=ExampleGridView.PageIndex + 1%>
of
<%=ExampleGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>
In order to have the data paged appropriately in the ASP.NET page there are a couple of settings on the ObjectDataSource we must set in order to have paging work correctly:
A.EnablePaging—set this to True.
B.MaximumRowsParameterName—this property provides the name of the first integer parameter to the method that pages the results. It defaults to maximumRows. If you use a different parameter name in your methods, you'll need to specify that parameter name in this property.
C.StartRowIndexParameterName—similar to MaximumRowsParameterName, in that this property value specifies the second integer parameter name for paging. It defaults to startRowIndex, so you only need to set this property explicitly if you use a different parameter name in your code.
D.SelectCountMethod—the name of the method that returns the total number of records to be paged through.
And one property we must set in order to have sorting work properly:
1.SortParameterName—the name of the string input parameter specifying how to sort the data.
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN"
"http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<script runat="server">
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Grid Example Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="GridDataSource"
Runat="server" TypeName="GridProductDAL"
SortParameterName="SortExpression"
SelectMethod="GridGetProducts" EnablePaging="True"
SelectCountMethod="GridTotalNumberOfProducts">
</asp:ObjectDataSource>
<asp:GridView ID="ItemproductsGridView" AllowPaging="True"
BorderColor="White" BorderStyle="Ridge"
CellSpacing="1" CellPadding="3" GridLines="None"
BackColor="White" BorderWidth="2px"
AutoGenerateColumns="False"
DataSourceID="GridDataSource"
Runat="server" AllowSorting="True">
<FooterStyle ForeColor="Black" BackColor="#C6C3C6"></FooterStyle>
<PagerStyle ForeColor="Black" HorizontalAlign="Right"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C"></HeaderStyle>
<Columns>
<asp:BoundField HeaderText="Product"
DataField="ItemProductName"
SortExpression="ItemProductName"></asp:BoundField>
<asp:BoundField HeaderText="Unit Price"
DataField="ItemUnitPrice" SortExpression="ItemUnitPrice"
DataFormatString="{0:c}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Units In Stock"
DataField="ItemUnitsInStock"
SortExpression="ItemUnitsInStock"
DataFormatString="{0:d}">
<ItemStyle HorizontalAlign="Right"></ItemStyle>
</asp:BoundField>
<asp:BoundField HeaderText="Quantity Per Unit"
DataField="ItemQuantityPerUnit"></asp:BoundField>
</Columns>
<SelectedRowStyle ForeColor="White"
Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=ItemproductsGridView.PageIndex + 1%>
of
<%=ItemproductsGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>
