Evjen c07.tex V2 - 01/28/2008 2:01pm Page 375
Chapter 7: Data Binding in ASP.NET 3.5
Summary
In this chapter, you examined data binding in ASP.NET. The introduction of data source controls such as
the LinqDataSource control, SqlDataSource control, or the XmlDataSource control makes querying and
displaying data from any number of data sources an almost trivial task. Using the data source controls’
own wizards, you learned how easy it is to generate powerful data access functionality with almost no
code required.
You examined how even a beginning developer can easily combine the data source controls with the
GridView, ListView, and DetailsView controls to create powerful data manipulation applications with a
minimal amount of coding.
You saw how ASP.NET includes a multitude of controls that can be data-bound, specifically examining
how, since ASP.NET 1.0/1.1, many controls have been enhanced, and examining the features of the data
bound controls that are included in the ASP.NET toolbox, such as the GridView, TreeView, ListView,
and Menu controls, the new FormView control.
Finally, you looked at how the inline data-binding syntax has been improved and strengthened with the
addition of the XML-specific data-binding expressions.
375
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 377
Data Management
with ADO.NET
This chapter provides information on programming with the data management features that are
part of ADO.NET, a key component of the .NET Framework and of your ASP.NET development.
The discussion begins with the basics of ADO.NET and later dives into the ways you can use various
features that make up ADO.NET to manage data contained in a relational database.
ADO.NET, first introduced in version 1.0 of the .NET Framework, provided an e xtensive array of
features to handle live data in a connected mode or data that is disconnected from its underlying
data store. ADO.NET 1.0 was primarily developed to address two specific problems in getting
at data. The first had to do with the user’s need to access data once and to iterate through a
collection of data in a single instance. This need often arose in Web application development.
ADO.NET addresses a couple of the most common data-access strategies that are used for applica-

tions today. When classic ADO was developed, many applications could be connected to the data
store almost indefinitely. Today, with the explosion of the Internet as the means of data communi-
cation, a new data technology is required to make data accessible and updateable in a disconnected
architecture.
The first of these common data-access scenarios is one in which a user must locate a collection of
data and iterate through this data a single time. This is a popular scenario for Web pages. When a
request for data from a Web page that you have created is received, you can simply fill a table with
data from a data store. In this case, you go to the data store, grab the data that you want, send
the data across the wire, and then populate the table. In this scenario, the goal is to get the data in
place as fast as possible.
The second way to work with data in this disconnected architecture is to grab a collection of data
and use this data separately from the data store itself. This could be on the server or even on the
client. Although the data is disconnected, you want the ability to keep the data (with all of its tables
and relations in place) on the client side. Classic ADO data was represented by a single table that
you could iterate through. ADO.NET, however, can be a reflection of the data store itself, with tables,
columns, rows, and relations all in place. When you are done with the client-side copy of the data,
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 378
Chapter 8: Data Management with ADO.NET
you can persist the changes that you made in the local copy of data directly back into the data store.
The technology that gives you this capability is the DataSet, which will be covered shortly.
Although classic ADO was geared for a two-tiered environment (client-server), ADO.NET addresses a
multi-tiered environment. ADO.NET is easy to work with because it has a unified programming model.
This unified programming model makes working with data on the server the same as working with data
on the client. Because the models are the same, you find yourself more productive when working with
ADO.NET.
Basic ADO.NET Features
This chapter begins with a quick look at the basics of ADO.NET and then provides an overview of basic
ADO.NET capabilities, namespaces, and classes. It also reviews how to work with the
Connection
,

Command
,
DataAdapter
,
DataSet
,and
DataReader
objects.
Common ADO.NET Tasks
Before jumping into the depths of ADO.NET, step back and make sure that you understand some of the
common tasks you might perform programmatically within ADO.NET. This next section looks
at the process of selecting, inserting, updating, and deleting data.
The following example makes use of the
Northwind.mdf
SQL Server Express Database file. To get this
database, please search for ‘‘Northwind and pubs Sample Databases for SQL Server 2000’’. You can find
this link at
www.microsoft.com/downloads/details.aspx?familyid=06616212-0356-46a0-
8da2-eebc53a68034&displaylang=en
. Once installed, you will find the Northwind.mdf file in the
C:\ SQL Server 2000 Sample Databases directory. To add this database to your ASP.NET application,
create an
App_Data
folder within your project (if it isn’t already there) and right-click on the folder and
select Add Existing Item. From the provided dialog box, you are then able to browse to the location of the
Northwind.mdf
file that you just installed. If you are having trouble getting permissions to work with
the database, make a data connection to the file from the Visual Studio Server Explorer by right-clicking
on the Data Connections node and selecting Add N ew Connection from the provided menu. You will be
asked to be made the appropriate user of the database. ThenVS will make the appropriate changes on your

behalf for this to occur.
Selecting Data
After the connection to the data source is open and ready to use, you probably want to read the data from
the data source. If you do not want to manipulate the data, but simply to read it o r transfer it from one
spot to another, you use the
DataReader
class.
In the following example (Listing 8-1), you use the
GetCompanyNameData()
function to provide a list of
company names from the SQL Northwind database.
Listing 8-1: Reading the data from a SQL database using the DataReader class
VB
Imports Microsoft.VisualBasic
Imports System.Collections.Generic
Continued
378
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 379
Chapter 8: Data Management with ADO.NET
Imports System.Data
Imports System.Data.SqlClient
Public Class SelectingData
Public Function GetCompanyNameData() As List(Of String)
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim cmdString As String = "Select CompanyName from Customers"
conn = New SqlConnection("Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|

\
NORTHWND.MDF;Integrated Security=True;
User Instance=True") ’ Put this string on one line in your code
cmd = New SqlCommand(cmdString, conn)
conn.Open()
Dim myReader As SqlDataReader
Dim returnData As List(Of String) = New List(Of String)
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
While myReader.Read()
returnData.Add(myReader("CompanyName").ToString())
End While
Return returnData
End Function
End Class
C#
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
public class SelectingData
{
public List
<
string
>
GetCompanyNameData()
{
SqlConnection conn;
SqlCommand cmd;
string cmdString = "Select CompanyName from Customers";
conn = new

SqlConnection(@"Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True"); // Put this string on one line in your code
cmd = new SqlCommand(cmdString, conn);
conn.Open();
SqlDataReader myReader;
List
<
string
>
returnData = new List
<
string
>
();
myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (myReader.Read())
{
returnData.Add(myReader["CompanyName"].ToString());
Continued
379
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 380
Chapter 8: Data Management with ADO.NET
}
return returnData;
}

}
In this example, you create an instance of both the
SqlConnection
and the
SqlCommand
classes. Then,
before you open the connection, you simply pass the
SqlCommand
class a SQL command selecting specific
data from the Northwind database. After your connection is opened (based upon the commands passed
in), you create a
DataReader
. To read the data from the database, you iterate through the data with
the
DataReader
by using the
myReader.Read()
method. After the
List(Of String)
object is built, the
connection is closed, and the object is returned from the function.
Inserting Data
When working with data, you often insert the data into the data source. Listing 8-2 shows you how to do
this. This data may have been passed to you by the end user through the XML Web Service, or it may be
data that you generated within the logic of your class.
Listing 8-2: Inserting data into SQL Server
VB
Public Sub InsertData()
Dim conn As SqlConnection
Dim cmd As SqlCommand

Dim cmdString As String = "Insert Customers (CustomerID, _
CompanyName, ContactName) Values (’BILLE’, ’XYZ Company’, ’Bill Evjen’)"
conn = New SqlConnection("Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True") ’ Put this string on one line in your code
cmd = New SqlCommand(cmdString, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Sub
C#
public void InsertData()
{
SqlConnection conn;
SqlCommand cmd;
string cmdString = "Insert Customers (CustomerID, CompanyName,
ContactName) Values (’BILLE’, ’XYZ Company’, ’Bill Evjen’)";
conn = new
SqlConnection(@"Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True"); // Put this string on one line in your code
cmd = new SqlCommand(cmdString, conn);

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
380
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 381
Chapter 8: Data Management with ADO.NET
Inserting data into SQL is pretty straightforward and simple. Using the SQL command string, you
insert specific values for specific columns. The actual insertion is initiated using the
cmd.Execute-
NonQuery()
command. This executes a command on the data when you don’t want anything
in return.
Updating Data
In addition to inserting new records into a database, you frequently update existing rows of data
in a table. Imagine a table in which you can update multiple records at once. In the example in
Listing 8-3, you want to update an employee table by putting a particular value in the
emp_bonus
column
if the employee has been at the company for five years or longer.
Listing 8-3: Updating data in SQL Server
VB
Public Function UpdateEmployeeBonus() As Integer
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim RecordsAffected as Integer
Dim cmdString As String = "UPDATE Employees SET emp_bonus=1000 WHERE " & _
"yrs_duty
>
=5"

conn = New SqlConnection("Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True") ’ Put this string on one line in your code
cmd = New SqlCommand(cmdString, conn)
conn.Open()
RecordsAffected = cmd.ExecuteNonQuery()
conn.Close()
Return RecordsAffected
End Function
C#
public int UpdateEmployeeBonus()
{
SqlConnection conn;
SqlCommand cmd;
int RecordsAffected;
string cmdString = "UPDATE Employees SET emp_bonus=1000 WHERE yrs_duty
>
=5";
conn = new
SqlConnection(@"Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True"); // Put this string on one line in your code

cmd = new SqlCommand(cmdString, conn);
conn.Open();
RecordsAffected = cmd.ExecuteNonQuery();
conn.Close();
return RecordsAffected;
}
381
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 382
Chapter 8: Data Management with ADO.NET
This update function iterates through all the employees in the table and changes the value of the
emp_bonus field to
1000
if an employee has been with the company for more than five years. This is
done with the SQL command string. The great thing about these update capabilities is that you can
capture the number of records that were updated by assigning the
ExecuteNonQuery()
command to the
RecordsAffected
variable. The total number of affected records is then returned by the function.
Deleting Data
Along with reading, inserting, a nd updating data, you sometimes need to delete data from the data
source. Deleting data is a simple process of using the SQL command string and then the
ExecuteNon-
Query()
command as you did in the update example. See Listing 8-4 for an illustration of this.
Listing 8-4: Deleting data from SQL Server
VB
Public Function DeleteEmployee() As Integer
Dim conn As SqlConnection
Dim cmd As SqlCommand

Dim RecordsAffected as Integer
Dim cmdString As String = "DELETE Employees WHERE LastName=’Evjen’"
conn = New SqlConnection("Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True") ’ Put this string on one line in your code
cmd = New SqlCommand(cmdString, conn)
conn.Open()
RecordsAffected = cmd.ExecuteNonQuery()
conn.Close()
Return RecordsAffected
End Function
C#
public int DeleteEmployee()
{
SqlConnection conn;
SqlCommand cmd;
int RecordsAffected;
string cmdString = "DELETE Employees WHERE LastName=’Evjen’";
conn = new
SqlConnection(@"Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True"); // Put this string on one line in your code

cmd = new SqlCommand(cmdString, conn);
conn.Open();
RecordsAffected = cmd.ExecuteNonQuery();
conn.Close();
return RecordsAffected;
}
You can assign the
ExecuteNonQuery()
command to an
Integer
variable (just as you did for the update
function) to return the number of records deleted.
382
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 383
Chapter 8: Data Management with ADO.NET
Basic ADO.NET Namespaces and Classes
The six core ADO.NET namespaces are shown in the following table. In addition to these namespaces,
each new data provider can have its own namespace. As an example, the Oracle .NET data provider adds
anamespaceof
System.Data.OracleClient
(for the Microsoft-built Oracle data provider).
Namespace Description
System.Data
This namespace is the core of ADO.NET. It contains classes used by all
data providers. It contains classes to represent tables, columns, rows, and
the
DataSet
class. It also contains several useful interfaces, such as
IDbCommand
,

IDbConnection
,and
IDbDataAdapter
. These interfaces are
used by all managed providers, enabling them to plug into the core of
ADO.NET.
System.Data.Common
This namespace defines common classes that are used as base classes for
data providers. All data providers share these classes. A few examples
are
DbConnection
and
DbDataAdapter
.
System.Data.OleDb
This namespace defines classes that work with OLE-DB data sources
using the .NET OleDb data provider. It contains classes such as
OleDbConnection
and
OleDbCommand
.
System.Data.Odbc
This namespace defines classes that work with the ODBC data sources
using the .NET ODBC data provider. It contains classes such as
OdbcConnection
and
OdbcCommand
.
System.Data.SqlClient
This namespace defines a data provider for the SQL Server 7.0 or higher

database. It contains classes such as
SqlConnection
and
SqlCommand
.
System.Data.SqlTypes
This namespace defines a few classes that represent specific data types
for the SQL Server database.
ADO.NET has three distinct types of classes commonly referred to as
Disconnected
,
Shared
,and
Data
Providers
.The
Disconnected
classes provide the basic structure for the ADO.NET framework. A good
exampleofthistypeofclassisthe
DataTable
class. The objects of this class are capable of storing data
without any dependency on a specific data provider. The
Shared
classes form the base classes for data
providers and are shared among all data providers. The
Data Provider
classes are meant to work with
different kinds of data sources. They are used to perform all data-management operations on specific
databases. The
SqlClient

data provider, for example, works only with the SQL Server database.
A data provider contains
Connection
,
Command
,
DataAdapter
,and
DataReader
objects. Typically, in
programming ADO.NET, you first create the
Connection
object and provide it with the necessary
information, such a s the connection string. You t hen create a
Command
object and provide it with the
details of the SQL command that is to be executed. This command can be an inline SQL text command, a
stored procedure, or direct table access. You can also provide parameters to these commands if needed.
After you create the
Connection
and the
Command
objects, you must decide whether the command
returns a result set. If the command doesn’t return a result set, you can simply execute the command by
calling one of its several
Execute
methods. On the other hand, if the command returns a result set, you
must make a decision about whether you want to retain the result set for future uses without maintain-
ing the connection to the database. If you want to retain the result set, you must create a
DataAdapter

383
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 384
Chapter 8: Data Management with ADO.NET
object and use it to fill a
DataSet
or a
DataTable
object. These objects are capable of maintaining their
information in a disconnected mode. However, if you don’t want t o retain the result set, but rather to
simply process the command in a swift fashion, you can use the
Command
object to create a
DataReader
object. The
DataReader
object needs a live connection to the database, and it works as a forward-only,
read-only cursor.
Using the Connection Object
The
Connection
object creates a link (or connection) to a specified data source. This object must contain
the necessary information to discover the specified data source and to log in to it properly using a defined
username and password combination. This information is provided via a single string called a connection
string. You can also store this connection string in the
web.config
file of your application.
Every type of data provider has a connection object of some kind. The data provider for working with a
SQL data store includes a
SqlConnection
class that performs this type of operation. T he

SqlConnection
object is a class that is specific to the
SqlClient
provider. As discussed earlier in this chapter, the
SqlClient
provider is built for working with the SQL Server 7.0 and higher databases. The properties
for the
SqlConnection
class are shown in the following table.
Property Description
ConnectionString
This property allows you to read or provide the connection string that should
be used by the
SqlConnection
object.
Database
This read-only property returns the name of the database to use after the
connection is opened.
Datasource
This read-only property returns the name of the instance of the SQL Server
database used by the
SqlConnection
object.
State
This read-only property returns the current state of the connection. The possible
values are
Broken
,
Closed
,

Connecting
,
Executing
,
Fetching
,and
Open
.
Connecting to a data source is probably the most common task when you are working with data. This
example and the ones that follow assume that you have a SQL Server database. In order to connect to
your SQL Server database, you use the
SqlConnection
class. This is shown in Listing 8-5.
Listing 8-5: Connecting to a SQL database
VB
Dim conn as SqlConnection
conn = New SqlConnection("Data Source=.
\
SQLEXPRESS;AttachDbFilename=
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True") ’ Put this string on one line in your code
conn.Open()
C#
SqlConnection conn;
conn = new
SqlConnection(@"Data Source=.
\
SQLEXPRESS;AttachDbFilename=

384
Evjen c08.tex V2 - 01/28/2008 2:05pm Page 385
Chapter 8: Data Management with ADO.NET
|DataDirectory|
\
NORTHWND.MDF;Integrated Security=True;
User Instance=True"); // Put this string on one line in your code
conn.Open();
To make this connection work, be sure that the proper namespaces are imported before you start using
any of the classes that work with SQL. The first step in making a connection is to create an instance of the
SqlConnection
class and assign it to the
conn
instance. This
SqlConnection
class is initialized after you
pass in the connection string as a parameter to the class. In this case, you are connecting to the Northwind
database that resides on your local machine using the system administrator’s login credentials.
Another means of making a connection is to put the connection string within the application’s
web.config
file and then to make a reference to the
web.config
file. With ASP.NET 3.5, you will find that there is an
easy way to manage the storage of your connection strings through the use of the
web.config
file. This
is actually a better way to store your connection strings rather than hard-coding them within the code
of the application itself. In addition to having a single point in the application where the credentials
for database access can be managed, storing credentials in the
web.config

also gives you the ability to
encrypt the credentials.
To define your connection string within the
web.config
file, you are going t o make use of the
<
connectionStrings
> section. From this section, you can place an <
add
> element within it to define
your connection. An example of this is illustrated in Listing 8-6.
Listing 8-6: Providing your connection string within the web.config file
<
connectionStrings
>
<
add name="DSN_Northwind" connectionString="Data
Source=.
\
SQLEXPRESS;AttachDbFilename=|DataDirectory|
\
NORTHWND.MDF;Integrated
Security=True;User Instance=True"
providerName="System.Data.SqlClient" /
>
<
/connectionStrings
>
In many places of this chapter, you will see that the actual connection string i s broken up on multiple
lines. This connection string will need to be on a single line within your code or broken up with string

concatenation.
Now t hat you have a connection string within the
web.config
file, you can then make use of that
connection string directly in your code by using the
ConnectionManager
object as illustrated here in
Listing 8-7.
Listing 8-7: Using the connection string found in the web.config file
VB
conn = New _
SqlConnection( _
ConfigurationManager.ConnectionStrings("DSN_Northwind").ConnectionString)
C#
conn = new
SqlConnection(
ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString);
385