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

Visual Studio includes a number of wizards, including the (now obsolete) DataAdapter Configuration wizard (DACW) and its replacement, the new Data Source wizard. It seems that Microsoft wants developers to use its new TableAdapter over the DataAdapter for reasons I discussed in Chapter 6, "Building Data Sources, DataSets, and TableAdapters." The TableAdapter is generated by the Data Source wizardit's used to provide much of the functionality of the DataAdapter but provide the functionality exposed on the DataSet to the DataTable.

IMHO

I'm of the opinion that the DACW has been around too long to be unceremoniously shoved into a ditch at the edge of town like a derelict Mercedes.

The problem with the DACW and several other code generators used by Visual Studio is their heavy dependency on the SqlCommandBuilder. Sure, there are times when you (and I) find it's just simpler to use these wizards to generate ADO.NET code, and while we wince, we still build demos, prototypes, and "quick and dirty" applications that use these tools.

IMHO

In Chapter 6, we toured the SqlCommandBuilder when I discussed building updateable DataSets and DataTables. You should already know why I don't recommend its use.

What the CommandBuilder Is Supposed to Do

When you have a simple table-based query that only references a single table and you want to be able to update the table's rows, someone has to build a set of action commands to do the job. That's what the CommandBuilder is supposed to do. This relieves you of the responsibility of writing your own action commands (in these simple cases) by automatically constructing the T-SQL action queries, ADO.NET Command objects, and their associated Parameters collections given a valid SelectCommand. Cool. And it works, but only in a very limited and relatively inefficient way, and for a price (as I describe in this chapter). However, unlike DACW-generated code, the commands created by the CommandBuilder are unseenin another black box (like the notorious Visual Basic 6.0 Data Environment Designer).

Tip

One can use the CommandBuilder indirectly (as when using a Visual Studio data access code-generating wizard) or directly in your own code, as when you have not built a strongly typed TableAdapter and need an updateable DataSet. Frankly, neither one of these techniques is very appealing.

When the CommandBuilder is called by the TableAdapter or DataAdapter wizards at design time, Visual Studio extracts the generated code from the CommandBuilder and uses it to build strongly typed classes that reflect the schema of the data referenced in a provided SELECT statement. The CommandBuilder assumes (requires) that the SELECT statement is relatively simple. If it contains a JOIN, refers to a stored procedure, or generates multiple resultsets, the CommandBuilder can give up as quickly as my granddaughter when asked to eat broccoli. I'll dig into these details later in this chapter.

One other important point: The CommandBuilder does not generate any post-action fetch statements. This means that you (or Visual Studio) must add fetch queries to the INSERT and UPDATE commands to retrieve the current values in the changed row. For example, once a row is added, you'll need to fetch the new identity value. The same is true for the new TimeStamp value on any row added or changed. Fortunately, Visual Studio does this for you by adding the needed fetch queries to the generated UpdateCommand and InsertCommand. ADO.NET is there ready to help post these updated column values back to the in-memory DataTableassuming it's correctly programmed.

Generating the Action Commands

In order to create an updateable DataTable in ADO.NET, someone or something (in this case) has to generate three Command objectswell, four really. The first (the SelectCommand) is used to fetch the initial rowset using a (very simple) SELECT statement. The other three Command objects are (eventually) built by the CommandBuilder using this SELECT statement, so it's an important first step. These four Command objects are named as follows:

• SelectCommand: Specifies the SQL code and parameters needed to fetch the rowset(s) based on a single (simple) SELECT query. Yes, you can specify more complex SELECT queries and even include a JOIN, but if you do, you'll find that the CommandBuilder can't generate the remaining Commands.

• InsertCommand: Specifies the SQL code and parameters needed to insert a new row in a database table.

• DeleteCommand: Specifies the SQL code and parameters needed to delete a specific row from a database table.

• UpdateCommand: Specifies the SQL code and parameters needed to change a specific row in a database table.

The last three of these Command objects are created for you automatically and managed behind the scenes by the CommandBuilder. These so-called "action" commands (the InsertCommand, DeleteCommand, and UpdateCommand) are generated by the CommandBuilder class based on the columns and WHERE clause specified in the SelectCommand's SELECT statement. The CommandBuilder also requires that a query be executed against SQL Server to return metadata that describes the SELECT. This costs at least one full round-trip to the server to accomplish, so it can potentially hurt performance fairly badly if not handled wisely.

Consider that ADO.NET developers are responsible for supplying all of the SQL code needed to fetch and change the data in the target database tablethere are no "black boxes" that congers T-SQL on-the-fly in ADO.NET (like there is ADOc). This means that unless you write the code yourself, you'll have to depend on classes like the CommandBuilder, Visual Studio (which calls the CommandBuilder), or other third-party tools to generate it for you.

