Getting on the same page with your DataGrid

A succinct, understandable overview of how to set up a DataGrid so that you can page through the records of a data source.

This Content Component encountered an error

Let other users know how useful this tip is by rating it below. Got a tip or code of your own you'd like to share? Submit it here!


Introduction
The purpose of this article is to give a succinct, understandable overview of how to set up a DataGrid so that you can page through the records of a data source. To demonstrate how this is done, I'm going to take actual code from an application I developed for a large telecommunications company. I use Visual Studio.NET Enterprise Developer, which is reflected in this article. In Visual Studio.NET, drop a DataGrid control from the WebForms section of the toolbox onto the Web page. If you switch to HTML view of the page, you'll see some very simple syntax similar to the following:

<asp:DataGrid id="DataGrid1" style="Z-INDEX: 116; LEFT: 33px; POSITION: absolute; TOP: 104px" runat="server"><//asp:DataGrid>

Returning to the design view, I position my control where I want it and start setting properties using the control properties window. After a little tweaking here and there, I return to the HTML view and find my HMTL code has grown:


<asp:DataGrid
runat="server" 
id="myDataGrid"
Border="0"
Cellpadding="4"
AlternatingItemStyle-BackColor="#EFEFEF"
HeaderStyle-BackColor="Black"
HeaderStyle-ForeColor="White"
HeaderStyle-Font-Bold="True" 
AllowPaging="True"
PagerStyle-Mode="NextPrev"
PagerStyle-NextPageText="Next ->"
PagerStyle-PrevPageText="<- Previous"
PagerStyle-Font-Bold="True"
OnPageIndexChanged="myDataGrid_PageChanger"
style="Z-INDEX: 115; LEFT: 160px;
POSITION: absolute; TOP: 151px" Width="501px"
AutoGenerateColumns="False">

<AlternatingItemStyle BackColor="#EEF5FA">
</AlternatingItemStyle>

<HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#6FB1D9">
</HeaderStyle>

<Columns>
<asp:BoundColumn Visible="False" DataField="ID"
HeaderText="ID"></asp:BoundColumn>
<asp:BoundColumn DataField="DOC NUMBER" HeaderText="DOC
NUMBER"></asp:BoundColumn>
<asp:BoundColumn DataField="TITLE" HeaderText="TITLE"></asp:BoundColumn>
</Columns>

<PagerStyle 
NextPageText="Next ->"
Font-Bold="True"
PrevPageText="<- Prev">
</PagerStyle>

</asp:DataGrid>

Wow! A lot has happened, but most of the code was generated by Visual Studio just by me tinkering with the properties. Let's consider a few of the properties now.

AllowPaging="True"
This property has to be set to True to enable paging. If set to False, paging will not work.

AutoGenerateColumns="False"
In this particular case, I set this property to false because I am using bound columns. This gives me a little more control. For instance, I can set the visibility of the first column (the ID column) to false, and I can change the other column headers to some text that is not necessarily the same as the data source column headings. You don't have to do this.

OnPageIndexChanged="myDataGrid_PageChanger"
This piece of code was not automatically generated by Visual Studio. I had to shift to HTML view and enter it by hand. Don't leave it out. It is critical for getting paging to work. This is telling the program to call the procedure named "myDataGrid_PageChanger" whenever you change pages (click the pager link).

I need to write the "myDataGrid_PageChanger" routine myself. Here is the syntax that I put in the code-behind page.


Sub myDataGrid_PageChanger(ByVal Source As Object, ByVal E As 
DataGridPageChangedEventArgs)

myDataGrid.CurrentPageIndex = E.NewPageIndex
RefreshGrid()

End Sub

Do you see what happened? The event argument that was passed to the procedure was of the type "DataGridPageChangedEventArgs" which just happens to have a parameter named "NewPageIndex". The procedure sets the "current page index" to the "new page index" and calls the "RefreshGrid" procedure to refresh the grid. Just for the sake of completeness, here is the syntax (from my application) for refreshing the grid.

Private Sub RefreshGrid()

Dim strState As String
' Lets assume we want to look at the state of Alabama.
' I took out a lot of irrelevant code here just to keep the example simple.
strState = "AL"

' I have a separate function for getting the database connection string from the
' web.config file. That syntax is not covered here.
Dim strConnect As String = GetConnectString()

Dim strSQL As String
strSQL = "Select Docket_ID as [ID], Docket_Number as [DOC NUMBER],"
strSQL = strSQL & " Docket_Title as [TITLE] from dbv_Docket WHERE"
strSQL = strSQL & " State_Code = '" & strState & "'"
strSQL = strSQL & " Order by Docket_Number"

' Note. You can't use paging if the data source does not implement
' the ICollection interface. The DataReader does not implement the
' interface, so we can't use it.
Dim Conn As New SqlConnection(strConnect)
Dim AdapterDockets As SqlDataAdapter
Dim DataSetDockets As DataSet

Conn.Open()
AdapterDockets = New SqlDataAdapter(strSQL, Conn)
DataSetDockets = New DataSet()
AdapterDockets.Fill(DataSetDockets)
myDataGrid.DataSource = DataSetDockets
myDataGrid.DataBind()

End Sub

Conclusion
I certainly didn't exhaust all the possibilities, but I think this provides a reasonable overview. I am not, after all, expecting the Pulitzer prize for literature. I'll mention one thing of which you should be aware. Let's say your data source contains 100,000 records. You refresh the grid the first time and 100,000 records are returned and the grid moves to page one. You click on the pager link and what happens? Yep! 100,000 records are returned (again) and the grid moves to page two. You click on the pager link again and 100,000 records are returned and your grid moves to page three. You get the idea. If you are working with very large data sources, the default paging idea might not be what you want to do. I'll discuss some alternatives in a future article.

If you have any comments, corrections or other ideas, you can e-mail me at RogerMcCook@hotmail.com.

Roger D. McCook
McCook Software, Inc.

This was first published in November 2002

Dig deeper on .NET Framework database programming best practices

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close