Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)

All of the synchronous SqlCommand execute functions "block" as soon as they are executed. That is, before the server returns the first indication that a resultset is ready to process, the thread and connection used to execute the function are prevented from proceedingthey're blocked. This means you need to be prepared to wait for, at most, CommandTimeout^[2] seconds before another line of code on the thread that executed the function can proceedyour user has to wait, too. You can't depend on a Timer control or other events (on the same thread) to interrupt this processyou don't own the thread because it's being used by ADO.NET. This means you can't increment a progress bar or other animation to entertain your users and prevent them from pressing Ctrl+Alt+Delete or switching to another page. You also won't have an opportunity to execute a Cancel method if the query runs too longthat is, not unless you fire up another thread (like a BackgroundWorker thread) to do so.

^[2] I discussed the CommandTimeout property in Chapter 10, "Managing SqlCommand Objects"remember, the time you wait can be far longer than this setting.

Yes, if you're so inclined, you can start another thread of execution to execute code while the SqlCommand execute function is running. But what's the point? If you use the asynchronous versions of the execute functions, you won't have to. But what if you need to simply cancel a longer-than-anticipated execution? If this is what you have in mind, you can indeed use a BackgroundWorker thread to execute the Cancel method, but it won't be easy. Suffice it to say, while the execute function is running, nothing else on your thread of execution can get any attentionnot until the server returns the first resultset. As I'll show you later, it's even more complicated than that....

Let's take a closer look at each of the execute functions. Table 11.1 lists the four synchronous execute functions, and shows the object type returned.

Choosing the "Right" Execute Function

As with most performance and architecture tips, I suggest you use the execute function that does what you want and no more. For example, if you need to execute only a DML command (an action command), don't use an execute function that expects a rowset. If you need a single value (like a count or a Boolean) from the server, use the ExecuteScalarnot ExecuteReader or ExecuteXmlReader, as they are overkill. Consider that rowset processing is complex and expensive, so you should take as many steps as practical to avoid data access strategies that depend on rowsets. Returning XML is the most expensive approach of all. While it's useful to communicate with disparate data sources and XML web services (and space aliens), it's usually too verbose and inefficient to use except when it's really needed. Whenever, possible, use a rowset-less execute function with parameters. You'll find that it's far faster to return 100 OUTPUT parameters from SQL Server than a single row of data. Table 11.2 categorizes the various query types and the best execute function to use.

IMHO

Returning XML is the most expensive approach of allbut particularly effective when communicating with space aliens.

Managing the Connection

All SqlCommand execute functions require an open SqlConnection. Your code must hold it open until all rows are fetched. That is, the SqlConnection object associated with the SqlCommand must be open before execution and must be left open until any rowsets are fetched or you execute Cancel. This means if you pass the SqlDataReader to another function, it must (somehow) Close (or Dispose) the SqlConnection used to feed the SqlDataReader. If it does not, you risk the (very real) chance of orphaning the SqlConnection object and (eventually or possibly very quickly) overflowing the connection pool.

To make SqlConnection object management easier, the ExecuteReader function can be programmed to automatically close the associated SqlConnection as soon as the SqlDataReader itself is closed. The SqlDataReader supports a Close method, which is called:

• Automatically when you pass (bind) a SqlDataReader to a complex bound control (one that accepts an entire rowset, like the DataGridView, ListBox, or ComboBox controls).

• When you use the DataTable or DataSet Load methods. Only the ExecuteReader function has this featurenone of the other execute functions supports automatically closing the connection when the SqlDataReader is closed, so you'll need to be especially vigilant when working with execute functions other than ExecuteReader.

  Tip

  Only the ExecuteReader supports the CommandBehavior.CloseConnection option.

• When you execute the Dispose method on the SqlDataReader. No, there is no compelling reason to call Dispose on a SqlDataReader, other than to take advantage of the underlying Close it performs.

