Automatically Generated Commands

for RuBoard

A recent survey of a large Silicon Valley-based corporation's application code library yielded an interesting characteristic about their major intranet applications. This characteristic was that nearly 90% of the code acted (read, updated, inserted, or deleted) against only one table at a time. There were many reasons for this including performance (keeping tight, short transactions), concurrency issues (minimizing of locking), and a desire to keep application code very small and modular. This is very typical for most organizations. With this in mind, the advent of having code generated automatically if it meets certain criteria was made a reality in .NET.

As you analyze your coding requirements and find that you will meet these criteria, you can take advantage of having .NET generate much of your update, insert, and delete code for you. .NET uses an object called CommandBuilder to automatically generate the DeleteCommand , InsertCommand , and UpdateCommand of the DataAdapter . It is available for both the OLE DB and SQL data adapters (the SqlCommandBuilder class and the OleDbCommandBuilder class). From a benefits point of view, this translates directly into smaller, more easily maintained code throughout. Now, this is a really big benefit.

The SqlDataAdapter and the OleDbDataAdapter do not automatically generate the SQL statements required to reconcile changes made to a DataSet with the associated data source. You must explicitly set the SelectCommand property of the data adapter to make this happen!

Automatically Generated Commands Criteria

To take advantage of the automatically generated commands capability, you must only be dealing with a single table at a time. The table must also have a unique primary key or unique column of some kind; otherwise , an InvalidOperation exception will be encountered and no automatically generated commands will be created. The generated code does not take into account any underlying relationships that might exist that logically relate tables such as foreign key constraints, and so on. The responsibility is on you to make sure you are not interfering with this type of situation.

The logic for this automatically generated code will also adhere to the optimistic concurrency model (see Chapter 14, "Managing ADO.NET Concurrency," for more detailed information on what the optimistic concurrency model is). In other words, the data that is read by the SelectCommand will not be locked for update and can be modified by any other user or application at any time. And, the update and delete code that is automatically generated will contain a WHERE clause that compares all of the original data values against what exists in the database. If it fails this comparison, the command will not succeed and will throw a DBConcurrencyException .

In the case of an update, if the row that it is targeting was deleted, the update will fail with the same DBConcurrencyException .

Speaking of the SelectCommand , the CommandBuilder must execute the SelectCommand in order to retrieve the necessary metadata to build the INSERT , UPDATE , and DELETE commands. So, this will be one extra query that must be executed (only a slight , but necessary nuisance).

If anything changes with the metadata after this initial SelectCommand retrieval (for example, you select a different column and want to update this value instead), you will have to refresh this metadata, which will also cause a refresh of the automatically generated UPDATE , INSERT , and DELETE code.

CommandBuilder has a bit of difficulty mapping output parameters that are identity columns (or autonumbers columns ). In this case, you will not be able to use this feature and have to explicitly code the UPDATE command.

And last, but not least, if any column names or table names contain any special characters like periods, quotation marks, spaces, or other nonalphanumeric characters (even if delimited by brackets [] ), this will cause the generated logic to fail. This would only affect perhaps .05% of the world. Most folks name their columns and tables in a fairly standard way.

This limitation does not apply to table names in the form of schema.owner.table, though.

for RuBoard