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

If you're new to ADO.NET but familiar with ADO.NET classic, you might not realize that there is no way to execute a query (or any T-SQL command) without using a SqlCommand object of some kind. The SQL Server SqlClient.NET Data Provider exposes these as the SqlCommand class. In ADO.NET classic, you can simply execute SQL right off the SqlConnection object, by using the Recordset Open method or several other techniques.

This is not the case with ADO.NETyou must create a SqlCommand object and populate its properties to execute the SQL. Yes, there is a CreateCommand method on the SqlConnection class, but it has no overloads, so it does not save you any time, money, or wear and tear on your keyboard. However, I don't know how much easier it can be to write a couple of lines of code to create and configure a simple SqlCommand object, as shown in Figure 10.1. Sure, you're going to have to create a SqlConnection object to link to the SqlCommand, but that's easy, too.

Understanding the SqlCommand Properties

The SqlCommand class exposes a number of basic properties that help ADO.NET understand how to execute it. These contain the SQL query, the type of query, and how long SQL Server should take to execute it (before giving up). Let's take a close look at these properties, as shown in Table 10.1each of these properties is discussed in more detail later in this section.

Coding the CommandType and CommandText Properties

Visual Studio has a couple of wizards (and at least one that Microsoft does not want you to use) to help you instantiate and populate a SqlCommand instance, and you can use the CommandBuilder to create the CommandText for your action commands. However, you're on your own when it comes to building the SQL for the SELECT commands used to return a rowset. I devoted Chapter 7, "Managing Data Tools and Data Binding," to the wizard approach, as it has a number of issues that you need to be aware of before you venture too far down that road. Suffice it to say that most professional developers soon discover that they have to create their own queries with very little help from the wizards once their designs grow beyond the most elementary query/update schemes.

ADO.NET is instructed how to execute the T-SQL or named stored procedure provided in the CommandText by setting the CommandType property. This defaults to "Text", the setting required to execute a T-SQL ad hoc query. If you don't set the CommandType when executing a named stored procedure, ADO.NET throws a syntax exception when the SqlCommand is executed.

The CommandText property typically contains the T-SQL you wish to execute, as I describe later in this chapter. In SQL Server, the T-SQL you provide can also be a batch of several statements concatenated together (with or without a semicolon) that are executed in series. Of course, the T-SQL you provide must be syntactically correct and use appropriate (named) parameter placeholders.

IMHO

No, the SqlClient provider does not support the TableDirect enumerator, and since I don't recommend that approach for SQL Server, it's no big loss.

Remember, if you name a stored procedure in the CommandText, you need to set the CommandType to StoredProcedureif you don't, you'll get a syntax error when the SqlCommand is executed. I don't know how many times this exception has irritated meespecially late at night when I'm not running on all cylinders. When calling stored procedures, you'll need to append a Parameter object to the Parameters collection for each (required) input and all OUTPUT parameters. Each parameter of a stored procedure that does not have a default value assigned in the stored procedure definition must be supplied a value. You'll also need another Parameter for the RETURN value if this is of interest to your code. I discuss executing stored procedures later in this chapter.

When you set the CommandType to "Text", ADO.NET calls the system stored procedure sp_executesql to execute the SQL in the CommandText on the serverunless you set the Prepare property to True when ADO.NET executes the extended stored procedure sp_prepexec (that combines a "preparation" step with an execute step sp_execute). I discuss the Prepare property later in this chapter. The only reason I mention this is the issues caused by use of these procedures to execute your T-SQL. It seems that there are a number of perfectly reasonable (albeit more complex) queries that cannot be executed using the "Text" CommandTypethey must be executed via stored procedures to work. I'm still lobbying for a fix, but again, don't hold your breathMicrosoft is busy elsewhere working on shiny new toys.

The alternative CommandType is "stored procedure". In this case, ADO.NET simply builds a string that has the stored procedure name (the CommandText) concatenated with the input Parameter objects (by name) and executes it directly on the server.

Maximizing CommandText Performance

There are several approaches to populating the CommandText property that you need to fully understand before moving on. A bit later in this chapter, I examine ad hoc queries where you simply (perhaps not so simply) hard-code the SQL, parameter-driven queries and show how to use SqlCommand objects to call stored procedures. At this point, I want to hammer in a few guidelines to make sure your queries are as efficient as they can be:

