Evjen c08.tex V2 - 01/28/2008 2:05pm Page 386
Chapter 8: Data Management with ADO.NET
Forthislineofcodetowork,youaregoingtohavetomakeareferencetothe
System.Configuration
namespace.
When you complete your connection to the data source, be sure that you close the connection by using
conn.Close()
. The .NET Framework does not implicitly release the connections when they fall out
of scope.
Using the Command Object
The
Command
object uses the
Connection
object to execute SQL queries. These queries can be in the form
of inline text, stored procedures, or direct table access. If the SQL query uses a
SELECT
clause, the result
set it returns is usually stored in either a
DataSet
or a
DataReader
object. The
Command
object provides a
number of Execute methods that can be used to perform various types of SQL queries.
Next, take a look at some of the more useful properties of the
SqlCommand
class, as shown in the
following table.
Property Description

CommandText
This read/write property allows you to set or retrieve either the T-SQL statement
or the name of the stored procedure.
CommandTimeout
This read/write property gets or sets the number of seconds to wait while
attempting to execute a particular command. The command is aborted after it
times out and an exception is thrown. The default time allotted for this operation
is 30 seconds.
CommandType
This read/write property indicates the way t he
CommandText
property should be
interpreted. The possible values are
StoredProcedure
,
TableDirect
,and
Text
.
The value of
Text
means that your SQL statement is inline or contained within
the code itself.
Connection
This read/write property gets or sets the
SqlConnection
object that should be
used by this
Command
object.

Next, take a look at the various Execute methods that can be called f rom a
Command
object.
Property Description
ExecuteNonQuery
This method executes the command specified and returns the number of rows
affected.
ExecuteReader
This method executes the command specified and returns an instance of the
SqlDataReader
class. The DataReader object is a read-only and forward-only
cursor.
ExecuteRow
This method executes the command and returns an instance of the
SqlRecord
class. This object contains only a single returned row.
386
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 387
Chapter 8: Data Management with ADO.NET
Property Description
ExecuteScalar
This method executes the command specified and returns the first column of
the first row in the form of a generic object. The remaining rows and columns
are ignored.
ExecuteXmlReader
This method executes the command specified and returns an instance of the
XmlReader
class. This method enables you to use a command that returns the
results set in the form of an XML document.
Using the DataReader Object

The
DataReader
object is a simple forward-only and read-only cursor. It requires a live connection with
the data source and provides a very efficient way of looping and consuming all or part of the result
set. This object cannot be directly instantiated. Instead, you must call the
ExecuteReader
method of the
Command
object to obtain a valid
DataReader
object.
When using a
DataReader
object,besuretoclosetheconnectionwhenyouaredoneusingthedata
reader. If not, then the connection stays alive. The connection utilized stays alive until it is explicitly
closed using the
Close()
method or until you have enabled your
Command
object to close the connection.
You can close the connection after using the data reader in one of two ways. One way is to provide
the
CommandBehavior.CloseConnection
enumeration while calling the
ExecuteMethod
of the
Command
object. This approach works only if you loop through the data reader until you reach the end of the result
set, at which point the reader object automatically closes the connection for you. However, if you don’t
want to keep reading the data reader until the end of the result set, you can call the

Close()
method of
the
Connection
object yourself.
Listing 8-8 shows the
Connection
,
Command
,and
DataReader
objects in action. It shows how to connect
to the Northwind database (an example database found in the Microsoft’s SQL Server 7.0, 2000, 2005,
or 2008 database servers), read the Customers table within this database, and display the results in a
GridView server control.
Listing 8-8: The SqlConnection, SqlCommand, and SqlDataReader objects in action
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 MyReader As SqlDataReader
Dim MyConnection As SqlConnection = New SqlConnection()
Continued
387
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 388
Chapter 8: Data Management with ADO.NET
MyConnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
Dim MyCommand As SqlCommand = New SqlCommand()
MyCommand.CommandText = "SELECT TOP 3 * FROM CUSTOMERS"
MyCommand.CommandType = CommandType.Text
MyCommand.Connection = MyConnection
MyCommand.Connection.Open()
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
gvCustomers.DataSource = MyReader
gvCustomers.DataBind()
MyCommand.Dispose()
MyConnection.Dispose()
End If
End Sub
<
/script
>
<
html

