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

be aware of the source of that data.

A DataSet will always contain at least one DataTable, but it can contain many.

These DataTables contain DataColumns and DataRows. If we needed to establish a

Licensed to [email protected]

Advanced Data Access

519

relationship between multiple DataTables within a DataSet, we’d use

DataRelations. Finally, we’d create DataViews to query the DataSet.

Data Tables in ADO.NET 2.0 and Later Versions

The DataTable object in ADO.NET 2.0 and later versions is more powerful than

it was in previous incarnations of the technology. It can now be used independently

of DataSets in cases where the full power of a DataSet is not required.

A DataSet
mirrors the structure of a relational database, as Figure 12.23 shows.

Figure 12.23. The structure of a DataSet, which closely resembles that of a database

You can see the parallel between the DataSet’s structure and that of a database. A

database contains tables; here, the DataSet contains DataTables. Tables in a database

have columns and rows; our DataTables have DataColumns and DataRows. When

we work in a database, we establish relationships between tables; here, we’d create

DataRelations. The major difference between DataSets and databases is that

DataSets are memory-resident, while a centralized database resides inside a database

management system.

Let’s see how we can create a DataSet within code.

Binding DataSets to Controls

Now that you have some understanding of the structure of a typical DataSet, let’s

look at the process involved in creating a DataSet in code, and binding a DataTable

Licensed to [email protected]

520

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

to a control. To illustrate this example, we’ll create a simple page that displays the

Dorknozzle departments; we’ll call this page
Departments.aspx
.

Create a new web form called
Departments.aspx
, as you have for the other pages in

the Dorknozzle project. Update the generated code like this:

Dorknozzle\VB\14_Departments.aspx
(excerpt)

<%@ Page Language="VB" MasterPageFile="~/Dorknozzle.master"

AutoEventWireup="false" CodeFile="Departments.aspx.vb"

Inherits="Departments" title="
Dorknozzle Departments
" %>

ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

Dorknozzle Departments

So far, everything looks familiar. We have a blank page based on
Dorknozzle.master
,

with an empty GridView control called departmentsGrid. Our goal through the rest

of this chapter is to learn how to use the DataSet and related objects to give life to

the GridView control.

Switch to
Design
view, and double-click on an empty part of the form to generate

the Page_Load event handler. Add references to the System.Data.SqlClient

namespace (which contains the SqlDataAdapter class), and, if you’re using VB, the

System.Data namespace (which contains classes such as DataSet, DataTable, and

so on) and the System.Configuration namespace (which contains the

ConfigurationManager class, used for reading connection strings from
Web.config
).

Here’s how the code should look:

Visual Basic

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

Imports System.Data.SqlClient

Imports System.Data

Imports System.Configuration

Partial Class Departments

Licensed to [email protected]

Advanced Data Access

521

Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles Me.Load

⋮

End Sub

⋮

End Class

C#

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

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class Departments : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

⋮

}

⋮

}

Next, we’ll add a method called BindGrid, which populates the GridView control

using an SqlDataAdapter and a DataSet. We’ll call BindGrid from Page_Load only

when the page is loaded for the first time. We assume that any postback events

won’t affect the data that’s to be displayed by the grid, so we populate the grid just

once, when the page loads:

Licensed to [email protected]

522

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

Visual Basic

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

Protected Sub Page_Load(ByVal sender As Object,

➥ ByVal e As System.EventArgs) Handles Me.Load

If Not Page.IsPostBack Then

BindGrid()

End If

End Sub

Private Sub BindGrid()

Dim conn As SqlConnection

Dim dataSet As New DataSet

Dim adapter As SqlDataAdapter

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")

departmentsGrid.DataSource = dataSet

departmentsGrid.DataBind()

End Sub

C#

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

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

}

private void BindGrid()

{

SqlConnection conn;

DataSet dataSet = new DataSet();

SqlDataAdapter adapter;

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

conn = new SqlConnection(connectionString);

adapter = new SqlDataAdapter(

Licensed to [email protected]

Advanced Data Access

523

"SELECT DepartmentID, Department FROM Departments",

conn);

adapter.Fill(dataSet, "Departments");

departmentsGrid.DataSource = dataSet;

departmentsGrid.DataBind();

}

Execute the project, and browse to your Departments page. You should see a display

that’s similar to
Figure 12.24.

The grid is already styled because we have a GridView skin in place. At this point,

we’ve achieved a level of functionality that you might otherwise have reached using

SqlCommand and SqlDataReader, or the SqlDataSource; the difference is that, this

time, we’ve used an SqlDataAdapter and a DataSet.

An SqlDataAdapter object is created in much the same way as an SqlCommand object.

We simply provide it with an SQL statement and an SqlConnection object. However,

it’s the line that immediately follows the creation of the adapter that does all the

work. The Fill method of the SqlDataAdapter fills our DataSet with the data returned by the SQL query. The Fill method accepts two parameters: the first is the DataSet object that needs to be filled, the second is the name of the table that we

want to create within the DataSet.

Once the DataSet has been filled with data, it’s simply a matter of binding the

DataSet to the GridView, which we do using the same approach we’d use to bind

an SqlDataReader.

Moving on, let’s see how we can add another DataTable to our DataSet. The following code uses the SelectCommand property of the SqlDataAdapter object to create a new command on the fly, and fill the same DataSet with a new DataTable called

Employees:

Licensed to [email protected]

524

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

Figure 12.24. The Departments page

Visual Basic

⋮

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", conn)

adapter.Fill(dataSet, "Departments")

adapter.SelectCommand = New SqlCommand(_

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

adapter.Fill(dataSet, "Employees")

departmentsGrid.DataSource = dataSet

departmentsGrid.DataMember = "Employees"

departmentsGrid.DataBind()

⋮

C#

⋮

adapter = new SqlDataAdapter(

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

adapter.Fill(dataSet, "Departments");

adapter.SelectCommand = new SqlCommand(

Licensed to [email protected]