• Ensure that you're fetching "just enough" data in your queries and no more. Don't ask for columns you don't need or use.

• Avoid use of SELECT*, as this syntax returns all columns currently defined for the tablepossibly more data than you need. In some cases, limiting the columns you return can result in an "index seek", which does not require secondary table access.
• Don't ask for more rows than you need in the immediate future. Except in connectionless architectures (PDAs and phones), use the available connection to fetch more databut only as you need it.

• Avoid queries that have to be executed with row scans or are so broad the indexes can't help retrieve the rows.

• Don't let the user dictate how many rows to fetchat least, keep the number within reason. One greedy user should not cripple the performance of others. Use paging techniques to fetch the "nth" block of data from the server.

• Use parameter-based queries and stored procedures whenever possible. Avoid (like the plague) ad hoc queries that accept parameters generated by the user. Remember, all data is evil until proven innocentespecially if it comes from people.

  IMHO

  All data is guilty until proven innocent.

• Always use a WHERE clause or TOP clause (or both) to limit the number of rows returned. This dramatically improves scalability and performance, as it reduces network load and server load, and leverages existing indexes.

• Use the query analysis tools to determine whether your query is making the best use of existing indexes. If you're using stored procedures, make sure their query plans match the input parameters each time they are called. Earlier chapters discussed these issues in depth.

• Minimize round-trips to the server. Each time you query the server, the expense of reopening the connection, opening a channel, and all of the other overhead involved in executing the query and returning the resultsets is fairly high and should be minimized. If you can, use multiple resultset queries that return more than one set of results in a single round-trip. Think of a round-trip to the server as a trip to the grocery store on the other side of town. Make the best use of your travel time and the items you bring back. Remember that data spoils faster than milk on a warm day.

• Avoid use of the CommandBuilder to build the CommandText at runtime. I discussed this issue in depth in Chapter 8, "Getting Started with ADO.NET."

Setting the Connection Property

Each SqlCommand object that you expect to execute must be associated with a SqlConnection object capable of executing the T-SQL in the CommandText. This property can be set using one of the New constructors or by simply providing the name of the SqlConnection object (before the SqlCommand is executed). The SqlConnection need not be open as you build the SqlCommand object, as ADO.NET won't automatically use an open connection to "guess" at the other properties or populate the Parameters collection (as ADOc did). The SqlCommand class can also accept a System.Data.Common.DbConnection object. This means you can create an alternative way to set up a valid SqlConnection object for use with your commands.

The SqlConnection object associated with the SqlCommand object is not only used to provide an access path to the data source; it's also used to generate transaction scope and Transaction objects. The SqlConnection object is also the host for the InfoMessage event, which fires when a stored procedure executes a RAISERROR or, in some cases, when a SqlException is raised. If you specify Integrated Security, the CommandText is executed on the server with the rights granted to the current user.

Setting the CommandTimeout Property

When you execute the SqlCommand object, ADO.NET passes the CommandText to SQL Server (or the designated data source) and begins to wait for the first packet of "results" to return from the server. Unless you reset the CommandTimeout value, ADO.NET waits (the default) 30 seconds for the SqlCommand to begin to return rows. No, the clock does not start counting until SQL Server responds. If the NIC or Net is down, you might wait a lot longer. In addition, if SQL Server returns a single resultset from the query and the server hangs, ADO.NET considers the timeout to be satisfied and continues to wait (indefinitely) for the remaining rows. If you need to reset the CommandTimeout value to its default setting, you can use the ResetCommandTimeout method.

If you have a query that takes longer than 30 seconds to execute, I recommend that you:

• Reconsider your query strategy. If this is a fetch query that takes longer than 15 seconds, it's likely (but not certain) that it's returning too many rows or the procedure is not granular enough. This is especially important in interactive applications. Users won't wait forever for a query to complete.

• If your query has to wait for another query to complete before it can complete, you've created a deadlock condition that the server might (just might) catch. In this case, you need to decide how to proceedeither execute your queries in a different order or back off and try again later.

• Consider that you might get a "Connection timeout" exception that's returned by the connection-pooling mechanism. The Command timeout property has no effect on this behavior.

IMHO

It's not how fast you ask questions; it's how fast smart questions can be answered.

As shown in Figure 10.2, my (quite informal) studies have shown that the propensity of a person to press Ctrl+Alt+Del, smash the monitor, or take other (all-too-often) drastic actions as their wait time approaches 60 seconds really depends on the culture and the region. Some users seem to be quite happy waiting for 1545 seconds without worrying if their program will ever return. Generally, those are what Peter calls "civil serpents". However, as the wait time nears 60 seconds, most cultures (except for some monks in Nepal who are particularly laid-back) give up waiting and start taking action. These actions range in intensity from simply clucking softly, to resetting the system, to using a 12-guage shotgun on the monitor. It's interesting how users take their frustrations out on the monitor, when it was the system itself or the programmer down the hall that was more likely at fault. The chart shown in Figure 10.2 shows how this tendency varies as to locale.

One way to prevent users from this frustration is to entertain them. This is how Microsoft Windows handles the fact that it takes so long to simply move data from place to place. For example, when you expect the operation to take a long time, show the user a progress bar or play an interesting AVI file on pig farming downloaded from the Discovery Channel. This approach usually distracts the user enough to give your application time to get the operation finished.

Unfortunately, none of us really knows how long a query is going to execute, so some guesswork is often involved. I'll talk about the GuessQueryRuntime class in my next EBook. But seriously, you probably know how long it should take to run your query based on past experience, testing, or just an educated guess. I suggest you set the CommandTimeout property to reflect that value plus a factor to account for the stuff that happens that you didn't expect, and set up an exception handler to trap the timeout exception.

Setting the Parameters Property

Most T-SQL queries you execute include one or more input, OUTPUT, in-out, or RETURN value parameters. I call these the "gazintas" and "gazoutas", as they pass information into the T-SQL and get values back out of the resultset. The Parameters property contains the collection used to hold parameter descriptions (saved as Parameter objects), so they can be inserted into the SQL just before execution. I show you how to populate and manage the Parameters collection later in this chapter. This same Parameters collection can also collect data back from an action command (one that executes an INSERT or UPDATE, in this case). If properly configured (and I'll show you how in Chapter 12, "Managing Updates"), you can fetch the newly created row values, including the new Identity value, using these Parameter objects.

Setting the Notification and NotificationAutoEnlist Properties

One of the new 2.0 Framework features is the ability to monitor the rowset accessed by the CommandText query. When the data changes, your application can be programmed to trap a SQL Server 2005generated notification event. The Notification and NotificationAutoEnlist properties are used to set up and manage this feature. I discuss notifications along with these properties in an upcoming EBook.

Setting the Transaction Property

When you need to ensure that the operations in two or more commands are executed together (or not at all), you must "wrap" the operations in a transaction of some kind. For example, when you want to delete a customer record, you need to do so in stepsdeleting the "child" orders first and then the "parent" customer records. These operations should be done with an "atomic" operation that either completes in its entirety or does nothing at all. Generally, I suggest that transactions be carried out on the server in stored procedures. This simplifies application design and precludes interrupting the transaction (which blocks server resources) accidentally with user-interface code. I discuss transaction management in Chapter 12.

Setting the UpdatedRowSource Property

When working with parent/child relationships, one of the problems you have to face is keeping the child rows synchronized with newly added parent rows. The UpdatedRowSource property (when set to True) automatically propagates newly assigned parent PKs to related child rows once the parent rows are added to the database. I discuss parent/child relationships and managing updates in Chapter 12.

Understanding the SqlCommand Methods

Clearly, the SqlCommand class is a pivotal piece of the ADO.NET data access paradigm. As such, it's not surprising that it has so many methods (used to manage object instantiation and tear-down) and functions (used to configure and carry out the SqlCommand object's functionality). At this point, I'm ready to make you aware of these methods and properties (and not much more)I'll get into a lot more detail later in this chapter. Table 10.2 lists the methods with a brief description. All are detailed in the subsequent sections.

Using the Cancel Method

The Cancel method is used to ask (request, petition) ADO.NET and SQL Server to stop processing the operation(s) requested by the most recent Execute method (like ExecuteReader). You won't get an opportunity to call the Cancel method if you use the Fill or Update methodsthey're strictly synchronous.

If you simply close a DataReader after having used ExecuteReader to create it, ADO.NET loops through any rowset(s) generatedreturning any and all rows to the client before actually closing the DataReader (and possibly closing the SqlConnection). On the server end of the wire, your SQL Server instance is still required to finish looking for rows requested by your query.

