ADO.NET Generates SQL for You Via the SqlCommandBuilder
Generating SQL Using the SqlCommandBuilder
The SqlCommandBuilder needs a SqlDataAdapater and a SELECT SQL statement to generate INSERT, UPDATE, and DELETE SQL statements. The SELECT statement indicates the schema of the result set, and the rest is filling in the blanks for the other commands.
For example, if I know the columns selected, I can use those column names and data types to generate the remaining statements. Listing 2 demonstrates a method that obtains a DataTable, generates INSERT, UPDATE, and DELETE commands, makes related modifications to the DataTable, and then sends those changes to the database provider.
Private Sub UseCommandBuilder() Dim connectionString As String = _ "Integrated Security=SSPI;Persist Security Info=False;" + _ "Initial Catalog=Northwind;Data Source=LAP800" Dim connection As SqlConnection = _ New SqlConnection(connectionString) connection.Open() Try Dim command As SqlCommand = _ New SqlCommand("SELECT * FROM Customers", connection) Dim adapter As SqlDataAdapter = _ New SqlDataAdapter(command) Dim builder As SqlCommandBuilder = _ New SqlCommandBuilder(adapter) ' Show commands Console.WriteLine(builder.GetInsertCommand().CommandText) Console.WriteLine(builder.GetUpdateCommand().CommandText) Console.WriteLine(builder.GetDeleteCommand().CommandText) Dim table As DataTable = New DataTable adapter.Fill(table) 'Use Insert Dim row As DataRow = table.NewRow row("CustomerID") = "PAULK" row("CompanyName") = "Pauly's Edibles" row("ContactName") = "Paul Kimmel" row("ContactTitle") = "The Fat Man" table.Rows.Add(row) 'Use Update Dim results() As DataRow = _ table.Select("CustomerID = 'ALFKI'") results(0)("Region") = "DE" 'Use Delete Dim deleteThese() As DataRow = _ table.Select("CustomerID = 'FARLA'") For Each row In deleteThese row.Delete() Next adapter.Update(table) table.AcceptChanges() Catch ex As Exception Debug.WriteLine(ex.Message) Throw Finally connection.Close() End Try End Sub End Module
Listing 2 modifies Listing 1 to include a SqlCommandBuilder. The SqlCommandBuilder is initialized with a SqlDataAdapter, which it uses to generate the remaining CRUD (Create, Read, Update, and Delete) commands. Next, it reads the result set, inserts, updates, and deletes a row. The example updates Alfreds Futterkiste's region to DE (Deutschland or Germany), adds Pauly's Edibles, and removes FARLA from your list of customers. To illustrate that all of the verbosely generated SQL worked, Figure 1 shows the Northwind table after the table updated.
Figure 1: Northwind Table After Listing 2 Table Update
It May Not Be Pretty, But It Works
The SQL that the SqlCommandBuilder generated is verbose and kind of ugly, but it does the job. Sometimes, what you need is an effective and inexpensive solution. The aesthetics of the functional and inexpensive can be just as elegant as those of the sublime but atrociously expensive.
You now know that ADO.NET will write SQL for you via the SqlCommandBuilder. Now, choose the situation when this functionality works best. It is every software engineer's responsibility to figure out which tools in his or her arsenal to use for which tasks.
Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Look for his upcoming book UML DeMystified from McGraw-Hill/Osborne (Spring 2005). Paul is also the founder and chief architect for Software Conceptions, Inc, founded 1990. He is available to help design and build software worldwide. You may contact him for consulting opportunities or technology questions at email@example.com.
If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org.
Copyright © 2004 by Paul Kimmel. All Rights Reserved.
Page 2 of 2