>
<
body
>
<
form id="form1" runat="server"
>
<
div
>
<
asp:GridView ID="gvCustomers" runat="server"
>
<
/asp:GridView
>
<
/div
>
<
/form
>
<
/body
>
<
/html
>
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)
{
SqlDataReader MyReader;
SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText = "SELECT TOP 3 * FROM CUSTOMERS";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
MyCommand.Connection.Open();
Continued
388
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 389

Chapter 8: Data Management with ADO.NET
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
gvCustomers.DataSource = MyReader;
gvCustomers.DataBind();
MyCommand.Dispose();
MyConnection.Dispose();
}
}
<
/script
>
ThecodeshowninListing8-8usesthe
SqlConnection
class to create a connection with the
Northwind database using the connection string stored in the
web.config
file. This connection string
is then retrieved using the
ConfigurationManager
class. It is always best to store your connection strings
inside the
web.config
and to reference them in this manner. If you have a single place to work with your
connection strings, any task is a lot more manageable than if you place all your connection strings in the
actual code of your application.
After working with the connection string, this bit of code from Listing 8-8 creates a
Command
object using
the
SqlCommand

class because you are interested in working with a SQL database. Next, the code provides
the command text, command type, and connection properties. After the command and the connection are
created, the code opens the connection and executes the command by calling the
ExecuteReader
method
of the
MyCommand
object. After receiving the data reader from the
Command
object, you simply bind the
retrieved results to an instance of the GridView control. The results are shown in Figure 8-1.
Figure 8-1
Using Data Adapter
The
SqlDataAdapter
is a special class whose purpose is to bridge the gap between the disconnected
DataTable
objects and the physical data source. The
SqlDataAdapter
provides a two-way data transfer
mechanism. It is capable of executing a
SELECT
statement on a data source and transferring the result
set into a
DataTable
object. It is also capable of executing the standard
INSERT
,
UPDATE
,and

DELETE
statements and extracting the input data from a
DataTable
object.
389
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 390
Chapter 8: Data Management with ADO.NET
The commonly used properties offered by the
SqlDataAdapter
class are shown in the following table.
Property Description
SelectCommand
This read/write property sets or gets an object of type
SqlCommand
.This
command is automatically executed to fill a
DataTable
with the result set.
InsertCommand
This read/write property sets or gets an object of type
SqlCommand
.This
command is automatically executed to insert a new record to the SQL Server
database.
UpdateCommand
This read/write property sets or gets an object of type
SqlCommand
.This
command is automatically executed to update an existing record on the SQL
Server database.

DeleteCommand
This read/write property sets or gets an object of type
SqlCommand
.This
command is automatically executed to delete an existing record on the SQL
Server database.
The
SqlDataAdapter
class also provides a method called
Fill()
. Calling the
Fill()
method
automatically executes the command provided by the
SelectCommand
property, receives the result
set, and copies it to a
DataTable
object.
The code example in Listing 8-9 illustrates how to use an object of
SqlDataAdapter
class to fill a
DataTable
object.
Listing 8-9: Using an object of SqlDataAdapter to fill a DataTable
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 MyTable As DataTable = New DataTable()
Dim MyConnection As SqlConnection = New SqlConnection()
MyConnection.ConnectionString = _
ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString
Dim MyCommand As SqlCommand = New SqlCommand()
MyCommand.CommandText = "SELECT TOP 5 * FROM CUSTOMERS"
MyCommand.CommandType = CommandType.Text
MyCommand.Connection = MyConnection
Dim MyAdapter As SqlDataAdapter = New SqlDataAdapter()
MyAdapter.SelectCommand = MyCommand
390
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 391
Chapter 8: Data Management with ADO.NET
MyAdapter.Fill(MyTable)
gvCustomers.DataSource = MyTable.DefaultView
gvCustomers.DataBind()

MyAdapter.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 MyTable = new DataTable();
SqlConnection MyConnection = new SqlConnection();

MyConnection.ConnectionString =
ConfigurationManager.
ConnectionStrings["DSN_Northwind"].ConnectionString;
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText = "SELECT TOP 5 * FROM CUSTOMERS";
MyCommand.CommandType = CommandType.Text;
MyCommand.Connection = MyConnection;
SqlDataAdapter MyAdapter = new SqlDataAdapter();
MyAdapter.SelectCommand = MyCommand;
MyAdapter.Fill(MyTable);
gvCustomers.DataSource = MyTable.DefaultView;
gvCustomers.DataBind();
MyAdapter.Dispose();
MyCommand.Dispose();
MyConnection.Dispose(); }
}
<
/script
>
ThecodeshowninListing8-9createsa
Connection
and
Command
object and then proceeds to create an
instance of the
SqlDataAdapter
class. It then sets the
SelectCommand
property of the
DataAdapter