If, on the other hand, you use the SqlCommand.Cancel method, the SqlClient data provider is requested to stop executing the current batch and sending rows back to your application. This can save a dramatic amount of time if you change your mind about the command currently running.

No, you can't expect to use the Cancel method while the query is runningunless you run the execute function on a separate thread. And then, what's the point of setting up the custom code to run an execute method on another thread? ADO.NET 2.0 has implemented asynchronous operations. I'll discuss these asynchronous functions in Chapter 11, "Executing SqlCommand Objects."

Based on conversations I've had with Microsoft, I understand that you should never call the Cancel method unless you're trying to execute a single-resultset SELECT command. Trying to stop more complex queries is problematic, at bestsomething akin to stopping an airplane's takeoff at an arbitrary point in timeonce the ground-rollout has progressed past the point of no return, it's unwise to abort. Based on how SQL Server executes queries and action commands, the Cancel method might not be received in time to stop an UPDATE or complex stored procedure. When dealing with complex queries, it's best to let ADO.NET unravel the operations. If you have started transactions to protect the referential or data integrity of your database, these should continue to do their job if you simply close the SqlDataReader.

Any number of times, I've found that SQL Server seems to lock up for long periods of time when I use the Cancel method unwisely. To make matters worse, I have been known to power-cycle the server to try to recover the system. Ah, this is not wise. Either avoid Cancel or learn to be patient.

Using the Dispose Method

I can't think of many reasons to use the Dispose method on the SqlCommand object, since object disposal is done automatically. However, if you create a SqlCommand object and want to ensure that its resources are released to the garbage collector, you can use the Dispose method. When you learn how to use the Using statement to help declare your SqlCommand (or any) objects, you'll find that it calls Dispose for you as the object falls from scope. I'll show examples of the Using statement later in this chapter.

Using the New Constructors

There are four New constructors implemented on the SqlCommand object. Ah, no, they aren't really "new", so if you're confused by that term, you must be new to OO (so to speak), so it would be a good idea to review Chapter 8, where I discussed New instance constructors. These are used to instantiate an instance of the SqlCommand class and (in most cases) populate the CommandText and, optionally, the Connection and Transaction properties. No, when you supply a string to the constructors that expects appropriate CommandText SQL, table, or stored procedure names, the constructor does not edit the stringit assumes you know what you're doing. Syntax checks are made by SQL Server when the CommandText is executed. ADO.NET also does not automatically guess that you're actually passing the name of a database table or stored procedurethat means you'll have to set the CommandType property yourselfit's a common mistake, so be sure to check before executing. Figure 10.3 shows typical uses of the SqlCommand New constructors.

If you look closely (perhaps not that closely), you might spot several issues with this code.

• The first constructor simply creates a SqlCommand object, but it's not ready to be executedit's up to your code to populate the required properties before it can be used.

