Evjen c08.tex V2 - 01/28/2008 2:05pm Page 396
Chapter 8: Data Management with ADO.NET
XML file. It didn’t even allow you to serialize the
DataTable
object independently of the larger and
encompassing
DataSet
object. This limitation required you to always use the
DataSet
object to perform
any operation on a
DataTable
. T he current version of ADO.NET removes this limitation and enables you
to work directly with the
DataTable
for all your needs. In fact, we recommend that you don’t use the
DataSet
object unless you need to work with multiple
DataTable
objects and need a container object to
manage them. If you end up working with only a single table of information, then it is best to work with
an instance of the
DataTable
object rather than a
DataSet
that contains only a single
DataTable
.
The current version of ADO.NET provides the capability to load a
DataTable
in memory by consuming a

data source using a
DataReader
. In the past, you were sometimes restricted to creating multiple overloads
of the same method just to work with both the
DataReader
and the
DataTable
objects. Now you have
the flexibility to write the data access code one time and reuse the
DataReader
— either directly or to fill
a
DataTable
, as shown in Listing 8-11.
Listing 8-11: How to load a DataTable from a DataReader
VB
<
%@ Page Language="VB" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"

>
Protected Sub Page_Load(ByVal sender As Object, _
ByVal e As System.EventArgs)
If Not Page.IsPostBack Then
Dim MyDataTable As DataTable
Dim MyReader As SqlDataReader
Dim CityParam As SqlParameter
Dim MyConnection As SqlConnection = New SqlConnection()
MyConnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
Dim MyCommand As SqlCommand = New SqlCommand()
MyCommand.CommandText = _
"SELECT * FROM CUSTOMERS WHERE CITY = @CITY"
MyCommand.CommandType = CommandType.Text
MyCommand.Connection = MyConnection
CityParam = New SqlParameter()
CityParam.ParameterName = "@CITY"
CityParam.SqlDbType = SqlDbType.VarChar
CityParam.Size = 15
CityParam.Direction = ParameterDirection.Input
CityParam.Value = "London"
MyCommand.Parameters.Add(CityParam)
MyCommand.Connection.Open()
Continued
396
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 397
Chapter 8: Data Management with ADO.NET
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
MyDataTable = New DataTable()
’ Loading DataTable using a DataReader

MyDataTable.Load(MyReader)
gvCustomers.DataSource = MyDataTable
gvCustomers.DataBind()
MyDataTable.Dispose()
MyCommand.Dispose()
MyConnection.Dispose()
End If
End Sub
<
/script
>
C#
<
%@ Page Language="C#" %
>
<
%@ Import Namespace="System.Data" %
>
<
%@ Import Namespace="System.Data.SqlClient" %
>
<
%@ Import Namespace="System.Configuration" %
>
<
script runat="server"
>
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack )

{
DataTable MyDataTable;
SqlDataReader MyReader;
SqlParameter CityParam;
SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText =
"SELECT * FROM CUSTOMERS WHERE CITY = @CITY";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
CityParam = new SqlParameter();
CityParam.ParameterName = "@CITY";
CityParam.SqlDbType = SqlDbType.VarChar;
CityParam.Size = 15;
CityParam.Direction = ParameterDirection.Input;
CityParam.Value = "London";
MyCommand.Parameters.Add(CityParam);
Continued
397
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 398
Chapter 8: Data Management with ADO.NET
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
MyDataTable = new DataTable();
// Loading DataTable using a DataReader
MyDataTable.Load(MyReader);
gvCustomers.DataSource = MyDataTable;
gvCustomers.DataBind();

MyDataTable.Dispose();
MyCommand.Dispose();
MyConnection.Dispose();
}
}
<
/script
>
Not only can you load a
DataTable
object from a
DataReader
object, you can also retrieve a
DataTableReader
from an existing
DataTable
object. This is accomplished by calling the
Create-
DataReader
method of the
DataTable
class. This method returns an instance of the
DataTableReader
object that can be passed to any method that expects to receive a
DataReader
.
Deciding When to Use a DataSet
As revolutionary as a
DataSet
might be, it is not the best choice in every situation. Often, it may not be