object
to the
Command
object it had previously created. After the
DataAdapter
object is ready for executing, the
code executes the
Fill()
method, passing it an instance of the
DataTable
class. The
Fill()
method
populates the
DataTable
object. Figure 8-2 shows the result of executing this code.
391
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 392
Chapter 8: Data Management with ADO.NET
Figure 8-2
Using Parameters
Most serious database programming, regardless of how simple it might be, requires you to configure
SQL statements using parameters. Using parameters helps guard against possible SQL injection attacks.
Obviously, a discussion on the basics of ADO.NET programming is not complete without covering the
use of parameterized SQL statements.
Creating a parameter is as simple as declaring an instance of the
SqlParameter
class and providing it
the necessary information, such as parameter name, value, type, size, direction, and so on. The following
table shows the properties of the

SqlParameter
class.
Property Description
ParameterName
This read/write property gets or sets the name of the parameter.
SqlDbType
This read/write property gets or sets the SQL Server database type of the
parameter value.
Size
This read/write property sets or gets the size of the parameter value.
Direction
This read/write property sets or gets the direction of the parameter, such as
Input
,
Output
,or
InputOutput
.
SourceColumn
This read/write property maps a column from a
DataTable
to the parameter. It
enables you to execute multiple commands using the
SqlDataAdapter
object and
pick the correct parameter value from a
DataTable
column during the command
execution.
Value

This read/write property sets or gets the value provided to the parameter object.
This value is passed to the parameter defined in the command during runtime.
Listing 8-10 modifies the code shown in Listing 8-5 to use two parameters while retrieving the list of
customers from the database.
392
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 393
Chapter 8: Data Management with ADO.NET
Listing 8-10: The use of a parameterized SQL statement
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 MyReader As SqlDataReader
Dim CityParam As SqlParameter
Dim ContactParam 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 AND CONTACTNAME = @CONTACT"
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 = "Berlin"
ContactParam = New SqlParameter()
ContactParam.ParameterName = "@CONTACT"
ContactParam.SqlDbType = SqlDbType.VarChar
ContactParam.Size = 15
ContactParam.Direction = ParameterDirection.Input
ContactParam.Value = "Maria Anders"
MyCommand.Parameters.Add(CityParam)
MyCommand.Parameters.Add(ContactParam)
MyCommand.Connection.Open()
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection)
gvCustomers.DataSource = MyReader
gvCustomers.DataBind()
MyCommand.Dispose()
MyConnection.Dispose()
End If
Continued

393
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 394
Chapter 8: Data Management with ADO.NET
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)
{
SqlDataReader MyReader;
SqlParameter CityParam;
SqlParameter ContactParam;

SqlConnection MyConnection = new SqlConnection();
MyConnection.ConnectionString =
ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
SqlCommand MyCommand = new SqlCommand();
MyCommand.CommandText =
"SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = @CONTACT";
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 = "Berlin";
ContactParam = new SqlParameter();
ContactParam.ParameterName = "@CONTACT";
ContactParam.SqlDbType = SqlDbType.VarChar;
ContactParam.Size = 15;
ContactParam.Direction = ParameterDirection.Input;
ContactParam.Value = "Maria Anders";
MyCommand.Parameters.Add(CityParam);
MyCommand.Parameters.Add(ContactParam);
MyCommand.Connection.Open();
MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
gvCustomers.DataSource = MyReader;
gvCustomers.DataBind();
Continued
394
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 395
Chapter 8: Data Management with ADO.NET

MyCommand.Dispose();
MyConnection.Dispose();
}
}
<
/script
>
The code shown in Listing 8-8 uses a parameterized SQL statement that receives the name of the city and
the contact person to narrow the result set. These parameters are provided by instantiating a couple of
instances of the
SqlParameter
class and filling in the appropriate name, type, size, direction, and value
properties for each object of
SqlParameter
class. From there, you add the populated parameters to the
Command
object by invoking the
Add()
method of the
Parameters
collection. The result of executing this
code is shown in Figure 8-3.
Figure 8-3
Understanding DataSet and DataTable
Most programmers agree that the
DataSet
class is the most commonly used part of ADO.NET in real-
world, database-driven applications. This class provides mechanisms for managing data when it is
disconnected from the data source. This capability to handle data in a disconnected state was first
introduced in .NET during the 1.0 version of ADO.NET. The current 3.5 version of ADO.NET retains

all the features of its predecessors and provides a few newer, much needed features.
An object created from the
DataSet
class works as a container for other objects that are created from the
DataTable
class. The
DataTable
object represents a logical table in memory. It contains rows, columns,
primary keys, constraints, and relations with other
DataTable
objects. Therefore, you could have a
DataSet
that is made up of two distinct tables such as a Customers and an Orders table. Then you could
use the
DataSet
, just as you would any other relational data source, to make a relation between the two
tables in order to show all the orders for a particular customer.
Most of the disconnected data-driven programming is actually done using one or more
DataTable
objects
within the
DataSet
. However, the previous versions of ADO.NET didn’t allow you to work directly with
the
DataTable
object for some very important tasks, such a s reading and writing data to and from an
395