Connections, Commands, and DataReaders

All interactions with a database using ADO.NET involve, either implicitly or explicitly, connection and command objects. Connection objects represent physical connections to a database. They come in two flavors: SqlConnection for Microsoft SQL Server databases and OleDbConnection for others. Command objects represent the commands performed on a database. They too come in provider-specific versions: SqlCommand and OleDbCommand.

The canonical usage pattern for executing database commands in ADO.NET is as follows:

  1. Create a connection object encapsulating a connection string.

  2. Open the connection by calling Open on the connection object.

  3. Create a command object encapsulating both an SQL command and the connection that the command will use.

  4. Call a method on the command object to execute the command.

  5. Close the connection by calling Close on the connection object.

SqlCommand and OleDbCommand implement several methods that you can call to execute a command. Which method you call and what you get in return depends on the command being executed. If the command is a query, you get back a DataReader object (SqlDataReader or OleDbDataReader) encapsulating the results. Connection, Command, and DataReader are three of the most important types defined in ADO.NET. The next several sections describe them in detail.

The SqlConnection Class

Before you can perform an operation on a database, you must open a connection to it. ADO.NET鈥檚 System.Data.SqlClient.SqlConnection class represents connections to SQL Server databases. Inside a SqlConnection object is a connection string. The following statements create a SqlConnection object and initialize it with a connection string that opens the Pubs database that comes with SQL Server, using the user name 鈥渟a鈥?and a blank password:

SqlConnection聽conn聽=聽new聽SqlConnection聽();
conn.ConnectionString聽= "server=localhost;database=pubs;uid=sa;pwd=";

ConnectionString is the SqlConnection property that stores the connection string. SqlConnection features an alternative constructor that creates a SqlConnection object and initializes the ConnectionString property in one step:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

SqlConnection verifies that the connection string is well formed when the string is assigned. The following statement throws a System.ArgumentException exception because Srvr is not a valid parameter:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("srvr=localhost;database=pubs;uid=sa;pwd=");

Parameter values in the connection string aren鈥檛 tested until you open the connection, so a connection string assignment operation will not throw an exception if the server name, database name, user ID, or password is invalid.

The connection string鈥檚 Server parameter identifies the instance of SQL Server that contains the database and the machine on which it resides. Server=localhost, which can also be written Server=(local) or Data Source=(local), identifies the host machine (the machine that鈥檚 executing the ADO.NET code) as the one that hosts the database and implicitly identifies the default instance of SQL Server. SQL Server 2000 permits up to 16 different instances to be installed on a given machine. One instance is typically designated as the default instance; others are referenced by name. The following statements create a SqlCommand object referencing the Pubs database in an instance of SQL Server named Wintellect on a remote machine named Hawkeye:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=hawkeye\wintellect;database=pubs;uid=sa;pwd=");

The Database parameter, which can also be written Initial Catalog, identifies the database. Uid, whose alternate form is User ID, specifies the user name, and Pwd, which can optionally be written Password, specifies the password. The abbreviated parameter names are a holdover from ODBC and are officially considered deprecated. Nonetheless, I use them in most of my examples to keep the connection strings as compact as possible.

Server, Database, Uid, and Pwd aren鈥檛 the only parameters you can include in a SQL Server connection string. A complete list is available in the documentation for the SqlConnection.ConnectionString property. Other commonly used connection string parameters include Min Pool Size and Max Pool Size, which set limits on the size of the connection pool (the defaults are 0 and 100, respectively); Pooling, which enables and disables connection pooling (default=true); Integrated Security, which enables and disables integrated security (the default is false, which authenticates the user on the basis of the user name and password in the connection string; if Integrated Security is true, SQL Server uses Windows access tokens for authentication); and Connect Timeout, which specifies the maximum length of time, in seconds, you鈥檙e willing to wait when opening a connection (default=15). The following statements use some of these parameters to more carefully control the connection attributes:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=hawkeye\wintellect;database=pubs;uid=sa;pwd=;" +
聽聽聽 "min聽pool聽size=10;max聽pool聽size=50;connect聽timeout=10");

Setting the minimum pool size to some value greater than 0 preloads the connection pool with the specified number of connections and helps a data-driven application that expects heavy demand get off to a fast start.

The OleDbConnection Class

System.Data.OleDb.OleDbConnection represents connections to databases accessed through the .NET Framework鈥檚 OLE DB .NET provider. The format of connection strings used with OleDbConnection is patterned after OLE DB connection strings and differs slightly from that of SqlConnection. The following statement creates an OleDbConnection object encapsulating a connection to SQL Server鈥檚 Pubs database on the local machine:

OleDbConnection聽conn聽=聽new聽OleDbConnection
聽聽聽聽("provider=sqloledb;server=localhost;database=pubs;uid=sa;pwd=");

The Provider parameter identifies the OLE DB provider used to interact with the database鈥攊n this case, SQLOLEDB, which is Microsoft鈥檚 OLE DB provider for SQL Server. Changing the provider to MSDAORA would target Oracle databases instead.