• When you use the Using syntax to declare the SqlDataReader. Figure 11.1 illustrates the use of the Using operator to manage the SqlConnection and SqlDataReader object instantiation state. When the logic reaches the End Using, the object Dispose method is called behind the scenes. (The Using operator is a Visual Basic .NET 2.0 Framework feature). This approach is appropriate when you create a just-in-time SqlConnection object in a function and don't intend to share it or use it outside the current code scope.

  Figure 11.1. Using the Using operator to manage object state and tear-down.

C# developers have worked with the Using block since its inceptionit was added to Visual Basic .NET in Visual Studio 2005. Using guarantees that the object declared by the Using operator is disposed of when the code path leaves the Using/End Using block. This means that if there is an exception (even an unhandled exception), the object is properly disposed of. It also means that objects created within a Using block can't be passed outside the block.

Using ExecuteNonQuery

The ExecuteNonQuery function is the fastest (and lightest) of all of the SqlCommand functions, so it's understandable why it has the fewest features. It's used to execute action queries or any time when you don't care about or don't expect a rowset to be returned from the query. If a rowset arrives, it's ignored. However, any OUTPUT or RETURN value stored procedure parameters are returnedimmediately, as there is no rowset to return.

Capturing the "Records Affected" Value After Any Command

ExecuteNonQuery can return an integer (bigint) that's set by the records affected^[3] value returned by the queryunless you turn it off. One common practice is to use the SET NOCOUNT ON phrase in your T-SQL. This tells SQL Server to skip the extra packets needed to report rowset-less resultsets and the rows affected value. It makes handling multiple resultset queries easier and reduces the amount of unneeded information on the wire, but it disables the ability to trap the (often useful) rows affected value. It turns out that the ExecuteReader automatically ignores all resultsets that aren't bearing rowsets.

^[3] a.k.a. "rows affected".

The example shown in Figure 11.2 illustrates setting up an action command that performs an UPDATE on the database but does so in a transaction, so I don't arbitrarily change the data. In this case, the rows affected value is returned by the ExecuteNonQuery function. Yes, even SELECT statements return a rows affected valuethe SqlDataReader.RecordsAffected property exposes this integer.

Keep in mind that there is no automatic mechanism provided with the ExecuteNonQuery function to close the SqlConnection after the ExecuteNonQuery is executed. This means it's always up to your code to do sounless you're working with a persistent connection that's left open for the life of the application.

Using ExecuteScalar

ExecuteScalar is very similar to ExecuteNonQuery, except that it returns the first column from the first row of the first resultset. All other rows and resultsets are discarded after it is executed. Figure 11.3 illustrates the ExecuteScalar function to return an Integer from the query. Note that ExecuteScalar returns an Object, so you'll have to cast it to your variable.

Using ExecuteXMLReader

I originally planned to devote a chapter to XML handling, but I ran out of timeI hope to write an EBook on XML handling sometime in the future. For now, consider that since SQL Server 2000 incorporated XML data streams, developers have found ways to use (and abuse) XML. When you execute a T-SQL command that returns XML (using the FOR XML operator), you'll need to capture and manage the returning XML stream. The ExecuteXMLReader function returns a System.Xml.XmlReader object to capture it. Microsoft seems to think this is a good use of your bandwidthespecially for "mass quantities of data". I get an image of the Coneheads consuming mass quantities of beer by the six-packbut perhaps you were too young to stay up for SNL.

Using ExecuteReader

The ExecuteReader is probably the most commonly used SqlCommand execution function so I'll spend quite a bit of time discussing it. It's used when you need to execute any SqlCommand that returns one or more resultsets (possibly) containing rowsets. Of course, you might expect that the query always returns a rowset, but what if it doesn't? Why would executing a stored procedure that usually returns a rowset suddenly not work any longer? Well, what if the stored procedure logic is altered or simply branches into other logic that executes another SELECT or action command before it returns the usual rowset? I'll discuss these issues later in this chapter, but I'll give you a hintmultiple resultsets.

One set of options I want to mention at this point is the SqlDataReader "Command Behaviors" associated with (just) the ExecuteReader function. These options, which can be used alone or in combination, configure the SqlDataReader to enable automatic handling of the data and other functions.

