Questions That Should Be Asked More Frequently | Microsoft ADO.NET (Core Reference) (PRO-Developer)

Using Command Objects in Code

Command objects let you execute many different types of queries. Some Command objects retrieve data in the form of a result set, and others modify the content or structure of the data store. Let's look at how to create Command objects and use them to perform a variety of tasks.

Creating a Command Object

You can create a Command object in three ways. The first way is to simply create a new instance of an object using the new keyword and then set the appropriate properties. Or, you can use one of the available constructors to specify the query string and a Connection object. The third way is to call the CreateCommand method of the Connection object (which we examined in Chapter 3), as shown here:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName FROM Customers" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As OleDbCommand cmd = cn.CreateCommand() cmd.CommandText = strSQL cmd = New OleDbCommand() cmd.CommandText = strSQL cmd.Connection = cn cmd = New OleDbCommand(strSQL, cn)

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName FROM Customers"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd; cmd = cn.CreateCommand(); cmd.CommandText = strSQL; cmd = new OleDbCommand(); cmd.CommandText = strSQL; cmd.Connection = cn; cmd = new OleDbCommand(strSQL, cn);

Executing a Non-Row-Returning Query

Queries that don't return a result set are generally referred to as action queries—a term we'll use occasionally in this text. There are two main categories of action queries:

Data manipulation language (DML) queries Also known as query-based updates (QBUs), these modify the contents of your database. Here are a few examples:

UPDATE Customers SET CompanyName = 'NewCompanyName'        WHERE CustomerID = 'ALFKI' INSERT INTO Customers (CustomerID, CompanyName)        VALUES ('NewID', 'NewCustomer') DELETE FROM Customers WHERE CustomerID = 'ALFKI'

Data definition language (DDL) queries These modify the structure of your database, as shown in the following examples:

CREATE TABLE Table1 (Field1 int NOT NULL                       CONSTRAINT PK_Table1 PRIMARY KEY,                       Field2 varchar(32)) ALTER VIEW View1 AS SELECT Field1, Field2 FROM Table1 DROP PROCEDURE StoredProcedure1

To execute an action query, you create a Command object, set its Connection property to an open connection, set its CommandText property to the query string you want to submit, and call the Command's ExecuteNonQuery method, as shown here:

Visual Basic .NET

Dim cn As New OleDbConnection() cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                       "Initial Catalog=Northwind;Trusted_Connection=Yes;" cn.Open() Dim cmd As OleDbCommand = cn.CreateCommand() cmd.CommandText = "UPDATE Customers SET CompanyName = 'NewCompanyName'" & _                   " WHERE CustomerID = 'ALFKI'" cmd.ExecuteNonQuery()

Visual C# .NET

OleDbConnection cn = new OleDbConnection(); cn.ConnectionString = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                       "Initial Catalog=Northwind;Trusted_Connection=Yes;"; cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "UPDATE Customers SET CompanyName = 'NewCompanyName' " +                    "WHERE CustomerID = 'ALFKI'"; cmd.ExecuteNonQuery();


	Despite what the ExecuteNonQuery method's name implies, action queries are valid queries. They simply don't return rows. I'm not entirely sure why the method is called ExecuteNonQuery. (I just work here.)

Executing an action query is often only half the battle. When you execute the following query, there are two possible outcomes, success or failure:

CREATE TABLE NewTable (NewTableID int NOT NULL                        CONSTRAINT PK_NewTable PRIMARY KEY,                        OtherField varchar(32))

The query either successfully creates your new table or fails, perhaps because a table of the same name already exists, you didn't use the right syntax for the query, or you don't have an open connection to your database. The point here is that if you execute the query and it doesn't generate an error, you successfully created your new table.

With action queries that are designed to modify or delete an existing row, you need to do more than simply execute the query successfully. Let's take another look at the query we executed in a code snippet on the previous page (to change the company name for a specific customer):

UPDATE Customers SET CompanyName = 'NewCompanyName'          WHERE CustomerID = 'ALFKI'

In some cases, executing this query might not modify that customer's company name—for example, if another user has deleted this row from your table. The database will execute the query, but because no rows satisfy the criteria in the WHERE clause, the query will not modify any rows. To the database, this outcome does not constitute failure.

