Build Your Own ASP.NET 3.5 Website Using C# & VB (91 page)

Advanced Data Access

525

"SELECT EmployeeID, Name, MobilePhone FROM Employees", conn);

adapter.Fill(dataSet, "Employees");

departmentsGrid.DataSource = dataSet;

departmentsGrid.DataMember = "Employees";

departmentsGrid.DataBind();

⋮

This code binds the Employees table of the DataSet to the GridView control by

setting the GridView’s DataMember property. The GridView will now appear as

shown in Figure 12.25.

Figure 12.25. Displaying data from a DataTable in a GridView

It’s easy to imagine how quickly you could fill a page containing many GridViews

using only one DataSet as the source.

As you’ve learned thus far, DataTables are elements that hold data within a DataSet.

Just like tables in a database, DataTables are built from columns and rows. However,

unlike tables in databases, DataTables reside in memory, which gives us the ability

to page, sort, and filter the data in ways that just wouldn’t be possible with an

SqlDataReader.

Implementing Paging

We saw the GridView’s paging functionality in action earlier in this chapter. When

we bound the GridView to the SqlDataProvider, the paging functionality was

automatically implemented. Now that we’re binding the GridView to a DataSet,

there’s a little more work involved in getting paging up and running. However, the

effort will be more than worthwhile if performance is an issue for your application.

Licensed to [email protected]

526

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

The task of implementing paging in a GridView that has been bound to an

SqlDataAdapter involves a two-step process. First, we need to set the AllowPaging

property of the GridView to True, and set its PageSize value to reflect the number

of items we want to see on every page. Open
Departments.aspx
in Visual Web Developer and set AllowPaging to True, and PageSize to 4 on the departmentsGrid control, as shown below:

Dorknozzle\VB\16_Departments.aspx
(excerpt)

AllowPaging="True" PageSize="4"
>

Next, we need to handle the PageIndexChanging event of the GridView control.

This event is fired when the user clicks one of the paging controls; we’ll need to

update the data displayed in the grid accordingly.

Double-click the PageIndexChanging entry in the
Properties
window, as shown in

Figure 12.26
, to have Visual Web Developer generate an empty PageIndexChanging event handler for you.

Figure 12.26. Creating the PageIndexChanging event handler

Finally, fill in the generated event handler as shown below:

Licensed to [email protected]

Advanced Data Access

527

Visual Basic

Dorknozzle\VB\17_Departments.aspx.vb
(excerpt)

Protected Sub departmentsGrid_PageIndexChanging(

➥ ByVal sender As Object,

➥ ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs)

➥ Handles departmentsGrid.PageIndexChanging

Dim newPageIndex As Integer = e.NewPageIndex

departmentsGrid.PageIndex = newPageIndex

BindGrid()

End Sub

C#

Dorknozzle\CS\17_Departments.aspx.cs
(excerpt)

protected void departmentsGrid_PageIndexChanging(object sender,

GridViewPageEventArgs e)

{

int newPageIndex = e.NewPageIndex;

departmentsGrid.PageIndex = newPageIndex;

BindGrid();

}

In this code, we’ve retrieved the index of the requested page from e.NewPageIndex

parameter, and used its value to set the PageIndex property of the GridView. We’ve

then bound the grid to its data source once more.

Execute the project again. When you click a paging link within the grid, the display

should update quickly
, as Figure 12.27 shows
.

Licensed to [email protected]

528

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Figure 12.27. Viewing Departments with paging functionality

Storing Data Sets in View State

Now, we’re able to page through our list of departments, but the code isn’t anywhere

near as efficient as it could be. Every time we display another page of departments

in our GridView, we call the BindData method, which executes the following code

in order to retrieve a list of departments:

Visual Basic

Dorknozzle\VB\15_Departments.aspx.vb
(excerpt)

conn = New SqlConnection(connectionString)

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", conn)

adapter.Fill(dataSet, "Departments")

C#

Dorknozzle\CS\15_Departments.aspx.cs
(excerpt)

conn = new SqlConnection(connectionString);

adapter = new SqlDataAdapter(

"SELECT DepartmentID, Department FROM Departments", conn);

adapter.Fill(dataSet, "Departments");

Licensed to [email protected]

Advanced Data Access

529

Given that this list of departments is unlikely to change while we’re browsing

through its pages, wouldn’t it be better if we had to query the database only once?

Well, given that we now have a complete copy of the data in the Departments table,

we can! Modify the BindGrid method as shown below:

Visual Basic

Dorknozzle\VB\18_Departments.aspx.vb
(excerpt)

Private Sub BindGrid()

Dim conn As SqlConnection

Dim dataSet As New DataSet

Dim adapter As SqlDataAdapter

If ViewState("DepartmentsDataSet") Is Nothing Then

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

conn = New SqlConnection(connectionString)

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", _

conn)

adapter.Fill(dataSet, "Departments")

ViewState("DepartmentsDataSet") = dataSet

Else

dataSet = ViewState("DepartmentsDataSet")

End If

departmentsGrid.DataSource = dataSet

departmentsGrid.DataBind()

End Sub

C#

Dorknozzle\CS\18_Departments.aspx.cs
(excerpt)

private void BindGrid()

{

SqlConnection conn;

DataSet dataSet = new DataSet();

SqlDataAdapter adapter;

if(ViewState["DepartmentsDataSet"] == null)

{

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

adapter = new SqlDataAdapter(

"SELECT DepartmentID, Department FROM Departments",

Licensed to [email protected]

530

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

conn);

adapter.Fill(dataSet, "Departments");

ViewState["DepartmentsDataSet"] = dataSet;

}

else

{

dataSet = (DataSet)ViewState["DepartmentsDataSet"];

}

departmentsGrid.DataSource = dataSet;

departmentsGrid.DataBind();

}

Here, we’re using the ViewState collection to store our DataSet. The ViewState

collection works a lot like the Session collection, except that instead of storing data

for access by the entire application, ViewState stores data for just this one page

while the user is interacting with it. If the users navigate away from this page, the

data in ViewState will be lost—even if they return to the page within the same

session.

In this revised version of BindGrid, we start by checking the ViewState collection

for an item named DepartmentsDataSet. If no such item exists, we create a new

DataSet, fill it with data from the database, as before, and store it in ViewState. If

an item named DepartmentsDataSet does exist in ViewState, we simply save that

item into our local variable, dataSet. Regardless of how the DataSet is loaded, we

bind it to our GridView as we did before.

If you save your work and load the Departments page in your browser, you should

see that the page runs exactly as it did previously, except that now the database is

accessed only once, the first time the page loads.

Implementing Sorting

To implement sorting functionality, we need to understand a few details of the inner

workings of data binding.

Technically, you can’t bind a DataSet to a GridView control, because a DataSet

can contain many tables, whereas the GridView control can only handle one set of

rows and columns. However, by virtue of the fact that your DataSet has, so far, only

Licensed to [email protected]

Advanced Data Access

531

contained a single DataTable, the GridView control has been smart enough to figure

out that what you probably meant was the following:

Visual Basic

departmentsGrid.DataSource = dataSet.Tables("Departments")

departmentsGrid.DataBind()

C#

departmentsGrid.DataSource = dataSet.Tables["Departments"];

departmentsGrid.DataBind();

However, the above code isn’t technically correct in the strictest sense. All of the

GridView’s data binding is actually achieved through DataView objects. Thankfully,

each DataTable has a DefaultView property, which the GridView will automatically

use whenever you bind it to a DataTable. So, the following code listings have the

same functionality as those we saw above: