Using Command Objects

for RuBoard

To use a command object, you must first instantiate it. The constructor of command objects is generally overloaded and can accept the statement to execute ( CommandText ), the connection through which the command should be executed ( Connection ), and the transaction to associate with the connection ( Transaction ). Keep in mind that, as you learned yesterday , if the connection object has had its BeginTransaction method called, you must populate the Transaction property of the command or an InvalidOperationException will be thrown. For example, a command will typically be instantiated as follows :

 Dim con As New OleDbConnection( _   "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=nwind.mdb") Dim com As New OleDbCommand("Products", con) com.CommandType = CommandType.TableDirect 

In this case, the OleDbCommand object's constructor is passed the name of a table in the database as well as the OleDbConnection object to use.

When you populate the CommandText property, it can be set to any valid command syntax supported by the provider or data store. Examples include SQL statements, names of stored procedures, names of tables, or simply executable commands in the language of the data store. Typically, of course, the commands take the form of stored procedures, SQL statements, or table names . The type of command is specified using the CommandType property and enumeration and can be set to the value StoredProcedure , Text , or TableDirect . Obviously, StoredProcedure is used when the data store supports procedures or functions on the server, as in SQL Server when CommandText is set to the name of a stored procedure or in Access (Jet) when it's set to the name of a query. The Text value of the CommandType enumeration is used when the command is simply a SQL statement or a data store “specific command. For example, you would use Text when the CommandText is populated with the text SELECT * FROM Titles or when it contains the Transact -SQL command SET DEADLOCK PRIORITY . In both cases, it's the responsibility of the data store or provider to parse the command. As shown in the previous code snippet, CommandType can also be set to TableDirect . This works when using the OleDbCommand object and instructs the provider to retrieve all the rows from the table. However, the SqlCommand object will throw an exception if a command is executed with the CommandType set to TableDirect .

Using Stored Procedures Versus Dynamic SQL

As you might have noticed, most of the examples in this book have relied on stored procedures to encapsulate the SQL used to insert, update, and delete data from the ComputeBooks database. The reason for this is simple. Stored procedures offer abstraction, better performance, and increased security over using dynamic or inline SQL in the command object. For any data store that supports it, stored procedures are typically the recommended approach. The benefits of using stored procedures are outlined in the following list:

• Abstraction. By using stored procedures, you can abstract the SQL from the applications that consume it. This reduces complexity in the application and offers reusability across applications. In addition, developers more familiar with the workings of the particular data store can specialize in writing procedures that are optimized by making sure that they use proper indexes, for example. Simply put, stored procedures can be thought of as the data access API to your data store.

• Performance. In SQL Server 7.0 and 2000, stored procedures offer certain reuse of cached execution plans on the server. When a procedure or any statement is executed on the server, an execution plan is created and placed into the cache. For stored procedures, this execution plan will be reused with each invocation of the procedure until the SQL Server is restarted. Although the execution plans are also cached for dynamic SQL statements, the server must use a matching algorithm to attempt to match each incoming statement with a cached execution plan. This process, referred to as auto-parameterization, is efficient but not foolproof depending on how arguments are passed to the procedure. Therefore, it is recommended that you use sp_executesql on dynamically generated SQL statements if you must use them to ensure that the parameters can be discovered . In addition, stored procedures reduce network traffic because hundreds of bytes in a complicated SQL statement needn't be sent over the wire.

• Security. Because stored procedures offer a modular programming model, users can be granted permission to use them and denied permission to access the underlying tables and views. This allows administrators to ensure that the application can execute only the stored procedures it should be executing.

Of course, there are data stores that don't support stored procedures and cases in which it makes sense to write dynamic SQL. For example, if you're developing a packaged application that must work against several data stores that your clients might use, dynamic SQL makes sense. In these cases, you can certainly use dynamic SQL, even with parameters, as you'll learn later today.

Note

However, even in this case, to access the data most efficiently , you'll want to explore ways of adding a layer of abstraction in order to use procedures where it makes sense. On Day 17, "ADO.NET in the Data Services Tier," you'll learn about the concept of data factories and how they can be used to abstract the SQL and the providers used for an application.

Retrieving Data

Command objects generally support two methods for retrieving data from the data store that are implemented in the IDbCommand interface: ExecuteReader and ExecuteScalar . In addition, providers such as SqlClient extend the functionality by including additional execute methods such as ExecuteXmlReader .

ExecuteReader

The ExecuteReader method is used to return an object that implements the IDataReader interface in order to provide streamed access to the data store. As you'll recall from Day 1, "ADO.NET in Perspective," streamed access is the second programming model, in addition to disconnected or cached access using the DataSet , that ADO.NET supports. After a data reader has been opened, its data is exposed as read-only and accessed in a loop as shown in Listing 10.1 ( assuming that com is the command object shown in the previous snippet).

Listing 10.1 Executing a data reader. This listing uses a command object to execute and traverse a data reader.

 Dim rowData() As Object Dim dr As OleDbDataReader Try   dr = com.ExecuteReader()   Do While dr.Read     dr.GetValues(rowData)     ProcessRow(rowData)   Loop Catch e As OleDbException   ' handle error Finally   If Not dr Is Nothing AndAlso Not dr.IsClosed Then dr.Close()   con.Close() End Try 

Although not shown in Listing 10.1, the connection associated with the command must have been opened before the ExecuteReader method is called; otherwise , an InvalidOperationException will result. (Only when using a data adapter is the connection opened and closed implicitly.) You'll notice in this snippet that the OleDbDataReader is actually instantiated by the ExecuteReader method and so doesn't need to be instantiated with the New operator. Once open , the data reader is traversed using the Read method, as you'll learn about in more detail tomorrow. After the data has been traversed, the Finally block ensures that the data reader is closed and then closes the connection.

Note

The Finally block uses the new VB .NET short-circuited AndAlso operator. It's useful in this case because if dr is Nothing , the expression Not dr.IsClosed would throw an exception. This way, the expression after the AndAlso won't be evaluated if the data reader hasn't been created.

The ExecuteReader method is also overloaded to accept an argument from the CommandBehavior enumeration. This argument influences how the data reader behaves and gives hints as to how the provider might optimize the execution of the command. The enumeration includes the values shown in Table 10.1.

Table 10.1. Command behaviors. The CommandBehavior enumeration is used to allow the provider to optimize the command.

Value	Description
CloseConnection	Closes the connection object associated with the data reader when the data reader is closed.
Default	Same as not using a command behavior. Typically indicates that the data reader can return multiple result sets.
KeyInfo	The query returns column and primary key information and doesn't lock any rows on the data store.
SchemaOnly	The query returns column information only.
SequentialAccess	Allows large columns of binary data to be read as a stream using the GetBytes or GetChars method.
SingleResult	The query will return a single result set.
SingleRow	The query will return a single row for each result set.

As you can see, the command behaviors affect both how the result sets are processed by the provider and what information is returned from the data store. For example, the SingleResult and SingleRow values typically have no effect on the data store but can be used by the provider to optimize the way in which the results are processed, thereby increasing performance. In fact, when using SingleRow , the OleDb provider binds to the result using the OLE DB IRow interface rather than the IRowset interface and exposes only a single row in the data reader. KeyInfo , SchemaOnly , and SequentialAccess , on the other hand, affect what information is returned by the data store and how that information is returned. Using SchemaOnly with the ExecuteReader method of the SqlCommand object prefixes the statement with the SET FMTONLY ON statement, which returns only column information and no rows. Using KeyInfo prefixes the SET NO_BROWSETABLE ON statement in order to return primary key information. We'll discuss the SequentialAccess value in more detail tomorrow because it affects how data is read using a data reader. You can also use more than one behavior in a bitwise combination because the enumeration supports the FlagsAttribute , as illustrated here:

 dr = com.ExecuteReader(CommandBehavior.SingleResult Or CommandBehavior.KeyInfo) 