So, how can you tell whether the query modified one row? If you submit this query in a tool such as Microsoft SQL Server Query Analyzer, you'll see a message like this one:

(1 row(s) affected)

Keep in mind that if the action query doesn't modify any rows because no rows satisfied the criteria in the WHERE clause, no error occurs.

The Command lets you retrieve this information by returning the number of rows affected by your query as the return value of the ExecuteNonQuery method, as shown here:

Visual Basic .NET

Dim strConn As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As OleDbCommand = cn.CreateCommand() cmd.CommandText = "UPDATE Customers SET CompanyName = 'NewCompanyName'" & _                   " WHERE CustomerID = 'ALFKI'" Dim intRecordsAffected As Integer = cmd.ExecuteNonQuery() If intRecordsAffected = 1 Then     Console.WriteLine("Update succeeded") Else     'Assume intRecordsAffected = 0     Console.WriteLine("Update failed") End If

Visual C# .NET

string strConn; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "UPDATE Customers SET CompanyName = 'NewCompanyName' " +                    "WHERE CustomerID = 'ALFKI'"; int intRecordsAffected = cmd.ExecuteNonQuery(); if (intRecordsAffected == 1)     Console.WriteLine("Update succeeded"); else     //Assume intRecordsAffected = 0     Console.WriteLine("Update failed");

In the code, we assume that if the query didn't modify one row, it modified zero rows. But there are other possible return values from ExecuteNonQuery. If you execute anything other than a DML query, ExecuteNonQuery will return -1. There are also situations in which a DML query will modify more than one row.

However, in the code snippet, we used the table's primary key field in the WHERE clause. Because the Customer table uses the CustomerID field as its primary key, no two rows can have the same value for the CustomerID field. We therefore know that the query cannot modify more than one row.

Using a DataReader Object to Examine the Results of a Query

What if you want to execute a query that returns a result set? The Command object has an ExecuteReader method that returns a DataReader object that you can use to examine the results of your query.

The DataReader object is similar to other reader objects in the .NET Framework, such as the XmlReader, TextReader, and StreamReader objects. Each of these objects is an efficient, lightweight object that lets you examine (in a read-only fashion) the data that the object exposes. The TextReader object, for example, has methods that let you read the contents of a text file one line at a time. Similarly, the DataReader exposes properties and methods that let you loop through the results of your query.

Developers who've worked with RDO and ADO or the lower-level ODBC and OLE DB API might be familiar with the term firehose cursor. This is the mechanism that databases use to return the results of a query as quickly as possible. Firehose cursors forgo functionality in favor of performance. Once you've read one row from the result set and moved on to the next row, the previous row is no longer available. The results come at you fast and furious, like water from a fire hose—hence the name. The ADO.NET DataReader lets you access the firehose cursor directly. To create a DataReader, you simply call the ExecuteReader method on a Command object.

Fetching the Results

The following code snippet shows how to examine the results of a simple query using a DataReader object:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "SELECT CustomerID, CompanyName FROM Customers" Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader = cmd.ExecuteReader() While rdr.Read()     Console.WriteLine(rdr("CustomerID") & " - " & rdr("CompanyName")) End While rdr.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +            "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "SELECT CustomerID, CompanyName FROM Customers"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataReader rdr = cmd.ExecuteReader(); while (rdr.Read())     Console.WriteLine(rdr["CustomerID"] + " - " + rdr["CompanyName"]); rdr.Close();

Note that the code calls the Read method before reading the first row of the result set because the first row is not available immediately after you call ExecuteReader. This represents a change from previous object models such as ADO. The DataReader that the Command object returns does not make the first row of data available until you call the Read method.

The first time you call the Read method, the DataReader moves to the first row in the result set. Subsequent calls to the Read method move to the next row. The method also returns a Boolean value to indicate whether the DataReader had another row available. So, if Read returns True, the DataReader moved to the next available row. When the Read method returns False, you've reached the end of the results.

Fetching Faster

The DataReader has a default parameterized property named Item. The preceding code snippet implicitly used the Item property to access the values stored in the CustomerID and CompanyName columns in the result set. This code is inefficient, however. We can improve the performance of the code in two ways.

Using ordinal-based lookups