Handling ConcurrencyUpdate and Insert Collisions

The concurrency problem that you and the data access interfaces have to deal with is that you (should) want to know if the data row in the database table you're changing still (doesn't, or already) exists, and if the data has changed since you initially fetched the data. This is called "concurrency management." It's a concept you have to be concerned about even in a single-user database. In multi-user databases that are designed to permit more than one client access to the same row(s), the task of managing concurrency gets far more complex.

IMHO

I'm of the opinion that it's better to build concurrency into an application's fundamental design instead of tacking it on later. It's kinda like designing city streets and highways to include overpasses and traffic lights that prevent cars from running into each other instead of figuring out where to position ambulances and tow trucks to cart off the bodies and crashed cars after the collisions.

Let's go back in time for a moment to ADOc. When you use the Recordset Update method, ADOc generates the SQL to make the changes to the database at runtime. This means it creates appropriate INSERT, UPDATE, and DELETE statements on-the-fly to post changes made to the rows in the Recordset.

In the process of generating the action commands on-the-fly, ADOc uses the Update Criteria property, along with extra metadata, fetched (that bulks up the Recordset) to adapt the type of concurrency code it generates to match your specific needs. For example, you can choose from four different types of concurrency management in ADOc by using these Update Criteria settings:

1. adCriteriaKey: Find the row to update using only the primary key. This (essentially) was a "last wins" update strategy.

2. adCriteriaAllCols: Compare all columns of the current row's original values with the existing row's values. If they match, ADO thinks that no other changes had been made since the row was last read. Of course, the row could have been changed a million times, but as long as the values were set back to the original state, this concurrency option would not notice. Note that this "shotgun" approach is the only option available in the SqlCommandBuilder.

3. adCriteriaUpdCols: Compares only those columns that have changed with those currently stored in the database. If these match, the UPDATE succeeds.

4. adCriteriaTimeStamp: Compares the row's TimeStamp column with the value in the database. If the TimeStamp columns are the same, the row has not been modified and the UPDATE succeeds.

The problem with this runtime T-SQL-generation approach is that it's also not particularly flexibleyou have no control over the way the code is generated beyond the four Update Criteria settings. You still can't tune the T-SQL to meet special-case needs. As you'll see with ADO.NET, ADOc also makes some pretty challenged decisions when it comes to handling rights (permissions to update specific columns).

How Does ADO.NET's CommandBuilder Handle Concurrency?

ADO.NET 1.0 was lambasted when it first came out because the "new and improved" CommandBuilder implementations (for all .NET Data Providers) use a "shotgun" approach to generate Command object codeit tests for all columns in the target row against the original row fetched by the SELECT, as shown in Figure 12.1. Notice that this T-SQL UPDATE statement (automatically generated by the CommandBuilder) tests each column's original value against the current server-side value. If all match, the UPDATE succeedsas long as the PK value is found.

This approach is especially challenged in that it ignores that some columns you SELECT might be read-only and not meant to be included in the UPDATE statement. For example, those columns you don't have sufficient rights to change, like Salary, are included in the UPDATE.

To address some of these issues, the Microsoft team added a new ConflictOption to the CommandBuilder to help expose some of the ADOc functionalitythis new property goes partway toward a solution. Let's take a quick look at this property to see how it can be used to help manage (simple single-table) concurrency problems. These new option switches are exposed in the Data Source wizard, as discussed in Chapter 6.

The ConflictOption property can be set to any of three options:

1. CompareAllSearchableValues: When set (the default), UPDATE and DELETE statements are generated to include T-SQL that compares all selected column original values with the same columns in the server table. This does not include expressions returned by the SELECT or columns you add to the DataTable manually in code. The code in Figure 12.1 was generated using this option.

2. CompareRowVersion: If a Timestamp column is present, it's used in the WHERE clause to determine if the row has been changed since the data was fetched. If the value is unchanged, the UPDATE or DELETE proceeds.

3. OverwriteChanges: In this case, the generated UPDATE code merely verifies that the target row for an UPDATE or DELETE exists by matching the PKif the row exists, the action is executed.

Given that the ADOc approach required quite a bit of help from the fetched metadata (which made the Recordset much larger than it had to be) and didn't work particularly well with some providers, Microsoft found it easier to take a bare-bones approach with ADO.NET. This is understandable because many developers didn't expect ADO to construct the action commands anywaythey had already moved their action command logic to stored procedures. Having ADO.NET do more than absolutely necessary was also contrary to its charter: to be light, fast, and easy. It was also important to simplify the construction of .NET data providers so it would not take an OLE DB brain surgeon to build them. This strategy plopped the responsibility for the action query SQL back on the ADO.NET developer. Unfortunately, that's kinda like dropping an angry porcupine in your date's lap.

