Console.WriteLine(
"After DELETE: Number of employees {0}\n"
, cmdqry.ExecuteScalar()
);
}
catch (SqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
Console.WriteLine("Connection Closed.");
}
}
}
}
3. Make CommandParameters the startup project, and then run it by pressing
Ctrl+F5. You should see the results in Figure 11-7.
Figure 11-7. U
sing command parameters
CHAPTER 11 ■ EXECUTING COMMANDS 231
9004ch11final.qxd 12/13/07 4:09 PM Page 231
How It Works
First, you set up your sample data.
// set up rudimentary data
string fname = "Zachariah";
string lname = "Zinn";
You then add two parameters, @fname and @lname, to the Parameters collection prop-
erty of the command you want to parameterize.
// create commands

parameter names rather than their values. Values are substituted for parameters when
CHAPTER 11 ■ EXECUTING COMMANDS232
9004ch11final.qxd 12/13/07 4:09 PM Page 232
the SQL is submitted to the database server, not when the values are assigned to the
members of the
Parameters collection.
Summary
In this chapter, we covered what an ADO.NET command is and how to create a command
object. We also discussed associating a command with a connection, setting command
text, and using
ExecuteScalar(), ExecuteReader(), and ExecuteNonQuery() statements.
In the next chapter, you’ll look at data readers.
CHAPTER 11 ■ EXECUTING COMMANDS 233
9004ch11final.qxd 12/13/07 4:09 PM Page 233
9004ch11final.qxd 12/13/07 4:09 PM Page 234
Using Data Readers
In Chapter 11, you used data readers to retrieve data from a multirow result set. In this
chapter, we’ll look at data readers in more detail. You’ll see how they’re used and their
importance in ADO.NET programming.
In this chapter, we’ll cover the following:
• Understanding data readers in general
• Getting data about data
• Getting data about tables
• Using multiple result sets with a data reader
Understanding Data Readers in General
The third component of a data provider, in addition to connections and commands, is
the
data reader. Once y
ou’ve connected to a database and queried it, you need some way
to access the result set. This is where the data reader comes in.