Perhaps the most interesting of the values shown in Table 10.1 is CloseConnection . This behavior is particularly effective when you create methods that return data readers. This is the case because it allows the calling code to simply close the data reader, and the underlying connection will be closed automatically. Without this behavior, the calling code wouldn't have access to the connection object because it isn't exposed by the data reader. This is the key to writing data access and data factory classes that use data readers. This concept is illustrated in the following code snippets:

 Public Function ReadTitles() As SqlDataReader   ' create the connection and command   return com.ExecuteReader(CommandBehavior.CloseConnection) End Function 

The client code can then simply close the data reader when finished, like so:

 Try   dr = ReadTitles()   ' process the rows Catch e As SqlException   ' handle error Finally   If Not dr Is Nothing AndAlso Not dr.IsClosed Then dr.Close() End Try 

Note that unlike in the snippet shown previously, the Close method of the connection object isn't called because this code has no access to it. However, the connection will be closed and thereby released to the connection pool if pooling is enabled when the data reader is closed.

ExecuteScalar

As shown in Table 10.1, the ExecuteReader method can be passed the combination of the SingleRow and SingleResult behaviors in order to return only the first column of the first row from the result set. Command objects provide an easier way to do this using the ExecuteScalar method.

ExecuteScalar is effective for returning the results of a SQL statement that uses an aggregate, such as the SUM , AVG , MIN , MAX , and COUNT functions. For example, Listing 10.2 shows how ExecuteScalar could be used to return the average price of all titles in the ComputeBooks Titles table:

Tip

Although you can specify SQL statements or execute stored procedures that return multiple rows with ExecuteScalar , you wouldn't want to. Doing so increases the burden on the server when you're only going to use the first column of the first row. As a rule of thumb, your stored procedures or SELECT statements should ask only for data that the application will use.

Listing 10.2 Using ExecuteScalar . This listing returns data from an aggregate function using the ExecuteScalar method.

 Dim avgSql As String = "SELECT AVG(Price) FROM Titles" Dim avgPrice As Decimal Dim com As New SqlCommand(avgSql, con) com.CommandType = CommandType.Text Try   con.Open()   avgPrice = CType(com.ExecuteScalar, Decimal) Catch e As SqlException Finally   con.Close() End Try 

In this case, you'll notice that dynamic SQL is used to specify the SELECT statement. Just as with ExecuteReader , the connection must be opened before ExecuteScalar is called and must be explicitly closed in the Finally block. The ExecuteScalar method isn't overloaded and returns the value as a System.Object , so the CType function is used to cast the value to the appropriate type.

There are occasions when you might want to execute a command that returns multiple single-value, single-column results. Unfortunately, the ExecuteScalar method doesn't support this, so you would need to use a data reader as shown in Listing 10.3.

Listing 10.3 Returning multiple result sets. This listing shows how you would return more than one single-value, single-column result set using a data reader.

 Dim sql As String Dim avgPrice As Decimal Dim orders As Integer Dim dr As SqlDataReader sql = "SELECT AVG(Price) FROM Titles;SELECT COUNT(*) FROM Orders" Dim com As New SqlCommand(avgSql, con) com.CommandType = CommandType.Text dr = com.ExecuteReader() dr.Read() avgPrice = dr.GetDecimal(0) If dr.NextResult() Then   dr.Read()   orders = dr.GetInteger(0) End If 

Caution

Although the documentation says otherwise, you can't use the SingleRow command behavior in this case against SQL Server using either SqlClient or OleDb. Doing so prevents the second result set from being read.

ExecuteXmlReader

The command object is an excellent place for providers to add their own functionality by extending the class to include custom execute functions that return data in a specific way. The SqlClient provider does this by including the ExecuteXmlReader function to return XML produced by the FOR XML statement that was introduced in SQL Server 2000.

Although a complete discussion of the FOR XML syntax is beyond the scope of this book, to use the statement, you simply append the FOR XML clause to a SELECT statement. SQL Server returns the results formatted as XML, based on the mode , which can be RAW , AUTO , or EXPLICIT . The syntax for a SELECT statement with a FOR XML clause looks like this:

 SELECT ... FROM ... [WHERE] FOR XML RAW  AUTO  EXPLICIT [,XMLDATA] [,ELEMENTS] [,BINARY Base64] 