We supplied the name of the column in our code snippet. In order to return the value stored in that column, the DataReader has to locate the column in its internal structure based on the string we supplied. Remember that in our code snippet, we ask the DataReader to perform that string-based lookup for each row in the result set. Supplying the index, or ordinal, for the column will improve the performance of our code.

This coding technique can be applied to nearly all objects that expose collections. I've recommended this technique to many developers who were writing ADO code and looking for ways to improve performance. Most developers have agreed that this technique would improve performance, but some have hesitated for fear it might limit their flexibility.

The order of the columns in your result set won't change unless you change the query string or you make a change to the structure of your database object (table, view, or stored procedure) and you're retrieving all columns that your database object returns. In the vast majority of applications, you can hard-code the index values for each column into your application without encountering problems.

However, you might encounter situations where you'll know the column name but not its index. The DataReader offers an elegant way to determine a column's index based on its name. The GetOrdinal method accepts a string that represents the column name and returns an integer to denote that column's ordinal. This method is a welcome addition to the ADO.NET object model because it can help you improve performance without losing the flexibility that comes with string-based lookups.

The following code snippet improves on our original DataReader code snippet. It uses the GetOrdinal method to get the ordinal values for the two columns we want to examine and then uses those values to examine the contents of each row. This improves performance because we perform a string-based search of the collection only once per column. In our original code snippet, we performed the string-based search each time we fetched data from a column.

Visual Basic .NET

... Dim rdr As OleDbDataReader = cmd.ExecuteReader() Dim intCustomerIDOrdinal As Integer = rdr.GetOrdinal("CustomerID") Dim intCompanyNameOrdinal As Integer = rdr.GetOrdinal("CompanyName") While rdr.Read()     Console.WriteLine(rdr(intCustomerIDOrdinal) & " - " & _                       rdr(intCompanyNameOrdinal)) End While rdr.Close()

Visual C# .NET

... OleDbDataReader rdr = cmd.ExecuteReader(); int intCustomerIDOrdinal = rdr.GetOrdinal("CustomerID"); int intCompanyNameOrdinal = rdr.GetOrdinal("CompanyName"); while (rdr.Read())     Console.WriteLine(rdr[intCustomerIDOrdinal] + " - " +                        rdr[intCompanyNameOrdinal]); rdr.Close();

But we're not done yet...

Using the appropriate type-specific Get method

The DataReader also exposes a series of methods that return data in the different .NET Framework data types (such as string, 32-bit integer, decimal, and double). Currently, our code snippet implicitly uses the Item property, which returns the contents of the specified column in the generic Object data type. To display the value stored in the console window, the console has to convert the generic object to a string. Because the CustomerID and CompanyName columns contain string data, we can use the GetString method of the DataReader to return the contents of the columns as a string, as shown here:

Visual Basic .NET

... Dim rdr As OleDbDataReader = cmd.ExecuteReader() Dim intCustomerIDOrdinal As Integer = rdr.GetOrdinal("CustomerID") Dim intCompanyNameOrdinal As Integer = rdr.GetOrdinal("CompanyName") While rdr.Read()     Console.WriteLine(rdr.GetString(intCustomerIDOrdinal) & " - " & _                       rdr.GetString(intCompanyNameOrdinal)) End While rdr.Close()

Visual C# .NET

... OleDbDataReader rdr = cmd.ExecuteReader(); int intCustomerIDOrdinal = rdr.GetOrdinal("CustomerID"); int intCompanyNameOrdinal = rdr.GetOrdinal("CompanyName"); while (rdr.Read())     Console.WriteLine(rdr.GetString(intCustomerIDOrdinal) + " - " +                        rdr.GetString(intCompanyNameOrdinal)); rdr.Close();

You should always use the type-specific Get method that corresponds to the data returned by the column in the result set. Let's say we want to query the sample Northwinddatabase for the OrderID and ProductID columns in the Order Details table and display the results in a list box. Even though the list box will display the data as strings, we need to use the GetInt32 method to fetch the contents of each of the columns. After we retrieve the data into an integer, we can convert the data to a string and display it in the list box.

Fetching Multiple Results

Some databases, such as SQL Server, allow you to execute a batch of queries that return multiple results. Let's say we want to issue the following query against the sample Northwind database:

SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers; SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders; SELECT OrderID, ProductID, Quantity, UnitPrice FROM [Order Details]

In our previous DataReader code snippets, we looped through the results of our query until the Read method returned False. Using that same code with a batch query will loop through the results of the only first query in the batch.

The DataReader exposes a NextResult method that lets you move to the results of the next row-returning query. The NextResult method is similar to the Read method in that it returns a Boolean value to indicate whether there are more results. However, unlike with the Read method, you should not call this method initially.

When the Read method returns False, you can check to see whether there are additional results to fetch by calling the NextResult method. When the NextResult method returns False, there are no more result sets. The following code snippet shows how to use the NextResult method to fetch the results of a batch query:

Visual Basic .NET

... cn.Open() Dim strSQL As String strSQL = "SELECT CustomerID, CompanyName FROM Customers;" & _          "SELECT OrderID, CustomerID FROM Orders;" & _          "SELECT OrderID, ProductID FROM [Order Details]" Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader = cmd.ExecuteReader() Do     Do While rdr.Read()         Console.WriteLine(rdr(0) & " - " & rdr(1))     Loop     Console.WriteLine() Loop While rdr.NextResult()

Visual C# .NET

... cn.Open(); string strSQL = "SELECT CustomerID, CompanyName FROM Customers;" +                 "SELECT OrderID, CustomerID FROM Orders;" +                 "SELECT OrderID, ProductID FROM [Order Details]"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataReader rdr = cmd.ExecuteReader(); do {     while (rdr.Read())         Console.WriteLine(rdr[0] + " - " + rdr[1]);     Console.WriteLine(); } while (rdr.NextResult());

Executing Batches of Action Queries

Developers often encountered a problem when they used ADO with SQL Server to retrieve the result set generated by a stored procedure. If you call a SQL Server stored procedure using the SQL Server OLE DB provider and the stored procedure executes an action query prior to the row returning query, your Recordset will be marked as closed instead of containing the results of the row-returning query.

This behavior is actually by design. The closed Recordset corresponds to the action query. More precisely, it corresponds to the informational message "n row(s) affected" that the query returns. You have to call NextRecordset to move to the results of the next query. Or you can add the statement SET NOCOUNT ON in your stored procedure to suppress these messages, which allows ADO to move immediately to the results of the first row-returning query.

This behavior is not restricted to stored procedures. You can create a similar situation by executing batch queries. Let's take a look at such a batch query with ADO 2.x and Visual Basic "Classic."

Visual Basic "Classic"

Dim cn As ADODB.Connection, rs As ADODB.Recordset Dim strConn As String, strSQL As String Dim intRecordsAffected As Integer strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Set cn = New ADODB.Connection cn.Open strConn strSQL = "INSERT INTO Customers ...;" & _          "SELECT CustomerID, CompanyName FROM Customers WHERE ...;" & _          "UPDATE Customers SET CompanyName = ... WHERE ...;" & _          "SELECT CustomerID, CompanyName FROM Customers WHERE ..." Set rs = cn.Execute(strSQL, intRecordsAffected, adCmdText) Do Until rs Is Nothing     Debug.Print "rs.State = " & rs.State & vbTab & _                 "intRecordsAffected = " & intRecordsAffected     Set rs = rs.NextRecordset(intRecordsAffected) Loop

Initially, the Recordset is closed and the intRecordsAffected variable contains 1. These results correspond to the INSERT query, which does not return rows and modifies one record in the database. Once we call NextRecordset, the Recordset is open and contains the results of the first SELECT query. Because the SELECT query doesn't modify any records in the database, the intRecordsAffected variable returns -1. The second call to NextRecordset returns a closed Recordset, and intRecordsAffected now contains the number of records affected by the UPDATE query. Calling NextRecordset again returns the results of the second SELECT query and sets intRecordsAffected to -1. The last call to NextRecordset returns a Recordset set to Nothing, indicating that there are no more results to process.

ADO.NET handles this same batch query differently. The ADO.NET development team saw this scenario as one of the top developer frustrations with ADO. To simplify the process of working with batch queries, the DataReader automatically moves you to the results of the first row-returning query. I think most developers will be pleased with this change in behavior. Unfortunately, it comes with a trade-off.

