When I started writing my book, Database Programming with Visual Basic .NET, I knew exactly what I wanted to do: create an ADO.NET reference book that was easy to read and could be read from cover to cover. I think I’ve managed to do that, but I’ve also added extra material to the book, such as how to use Message Queuing and Active Directory.
However, because of the many changes to ADO.NET between Beta 1 and Beta 2, I lost the race against time on a few topics. One such topic is the CommandBuilder class, which I’ll describe here.
How to Work with the CommandBuilder Class
The CommandBuilder class automatically generates the following properties of the DataAdapter class:
Before moving on, I would like to add that there is no CommandBuilder class; instead, you use the SqlCommandBuilder, OleDbCommandBuilder, or OdbcCommandBuilder class, depending on the data adapter you use (SqlDataAdapter, OleDbDataAdapter, or OdbcDataAdapter). The examples shown in this document use the SqlCommandBuilder class and related data classes.
When to Use the CommandBuilder Class
The CommandBuilder class can only be used when your DataTable maps to a single database table. This means that if your database table is a join of two or more tables, you cannot use the CommandBuilder class, but you’ll have to write the code yourself. This is the secret to the CommandBuilder class: It allows you to write less code. Nothing more, nothing less! There is, however, need for despair if your DataTables are generated from more than one database table. Database Programming with Visual Basic .NET describes how to set up the properties needed for performing the INSERT, UPDATE, and DELETE SQL operations.
I stated that you can use the CommandBuilder class when your DataTable is based on a single database table, which is correct, but there are a couple more caveats:
- The CommandBuilder won’t workor rather, the automatic generation will failif the table and/or column names contain any special characters. These special characters include a period (.), a single or double quotation mark (‘ or “), a space, or any nonalphanumeric character. This is also true if the table or column name containing the special character(s) is enclosed in brackets (). However, fully qualified names are supported. One fully qualified name is from the UserMan example database: UserMan.dbo.tblUser.
- The CommandBuilder only works with DataTables that aren’t related to any other DataTables in your DataSet. So if the Users table is related to the Rights table, you’ll have to write the code for the properties yourself.
Preparing the DataAdapter
You need to set up the DataAdapter properly before you can use the CommandBuilder. This includes setting the SelectCommand property, because it is the schema retrieved by this property that’s used for determining the syntax for the automatic generation.
Not only do you need to set up the SelectCommand property, but you also need to make sure that at least one primary key or unique column is returned as part of the SELECT statement. Obviously a SELECT *… statement will return the required column if the table holds a unique column, but you need to be aware of this requirement when you specify the columns to retrieve as part of the SELECT statement.
If you use the tblUser table from the UserMan database, the following statements will make or break the CommandBuilder:
- SELECT Id, FirstName, LastName, Password FROM tblUser
- SELECT FirstName, LastName, Password FROM tblUser
The first statement will work with the CommandBuilder, because the Id column, which is the primary key and thus unique, is one of the columns retrieved. The second statement will cause the CommandBuilder to fail, because none of the retrieved columns are unique.
Another thing you need to be aware of when using the CommandBuilder class is that when it’s executed, only properties that haven’t been set, or rather those that are equal to Nothing, will be automatically generated. However, this can be exploited if you for whatever reason want to set the UpdateCommand property yourself and have the CommandBuilder generate the InsertCommand and DeleteCommand properties for you.
Listing 1 does the same as Listing 3A-32 in Chapter 3A of Database Programming with Visual Basic .NET, but it uses the SqlCommandBuilder class instead of setting the properties manually.
Listing 1. Using the SqlCommandBuilder Class
1 Public Sub SetDataAdapterCommandPropertiesUsingCommandBuilder() 2 Const STR_SQL_USER_SELECT As String = "SELECT * FROM tblUser" 3 4 Dim cnnUserMan As SqlConnection 5 Dim cmmUserSelect As SqlCommand 6 Dim dadUserMan As SqlDataAdapter 7 Dim cmbUser As New SqlCommandBuilder(dadUserMan) 8 9 ' Instantiate and open the connection10 cnnUserMan = New SqlConnection(PR_STR_CONNECTION_STRING)11 cnnUserMan.Open()12 ' Instantiate the select command13 cmmUserSelect = New SqlCommand(STR_SQL_USER_SELECT, cnnUserMan)14 15 ' Instantiate data adapter16 dadUserMan = New SqlDataAdapter(STR_SQL_USER_SELECT, cnnUserMan)17 ' Set data adapter select command property18 dadUserMan.SelectCommand = cmmUserSelect19 End Sub
If you compare Listing 1 with Listing 3A-32 from the book, you will see how much work the CommandBuilder class does for youyou save 29 lines of code!
The CommandBuilder class, in the form of the SqlCommandBuilder, OleDbCommandBuilder, or OdbcCommandBuilder class, is used for automatically generating the DataAdapter properties used for updating the data source.There are restrictions regarding when you can use the CommandBuilder class, but if you set up the DataAdapter correctly and your DataTable and the table in the database conform to the restrictions applied by the CommandBuilder, you will save many lines of code. Use it whenever you have a trivial database table you want to update using a DataSet or DataTable class.
# # #