As with SqlConnection connection strings, OleDbConnection connection strings are not case-sensitive and can utilize a more verbose syntax in which Server equals Data Source, DataBase equals Initial Catalog, Uid equals User ID, and Pwd equals Password. The following statement is functionally equivalent to the previous one:

OleDbConnection聽conn聽=聽new聽OleDbConnection
聽聽聽聽("provider=sqloledb;data聽source=localhost;" +
聽聽聽 "initial聽catalog=pubs;user聽id=sa;password=");

OleDbConnection connection strings can also include File Name parameters targeting Microsoft Data Link (UDL) files and OLE DB Services parameters enabling and disabling certain features of the underlying unmanaged provider. For example, the following connection string disables connection pooling:

OleDbConnection聽conn聽=聽new聽OleDbConnection
聽聽聽聽("provider=sqloledb;data聽source=localhost;OLE聽DB聽Services=-2" +
聽聽聽 "initial聽catalog=pubs;user聽id=sa;password=");

Other OleDbConnection connection string parameters are supported, but these tend to vary among providers. Refer to the documentation for individual OLE DB providers for more information on valid connection string parameters.

Opening and Closing Connections

The mere act of creating a Connection object and supplying a connection string doesn鈥檛 physically open a connection to the database. Calling the object鈥檚 Open method does. A connection opened with Open should be closed with Close. Both SqlConnection and OleDbConnection feature Open and Close methods. The following code opens and closes a SQL Server connection:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");
conn.Open聽();
//聽TODO:聽Use聽the聽connection
conn.Close聽();

SqlConnection.Open throws a SqlException if it can鈥檛 establish a connection to the database. Operations performed on the database through an open connection also throw SqlExceptions if they fail. Because exceptions should never go uncaught, and because closing an open connection is vitally important, you should enclose statements that close database connections in finally blocks, as shown here:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");
try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽//聽TODO:聽Use聽the聽connection
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

The equivalent code for the OLE DB .NET provider looks like this. Note that the exception type is OleDbException rather than SqlException:

OleDbConnection聽conn聽=聽new聽OleDbConnection
聽聽聽聽("provider=sqloledb;server=localhost;database=pubs;uid=sa;pwd=");
try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽//聽TODO:聽Use聽the聽connection
}
catch聽(OleDbException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

Calling Close on a connection that鈥檚 not open isn鈥檛 harmful. Structuring your database access code this way ensures that the connection is closed even in the event of untimely errors. Failing to close open connections is debilitating to performance and to the very operation of the application. Always close database connections in finally blocks in production code.

Command Classes

An open connection to a database is of little value unless you use it to execute commands. To that end, ADO.NET provides a pair of command classes named SqlCommand and OleDbCommand. Both encapsulate SQL commands performed on a database, both rely on connections established with SqlConnection and OleDbConnection, and both include methods that you can call to execute the commands encapsulated inside them.

The following example uses a SqlCommand object to delete a record from the Pubs database鈥檚 鈥淭itles鈥?table using an SQL DELETE command:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");
try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand聽();
聽聽聽聽cmd.CommandText聽= "delete聽from聽titles聽where聽title_id聽=聽'BU1032'";
聽聽聽聽cmd.Connection聽=聽conn;
聽聽聽聽cmd.ExecuteNonQuery聽();聽//聽Execute聽the聽command
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

You can make your code more concise by creating a SqlCommand object and initializing its Connection and CommandText properties in one step:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");
try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("delete聽from聽titles聽where聽title_id聽=聽'BU1032'",聽conn);
聽聽聽聽cmd.ExecuteNonQuery聽();聽//聽Execute聽the聽command
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

You can also use the command object鈥檚 CommandTimeout property to specify the number of seconds you鈥檙e willing to give the command for it to complete:

SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽("delete聽from聽titles聽where聽title_id聽=聽'BU1032'",聽conn);
cmd.CommandTimeout聽=聽10;聽//聽Allow聽10聽seconds
cmd.ExecuteNonQuery聽();

The default command time-out is 30 seconds. A command that times out throws a SqlException. To prevent a command from timing out (probably not a good idea), set CommandTimeout to 0.

The preceding examples use ExecuteNonQuery to execute an SQL command. Command objects also have methods named ExecuteScalar and ExecuteReader. Which of the three you should use depends on the nature of the command that you鈥檙e executing.

The ExecuteNonQuery Method

The ExecuteNonQuery method is a vehicle for executing INSERT, UPDATE, DELETE, and other SQL commands that don鈥檛 return values鈥攆or example, CREATE DATABASE and CREATE TABLE commands. When used with INSERT, UPDATE, or DELETE, ExecuteNonQuery returns the number of rows affected by the command. For all other commands, it returns 鈥?.

Here鈥檚 an example that uses ExecuteNonQuery to add a record to the Pubs database鈥檚 鈥淭itles鈥?table using an INSERT command:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("insert聽into聽titles聽(title_id,聽title,聽type,聽pubdate) " +
聽聽聽聽聽聽聽 "values聽('JP1001',聽'Programming聽Microsoft聽.NET', " +
聽聽聽聽聽聽聽 "'business',聽'May聽2002')",聽conn);
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

The next example updates the record just added:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("update聽titles聽set聽title_id聽=聽'JP2002' " +
聽聽聽聽聽聽聽 "where聽title_id聽=聽'JP1001'",聽conn);
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

And this one removes the record from the database:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("delete聽from聽titles聽where聽title_id聽=聽'JP2002'",聽conn);
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

To create a new database named 鈥淢yDatabase鈥?with ExecuteNonQuery, simply change the command text to 鈥渃reate database MyDatabase.鈥?Follow up with CREATE TABLE and INSERT commands, and you can build a whole new database on the fly.

If ExecuteNonQuery fails, it throws an exception accompanied by a SqlException object. SqlException properties such as Message, Class, and Source contain detailed information about the error. A simple way to respond to a SqlException in a console application is to write the error message in the SqlException object to the console window:

catch聽(SqlException聽ex)聽{
聽聽聽聽Console.WriteLine聽(ex.Message);
}

Examples of statements that throw exceptions are UPDATEs with invalid field names and INSERTs that violate primary key constraints. Note that UPDATE and DELETE commands targeting nonexistent records do not constitute errors; ExecuteNonQuery simply returns 0.

The ExecuteScalar Method

The ExecuteScalar method executes an SQL command and returns the first row of the first column in the result set. One of its most common uses is to execute SQL functions such as COUNT, AVG, MIN, MAX, and SUM, which return single-row, single-column result sets. The following example writes the largest advance payment recorded in the Pubs database to a console window:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("select聽max聽(advance)聽from聽titles",聽conn);
聽聽聽聽decimal聽amount聽=聽(decimal)聽cmd.ExecuteScalar聽();
聽聽聽聽Console.WriteLine聽("ExecuteScalar聽returned聽{0:c}",聽amount);
}
catch聽(SqlException聽ex)聽{
聽聽聽聽Console.WriteLine聽(ex.Message);
}
finally聽{
聽聽聽聽conn.Close聽();
}