As you might expect, with FOR XML , you can easily transform a result set into XML. However, you can use the clause only in statements that return data directly to a client and therefore can be processed using the ExecuteXmlReader method. For example, you can't use FOR XML in view- or user -defined function (UDF) definitions, nested SELECT statements, stored procedures that manipulate the result set, INSERT statements, or statements that use a COMPUTE BY clause.

Using FOR XML

To give you a brief overview, the three modes of the FOR XML statement are

• XML RAW . XML RAW produces nonhierarchical, generic XML by generating one XML row element for each row that the query returns and mapping each returned column as an XML attribute. XML RAW is most useful when you have generic client code that expects flat XML documents and looks for row elements. Unlike XML AUTO , XML RAW supports the GROUP BY clause and aggregates. All the XML modes let you return the schema by using the XMLDATA argument after the FOR XML clause.

• XML AUTO . XML AUTO produces a hierarchical document by transforming into an element each table that the SELECT clause references. By default, XML AUTO transforms each column into an attribute unless you use the ELEMENTS argument to create sub-elements. Keep in mind that XML AUTO doesn't support the GROUP BY clause; the column order in the SELECT statement determines the attributes' nesting order. XML AUTO lets you use table or column aliases as element or attribute names; however, this mode's default is to use the table or view name as the element or attribute name. You can use the BINARY Base64 argument to return image and binary data in binary base64-encoded format. If you don't use BINARY Base64 , XML AUTO returns a URL that you can query to return binary data.

• XML EXPLICIT . XML EXPLICIT is the most sophisticated and most powerful XML mode. As the name implies, with EXPLICIT mode, you explicitly define the schema for the returned XML by creating a virtual table that SQL Server translates into XML. Because this mode is so flexible, it's particularly good for creating hierarchical documents. EXPLICIT mode enables you to define each column as an attribute or element and even create elements not represented in your database. When you use this mode, you must prefix the result set with two columns, Tag and Parent , which create the hierarchical structure of the resulting XML. As with the other modes, you also must specify the element and attribute names within the SELECT clause.

To get an idea for how the ExecuteXmlReader method can be used consider the code in Listing 10.4.

Listing 10.4 Using ExecuteXmlReader . This listing uses ExecuteXmlReader to return results from a FOR XML statement in SQL Server 2000.

 Dim xlr As XmlReader Dim xmlSql As String xmlSql = "SELECT isbn, title, price FROM Titles xmlSql &= "WHERE Author = 'Fox, Dan' FOR XML AUTO, ELEMENTS" Dim com As New SqlCommand(xmlSql, con) com.CommandType = CommandType.Text xlr = com.ExecuteXmlReader() xlr.MoveToContent() Do While xlr.Read   ' parse the XML Loop 

In Listing 10.4, the xmlSql string contains the SELECT statements, which includes the FOR XML clause. Here the AUTO, ELEMENTS arguments are used to transform each table and column in the SELECT statement into an element as shown in the following code snippet. After the method has executed, the resulting XmlReader from the System.Xml namespace can be used to navigate the document using its Read method. The resulting XML is shown in Listing 10.5.

Listing 10.5 XML output. This listing shows the XML fragment output from the FOR XML statement in Listing 10.4.

 <Titles>   <ISBN>06720083X</ISBN>   <Title>Pure Visual Basic: a code-intensive     premium reference/versions 5 &amp; 6</Title>   <Price>24.9900</Price> </Titles> <Titles>   <ISBN>06720072X</ISBN>   <Title>Building Distributed Applications with Visual Basic .NET</Title>   <Price>44.9900</Price> </Titles> 

You'll notice that the XML produced is actually an XML fragment because it doesn't have a root element.

Note

The XmlReader is analogous to the data reader and is used to provide streamed access to XML. The System.Xml namespace also provides the XmlDocument class, analogous to the DataSet , to provide cached access to XML documents.