As a result of this change, the DataReader does not provide a way for you to determine the number of rows modified by each individual action query. The RecordsAffected property on the DataReader acts as a running total. Probably the best way to explain this behavior is by showing an example:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "INSERT INTO Customers ...;" & _          "SELECT CustomerID, CompanyName FROM Customers WHERE ...;" & _          "UPDATE Customers SET CompanyName = ... WHERE ...;" & _          "SELECT CustomerID, CompanyName FROM Customers WHERE ..." Dim cmd As New OleDbCommand(strSQL, cn) Dim rdr As OleDbDataReader = cmd.ExecuteReader() Do     Console.WriteLine("RecordsAffected = " & rdr.RecordsAffected)     Do While rdr.Read()         Console.WriteLine(vbTab & rdr.GetName(0) & " - " & rdr.GetValue(0))     Loop     Console.WriteLine() Loop While rdr.NextResult()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "INSERT INTO Customers ...;" +          "SELECT CustomerID, CompanyName FROM Customers WHERE ...;" +          "UPDATE Customers SET CompanyName = ... WHERE ...;" +          "SELECT CustomerID, CompanyName FROM Customers WHERE ..."; OleDbCommand cmd = new OleDbCommand(strSQL, cn); OleDbDataReader rdr = cmd.ExecuteReader(); do {     Console.WriteLine("RecordsAffected = " + rdr.RecordsAffected);     while (rdr.Read())         Console.WriteLine("\t" + rdr.GetName[0].ToString() +                            " - " + rdr.GetValue[0].ToString());     Console.WriteLine(); } while (rdr.NextResult());

You probably noticed that the ADO.NET code looks similar to the ADO code. The results, however, are slightly different. When we create the DataReader by calling the ExecuteReader method on the Command, the DataReader is ready to return the results of the first SELECT query immediately. When we call NextResult, we move to the results of the second SELECT query. The second call to NextResult returns False because there are no more row-returning queries to process, and we leave the loop.

The other major change in behavior from ADO is the behavior of the RecordsAffected property of the DataReader. Let's assume that the INSERT query and the UPDATE query each modify one record in the database. The RecordsAffected property will return the sum of the records affected by all action queries preceding the row-returning query that the DataReader is currently fetching.

So, when the ExecuteReader method returns the DataReader, its RecordsAffected property will return 1. After we call the NextResult method, the RecordsAffected property will return 2.

Keep in mind that non-DML action queries (such as CREATE PROCEDURE and DROP TABLE) return -1 for the number of records they affect because they're not designed to affect records.

If you need to determine the number of rows affected by individual queries using ADO.NET, split the batch into its individual queries and execute each query separately.

Closing a DataReader

In the ADO.NET object model, it's vitally important that you close your DataReader objects as quickly as possible. As of this writing, a Connection object that has an open DataReader is considered blocked. If you try to open a second DataReader before closing the first one, you'll receive an exception whose text indicates that the operation "requires an open and available connection."

Developers who have some experience with ADO might be surprised by this restriction, but those who've used RDO might not. Different Microsoft data access technologies have handled this scenario differently.

If you try to open two firehose cursors against a SQL Server database using ADO, everything will just work and you won't receive an error. This is because the OLE DB specification states that when the current connection is blocked, the OLE DB provider will perform the requested action on a new connection.

RDO developers might recognize the error message "Connection is busy with results from another hstmt." ODBC does not do any behind-the-scenes work to try to help you out. If you try to use a connection that's busy, you'll simply receive an error message.

Which of these approaches (raising an error or performing the desired action on a new connection) is better? Developers, both inside and outside of Microsoft, can't seem to agree. In fact, each successive Microsoft data access technology has handled the scenario differently than its predecessor: VBSQL raises an error, DAO/Jet creates a new connection, RDO raises an error, ADO creates a new connection, and ADO.NET raises an error. As they say in New England, "If you don't like the weather, just wait a while."


	I believe VBSQL predates DAO/Jet, but carbon dating is difficult and the results are not 100 percent accurate.

The DataReader is built for performance. Regardless of the restriction that an open DataReader blocks a Connection, you should pull the results of your query off the wire as quickly as possible after issuing the query. If you need to move back and forth between the results of separate queries, you should use a DataSet or consider storing the results of your queries in a business object of some sort.