appropriate to use a
DataSet
; instead it might be better to use a
DataReader
.
With ADO 2.6, it was possible to perform a command upon a data store and get back a single collection
of data made up of any number of rows. You could then iterate through this collection of data and use
it in some fashion. Now ADO.NET can use the
DataSet
to return a collection of data that actually keeps
its structure when removed from the data store. In some situations, you benefit greatly from keeping this
copy in its original format. By doing so, you can keep the data disconnected in an in-memory cache in its
separate tables and work with the tables individually or apply relationships between the tables. You can
work with the tables in much the same manner as you do with other relational data sources — using a
parent/child relationship. If it is to your advantage to work with certain data with all its relationships in
place (in order to enforce a parent/child relationship upon the data); in this case, of course, it is better to
use a
DataSet
as opposed to a
DataReader
.
Because the
DataSet
is a disconnected copy of the data, you can work with the same records repeatedly
without having to go back to the data store. This capability can greatly increase performance and lessen
the load upon the server. Having a copy of the data separate from the data store also enables you to
continuously handle and shape the data locally. For instance, you might need to repeatedly filter or sort
through a collection of data. In this case, it would be of great advantage to work with a
DataSet
rather

than going back and forth to the data store itself.
Probably one of the greatest uses of the
DataSet
is to work w i th multiple data stores and come away with
a single collection of data. So for instance, if you have your Customers table within SQL and the orders
information for those particular customers within an Oracle database, you can very easily query each
data store and create a single
DataSet
with a Customers and an Orders table in place that you can use in
any fashion you choose. The DataSet is just a means of storage for data and doesn’t concern itself with
398
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 399
Chapter 8: Data Management with ADO.NET
where the data came from. So, if you are working with data that is coming from multiple data stores, it
is to your benefit to use the
DataSet
.
Because the
DataSet
is based upon XML and XML Schemas, it is quite easy to move the
DataSet
around — whether you are transporting it across tiers, processes or between disparate systems or
applications. If the application or system to which you are transferring the
DataSet
doesn’t understand
DataSet
s, the
DataSet
represents itself as an XML file. So basically, any system or application that can
interpret and understand XML can w ork with the

