GridView Paging and Sorting in ASP.NET using SqlDataSource

Introduction:

In this article I will explain how to enable paging and sorting in a GridView control using SqlDataSource in design view

Step 1:

Create a new ASP.NET Web Application and drag a GridView control in Default.aspx design view and select New Data Source from the Chose Data Source dropdown in the GridView Tasks pane

This will open Data Source Configuration Wizard

Step 2:

Select Data Source Type as Database in Data Source Configuration Wizard and click OK


Step 3:

This will open Configure Data Source wizard. Click on New Connection to create a new Data Connection. Enter your Server name. Select SQL Server as authentication type and enter user name and password or select Windows authentication. Select database name and click OK.


Step 4:

Click Next and select Table to bind with the GridView.


Click Next and then Finish

This will bind selected table with the GridView. Here all the rows of the table is displayed in the GridView in a single page.

Step 5:

To enable paging and sorting check Enable Paging and Enable Sorting in the GridView GridView Tasks pane

 
Step 6:

Select Auto Format from the GridView Tasks  pane and select a scheme to give a style to the GridView.

Final output:

GridView can be sorted by clicking on the header. Paging is enabled to view 10 rows at a time. It can be changed using property of the GridView

Conclusion:

In this method of enabling paging and sorting in a GridView, SqlDataSource is used in background. Following source code generated for the above output

<asp:GridView ID="GridView1" runat="server" AllowPaging="True"
     AllowSorting="True" AutoGenerateColumns="False" CellPadding="4"
     DataKeyNames="EmployeeID" DataSourceID="SqlDataSource1" ForeColor="#333333"
     GridLines="None">
     <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
     <Columns>
          <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID"
               InsertVisible="False" ReadOnly="True" SortExpression="EmployeeID" />
          <asp:BoundField DataField="FirstName" HeaderText="FirstName"
               SortExpression="FirstName" />
          <asp:BoundField DataField="DepartmentID" HeaderText="DepartmentID"
               SortExpression="DepartmentID" />
          <asp:BoundField DataField="Salary" HeaderText="Salary"
               SortExpression="Salary" />
          <asp:BoundField DataField="LastName" HeaderText="LastName"
               SortExpression="LastName" />
          <asp:BoundField DataField="HireDate" HeaderText="HireDate"
               SortExpression="HireDate" />
     </Columns>
     <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
     <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
     <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
     <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
     <EditRowStyle BackColor="#999999" />
     <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
     ConnectionString="<%$ ConnectionStrings:PagingConnectionString2 %>"
     SelectCommand="SELECT [EmployeeID], [FirstName], [DepartmentID], [Salary], [LastName], [HireDate] FROM [Employees]">
</asp:SqlDataSource>

Leave a Reply

Your email address will not be published. Required fields are marked *