Executing a Query That Returns a Single Value

What if you want to execute a query and retrieve a single cell (one row, one column) of data? Here are two examples of queries that return a single value:

SELECT COUNT(*) FROM Customers

SELECT CompanyName FROM Customers WHERE CustomerID = 'ALFKI'

Using a DataReader or a DataSet to retrieve this single value is probably overkill. The Command object has a method specifically designed for such queries: ExecuteScalar. This method returns the value through the generic object data type, which you can then convert to the desired data type, as shown here:

Visual Basic .NET

Dim strConn As String = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _                         "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As OleDbCommand = cn.CreateCommand() cmd.CommandText = "SELECT COUNT(*) FROM Customers" Dim intCustomers As Integer = CInt(cmd.ExecuteScalar()) cmd.CommandText = "SELECT CompanyName FROM Customers " & _                   "WHERE CustomerID = 'ALFKI'" Dim strCompanyName As String = Convert.ToString(cmd.ExecuteScalar)

Visual C# .NET

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +                  "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "SELECT COUNT(*) FROM Customers"; int intCustomers = Convert.ToInt32(cmd.ExecuteScalar()); cmd.CommandText = "SELECT CompanyName FROM Customers " +                   "WHERE CustomerID = 'ALFKI'"; string strCompanyName = Convert.ToString(cmd.ExecuteScalar());

The ExecuteScalar method is a great example of a feature that offers a better solution to a coding scenario that you might not have even realized was fairly inefficient.

Executing a Parameterized Query

Let's say you're building an application that lets a user examine the orders that your customers have placed. If you have 20 customers, you won't want to write 20 separate functions to return the orders for each specific customer. Instead, you should build a parameterized function that accepts information about the customer and returns the orders for that customer. Similarly, you can build queries that accept parameters. Let's look at the query that you'd use in the parameterized function to return the orders for a particular customer:

SELECT OrderID, CustomerID, EmployeeID, OrderDate        FROM Orders WHERE CustomerID = ?

The question mark is a parameter marker—the standard way of denoting a parameter in a query.

The SQL Server .NET data provider doesn't support the generic parameter marker ?; it requires named parameters that use the @ prefix instead. In the following query, @CustomerID is the named parameter:

    SELECT OrderID, CustomerID, EmployeeID, OrderDate            FROM Orders WHERE CustomerID = @CustomerID

We'll discuss executing parameterized queries with the SQL Server .NET data provider in more detail in Appendix A.

By simply changing the value of the parameter, we can use this query to retrieve the orders for any customer. The ADO.NET Parameter object is the structure you create to store the parameter information. The Command object also has a Parameters collection. The following code snippet creates a Command, adds a Parameter to its Parameters collection, supplies a value for the Parameter, and executes the Command to pull back information for the orders for a particular customer:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cn As New OleDbConnection(strConn) cn.Open() strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _          "FROM Orders WHERE CustomerID = ?" Dim cmd As New OleDbCommand(strSQL, cn) cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5) cmd.Parameters(0).Value = "ALFKI" Dim rdr As OleDbDataReader = cmd.ExecuteReader()

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); strSQL = "SELECT OrderID, CustomerID, EmployeeID, OrderDate " +          "FROM Orders WHERE CustomerID = ?"; OleDbCommand cmd = new OleDbCommand(strSQL, cn); cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5); cmd.Parameters[0].Value = "ALFKI"; OleDbDataReader rdr = cmd.ExecuteReader();

Once you've executed the query using the ExecuteReader method, you can fetch the results of the query using the DataReader object, just as you do with standard queries that don't use parameters.

Using parameterized queries can greatly simplify your programming. Changing the value of a parameter is much easier than programmatically concatenating query strings, especially because when you supply values in a parameter you don't need to worry about delimiting them. For example, if you wanted to search for an employee whose last name is O'Leary without using parameters, you would need to build a query string that looks something like this:

SELECT EmployeeID, LastName, FirstName FROM Employees        WHERE LastName = 'O''Leary'

Because you need to surround the literal value you're searching for with quotes, you have to replace the single quotes in the value with two consecutive single quotes. Developers who've tried to put double quotes into a string in their code can relate.

Calling a Stored Procedure