Note the cast that converts ExecuteScalar鈥檚 return value into a decimal value. ExecuteScalar is generically typed to return an Object, so a cast is required to convert it into a strong type. If you cast incorrectly, the .NET Framework throws an InvalidCastException. In this example, the cast works fine because the 鈥淎dvance鈥?field in the Pubs database is of type money, and the SQL money data type translates naturally into the .NET Framework鈥檚 decimal (System.Decimal) data type.

Another common use for ExecuteScalar is to retrieve BLOBs (binary large objects) from databases. The following example retrieves an image from the 鈥淟ogo鈥?field of the Pubs database鈥檚 鈥淧ub_info鈥?table and encapsulates it in a bitmap:

MemoryStream聽stream聽=聽new聽MemoryStream聽();
SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("select聽logo聽from聽pub_info聽where聽pub_id='0736'",聽conn);
聽聽聽聽byte[]聽blob聽=聽(byte[])聽cmd.ExecuteScalar聽();
聽聽聽聽stream.Write聽(blob,聽0,聽blob.Length);
聽聽聽聽Bitmap聽bitmap聽=聽new聽Bitmap聽(stream);
聽聽聽聽//聽TODO:聽Use聽the聽bitmap
聽聽聽聽bitmap.Dispose聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽stream.Close聽();
聽聽聽聽conn.Close聽();
}

Once the bitmap is created, you can do whatever you want with it: display it in a Windows form, stream it back in an HTTP response, or whatever. Note that in order for this sample to compile, you must include using statements that import the System.IO and System.Drawing namespaces as well as System and System.Data.SqlClient.

Incidentally, the previous code sample answers a frequently asked ADO.NET question: 鈥淗ow do I retrieve BLOBs from databases with ADO.NET?鈥?You might be interested in knowing how to write BLOBs to databases, too. The secret is to call ExecuteNonQuery on a command object that wraps an INSERT command containing an input parameter whose type is byte[]. To demonstrate, the following example inserts a record into the Pubs database鈥檚 鈥淧ub_info鈥?table and includes a BLOB in the record鈥檚 鈥淟ogo鈥?field:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("insert聽into聽pub_info聽(pub_id,聽logo)聽values聽('9937',聽@logo)",
聽聽聽聽聽聽聽聽conn);
聽聽聽聽cmd.Parameters.Add聽("@logo",聽blob);
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

Where does the variable named blob come from? It鈥檚 defined and initialized separately. Here鈥檚 an example that initializes blob with an image read from a file named Logo.jpg:

FileStream聽stream聽=聽new聽FileStream聽("Logo.jpg",聽FileMode.Open);
byte[]聽blob聽=聽new聽byte[stream.Length];
stream.Read聽(blob,聽0,聽(int)聽stream.Length);
stream.Close聽();

Using the techniques demonstrated here, it鈥檚 easy to write images or other BLOBs to databases and read them back. Do note that for the preceding INSERT command to work on the Pubs database, you must first add a record to the 鈥淧ublishers鈥?table containing the 鈥淧ub_id鈥?9937. If you don鈥檛, the INSERT will fail because of a foreign key constraint that stipulates that publisher IDs in the 鈥淧ub_info鈥?table also appear in the 鈥淧ublishers鈥?table.

