Connections, Commands, and DataReaders

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 s 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 sa 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 t 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 s 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 s 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 t 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 re 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 s 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 s 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 in this case, SQLOLEDB, which is Microsoft s 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 t physically open a connection to the database. Calling the object s 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 t 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 s not open isn t 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 s Titles 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 s CommandTimeout property to specify the number of seconds you re 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 re executing.

The ExecuteNonQuery Method

The ExecuteNonQuery method is a vehicle for executing INSERT, UPDATE, DELETE, and other SQL commands that don t return values for 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 1.

Here s an example that uses ExecuteNonQuery to add a record to the Pubs database s Titles 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 MyDatabase with ExecuteNonQuery, simply change the command text to create 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 s 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 Advance field in the Pubs database is of type money, and the SQL money data type translates naturally into the .NET Framework s 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 Logo field of the Pubs database s Pub_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: How 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 s Pub_info table and includes a BLOB in the record s Logo 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 s defined and initialized separately. Here s 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 s 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 Publishers table containing the Pub_id 9937. If you don t, the INSERT will fail because of a foreign key constraint that stipulates that publisher IDs in the Pub_info table also appear in the Publishers table.

Chapter 10 contains another excellent example of ExecuteScalar usage. That chapter s 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 s 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 s 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 s 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 s Title field. Fields can be referenced by name or by numeric index (0-based, of course).

You don t have to know a database s 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 Titles 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 s 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 s value. GetValue returns a generic Object, but it s 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 Titles table s Advance field. The WHERE clause in the SELECT command skips records whose Advance field is null. The call to GetOrdinal is required because GetDecimal accepts only integer indexes. GetOrdinal does exactly the opposite of GetName 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 s 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 t 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 t need to call Close on a Data Reader if you don t intend to reuse the connection, but there s 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 command 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 transactions 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 that is, they re 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 ve created a SQL Server database named MyBank that contains a table named Accounts. 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 Balance and Account_ID. Here s 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 s isolation level and sometimes does permit a client to read data from an unfinished transaction, but the preceding statement is conceptually accurate nonetheless.) Here s a revised code sample that uses ADO.NET s 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 re updated so that they can t be read or written. Serializable is admittedly too high an isolation level for this simple example, but in the real world, the transaction wouldn t be nearly so simple. At the very least, you d 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 s 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 s 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 parameterized queries ) to code redundant commands, especially commands whose input values come from user input. Here s a parameterized version of the previous section s 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 s 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 s replaceable parameters to the Command object by calling Add on the Command object s Parameters collection. Besides the obvious differences in class names, here s how parameterized command usage differs between the two providers:

• The SQL Server .NET provider requires replaceable parameters to be named; it does not accept ? characters. The OLE DB .NET provider, by contrast, doesn t accept named parameters; it only accepts question marks.

• The SQL Server .NET provider lets you add parameters in any order. The OLE DB .NET provider requires parameters to appear in the Parameters collection in the same order in which they appear in the command.

With regard to that last point, switching the following two statements doesn t 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 s 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 t 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 prepare a method that is, compile it so that it can be used again and again without having to be recompiled ad infinitum. However, you shouldn t 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 re 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 s 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 s 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 s name, set the CommandType property to CommandType.StoredProcedure, and initialize the Parameters collection with values representing the stored procedure s input parameters. It s 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 t care less about parameter order.

What about stored procedures that return data in output parameters? ADO.NET supports them too. Here s a simple stored procedure that takes an account ID as input and returns the account s 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 t 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.