Let's say we have a stored procedure that can return a row of data. Here's an example of a SQL Server stored procedure designed to do just that:

CREATE PROCEDURE GetCustomer (@CustomerID nchar(5)) AS     SELECT CustomerID, CompanyName, ContactName, ContactTitle            FROM Customers WHERE CustomerID = @CustomerID RETURN


	Some databases, such as Oracle, cannot return a result set from a stored procedure call in this fashion. For more information on fetching a result set from an Oracle stored procedure using ADO.NET, please see the Microsoft Knowledge Base.

How do we call this stored procedure from a Command? One option is to use the Command object's CommandType property. You can set this property to any value in the CommandType enumeration: Text, TableDirect, or StoredProcedure. The property is set to Text by default. Setting CommandType to StoredProcedure tells the Command that you're calling a stored procedure. The Command object will combine the value stored in its CommandText property with the information in its Parameters collection to generate the syntax to call your stored procedure, as shown here:

Visual Basic .NET

... Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As OleDbCommand = cn.CreateCommand() With cmd     .CommandText = "GetCustomer"     .CommandType = CommandType.StoredProcedure     .Parameters.Add("@CustomerID", OleDbType.WChar, 5)     .Parameters(0).Value = "ALFKI" End With Dim rdr As OleDbDataReader = cmd.ExecuteReader() If rdr.Read() Then     Console.WriteLine(rdr("CompanyName")) Else     Console.WriteLine("No customer found") End If rdr.Close() cn.Close()

Visual C# .NET

... OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "GetCustomer"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5); cmd.Parameters[0].Value = "ALFKI"; OleDbDataReader rdr = cmd.ExecuteReader(); if (rdr.Read())     Console.WriteLine(rdr["CompanyName"]); else     Console.WriteLine("No customer found"); rdr.Close(); cn.Close();

The standard way to call the stored procedure is to use the following syntax:

{? = CALL MyStoredProc(?, ?, ?)}

The initial parameter marker represents the return value of the procedure call and can be omitted if you're not going to use the value returned. Leaving off the return parameter changes the query's syntax to this:

{CALL GetCustomer(?)}

I prefer using this syntax in my code rather than relying on the CommandType property. If you want to query a table, view, or stored procedure that contains odd characters such as spaces in its name, you must surround the object name with delimiters. With an OleDbCommand object, setting the CommandType property to TableDirect or StoredProcedure doesn't surround the object name with delimiters in such cases. You need to add the delimiters yourself. This behavior is not consistent for all Command objects. For example, the SqlCommand object will correctly delimit object names automatically. Of course, if you don't put spaces in your table and stored procedure names, this won't be an issue for you (nudge, nudge).

My advice is to avoid relying on the CommandType property and instead use the appropriate syntax for your query in the CommandText property, as shown here:

Visual Basic .NET

Dim cmd As New OleDbDataAdapter() cmd.CommandText = "{CALL GetCustomer(?)}" cmd.CommandType = CommandType.Text

Visual C# .NET

OleDbDataAdapter cmd = new OleDbDataAdapter(); cmd.CommandText = "{CALL GetCustomer(?)}"; cmd.CommandType = CommandType.Text;

Developers with some SQL Server experience might be used to using the EXEC syntax for calling stored procedures in tools such as Query Analyzer. You're welcome to use this syntax with your Command objects, but keep in mind that not all databases support this syntax. You might need to change your syntax slightly if you need to query a different type of database.

Retrieving Data from Output Parameters

Not all stored procedures return information through a result set. Many procedures return information through output parameters. Let's say our SQL Server GetCustomer stored procedure looks like the following instead:

CREATE PROCEDURE GetCustomer (@CustomerID nchar(5),                               @CompanyName nvarchar(40) OUTPUT,                                @ContactName nvarchar(30) OUTPUT,                               @ContactTitle nvarchar(30) OUTPUT) AS     SELECT @CompanyName = CompanyName, @ContactName = ContactName,            @ContactTitle = ContactTitle            FROM Customers WHERE CustomerID = @CustomerID     IF @@ROWCOUNT = 1         RETURN 0     ELSE         RETURN -1

