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.

Visual Basic. NET Power Coding
Visual Basic(R) .NET Power Coding
ISBN: 0672324075
EAN: 2147483647
Year: 2005
Pages: 215
Authors: Paul Kimmel © 2008-2017.
If you may any questions please contact us: