Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

Data Tables in ADO.NET 2.0

The DataTable object in ADO.NET 2.0 is more powerful than it was in

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

of DataSets when the full power of a DataSet is not required.

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

Figure 12.21. The structure of a DataSet 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 look at 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 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.

498

Chapter 12: Advanced Data Access

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:

File: Departments.aspx (excerpt)

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

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

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

<asp:Content ID="Content1"

ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<h1>Dorknozzle Departments</h1>

<asp:GridView id="departmentsGrid" runat="server">

</asp:GridView>

</asp:Content>

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

Visual Basic File: Departments.aspx.vb (excerpt)

Imports System.Data.SqlClient

Imports System.Data

Imports System.Configuration

Partial Class Departments

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# File: Departments.aspx.cs (excerpt)

using System;

using System.Data;

using System.Configuration;

using System.Collections;

499

Binding DataSets to Controls

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

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.

Visual Basic File: 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()

' Define data objects

Dim conn As SqlConnection

Dim dataSet As New DataSet

Dim adapter As SqlDataAdapter

' Read the connection string from Web.config

Dim connectionString As String = _

ConfigurationManager.ConnectionStrings( _

"Dorknozzle").ConnectionString

' Initialize connection

conn = New SqlConnection(connectionString)

' Create adapter

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", _

conn)

' Fill the DataSet

adapter.Fill(dataSet, "Departments")

500

Chapter 12: Advanced Data Access

' Bind the grid to the DataSet

departmentsGrid.DataSource = dataSet

departmentsGrid.DataBind()

End Sub

C# File: Departments.aspx.cs (excerpt)

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGrid();

}

}

private void BindGrid()

{

// Define data objects

SqlConnection conn;

DataSet dataSet = new DataSet();

SqlDataAdapter adapter;

// Read the connection string from Web.config

string connectionString =

ConfigurationManager.ConnectionStrings[

"Dorknozzle"].ConnectionString;

// Initialize connection

conn = new SqlConnection(connectionString);

// Create adapter

adapter = new SqlDataAdapter(

"SELECT DepartmentID, Department FROM Departments",

conn);

// Fill the DataSet

adapter.Fill(dataSet, "Departments");

// Bind the grid to the DataSet

departmentsGrid.DataSource = dataSet;

departmentsGrid.DataBind();

}

Execute the project, and browse to your departments page, as shown in Fig-

ure 12.22.

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 differ-

ence is that, this time, we’ve used an SqlDataAdapter and a DataSet.

501

Binding DataSets to Controls