Generating SQL with a Command Builder

An excellent benefit of object-oriented systems is that you have tidy compartments in which to place additional functionality, the classes themselves. The OleDbCommandBuilder and SqlCommandBuilder classes can use the command—whether literal text or command object—used to initialize an adapter and generate additional command objects. For example, when you write a SELECT statement, the schema (the columns and data types) can be inferred or read, and knowing the schema, substitution can be employed to replace SELECT with UPDATE, INSERT, or DELETE. A benefit of .NET is that the CommandBuilder class already exists and will do the work for us. Listing 11.8 demonstrates how to generate SQL for inserting, updating, and deleting from a selection statement.

Listing 11.8. Generating SQL with an OleDbCommandBuilder Object

Private Sub GenerateSQL(ByVal SQL As String)
  Dim Connection As OleDbConnection = _
    New OleDbConnection(Database.ConnectionString)

  Dim Adapter As OleDbDataAdapter = _
    New OleDbDataAdapter(SQL, Connection)

  Dim Builder As OleDbCommandBuilder = _
    New OleDbCommandBuilder(Adapter)

  TextBox2.Text = Builder.GetDeleteCommand().CommandText
  TextBox3.Text += Builder.GetInsertCommand().CommandText
  TextBox4.Text += Builder.GetUpdateCommand().CommandText

End Sub

The OleDbCommandBuilder object is initialized with an instance of an adapter. The Builder Get methods—like GetDeleteCommand—open a connection to read the schema. This can be an expensive operation, but in the course of normal programming you will probably have opened the connection anyway. Listing 11.9 provides an example of the generated SQL.

Listing 11.9. An INSERT SQL Statement Generated by OleDbCommandBuilder

INSERT INTO products( ProductName , SupplierID , CategoryID ,
QuantityPerUnit , UnitPrice , UnitsInStock , UnitsOnOrder ,
ReorderLevel , Discontinued ) VALUES ( ? , ? , ? , ? , ? , ? , ? ,
? , ? )

The parameterized values can be filled in by an adapter during the course of an adapter operation. For example, we can use a command builder to generate SQL and call an adapter's Update method. The update method will fill in the missing information.