How can we use a Command to retrieve data from the output parameters? The Parameter object has a Direction property that accepts values from the ParameterDirection enumeration: ReturnValue, Input, InputOutput, and Output. The default for the property is Input. In order to retrieve information from this stored procedure, we need to set the Direction property on the parameters that aren't input-only.

The stored procedure uses the return parameter to indicate success or failure. So, in our code snippet we'll examine the value of the return parameter to determine whether we successfully located the desired customer in the table:

Visual Basic .NET

... Dim cn As New OleDbConnection(strConn) cn.Open() Dim cmd As OleDbCommand = cn.CreateCommand() With cmd     .CommandText = "{? = CALL GetCustomer(?, ?, ?, ?)}"     .Parameters.Add("@RetVal", OleDbType.Integer)     .Parameters.Add("@CustomerID", OleDbType.WChar, 5)     .Parameters.Add("@CompanyName", OleDbType.VarWChar, 40)     .Parameters.Add("@ContactName", OleDbType.VarWChar, 30)     .Parameters.Add("@ContactTitle", OleDbType.VarWChar, 30)     .Parameters("@ContactTitle").Direction = ParameterDirection.Output     .Parameters("@RetVal").Direction = ParameterDirection.ReturnValue     .Parameters("@CustomerID").Value = "ALFKI"     .Parameters("@CompanyName").Direction = ParameterDirection.Output     .Parameters("@ContactName").Direction = ParameterDirection.Output     .ExecuteNonQuery()     If Convert.ToInt32(.Parameters("@RetVal").Value) = 0 Then         Console.WriteLine(.Parameters("@CompanyName").Value)     Else         Console.WriteLine("Customer not found")     End If End With

Visual C# .NET

... OleDbConnection cn = new OleDbConnection(strConn); cn.Open(); OleDbCommand cmd = cn.CreateCommand(); cmd.CommandText = "{? = CALL GetCustomer(?, ?, ?, ?)}"; cmd.Parameters.Add("@RetVal", OleDbType.Integer); cmd.Parameters.Add("@CustomerID", OleDbType.WChar, 5); cmd.Parameters.Add("@CompanyName", OleDbType.VarWChar, 40); cmd.Parameters.Add("@ContactName", OleDbType.VarWChar, 30); cmd.Parameters.Add("@ContactTitle", OleDbType.VarWChar, 30); cmd.Parameters["@ContactTitle"].Direction = ParameterDirection.Output; cmd.Parameters["@RetVal"].Direction = ParameterDirection.ReturnValue; cmd.Parameters["@CustomerID"].Value = "ALFKI"; cmd.Parameters["@CompanyName"].Direction = ParameterDirection.Output; cmd.Parameters["@ContactName"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); if (Convert.ToInt32(cmd.Parameters["@RetVal"].Value) == 0)     Console.WriteLine(cmd.Parameters["@CompanyName"].Value); else     Console.WriteLine("Customer not found");

Executing a Query Within a Transaction

The Command object has a Transaction property that you must set in order to execute your Command within a Transaction. In the previous chapter, you saw how to create a Transaction object using the Command object's BeginTransaction method. The following code snippet shows how you can execute a Command on that Transaction.

Visual Basic .NET

... cn.Open() Dim txn As OleDbTransaction = cn.BeginTransaction() Dim strSQL As String = "INSERT INTO Customers (...) VALUES (...)" Dim cmd As New OleDbCommand(strSQL, cn, txn) Dim intRecordsAffected As Integer = cmd.ExecuteNonQuery() If intRecordsAffected = 1 Then     Console.WriteLine("Update succeeded")     txn.Commit() Else     'Assume intRecordsAffected = 0     Console.WriteLine("Update failed")     txn.Rollback() End If

Visual C# .NET

... cn.Open(); OleDbTransaction txn = cn.BeginTransaction(); string strSQL = "INSERT INTO Customers (...) VALUES (...)"; OleDbCommand cmd = new OleDbCommand(strSQL, cn, txn); int intRecordsAffected = cmd.ExecuteNonQuery(); if (intRecordsAffected == 1) {     Console.WriteLine("Update succeeded");     txn.Commit(); } else {     //Assume intRecordsAffected = 0     Console.WriteLine("Update failed");     txn.Rollback(); }

Don't forget to call the Commit or Rollback method on the Transaction object (depending on whether you want to save or discard the actions performed within the transaction).