Chapter 10 contains another excellent example of ExecuteScalar usage. That chapter鈥檚 LoginPage.aspx file uses ExecuteScalar to validate a user name and password by using an SQL COUNT command to see whether the user name and password exist in the database. Here鈥檚 that code again:

SqlConnection聽connection聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=weblogin;uid=sa;pwd=");

try聽{
聽聽聽聽connection.Open聽();

聽聽聽聽StringBuilder聽builder聽=聽new聽StringBuilder聽();
聽聽聽聽builder.Append聽("select聽count聽(*)聽from聽users " +
聽聽聽聽聽聽聽 "where聽username聽=聽\'");
聽聽聽聽builder.Append聽(username);
聽聽聽聽builder.Append聽("\'聽and聽cast聽(rtrim聽(password)聽as " +
聽聽聽聽聽聽聽 "varbinary)聽=聽cast聽(\'");
聽聽聽聽builder.Append聽(password);
聽聽聽聽builder.Append聽("\'聽as聽varbinary)");

聽聽聽聽SqlCommand聽command聽=聽new聽SqlCommand聽(builder.ToString聽(),
聽聽聽聽聽聽聽聽connection);

聽聽聽聽int聽count聽=聽(int)聽command.ExecuteScalar聽();
聽聽聽聽return聽(count聽>聽0);
}
catch聽(SqlException)聽{
聽聽聽聽return聽false;
}
finally聽{
聽聽聽聽connection.Close聽();
}

As described in Chapter 10, casting the password to SQL鈥檚 varbinary data type is a sneaky way to perform a case-sensitive string comparison.

The ExecuteReader Method

The ExecuteReader method exists for one purpose and one purpose only: to perform database queries and obtain the results as quickly and efficiently as possible. ExecuteReader returns a DataReader object: SqlDataReader if called on a SqlCommand object and OleDbDataReader if called on an OleDbCommand object. DataReader has methods and properties that you can call to iterate over the result set. It is a fast, forward-only, read-only mechanism for enumerating the results of database queries. It鈥檚 extremely efficient for retrieving result sets from remote machines because it pulls back only the data that you ask for. A query might produce a million records, but if you only read 10 of them with a DataReader, only a fraction of the total result set is actually returned.

The following example uses ExecuteReader and the resultant SqlDataReader to write the titles of all the books listed in the Pubs database to a console window:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand聽("select聽*聽from聽titles",聽conn);
聽聽聽聽SqlDataReader聽reader聽=聽cmd.ExecuteReader聽();
聽聽聽聽while聽(reader.Read聽())
聽聽聽聽聽聽聽聽Console.WriteLine聽(reader["title"]);
}
catch聽(SqlException聽ex)聽{
聽聽聽聽Console.WriteLine聽(ex.Message);
}
finally聽{
聽聽聽聽conn.Close聽();
}

Each call to SqlDataReader.Read returns one row from the result set. This example uses a property indexer to extract the value of the record鈥檚 鈥淭itle鈥?field. Fields can be referenced by name or by numeric index (0-based, of course).

You don鈥檛 have to know a database鈥檚 schema in advance to query it with a DataReader. You can get schema information from the DataReader itself. The next example queries for all the records in the 鈥淭itles鈥?table and displays the names of the fields:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand聽("select聽*聽from聽titles",聽conn);
聽聽聽聽SqlDataReader聽reader聽=聽cmd.ExecuteReader聽();
聽聽聽聽for聽(int聽i=0;聽i<reader.FieldCount;聽i++)
聽聽聽聽聽聽聽聽Console.WriteLine聽(reader.GetName聽(i));
}
catch聽(SqlException聽ex)聽{
聽聽聽聽Console.WriteLine聽(ex.Message);
}
finally聽{
聽聽聽聽conn.Close聽();
}

You can also get schema information by calling a DataReader鈥檚 GetSchema颅Table method. GetSchemaTable returns a DataTable object (described later in this chapter) from which you can enumerate fields.

The previous example used DataReader.GetName to retrieve field names. DataReader also has a GetValue method that you can use to retrieve a field鈥檚 value. GetValue returns a generic Object, but it鈥檚 complemented by numerous Get methods, such as GetInt32 and GetDecimal, that return strong data types. The following code uses GetDecimal to read decimal values from the 鈥淭itles鈥?table鈥檚 鈥淎dvance鈥?field. The WHERE clause in the SELECT command skips records whose 鈥淎dvance鈥?field is null. The call to GetOrdinal is required because GetDecimal accepts only integer indexes. GetOrdinal does exactly the opposite of GetName鈥?/span>it converts a field name into a numeric index:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=pubs;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("select聽*聽from聽titles聽where聽advance聽!=聽0",聽conn);
聽聽聽聽SqlDataReader聽reader聽=聽cmd.ExecuteReader聽();
聽聽聽聽int聽index聽=聽reader.GetOrdinal聽("advance");
聽聽聽聽while聽(reader.Read聽())
聽聽聽聽Console.WriteLine聽("{0:c}",聽reader.GetDecimal聽(index));
}
catch聽(SqlException聽ex)聽{
聽聽聽聽Console.WriteLine聽(ex.Message);
}
finally聽{
聽聽聽聽conn.Close聽();
}