Although you can certainly parse the XML in this fashion, the real power of using FOR XML is to transform the XML generated on the server into HTML or other formats using XML Stylesheet Language (XSL) transformations. The classes that enable you to do programmatic transformations can be found in the System.Xml.Xsl and System.Xml.XPath namespaces. This technique can be used to efficiently generate HTML on the Web server in an ASP.NET application without using server controls or manually looping through a data reader. For example, the CreateCatalog method in Listing 10.6 accepts an XmlReader as an argument and transforms the XML it contains into a stream using the titles.xsl XSL stylesheet.

Listing 10.6 Transforming XML. This method transforms the incoming XmlReader like that produced by the ExecuteXmlReader method and produces a stream that contains HTML.

 Private Function CreateCatalog(ByRef xlr As XmlReader) As Stream   Dim oXsl As New XslTransform()   Dim s As New MemoryStream()   Dim oDoc As XPathDocument   Try     ' Get an IXPathNavigable interface     oDoc = New XPathDocument(xlr)     ' Load the XSL stylesheet     oXsl.Load("titles.xsl")     ' Perform the transformation     oXsl.Transform(oDoc, Nothing, s)     s.Position = 0     Return s   Catch e As Exception     Throw New Exception("Could not create catalog", e)   End Try End Function 

You'll notice in this method the XmlReader passed in as xlr is first used in the constructor of the XPathDocument object. This must be done to create a cache for reading the input when the transformation is applied. The stylesheet is then loaded using the Load method and the transformation performed with the overloaded Transform method. The results of the transformation can be sent to a TextWriter , Stream , or XmlWriter . In this case, the method takes advantage of polymorphism to place the output into a MemoryStream that is then returned to the caller.

Note

The Position property of the MemoryStream is reset to 0 before the stream is passed back to the caller because the transformation will leave the pointer at the end of the stream.

The titles.xsl stylesheet that could be used with the output shown in Listing 10.5 is shown in Listing 10.7. This simple stylesheet creates an HTML document that contains a simple table including the ISBN, title, and price.

Listing 10.7 Simple XSL. This simple stylesheet creates an HTML document from the XML returned from SQL Server.

 <?xml version="1.0"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:output method="html" /> <xsl:template match="/"> <HTML><BODY> <TABLE>   <xsl:for-each select="Titles">     <TR>      <TD><xsl:value-of select="ISBN" /></TD>      <TD><xsl:value-of select="Title" /></TD>      <TD><xsl:value-of select="Price" /></TD>     </TR>   </xsl:for-each> </TABLE> </BODY></HTML> </xsl:template> </xsl:stylesheet> 

After the transformation is applied, the results are as shown in Listing 10.8.

Listing 10.8 XSL output. This is the HMTL output from the XSL document shown in Listing 10.7.

 <HTML>   <BODY>     <TABLE>       <TR>         <TD>06720083X </TD>         <TD>Pure Visual Basic: a code-intensive               premium reference/versions 5 &amp; 6</TD>         <TD>24.9900</TD>       </TR>       <TR>         <TD>06720606X </TD>         <TD>Building Distributed Application with Visual Basic .NET</TD>         <TD>44.9900</TD>       </TR>     </TABLE>   </BODY> </HTML> 

Of course, in an ASP.NET application, the MemoryStream could then be sent to the client's browser through the HttpResponse object.

Modifying Data

In addition to being used to retrieve data, the command object can also be used to execute statements that don't return results to the client. Typically, these statements perform some data modification and are executed using the ExecuteNonQuery method. Because ADO.NET does not provide any way to modify the schema of a database, the ExecuteNonQuery method can also be used to execute statements such as CREATE and ALTER TABLE .

Although a result set isn't returned from ExecuteNonQuery , any output parameters or return values are populated, as you'll learn in the next section. The method does, how ever, attempt to return the number of rows affected by the statement. As with the other execute methods of the command object, an InvalidOperationException will be thrown if the connection associated with the command isn't open or if the transaction property isn't set and a transaction is in progress.

Because ExecuteNonQuery is most often used for data modification, it's frequently used with parameters. The DeleteTitle method in Listing 10.9 shows a simple example of using ExecuteNonQuery .

