Using the .NET CommandBuilder

Introduction

The CommandBuilder class is a part of the .NET Framework. Its purpose is to automatically build SQL INSERT, UPDATE, and DELETE statements for a DataTable based on a SQL SELECT statement. It helps the developer write cleaner and more readable code by the avoiding code with embedded SQL statements.

There are different CommandBuilder classes for the different native data access libraries. Use the OleDbCommandBuilder when connecting to Databases via OLEDB and the SqlDataAdapter for Microsoft SQL Server Databases.

Getting started with the CommandBuilder

When creating a CommandBuilder a DataAdapter is passed as a parameter to the constructor. This links the CommandBuilder to a DataAdapter allowing it to read the SELECT command that has been associated with the DataAdapter. It will use the SELECT command to extract the schema information from the Table in the Database. Using this schema information it can automatically build INSERT, UPDATE and DELETE Command objects and the required SQL statements. e.g.

Dim adpAccess As New OleDbDataAdapter( _
	"SELECT * FROM [schemaDatabases]", _
	cnnDBAccessAuditorData) 
Dim cmdbAccessCmdBuilder As New OleDbCommandBuilder(adpAccess)

Note that the QuotePrefix and QuoteSuffix must be set when updating Access or SQL Server Databases to avoid errors with columns that have the same name as reserved words or column names that contain spaces. E.g.

cmdbAccessCmdBuilder.QuotePrefix = "["
cmdbAccessCmdBuilder.QuoteSuffix = "]"

To save the Inserts, Updates and Deletes from the DataTable or DataSet to the Database, set the InsertCommand, UpdateCommand and DeleteCommand properties of the DataAdapter from the CommandBuilder. e.g.

adpAccess.InsertCommand = cmdbAccessCmdBuilder.GetInsertCommand()

I have only set the InsertCommand as I am only adding rows, if you are updating and deleting rows then UpdateCommand and DeleteCommand must be set also. E.g.

adpAccess.UpdateCommand = cmdbAccessCmdBuilder.GetUpdateCommand()
adpAccess.DeleteCommand = cmdbAccessCmdBuilder.GetDeleteCommand()

Then the Update method of the DataAdapter can be called. E.g.

adpAccess.Update(dtbschemaDatabases)

For each row that was updated the UpdateCommand will be called, for the new rows InsertCommand will be called and for rows that have been deleted the DeleteCommand will be executed.

Full Code Listing

Dim cnnDBAccessAuditorData As _
  New OleDbConnection(gstrCnnStrAccessAuditorData)
Dim adpAccess As New OleDbDataAdapter( _
  "SELECT * FROM [schemaDatabases]", _
  cnnDBAccessAuditorData)
  
adpAccess.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim cmdbAccessCmdBuilder As New OleDbCommandBuilder(adpAccess)
cmdbAccessCmdBuilder.QuotePrefix = "["
cmdbAccessCmdBuilder.QuoteSuffix = "]"
Dim dtbschemaDatabases As New DataTable("schemaDatabases")
Dim objFindKey(1) As Object

' Get a list of all databases to add to schemaDatabases table
' generally it is not recommended to access the system tables directly
cmdDBReportSelect.CommandText = _
  "SELECT " & _
  "SERVERPROPERTY('ServerName') AsDatabaseServer, " & _
  "name As DatabaseName, " & _
  "'SQLDMOCompLevel_'" _
  " + CONVERT(VARCHAR(2), cmptlevel) AS Version, " & _
  "databasepropertyex(name, 'Collation') AS Collation " & _
  "FROM master..sysdatabases " & _
  "ORDER BY name"

cmdDBReportSelect.Connection = cnnSQLServer

cnnSQLServer.Open()

drdDBInfo = cmdDBReportSelect.ExecuteReader

adpAccess.Fill(dtbschemaDatabases)

' loop through the list of databases 
' and add any that are not in the list
While drdDBInfo.Read
  ' Check if the row exists
  objFindKey(0) = drdDBInfo("DatabaseServer").ToString()
  objFindKey(1) = drdDBInfo("DatabaseName").ToString()
  drwCurr = dtbschemaDatabases.Rows.Find(objFindKey)
  If drwCurr Is Nothing Then
    ' Row does not exist so add it in
    drwCurr = dtbschemaDatabases.NewRow()
    With drwCurr
      .BeginEdit()
      .Item("DatabaseServer") = _
        drdDBInfo("DatabaseServer").ToString()
      .Item("DatabaseName") = drdDBInfo("DatabaseName").ToString()
      .Item("Version") = drdDBInfo("Version").ToString()
      .Item("Collation") = drdDBInfo("Collation").ToString()
      .EndEdit()
    End With
    dtbschemaDatabases.Rows.Add(drwCurr)
  End If
End While

adpAccess.InsertCommand = cmdbAccessCmdBuilder.GetInsertCommand()

adpAccess.Update(dtbschemaDatabases)

cnnDBAccessAuditorData.Close() 

Conclusion

The CommandBuilder class allows the developer to get away from worrying about the detail of SQL and concentrate on the other things. But there are a few things to think about before using the CommandBuilder.

One of the tradeoffs is speed as the dynamically generated SQL from the CommandBuilder might be slower than using stored procedures for data access. The CommandBuilder also has to make an extra call to the database to retrieve the schema information for the Database Table.

Another issue is that the programmer also does not have any control over the SQL that the CommandBuilder generates, so if you do not like the SQL there are not many options.