In the further interest of type safety, DataReader also offers methods named GetFieldType and GetDataTypeName for determining a field鈥檚 type at run time. The former identifies the .NET Framework data type (for example, System.Decimal), while the latter identifies the SQL data type (for example, money).

Closing a DataReader

A potential gotcha regarding DataReaders has to do with their Close methods. By default, DataReader.Close does not close the connection encapsulated in the command object that created the DataReader. In other words, this is buggy code:

SqlDataReader聽reader聽=聽cmd.ExecuteReader聽();
聽聽.
聽聽.
聽聽.
//聽Close聽the聽connection
reader.Close聽();聽//聽Does聽NOT聽close聽the聽connection!

DataReader.Close closes the DataReader, which frees the connection associated with the DataReader so that it can be used again. For example, suppose you use a command object to create a DataReader and then try to use that command object (or the connection that it encapsulates) for something else, as shown here:

SqlCommand聽cmd聽=聽new聽SqlCommand聽("select聽*聽from聽titles",聽conn);
SqlDataReader聽reader聽=聽cmd.ExecuteReader聽();
while聽(reader.Read聽())
聽聽聽聽Console.WriteLine聽(reader["title"]);

cmd.CommandText聽= "select聽*聽from聽authors";
reader聽=聽cmd.ExecuteReader聽();
while聽(reader.Read聽())
聽聽聽聽Console.WriteLine聽(reader["au_lname"]);

The second call to ExecuteReader throws an InvalidOperationException. Why? Because the underlying connection is still associated with the first DataReader, which hasn鈥檛 been closed. To correct this error, close the first DataReader before reusing the connection:

SqlCommand聽cmd聽=聽new聽SqlCommand聽("select聽*聽from聽titles",聽conn);
SqlDataReader聽reader聽=聽cmd.ExecuteReader聽();
while聽(reader.Read聽())
聽聽聽聽Console.WriteLine聽(reader["title"]);

reader.Close聽();cmd.CommandText聽= "select聽*聽from聽authors";
reader聽=聽cmd.ExecuteReader聽();
while聽(reader.Read聽())
聽聽聽聽Console.WriteLine聽(reader["au_lname"]);

Now the code will work as intended. You don鈥檛 need to call Close on a Data颅Reader if you don鈥檛 intend to reuse the connection, but there鈥檚 no harm in calling Close anyway if it makes you feel more comfortable. (Can you spell D-E-F-E-N-S-I-V-E P-R-O-G-R-A-M-M-I-N-G?)

As an aside, you can configure a DataReader so that its Close method does close the underlying connection. The secret is to pass ExecuteReader a 鈥渃ommand behavior鈥?

reader聽=聽cmd.ExecuteReader聽(CommandBehavior.CloseConnection);

If you elect to close a connection this way, be sure to position the statement that closes the DataReader in a finally block to prevent exceptions from leaking connections.

Transacted Commands

Transacted database operations are an important element of many data-driven applications. A transaction is simply two or more otherwise independent units of work grouped together into one logical unit. A classic example is an application that transfers funds from one bank account to another by debiting money from one account (that is, one database record) and crediting it to another. The updates should be performed within the scope of a transaction. Why? So that if one of the operations fails, the other will fail (or be rolled back), too.

Much has been written in recent years about distributed transactions鈥攖ransactions that span two or more databases. The .NET Framework supports distributed transactions by leveraging the underlying distributed services in the operating system. In reality, however, the vast majority of database transactions are local rather than distributed鈥攖hat is, they鈥檙e performed on a single database. ADO.NET simplifies local transaction management by exposing a BeginTransaction method from its Connection classes and offering provider-specific Transaction classes to represent the resulting transactions.

To demonstrate, suppose you鈥檝e created a SQL Server database named MyBank that contains a table named 鈥淎ccounts.鈥?Each record in the table identifies the current balance in the account as well as the account number. Suppose this data is stored in fields named 鈥淏alance鈥?and 鈥淎ccount_ID.鈥?Here鈥檚 some simple database access code that transfers funds from account 1111 to account 2222:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=mybank;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();

聽聽聽聽//聽Debit聽$1,000聽from聽account聽1111
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("update聽accounts聽set聽balance聽=聽balance聽-聽1000 " +
聽聽聽聽聽聽聽 "where聽account_id聽=聽'1111'",聽conn);
聽聽聽聽cmd.ExecuteNonQuery聽();

聽聽聽聽//聽Credit聽$1,000聽to聽account聽2222
聽聽聽聽cmd.CommandText聽= "update聽accounts聽set聽balance聽= " +
聽聽聽聽聽聽聽 "balance聽+聽1000聽where聽account_id聽=聽'2222'";
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