Listing 10.9 Making a modification. This method deletes a single row in the Titles table using a stored procedure.

 Public Function DeleteTitle(ByVal isbn As String) As Boolean   Dim con As New SqlConnection(_connect)   Dim com As New SqlCommand("usp_DeleteTitle", con)   com.CommandType = CommandType.StoredProcedure   ' Add the parameter   com.Parameters.Add(new SqlParameter("@isbn",isbn))   Try     con.Open()     If com.ExecuteNonQuery() = 1 Then       Return True     Else       Return False     End If   Catch e As SqlException   Finally     con.Close()   End Try End Function 

In this listing, the usp_DeleteTitle stored procedure is passed to the constructor of the command along with the connection object. The isbn parameter is then created and added to the parameters collection of the command. Within the Try Catch block, the connection is opened and the command is executed. If the delete succeeds, the ExecuteNonQuery method should return 1 and True will be returned. If the row isn't found, ExecuteNonQuery will return and False will be returned from the method. The DeleteTitle method implies that isbn is the primary key or is unique in the table. This is because ExecuteNonQuery would return a number higher than 1 if the table contained more than one row with the given ISBN. A SqlException will be thrown only if the connection doesn't have permissions to access the procedure or if an error is encountered , such as the violation of a foreign key constraint in the event that another table references the row.

Of course, command objects are also used by data adapters to insert, update, and delete rows in the data store based on changes in a DataSet . As you'll learn in detail on Day 12, "Using Data Adapters," data adapters execute a command for each row that has been inserted, updated, or deleted in the tables of a DataSet . When a DataRow is updated, the data adapter also has the ability to read the first row returned (if there is one) from the command as well as the output parameters, and to use them to update the DataRow . This is controlled by the UpdatedRowSource property of the command object, as was discussed on Day 8, "Understanding .NET Data Providers."

Note

The UpdatedRowSource property will be set to None if the command is generated by a command builder such as SqlCommandBuilder .

Controlling the Command

In addition to the methods and properties already discussed, the command object exposes several members that enable you to control its behavior, including the timeout interval, cancellation, and preparing commands.

First, command objects expose CommandTimeout properties. This property controls how long the command will wait to attempt to execute before throwing an exception. Both SqlClient and OleDb default this property to 30 seconds, which you might consider reducing because users often abandon an application if it appears that nothing is happening for more than 15 seconds or so. You might experience a timeout if the rows on the server that you're attempting to read or write have been locked while a transaction is in process. This is the primary reason you want to make sure that any transactions you start will be finished as quickly as possible by either calling the Rollback or Commit methods of the transaction object.

Caution

Keep in mind that a command might not time out until after the execute method returns and when the data is being read. For example, when using a data reader, the command might appear to execute, but when the rows are being retrieved, it might run into one that is locked by another user. In that case, processing would stop and wait for the interval specified in the CommandTimeout property before throwing an exception. As a result, you'll need to trap for exceptions when reading data.

In some cases, you'll also be able to cancel the execution of a command against a data store by calling its Cancel method. For example, if you open a SqlDataReader using the ExecuteReader method of the SqlCommand object, you can subsequently call the Cancel method before the command has finished streaming the rows to the client. When this occurs, an exception will be thrown with the message Operation cancelled by user . In both the SqlClient and OleDb providers, if the command isn't currently executing or the request to cancel fails, no exception is thrown.

Finally, command objects expose the Prepare method. This method instructs the data store or provider to create a prepared or compiled version of the command so that it can be reused without incurring the overhead of recompiling it. For example, by invoking the Prepare method before executing a SqlCommand , the SqlClient provider will call the sp_prepexec system stored procedure to create a compiled version of the command. In that way, on subsequent executions, the compiled statement can be referenced directly, thereby increasing performance in the same way that precompiled stored procedures do. When the command is closed, the sp_unprepared stored procedure is executed to remove the compiled statement.

Note

Even though the OleDbCommand object supports this property, all providers might not. As a result, calling Prepare might not have any effect. Keep in mind as well that because Prepare is a method, it should be called after the command's properties, such as CommandType , have been fully configured.

for RuBoard