August 14, 2020
Hot Topics:

ADO.NET Generates SQL for You Via the SqlCommandBuilder

  • By Paul Kimmel
  • Send Email »
  • More Articles »

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.

Listing 2: Generating SQL Using the SqlCommandBuilder

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)
            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

            Dim table As DataTable = New DataTable

            '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"

            '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


        Catch ex As Exception
        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.

Click here for a larger image.

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 pkimmel@softconcepts.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

This article was originally published on November 30, 2004

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date