Chapter 12: Advanced Data Access

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

Binding DataSets to Controls

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

Chapter 12: Advanced Data Access

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

Binding DataSets to Controls

' 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 Figure 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 difference is that, this time, we’ve used an SqlDataAdapter and a DataSet.

501

Chapter 12: Advanced Data Access

Figure 12.22. The Departments page

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:

502

Binding DataSets to Controls

Visual Basic

' Create adapter

adapter = New SqlDataAdapter( _

"SELECT DepartmentID, Department FROM Departments", conn)

'Fill the DataSet adapter.Fill(dataSet, "Departments")

'Initialize the adapter with a new command adapter.SelectCommand = New SqlCommand(_

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

'Add the new table to the DataSet

adapter.Fill(dataSet, "Employees")

' Bind the grid to the DataSet departmentsGrid.DataSource = dataSet departmentsGrid.DataMember = "Employees" departmentsGrid.DataBind()

C#

// Create adapter

adapter = new SqlDataAdapter(

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

//Fill the DataSet adapter.Fill(dataSet, "Departments");

//Initialize the adapter with a new command adapter.SelectCommand = new SqlCommand(

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

//Add the new table to the DataSet

adapter.Fill(dataSet, "Employees");

// Bind the grid to the DataSet 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 would now appear as shown in Figure 12.23.

Figure 12.23. Displaying data from a DataTable in a GridView

503