• The second constructor attempts to set up a parameter query but fails to populate the Parameters collection with a properly defined Parameter object to manage the query parameter. You also get extra credit if you noticed that the "?" is an invalid parameter placeholder when using the SqlClient provider. All parameters (as I'll discuss later) must be namednot simply marked as in ADOc, OleDb, or Odbc, where one marks query parameters with "?".

• The third constructor attempts to call a stored procedure "AuthorsByYearBorn" and pass in the current SqlConnection. So far, so good, but when calling a stored procedure, remember that you must set the CommandType property to "StoredProcedure" so ADO.NET knows how to execute the CommandText.

• The fourth constructor example is syntactically correctit illustrates passing a string to the CommandText string in the New constructor. However, it also uses a silly way to determine how many authors were born before the year 1000. Wouldn't a SELECT be far easier and not require a transaction? Sure, but I wanted to illustrate use of a transaction.

Let's take another attempt at the code and see if it can be improved a bit. In this next version, I simply dropped the first constructor, as it did not do anything usefulit was kinda like a snowplow on a skateboard. Figure 10.4 shows the results of my efforts.

Tip

Parameter-driven queries are your best defense against SQL injection attacks.

Note that in Figure 10.4, I used a correct parameter marker for the ad hoc query (@AuthorID). I also used parameter markers that matched the named parameters being passed to the stored procedure. I'll get into the details of how to build the SqlParameterCollection later in this chapter. I also ripped out all of that (needless) transaction code and executed a simple SELECT to return a count of all rows that qualify for the WHERE clause. To return the result, I used ExecuteScalar to return the first column of the first row returned by the query as an object. I'll talk about the SqlCommand "execute" methods in Chapter 11.

Using the Prepare Method

The Prepare method was first introduced in the ODBC days when it was necessary to "prepare" SQL statements for execution. The Microsoft documentation (and the MCSE exam) seems to think that it's a good idea to call the Prepare method the first time a SqlCommand is executedI think so, too. One thing that's certain is that if you call Prepare, all variable-length parameters must have a non-zero size set and have explicit datatype definitions. This means you can't use the new ADO.NET 2.0 AddWithValue function to add a Parameter to the Parameters collection (it assumes the default datatype of nvarchar).

As I mentioned earlier, the Prepare method calls the Extended Stored Procedure (ESP) sp_prepexec, which combines the sp_prepare and sp_execute ESPs. These procedures create a cached version of the query plan (to ensure that it's reused when the SqlCommand is executed again) and execute the SQL. Using Prepare can mean a performance benefit once you pay the price of calling itPrepare is not free. While Prepare does not require an extra round-trip, it does incur some additional timewhich does not have to be spent on subsequent executionsassuming you make subsequent calls using the same SqlCommand.

Using the ResetCommandTimeout Method

When you need to set the CommandTimeout back to the default value (30 seconds), you can use the ResetCommandTimeout method or simply set the CommandTimeout property to 30that's what ADO.NET does behind the scenes.

Understanding the SqlCommand Support Functions

This discussion focuses on two "support" SqlCommand functions, as listed in Table 10.3. Since the Clone function is new in the 2.0 Framework and has so many useful purposes, I'll spend quite a bit of time helping you make best use of its features.

Using the Clone Function

The Clone function is fairly simplelike most Clone functions in .NET, it's used to make an exact duplicate of the object instance. Figure 10.5 illustrates how the Clone function can be used in code. In cases where you want to create several different Command object instances, you can use the Clone method to copy the populated "base" instance to create a new instance. In some of the examples shown later, I use the Clone method to make a copy of a DataRow or an entire DataTable. The magic of the Clone method is that it makes a copy of the datanot just set a pointer. This means you can have two independent sets of objects to work with.

Take a look at the code in Figure 10.5^[1]. Do you see any issues? As far as this goes, I expect that it might make more sense to pass in the @YearWanted parameter as an argument to the BuildCommandAndClone function. But that raises another point. Should you instantiate the SqlCommand object each time it's used? Object instantiation is not expensive, but it's not free, either. Let's take another look at how the Clone function can be used a bit more efficiently. In this case, I'm going to use an "overloaded" subroutine that has two "signatures"one that accepts a single integer parameter and another that accepts two integer parameters. I'll build a SqlCommand object, clone it, and add the additional parameter to the clone.

^[1] See the "Clone Function" example on the DVD.

First, I set up the objects I plan to use in the application. In this case, I create a SqlConnection object and two SqlCommand objects, as shown in Figure 10.6. Note that I extend the existing CommandText to incorporate another argument in the WHERE clause. Yes, I could use the Parameters.Clear function to clean out the Parameters collection, but this way I have to add only one additional parameter instead of two. Note that the "@YearWanted" parameter is used twice in the SELECT statement's WHERE clause in the cloned SqlCommand object. You're permitted to use parameters as many times as necessary or not at all in your T-SQL.

In Figure 10.7, I create two subroutines that are themselves "clones" of each other (in a way). These subs are "overloaded," which permits the developer to create more than one way to call the routinewith one or two arguments. In one case, I pass a single integerthe "year wanted", and in the second case, I pass twothe "year low" and "year high" to be passed to the "Between" operator in the SELECT statement's WHERE clause. The GetAndShowData routine does just that. It accepts one of the two SqlCommand objects (with the Parameter Value properties already set) and executes the query. The results are bound to a DataGridView control.

Before I move on, consider that it often makes sense to create your SqlCommand objects early in the application and reuse them for the lifetime of the application. In other words, you create them once and simply set the Parameter.Value property just before each execution.

Using the CreateParameter Function

One approach to creating SqlParameter objects is to create them individually and populate their properties one by one. This is (IMHO) a waste of time, as it's far easier to simply use the numerous overloads exposed on the SqlParameterCollection class that I discuss later in this chapter.