This code suffers from two potentially fatal flaws. The first is that if the debit succeeds but the credit fails, money disappears into thin air. Chances are neither account owner will be too happy with the results. The second problem is that if another application were to query for the account balances at exactly the wrong time (that is, after the debit but before the credit), it might get inconsistent results.

Performing these updates inside a transaction solves both problems. If one of the operations succeeds but the other fails, we can effectively fail the one that succeeded by failing the transaction. Also, databases that support transactions use locking to prevent other parties from seeing the results of incomplete transactions. (Locking behavior is dependent on the transaction鈥檚 isolation level and sometimes does permit a client to read data from an unfinished transaction, but the preceding statement is conceptually accurate nonetheless.) Here鈥檚 a revised code sample that uses ADO.NET鈥檚 transaction support to encapsulate the updates in a transaction:

SqlTransaction聽trans聽=聽null;
SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=mybank;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();

聽聽聽聽//聽Start聽a聽local聽transaction
聽聽聽聽trans聽=聽conn.BeginTransaction聽(IsolationLevel.Serializable);

聽聽聽聽//聽Create聽and聽initialize聽a聽SqlCommand聽object
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand聽();
聽聽聽聽cmd.Connection聽=聽conn;
聽聽聽聽cmd.Transaction聽=聽trans;

聽聽聽聽//聽Debit聽$1,000聽from聽account聽1111
聽聽聽聽cmd.CommandText聽= "update聽accounts聽set聽balance聽= " +
聽聽聽聽聽聽聽 "balance聽-聽1000聽where聽account_id聽=聽'1111'";
聽聽聽聽cmd.ExecuteNonQuery聽();

聽聽聽聽//聽Credit聽$1,000聽to聽account聽2222
聽聽聽聽cmd.CommandText聽= "update聽accounts聽set聽balance聽= " +
聽聽聽聽聽聽聽 "balance聽+聽1000聽where聽account_id聽=聽'2222'";
聽聽聽聽cmd.ExecuteNonQuery聽();

聽聽聽聽//聽Commit聽the聽transaction聽(commit聽changes)
聽聽聽聽trans.Commit聽();
}
catch聽(SqlException)聽{
聽聽聽聽//聽Abort聽the聽transaction聽(roll聽back聽changes)
聽聽聽聽if聽(trans聽!=聽null)
聽聽聽聽聽聽聽聽trans.Rollback聽();
}
finally聽{
聽聽聽聽conn.Close聽();
}

The revised code calls BeginTransaction on the open SqlConnection object to start a local transaction. IsolationLevel.Serializable assigns the transaction the highest isolation level possible, which locks down the records involved in the transaction while they鈥檙e updated so that they can鈥檛 be read or written. Serializable is admittedly too high an isolation level for this simple example, but in the real world, the transaction wouldn鈥檛 be nearly so simple. At the very least, you鈥檇 build in checks for negative balances and write a separate record to another table in the database documenting the transfer of funds. (A full discussion of isolation levels is beyond the scope of this chapter, but copious documentation regarding isolation levels and the various ramifications thereof is available in published literature.) BeginTransaction returns a SqlTransaction object representing the new transaction. A reference to that object is assigned to the SqlCommand object鈥檚 Transaction property. If both updates perform without error, this sample commits the transaction by calling Commit on the SqlTransaction object. Committing the transaction commits, or writes, the changes to the database. If, however, either update throws an exception, the exception handler aborts the transaction by calling Rollback on the SqlTransaction object. Aborting a transaction prevents the changes made within it from being committed to the database. On a practical level, it is now impossible to update one of these records without updating the other.

That, in a nutshell, is how ADO.NET handles transacted database operations. Note that because passing an invalid account number in a WHERE clause to an UPDATE command is not considered an error (ExecuteNonQuery returns 0 rather than throwing an exception), you must add logic to the sample in the previous paragraph if you want a bad account number to fail the transaction. In real life, that kind of protection is important.

Parameterized Commands

It鈥檚 not unusual for an application to execute the same command on a database repeatedly, varying only the value or values used in the command. The SQL INSERT command in the previous section is a perfect example. The same basic command was used to debit and credit accounts. The only difference from one invocation to the next was the amount of money involved and the account number.