CommandBehavior.CloseConnection

This is one of the most useful CommandBehavior settings. It simply tells ADO.NET to close the connection associated with the SqlCommand when the SqlDataReader is closed. That's great as long as your code or a complex bound control actually closes the SqlDataReader. No, you can't use this option on all SqlDataReader streams you build, as you might want to reuse the connection for other operations after the SqlDataReader is closed.

The problem with this setting is that it assumes that the SqlDataReader is indeed closed somewhere along the line. If you pass a connected SqlDataReader to another layer (all SqlDataReader streams are connected), you might not be assured that the receiving function will indeed close it. If the SqlConnection stream is created in a Private routine, the calling routine cannot address itwhen the creating routine loses scope, its objects are subject to disposal.

CommandBehavior.KeyInfo and CommandBehavior.SchemaOnly

Microsoft wanted to keep the overhead involved with a SqlDataReader as light as possible. To this end, they eliminated the base table name, key column information, and other DDL info from the base structure. If you're writing a tool that needs to examine the schema, you can tell the ExecuteReader method to include it by setting the CommandBehavior.KeyInfo switch. If all you need is schema and no data, you can set the CommandBehavior.SchemaOnly switch. If you want to browse this schema data, you can Or the two options together and use the GetSchemaTable function on the SqlDataReader. Figure 11.4^[4] illustrates using the KeyInfo and SchemaOnly CommandBehavior options to set up a call to the SqlDataReader GetSchemaTable function.

^[4] See the "GetSchemaTable" example on the DVD.

The GetSchemaTable function returns a DataTable that contains the data structure (the schema) of the database table referenced in the SELECT executed by the SqlCommand, as shown in Figure 11.5.

CommandBehavior.SequentialAccess

The wisdom (or lack thereof) of storing Binary Large Objects (BLOBs) like TEXT, IMAGE^[5], varchar(max), and varbinary(max) in the database has been hotly debated over the years. Sure, SQL Server has improved the performance and flexibility of these types, but I still find that it's often (far) faster to store this type of data in separate files and use the database to simply index the data.

^[5] I've heard that the IMAGE and TEXT datatypes are to be removed from SQL Server. I'll believe it when I see it.

If you're working with a database that includes BLOBs for some reason, you'll probably want to retrieve these values at one point in time or another. The SqlDataReader is capable of extruding a stream containing BLOBs, but it's best to inform ADO.NET that you expect to be processing these long (very long) streams of binary data. By default, the ExecuteReader function starts loading data as soon as the first row is available. Because each row can (potentially) contain over 2GB of data, it's best to fetch the data as it's made available instead of waiting for each row to populate. By setting the CommandBehavior.SequentialAccess option, the ExecuteReader function enables this behavior.

Once you choose the CommandBehavior.SequentialAccess option, a number of new "rules" apply. For example, you must access each field sequentially in your code. For example, if you return four columns and a BLOB, you should access the non-BLOB columns firstin sequence. I think you'll find that there are a number of irritating issues as you work with BLOBs stored in the database. I touch on these in a number of places elsewhere.

CommandBehavior.SingleResult and CommandBehavior.SingleRow

When you need only a single resultset or a single row, you can tell ADO.NET to ignore all but the first resultset or row. Of course, it might be wiser to request only a single resultset or row in the first place by rewriting your SQL or by using a TOP expression. I expect that ADO.NET does not "cancel" the remaining resultsets or rows, but simply fetches and discards them behind the scenes. This can negate any benefit from using these CommandBehavior options.

Note that the higher-level functions in ADO.NET, such as the DataAdapter or TableAdapter Fill and Update methods, also use the low-level ExecuteReader and ExecuteNonQuery functions to perform their operations.

Since you're holding a connection open during rowset population, it's not a good idea to let the user intervene. Rowset population (the process of fetching the rowset) should be completed as quickly as possible so that the connection can be released to the connection pool. This means you should re-examine designs that process inbound SqlDataReader rows as they arrive or let the user decide what to do with individual rows. In most cases, this approach can be better implemented by using server-side logic to process rows.