How the ADO.NET CommandBuilder Works

The CommandBuilder^[1] expects you to provide a viable, executable, but simple SelectCommand associated with a DataAdapter. It also expects a viable Connection. That's because the CommandBuilder opens the Connection associated with the DataAdapter and makes a round-trip to the server each and every time it's asked to construct the action queries. It closes the Connection when it's done. This round-trip query can be reproduced in your application if you want to get a better idea about what's being done behind the scenes to determine the table schema. It's also a good tutorial on the DataReader class's GetSchemaTable. Figure 12.2 illustrates the code to fetch the table schema from the Authors table.

^[1] We use the term "CommandBuilder" here as the discussion applies to all implementations of the CommandBuilderincluding the SqlCommandBuilder implemented in the SqlClient namespace.

In this example, our code does what the CommandBuilder does behind the scenesit returns a DataTable containing the schema details for the Authors table. We used SELECT * to get the query to return an in-depth schema report that includes all columns (as shown in Figure 12.3). Note that the Au_ID column has "IsKey" checked. This means that it's the table's primary key (or it's one of the columns that constitutes the PK). Once this DataTable is returned, the values it contains are used to generate the action queries. So, if this query does not indicate that there is a PK, the CommandBuilder won't be able to generate any of the action queries.

No, ADO.NET does not use the new 2.0 GetSchema method to uncover the query schema as we discussed in the last chapterperhaps because it did not exist years ago when this approach was originally designed.

Sure, GetSchemaTable works even when the query includes a JOIN or other complex logic, and it can be executed against a stored procedure or a view. That said, the CommandBuilder requires a pretty simple query that addresses a single database table. When you use the CommandBuilder against a query that returns duplicate columns or multiple tables, the CommandBuilder parser gives up trying to figure out how to build the action queries. Remember, the DataAdapter Update method is designed to provide updateability for a specific table. And behind the scenes, the DataAdapter and Data Source wizards call the CommandBuilder to generate some of their code.

Tip

Sure, it's possible to create your own CommandBuilder or custom wizard to generate the action queries you need. We suspect there are already several of these in the marketplace.

Coding the CommandBuilder

The code to add a SqlCommandBuilder to your application is shown in Figure 12.4.

What you can't see from this code is what's going on behind the scenes. However, even if you turn on the SQL Server Profiler (a really good idea when working with Visual Studio code generators), you still can't tell that construction of the CommandBuilder does anything, at least, not until the code executes the CommandBuilder GetUpdateCommand method. That's because the CommandBuilder class does not construct the action commands until they are first touchedas they are needed by the application. When we execute the GetUpdateCommand method that generated the SQL for the UpdateCommand (as shown in Figure 12.4), ADO.NET executes the T-SQL shown in Figure 12.5.

This code (generated by the GetSchemaTable method) returns a resultset that includes just the metadata for the table in the SELECT statement (FMTONLY=True). This resultset is used to construct the three action queries (InsertCommand, UpdateCommand, and DeleteCommand) associated with the DataAdaptereven though the same query was used to populate the DataAdapter's DataTable.

Remember, unless the SelectCommand is something more than a simple SELECT, as we mentioned earlier, the CommandBuilder cannot generate the action commands. It also gives up if the SelectCommand is too complex or contains a JOIN. There are several other limiting factors as well:

• Your SelectCommand must retrieve at least one primary key or unique column as part of the query. Just having a TimeStamp column without a primary key of some kind won't work.

• Your SELECT statement must refer to a single table; thus, the SelectCommand cannot refer to SQL queries, stored procedures, or views that contain JOIN operators.

• The SelectCommand must refer to columns that permit read/write operations. You can't include columns or expressions that can't be written to for some reason.

• The CommandBuilder makes no attempt to (nor does it provide any mechanism to) fetch output arguments from the action query (including the Identity value).

• If the CommandText, Connection, CommandTimeout, or Transaction properties of the SelectCommand change, you have to notify the CommandBuilder by executing the RefreshSchema method, which takes another round-trip.

• The CommandBuilder-generated UpdateCommand or DeleteCommand does not change any row that's been modified since last read. This means you'll have to write your own action query to "force through" an UPDATE or DELETE operation if that's how your concurrency scheme is designed.

• The CommandBuilder is designed to work with single, unrelated tables. That is, you can't expect to update tables with primary key/foreign key relationships.

• If the SelectCommand SQL columns contain special characters, such as spaces, periods, quotes, or non-alphanumeric characters, you can't use the CommandBuilder.

When the .NET Framework Version 1.1 shipped (along with Visual Studio .NET 2003), CommandBuilder support was added to both the OracleClient and Odbc .NET Framework data providers. No, the CommandBuilder is not universally supported by all .NET Framework data providers, so don't be surprised when your provider balks when you try to use it.