SQL programmers often use parameterized commands (frequently referred to as 鈥減arameterized queries鈥? to code redundant commands, especially commands whose input values come from user input. Here鈥檚 a parameterized version of the previous section鈥檚 INSERT command:

UPDATE聽Accounts聽SET聽Balance聽=聽Balance聽+聽?聽WHERE聽Account_ID聽=聽?

ADO.NET supports parameterized commands as well. The syntax, however, varies slightly depending on the provider that you use.

The following example demonstrates how to use parameterized commands with the SQL Server .NET provider. Transaction management code is omitted for clarity:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=mybank;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();

聽聽聽聽//聽Create聽and聽initialize聽a聽SqlCommand聽object
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand
聽聽聽聽聽聽聽聽("update聽accounts聽set聽balance聽=聽balance聽+聽@amount " +
聽聽聽聽聽聽聽 "where聽account_id聽=聽@id",聽conn);
聽聽聽聽cmd.Parameters.Add聽("@amount",聽SqlDbType.Money);
聽聽聽聽cmd.Parameters.Add聽("@id",聽SqlDbType.Char);

聽聽聽聽//聽Debit聽$1,000聽from聽account聽1111
聽聽聽聽cmd.Parameters["@amount"].Value聽=聽-1000;
聽聽聽聽cmd.Parameters["@id"].Value聽= "1111";
聽聽聽聽cmd.ExecuteNonQuery聽();

聽聽聽聽//聽Credit聽$1,000聽to聽account聽2222
聽聽聽聽cmd.Parameters["@amount"].Value聽=聽1000;
聽聽聽聽cmd.Parameters["@id"].Value聽= "2222";
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

And here鈥檚 the same example modified to work with the OLE DB .NET provider, with changes highlighted in bold:

OleDbConnection聽conn聽=聽new聽OleDbConnection
聽聽聽聽("provider=sqloledb;server=localhost;database=mybank;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();

聽聽聽聽//聽Create聽and聽initialize聽an聽OleDbCommand聽object
聽聽聽聽OleDbCommand聽cmd聽=聽new聽OleDbCommand
聽聽聽聽聽聽聽聽("update聽accounts聽set聽balance聽=聽balance聽+聽? " +
聽聽聽聽聽聽聽 "where聽account_id聽=聽?",聽conn);
聽聽聽聽cmd.Parameters.Add聽("@amount",聽OleDbType.Decimal);
聽聽聽聽cmd.Parameters.Add聽("@id",聽OleDbType.Char);

聽聽聽聽//聽Debit聽$1,000聽from聽account聽1111
聽聽聽聽cmd.Parameters["@amount"].Value聽=聽-1000;
聽聽聽聽cmd.Parameters["@id"].Value聽= "1111";
聽聽聽聽cmd.ExecuteNonQuery聽();

聽聽聽聽//聽Credit聽$1,000聽to聽account聽2222
聽聽聽聽cmd.Parameters["@amount"].Value聽=聽1000;
聽聽聽聽cmd.Parameters["@id"].Value聽= "2222";
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(OleDbException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

These samples are cleaner than the ones in the previous section and are also easier to maintain. Parameterized commands are to database programming as subroutines are to application programming.

As these examples demonstrate, the general approach to using parameterized commands in ADO.NET is to add Parameter (SqlParameter or OleDbParameter) objects containing the values of the command鈥檚 replaceable parameters to the Command object by calling Add on the Command object鈥檚 Parameters collection. Besides the obvious differences in class names, here鈥檚 how parameterized command usage differs between the two providers:

With regard to that last point, switching the following two statements doesn鈥檛 affect the SQL Server .NET provider in the least:

cmd.Parameters.Add聽("@amount",聽SqlDbType.Money);
cmd.Parameters.Add聽("@id",聽SqlDbType.Char);

But reversing the order of the equivalent statements in the OLE DB .NET sample generates a run-time exception. What鈥檚 scary is that if the two parameters were type-compatible, no exception would occur and the command would happily execute with bogus input parameters. Don鈥檛 forget about parameter order when using the OLE DB .NET provider! Also be aware that if a parameterized OleDbCommand uses one input value multiple times, that value must be added to the Parameters collection an equal number of times. The same is not true of parameterized SqlCommands, which use parameter names to resolve multiple references.

Stored Procedures

Both SqlCommand and OleDbCommand feature Prepare methods that you can call to 鈥減repare鈥?a method鈥攖hat is, compile it so that it can be used again and again without having to be recompiled ad infinitum. However, you shouldn鈥檛 use Prepare. Why? Because commands that are executed repeatedly on a database should be implemented as stored procedures. A stored procedure is nothing more than a user-defined command added to a database. Stored procedures execute faster than the equivalent dynamic SQL statements because they鈥檙e already compiled; the performance difference is akin to that of compiled code vs. interpreted code. Coding frequently used commands as stored procedures is a common technique for improving the performance of data-driven applications. Back-end databases are often where performance bottlenecks lie, so anything you can do to speed database operations will have a direct impact on overall performance.

ADO.NET supports stored procedures. The syntax is very much like that of parameterized commands. Here鈥檚 a stored procedure named proc_TransferFunds that transfers funds between accounts in the MyBank database used in previous examples. Observe that transaction management logic is built into the stored procedure, ensuring that the UPDATEs succeed or fail as a whole without any overt action on the part of the calling application:

CREATE聽PROCEDURE聽proc_TransferFunds
聽聽聽聽@Amount聽money,
聽聽聽聽@From聽char聽(10),
聽聽聽聽@To聽char聽(10)
AS
聽聽聽聽BEGIN聽TRANSACTION
聽聽聽聽UPDATE聽Accounts聽SET聽Balance聽=聽Balance聽-聽@Amount
聽聽聽聽聽聽聽聽WHERE聽Account_ID聽=聽@From
聽聽聽聽IF聽@@ROWCOUNT聽=聽0
聽聽聽聽BEGIN
聽聽聽聽聽聽聽聽ROLLBACK聽TRANSACTION
聽聽聽聽聽聽聽聽RETURN
聽聽聽聽END
聽聽聽聽UPDATE聽Accounts聽SET聽Balance聽=聽Balance聽+聽@Amount
聽聽聽聽聽聽聽聽WHERE聽Account_ID聽=聽@To
聽聽聽聽IF聽@@ROWCOUNT聽=聽0
聽聽聽聽BEGIN
聽聽聽聽聽聽聽聽ROLLBACK聽TRANSACTION
聽聽聽聽聽聽聽聽RETURN
聽聽聽聽END
聽聽聽聽COMMIT聽TRANSACTION
GO

Here鈥檚 how an application would call this stored procedure using the SQL Server .NET provider:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=mybank;uid=sa;pwd=");

try聽{
聽聽聽聽conn.Open聽();
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand聽("proc_TransferFunds",聽conn);
聽聽聽聽cmd.CommandType聽=聽CommandType.StoredProcedure;
聽聽聽聽cmd.Parameters.Add聽("@amount",聽1000);
聽聽聽聽cmd.Parameters.Add聽("@from",聽1111);
聽聽聽聽cmd.Parameters.Add聽("@to",聽2222);
聽聽聽聽cmd.ExecuteNonQuery聽();
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Handle聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

Notice how much simpler the code is. All the application has to do is create a SqlCommand object containing the stored procedure鈥檚 name, set the CommandType property to CommandType.StoredProcedure, and initialize the Parameters collection with values representing the stored procedure鈥檚 input parameters. It鈥檚 that easy.

The code would change only slightly if it targeted the OLE DB .NET provider. The connection string would change, and SqlConnection and SqlCommand would become OleDbConnection and OleDbCommand. Nothing else would change. However, a gotcha is lurking just beneath the surface. As it does for parameterized commands, the OLE DB .NET provider requires that the order of the parameters in the Parameters collection be consistent with the order in which the parameters are defined in the stored procedure. Changing the order of the Add method calls would render the stored procedure useless with the OLE DB .NET provider (or worse yet, might do the opposite of what you intended by reversing the account numbers). The SQL Server .NET provider, on the other hand, couldn鈥檛 care less about parameter order.

What about stored procedures that return data in output parameters? ADO.NET supports them too. Here鈥檚 a simple stored procedure that takes an account ID as input and returns the account鈥檚 current balance in an output parameter named @Balance. It also returns an integer value indicating whether the call succeeded. A return value of 0 means the call succeeded, while -1 means it did not:

CREATE聽PROCEDURE聽proc_GetBalance
聽聽聽聽@ID聽char聽(10),
聽聽聽聽@Balance聽money聽OUTPUT
AS
聽聽聽聽SELECT聽@Balance聽=聽Balance聽FROM聽Accounts聽WHERE聽Account_ID聽=聽@ID
聽聽聽聽IF聽@@ROWCOUNT聽=聽1
聽聽聽聽聽聽聽聽RETURN聽0
聽聽聽聽ELSE
聽聽聽聽BEGIN
聽聽聽聽聽聽聽聽SET聽@Balance聽=聽0
聽聽聽聽聽聽聽聽RETURN聽-1
聽聽聽聽END
GO

The following code sample uses the SQL Server .NET provider to call proc_GetBalance and retrieve both the return value and the output parameter:

SqlConnection聽conn聽=聽new聽SqlConnection
聽聽聽聽("server=localhost;database=mybank;uid=sa;pwd=");

try聽{
聽聽聽聽SqlCommand聽cmd聽=聽new聽SqlCommand聽("proc_GetBalance",聽conn);
聽聽聽聽cmd.CommandType聽=聽CommandType.StoredProcedure;
聽聽聽聽cmd.Parameters.Add聽("@id",聽1111);

聽聽聽聽SqlParameter聽bal聽=
聽聽聽聽聽聽聽聽cmd.Parameters.Add聽("@balance",聽SqlDbType.Money);
聽聽聽聽bal.Direction聽=聽ParameterDirection.Output;

聽聽聽聽SqlParameter聽ret聽=聽cmd.Parameters.Add聽("@return",聽SqlDbType.Int);
聽聽聽聽ret.Direction聽=聽ParameterDirection.ReturnValue;

聽聽聽聽cmd.ExecuteNonQuery聽();

聽聽聽聽int聽retval聽=聽(int)聽ret.Value;
聽聽聽聽decimal聽balance聽=聽(decimal)聽bal.Value;
}
catch聽(SqlException聽ex)聽{
聽聽聽聽//聽TODO:聽Catch聽the聽exception
}
finally聽{
聽聽聽聽conn.Close聽();
}

The key here is setting the Direction property of the SqlParameter that represents the output parameter to ParameterDirection.Output and the Direction property of the SqlParameter that represents the return value to ParameterDirection.ReturnValue. Only one parameter can be designated as a return value, but several can be marked as output parameters. (By the way, you can name the parameter that represents the return value anything you want; it doesn鈥檛 have to be named @Return.) On return, the application that called the stored procedure retrieves the output by reading it from the parameters鈥?Value properties.