Listing 12-1. DataLooper.cs
using System;
using System.Data;
using System.Data.SqlClient;
CHAPTER 12 ■ USING DATA READERS236
9004ch12final.qxd 12/13/07 4:07 PM Page 236
namespace Chapter12
{
class DataLooper
{
static void Main(string[] args)
{
// connection string
string connString = @"
server = .\sqlexpress;
integrated security = true;
database = northwind
";
// query
string sql = @"
select
contactname
from
customers
";
// create connection
SqlConnection conn = new SqlConnection(connString);
try
{
// open connection

How It Works
SqlDataReader is an abstr
act class and can’t be instantiated explicitly. For this reason,
y
ou obtain an instance of a
SqlDataReader b
y executing the
ExecuteReader method
of
SqlCommand.
// create data reader
SqlDataReader rdr = cmd.ExecuteReader();
ExecuteReader()
doesn’t just create a data reader, it sends the SQL to the connection
for execution, so when it returns you can loop through each row of the result set and
CHAPTER 12 ■ USING DATA READERS238
9004ch12final.qxd 12/13/07 4:07 PM Page 238
retrieve values column by column. To do this, you call the Read method of SqlDataReader,
which returns
true if a row is available and advances the cursor (the internal pointer to
the next row in the result set) or returns
false if another row isn’t available. Since Read()
advances the cursor to the next available row, you have to call it for all the rows in the
result set, so you call it as the condition in a
while loop:
// loop through result set
while (rdr.Read())
{
// print one row at a time
Console.WriteLine("{0}", rdr[0]);

.
The code
rdr[0]
is a reference to the data reader’s Item property and returns the value in the column spec-
ified for the curr
ent r
o
w
.
The v
alue is r
etur
ned as an object.
CHAPTER 12 ■ USING DATA READERS 239
9004ch12final.qxd 12/13/07 4:07 PM Page 239
Try It Out: Using Ordinal Indexers
In this example, you’ll build a console application that uses an ordinal indexer.
1. Add a new C# Console Application project named OrdinalIndexer to your
Chapter12 solution. Rename
Program.cs to OrdinalIndexer.cs.
2. Replace the code in OrdinalIndexer.cs with the code in Listing 12-2.
Listing 12-2. OrdinalIndexer.cs
using System;
using System.Data;
using System.Data.SqlClient;
namespace Chapter07
{
class OrdinalIndexer
{
static void Main(string[] args)

Console.WriteLine("\t{0} {1}",
"Company Name".PadRight(25),
"Contact Name".PadRight(20));
Console.WriteLine("\t{0} {1}",
"============".PadRight(25),
"============".PadRight(20));
// loop through result set
while (rdr.Read())
{
Console.WriteLine(" {0} | {1}",
rdr[0].ToString().PadLeft(25),
rdr[1].ToString().PadLeft(20));
}
// close reader
rdr.Close();
}
catch(Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
// close connection
conn.Close();
}
}
}
}
CHAPTER 12 ■ USING DATA READERS 241
9004ch12final.qxd 12/13/07 4:07 PM Page 241

while (rdr.Read())
{
Console.WriteLine(" {0} | {1}",
rdr[0].ToString().PadLeft(25),
rdr[1].ToString().PadLeft(20));
}
After processing all rows in the result set, you explicitly close the reader to free the
connection.
// close reader
rdr.Close();
Using Column Name Indexers
Most of the time we don’t really keep track of column numbers and prefer retrieving
values by their respective column names, simply because it’s much easier to remember
them by their names, which also makes the code more self-documenting.
You use column name indexing by specifying column names instead of ordinal
index numbers. This has its advantages. For example, a table may be changed by the
addition or deletion of one or more columns, upsetting column ordering and raising
exceptions in older code that uses ordinal indexers. Using column name indexers
would avoid this issue, but ordinal indexers are faster, since they directly reference
columns rather than look them up by name.
The following code snippet retrieves the same columns (CompanyName and
ContactName) that the last example did, using column name indexers.
// loop through result set
while (rdr.Read())
{
Console.WriteLine(" {0} | {1}",
rdr["companyname"].ToString().PadLeft(25),
rdr["contactname"].ToString().PadLeft(20));
}
R

System.Data.SqlTypes.
Table 12-1. SQL Server Typed Accessors
SQL Server Data Types .NET Type .NET Typed Accessor
bigint Int64 GetInt64
binary Byte[] GetBytes
bit Boolean GetBoolean
char String
or Char[] GetString or GetChars
datetime DateTime GetDateTime
decimal Decimal GetDecimal
float Double GetDouble
image
or long varbinary
Byte[] GetBytes
int
Int32
GetInt32
money Decimal GetDecimal
nchar String
or Char[] GetString or GetChars
ntext String or Char[] GetString or GetChars
numeric Decimal GetDecimal
CHAPTER 12 ■ USING DATA READERS244
9004ch12final.qxd 12/13/07 4:07 PM Page 244
SQL Server Data Types .NET Type .NET Typed Accessor
nvarchar String or Char[] GetString or GetChars
real Single GetFloat
smalldatetime DateTime GetDateTime
smallint Int16 GetInt16
smallmoney Decimal GetDecimal

DBTYPE_ERROR
ExternalException
GetValue
Continued
CHAPTER 12 ■ USING DATA READERS 245
9004ch12final.qxd 12/13/07 4:07 PM Page 245
Table 12-2. Continued
OLE DB Type .NET Type .NET Typed Accessor
DBTYPE_FILETIME DateTime GetDateTime
DBTYPE_GUID Guid GetGuid
DBTYPE_I4 Int32 GetInt32
DBTYPE_LONGVARCHAR String GetString
DBTYPE_NUMERIC Decimal GetDecimal
DBTYPE_R4 Single GetFloat
DBTYPE_I2 Int16 GetInt16
DBTYPE_I1 Byte GetByte
DBTYPE_UI8 UInt64 GetValue
DBTYPE_UI4 UInt32 GetValue
DBTYPE_UI2 UInt16 GetValue
DBTYPE_VARCHAR String GetString
DBTYPE_VARIANT Object GetValue
DBTYPE_WVARCHAR String GetString
DBTYPE_WSRT String GetString
To see typed accessors in action, you’ll build a console application that uses them.
For this example, you’ll use the Products table from the Northwind database.
Table 12-3 shows the data design of the table. Note that the data types given in the
table will be looked up for their corresponding typed accessor methods in Table 12-1 so
you can use them correctly in your application.
T
able 12-3.

Here, you’ll build a console application that uses typed accessors.
1. Add a new C# Console Application project named TypedAccessors to your
Chapter12 solution. Rename
Program.cs to TypedAccessors.cs.
2. Replace the code in TypedAccessors.cs with the code in Listing 12-3.
Listing 12-3. TypedAccessors.cs
using System;
using System.Data;
using System.Data.SqlClient;
namespace Chapter12
{
class TypedAccessors
{
static void Main(string[] args)
{
// connection string
string connString = @"
server = .\sqlexpress;
integrated security = true;
database = northwind
";
CHAPTER 12 ■ USING DATA READERS 247
9004ch12final.qxd 12/13/07 4:07 PM Page 247
// query
string sql = @"
select
productname,
unitprice,
unitsinstock,
discontinued

rdr.Close();
}
catch(Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
// close connection
conn.Close();
}
}
}
}
3. Make TypedAccessors the startup project, and run it by pressing Ctrl+F5. You
should see the results in Figure 12-3. (Only the first 20 rows are displayed in the
figure.)
Figure 12-3. Using typed accessors
How It Works
You query the Products table for ProductName, UnitPrice, UnitsInStock, and
Discontinued.
CHAPTER 12 ■ USING DATA READERS 249
9004ch12final.qxd 12/13/07 4:07 PM Page 249
// query
string sql = @"
select
productname,
unitprice,
unitsinstock,
discontinued

v
ersions fr
om nativ
e data types to .NET types fail, an ex
ception is thrown for invalid
casts
. F
or instance
, if y
ou try using the
GetString method on a bit data type instead of
using the
GetBoolean method, a
“
S
pecified cast is not valid” exception will be thrown.
CHAPTER 12 ■ USING DATA READERS250
9004ch12final.qxd 12/13/07 4:07 PM Page 250
Getting Data About Data
So far, all you’ve done is retrieve data from a data source. Once you have a populated data
reader in your hands, you can do a lot more. Here are a number of useful methods for
retrieving schema information or retrieving information directly related to a result set.
Table 12-4 describes some of the metadata methods and properties of a data reader.
Table 12-4. Data Reader Metadata Properties and Methods
Method or Property Name Description
Depth A property that gets the depth of nesting for the current row
FieldCount A property that holds the number of columns in the current row
GetDataTypeName A method that accepts an index and returns a string containing the
name of the column data type
GetFieldType A method that accepts an index and returns the .NET Framework

u
sing System.Data.SqlClient;
namespace Chapter12
{
class ResultSetInfo
{
static void Main(string[] args)
{
// connection string
string connString = @"
server = .\sqlexpress;
integrated security = true;
database = northwind
";
// query
string sql = @"
select
contactname,
contacttitle
from
customers
where
contactname like 'M%'
";
// create connection
SqlConnection conn = new SqlConnection(connString);
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);

"and its type is: {2}",
rdr.GetName(0),
rdr.GetOrdinal("contactname"),
rdr.GetFieldType(0));
CHAPTER 12 ■ USING DATA READERS 253
9004ch12final.qxd 12/13/07 4:07 PM Page 253
Console.WriteLine(
"'{0}' is at index {1} " +
"and its type is: {2}",
rdr.GetName(1),
rdr.GetOrdinal("contacttitle"),
rdr.GetFieldType(1));
rdr.Close();
}
catch(Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
conn.Close();
}
}
}
}
3. Make R
esultSetInfo the startup project, and run it by pressing Ctrl+F5. You should
see the results in Figure 12-4.
Figure 12-4. Displaying result set metadata
CHAPTER 12 ■ USING DATA READERS254

Console.WriteLine(
"'{0}' is at index {1} " +
"and its type is: {2}",
rdr.GetName(0),
rdr.GetOrdinal("contactname"),
rdr.GetFieldType(0));
So much for obtaining information about result sets. You’ll now learn how to get
information about schemas.
CHAPTER 12 ■ USING DATA READERS 255
9004ch12final.qxd 12/13/07 4:07 PM Page 255