DataSet
. This makes it a very popular transport
vehicle, and you see an example of it when you transport the
DataSet
from an XML Web service.
Last but not least, the
DataSet
enables you to program data with ease. It is much simpler than anything
that has been provided before the .NET Framework came to the scene. Putting the data within a class
object allows you to programmatically access the
DataSet
. The code example in Listing 8-12 shows you
just how easy it can be.
Listing 8-12: An example of working with the D ataSet object
VB
Dim conn As SqlConnection = New SqlConnection _
(ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString)
conn.Open()
Dim da As SqlDataAdapter = New SqlDataAdapter("Select * from Customers", conn)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "CustomersTable")
C#
SqlConnection conn = new SqlConnection
(ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter("Select * from Customers", conn);
DataSet ds = new DataSet();
da.Fill(ds, "CustomersTable");
Basically, when you work with data, you have to weigh when to use the
DataSet

. In some cases, you
get extreme benefits from using this piece of technology that is provided with ADO.NET. Sometimes,
however, you may find it is not in your best interests to use the
DataSet
. Instead, it is best to use the
DataReader
.
The
DataSet
can be used whenever you choose, but sometimes you would rather use the
DataReader
and work directly against the data store. By using the command objects, such as the
SqlCommand
and the
OleDbCommand
objects, you have a little more direct control over what is executed and what you get back
as a result set. In situations where this is vital, it is to your advantage not to use the
DataSet
.
When you don’t use the
DataSet
, you don’t incur the cost of extra overhead because you are reading
and writing directly to the data source. Performing operations in this manner means you don’t have to
instantiate any additional objects — avoiding unnecessary steps.
This is especially true in a situation when you work with Web Forms in ASP.NET. If you are dealing
with Web Forms, the Web pages are re-created each and every time. When this happens, not only is the
page re-created by the call to the data source, the
DataSet
is also re-created unless you are caching the
DataSet

in some fashion. This can be an expensive process; so, in situations such as this, you might find
399
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 400
Chapter 8: Data Management with ADO.NET
it to your benefit to work directly off the data source using the
DataReader
. In most situations when you
are working with Web Forms, you want to work with the
DataReader
instead of creating a
DataSet
.
The Typed DataSet
As powerful as the
DataSet
is, it still has some limitations. The
DataSet
is created at runtime. It accesses
particular pieces of data by making certain assumptions. Take a look at how you normally access a
specific field in a
DataSet
that is not strongly typed (Listing 8-13).
Listing 8-13: Accessing a field in a DataSet
VB
ds.Tables("Customers").Rows(0).Columns("CompanyName") = "XYZ Company"
C#
ds.Tables["Customers"].Rows[0].Columns["CompanyName"] = "XYZ Company";
The preceding code looks at the Customers table , the first row (remember, everything is zero-based) in
the column
CompanyName

, and assigns the value of
XYZ Company
to the field. This is pretty simple and
straightforward, but it is based upon certain assumptions and is generated at runtime. The
"Customers"
and
"CompanyName"
words are string literals in this line of code. If they are spelled wrong or if these items
aren’t in the table, an error occurs at runtime.
Listing 8-14 shows you how to assign the same value to the same field by using a typed
DataSet
.
Listing 8-14: Accessing a field in a typed DataSet
VB
ds.Customers(0).CompanyName = "XYZ Company"
C#
ds.Customers[0].CompanyName = "XYZ Company";
Now the table name and the field to be accessed are not treated as string literals but, instead, are encased
in an XML Schema and a class that is generated from the
DataSet
class. When you create a typed
DataSet
,
you are creating a class that implements the tables and fields based upon the schema used to generate
the class. Basically, the schema is coded into the class.
As you compare the two examples, you see that a typed
DataSet
is easier to read and understand. It is
less error-prone, and errors are realized at compile time as opposed to runtime.
In the end, typed

DataSet
s are optional, and you are free to use either style as you code.
Using Oracle as Your Database with ASP.NET 3.5
If you work in the enterprise space, in many cases you must work with an Oracle back-end database.
ADO.NET 2.0 has a built-in capability to work with Oracle using the
System.Data.OracleClient
namespace.
First, in order to connect ASP.NET to your Oracle database, you install the Oracle 10 g Client on your
Web server. You can get this piece of software from the Oracle Web site found at
oracle.com
.Ifyou
400
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 401
Chapter 8: Data Management with ADO.NET
are able to connect to your Oracle database from your Web server using SQL*Plus (an Oracle IDE f or
working with an Oracle database), can use the Microsoft-built Oracle data provider,
System.Data
.OracleClient
.
If you are still having trouble connecting to your Oracle database, you also may try to make sure that the
database connection is properly defined in your server’s .ora file found at
C:
\
Oracle
\
product
\
10.1.0
\
Client_1

\
NETWORK
\
ADMIN
. Note t hat the version number might be different.
After you know you can connect to Oracle, you can make use of the Microsoft-built Oracle data provider.
To utilize the built-in capabilities to connect to Oracle, your ASP.NET application must reference this
DLL. To do this, right-click your project in the Visual Studio Solution Explorer and select Add Reference
from the list of options presented. This gives you a long list of available .NET components. Select the
System.Data.OracleClient component
. Notice the two versions of this component (as illustrated in
Figure 8-4). You select the one that is built for the .NET Framework 2.0.
Figure 8-4
After this is added, you find the reference to this component in the
web.config
file of your ASP.NET
application (as presented in Listing 8-15).
401
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 402
Chapter 8: Data Management with ADO.NET
Listing 8-15: The reference to the S ystem.Data.OracleClient DLL in the web.config
<
configuration
>
<
system.web
>
<
compilation debug="true"
>

<
assemblies
>
<
add assembly="System.Data.OracleClient,
Version=2.0.0.0, Culture=neutral,
PublicKeyToken=B77A5C561934E089"/
>
<
/assemblies
>
<
/compilation
>
<
/system.web
>
<
/configuration
>
With this reference in place, you also reference this available DLL in your page along with
System.Data
.
This is demonstrated in Listing 8-16.
Listing 8-16: Referencing the System.Data.OracleClient DLL
VB
Imports System.Data
Imports System.Data.OracleClient
C#
using System.Data;

using System.Data.OracleClient;
With all the references in place, you are able to work with an Oracle backend in pretty much the same
manner as you work with a SQL Server backend. Listing 8-17 shows you just how similar it is.
Listing 8-17: Using the OracleClient object to connect to an Oracle database
VB
Dim conn As OracleConnection
Dim cmd As OracleCommand
Dim cmdString As String = "Select CompanyName from Customers"
conn = New _
OracleConnection("User Id=bevjen;Password=bevjen01;Data Source=myOracleDB")
cmd = New OracleCommand(cmdString, conn)
cmd.CommandType = CommandType.Text
conn.Open()
C#
OracleConnection conn;
OracleCommand cmd;
string cmdString = "Select CompanyName from Customers";
conn = new
OracleConnection("User Id=bevjen;Password=bevjen01;Data Source=myOracleDB");
cmd = new OracleCommand(cmdString, conn);
402
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 403
Chapter 8: Data Management with ADO.NET
cmd.CommandType = CommandType.Text;
conn.Open();
After you are connected and performing the PL-SQL commands you want, you can use the
Oracle-
DataReader
object just as you would use the
SqlDataReader

object.
Notice that, in this section, I have made reference to the Microsoft-built Oracle data provider. Another
option, and many developers consider this the better option, is to use the Oracle-built ODP.NET data
provider instead. This data provider can be freely downloaded from the Oracle download page at
oracle.com
. You can then reference this new DLL in your project. It is now simply a matter of i mport-
ing and working with
System.DataAccess.OracleClient
in your applications. The Oracle-built data
provider contains the capability to work with the more advanced feature provided from the Oracle 10 g
database.
The DataList Server Control
The DataList control has been around since the beginning of ASP.NET. It is part of a series of controls
that enable you to display your data (especially repeated types of data) using templates. Templates enable
you to create more sophisticated layouts for your data and perform functionss that controls such as the
GridView server control cannot.
Template-based controls like the DataList control require more work on your part. For instance, you have
to build common tasks for yourself. You cannot rely on other data controls, which you might be used to,
such s as paging.
Looking at the Available Templates
The idea, when using template-based controls such as the DataList control, is that you put together
specific templates to create your desired detailed layout. The DataList control has a number of templates
that you can use to build your display. The available templates are defined here in the following table:
Template Description
AlternatingItemTemplate
Works in conjunction with the
ItemTemplate
to provide a layout for
all the odd rows within the layout. This is commonly used if you want
to have a grid or layout where each row is distinguished in some way

(such as having a different background color).
EditItemTemplate
Allows for a row or item to be defined on how it looks and behaves
when editing.
FooterTemplate
Allows the last item in the template to be defined. If this is not
defined, then no footer will be used.
HeaderTemplate
Allows the first item in the template to be defined. If this is not
defined, then no header will be used.
ItemTemplate
The core template that is used to define a row or layout for each item
in the display.
403
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 404
Chapter 8: Data Management with ADO.NET
Template Description
SelectedItemTemplate
Allows for a row or item to be defined on how it looks and behaves
when selected.
SeparatorTemplate
The layout of any separator that is used between the items in
the display.
Working with ItemTemplate
Although you have seven templates available to you for use with the DataList control, at a minimum,
you are going to need the ItemTemplate. The following example, shown here in Listing 8-18, shows the
company names from the Northwind database.
Listing 8-18: Showing the company names from the Northwind database using
DataList
<

%@ Page Language="VB" AutoEventWireup="false" CodeFile="DataListControl.aspx.vb"
Inherits="DataListControl" %
>
<
html xmlns=" />>
<
head runat="server"
>
<
title
>
DataList Control
<
/title
>
<
/head
>
<
body
>
<
form id="form1" runat="server"
>
<
div
>
<
asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"
>

<
ItemTemplate
>
Company Name:
<
asp:Label ID="CompanyNameLabel" runat="server"
Text=’
<
%# Eval("CompanyName") %
>
’/
>
<
br /
>
<
br /
>
<
/ItemTemplate
>
<
/asp:DataList
>
<
asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="
<
%$ ConnectionStrings:DSN_Northwind %
>

"
SelectCommand="SELECT [CompanyName] FROM [Customers]"
>
<
/asp:SqlDataSource
>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
As stated, the DataList control requires, at a minimum, an
ItemTemplate
element where you define the
page layout for each item that is encountered from the data source. In this case, all the data is pulled
from the Northwind database sample using the SqlDataSource control. The SqlDataSource control pulls
only the CompanyName column from the Customers table. From there, the ItemTemplate section of the
DataList control defines two items within it. The first item is a static item, ‘‘Company Name:’’ followed by
a single ASP.NET server control, the Label server control. Second, the item is then followed by a couple
of standard HTML elements. The
Text
property of the Label control uses inline data binding (as shown
404

Evjen c08.tex V2 - 01/28/2008 2:05pm Page 405
Chapter 8: Data Management with ADO.NET
in the previous chapter of this book) to bind the values that are coming out of the SqlDataSource control.
If there were more than one data point coming out of the SqlDataSource control, you can still specifically
grab the data point that you are interested in using by specifying the item in the
Eval
statement.
<
asp:Label ID="CompanyNameLabel" runat="server"
Text=’
<
%# Eval("CompanyName") %
>
’/
>
Using the code from Listing 8-18 gives you the following results as illustrated in Figure 8-5.
Figure 8-5
If you then look at the source of the page, you can see that the DataList control uses tables by default to
lay out the elements.
<
table id="DataList1" cellspacing="0" border="0" style="border-collapse:collapse;"
>
<
tr
>
<
td
>
CompanyName:
405