DatabaseADO.NET Generates SQL for You Via the SqlCommandBuilder

ADO.NET Generates SQL for You Via the SqlCommandBuilder content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Everywhere you turn, someone is promoting software methodologies: Kent Beck with eXtreme Programming (XP), Dr. Barry Boehm with Agile methods, IBM/Rational with the Rational Unified Process (RUP), and Microsoft with services-oriented architecture (SOA). Although each offers something valuable for the programmer, few methods and few tools apply in every instance. Software development still comes down to the judgment of individual developers. Where do I spend my time? Where can I take shortcuts without compromising quality? Of all the tools and methodologies that are available, what suits my purposes in this instance—not every instance, just this instance?

For example, if you cause a stove fire by spilling peanut oil or Cognac onto a hot skillet, quickly covering the skillet will extinguish the fire. But if you are extinguishing an oil well fire, you might need an explosion or the exhaust from a turbine engine. Try using a ton of dynamite on your average skillet fire and you’ll blow up your house.

This article is about a simple tool, ADO.NET’s SQL command builder, which is a perfect example of this type of right-tool-for-the-right-job judgment. The command builder reads a schema and generates SQL for you; this can be an excellent timesaver in the appropriate circumstances. When applied with sound judgment, this technique is a nice shortcut.

In addition, because ADO.NET is based on a common, database-agnostic interface, every ADO.NET provider supports the command builder. Thus, while the techniques this tutorial demonstrates are for SQL Server, you can apply them to any database provider.

Connecting to a SQL Database

Before you get started, quickly review the basic design of ADO.NET. Based on a disconnected model and XML, ADO.NET was designed to enable you to connect to a provider (think database), interact or fetch the data you want, and then disconnect. This is sort of how Ivar’s Fish and Chips works in Seattle. You step up to the counter, shout out your order, and step back. Ivar’s satisfies your order, but you don’t wait at the counter, preventing others from ordering. The same is true of ADO.NET. Your request is satisfied but you do not hold the connection to the database; this allows others to connect and make requests. Also, because ADO.NET is stored internally as XML, it is very transportable across networks—especially the Internet—because XML is essentially text.

The best way to use ADO.NET, then, is to connect to the provider, make a request, and disconnect. Listing 1 demonstrates a request that retrieves the Customers table from the Northwind database.

Listing 1: Connecting a database, retrieving some data, and disconnecting

Private Sub ConnectToSqlDatabase()
Dim connectionString As String = _
    "Integrated Security=SSPI;Persist Security Info=False; " + _
    "Initial Catalog=Northwind;Data Source=LAP800"

    Dim connection As SqlConnection = New _
       Dim command As SqlCommand = _
          New SqlCommand("SELECT * FROM Customers", connection)

       Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)

       Dim data As DataSet = New DataSet

       Dim row As DataRow
       Dim column As DataColumn
       For Each row In data.Tables(0).Rows
          For Each column In data.Tables(0).Columns
             Console.WriteLine("{0}: {1}", _
                column.ColumnName, row(column.ColumnName))
          Console.WriteLine(New String("*", 40))

       Catch ex As Exception
       End Try
End Sub

The subroutine in Listing 1 contains a literal connection string. Generally, you include a literal connection string in every method because the specific content of the connection string has an impact on connection pooling. (Connection pooling simply means reusing connections where possible, rather than creating new connections.) Connections created with the same connection string can participate in connection pooling. It is preferable to place a connection string in a .config file, encrypted. Using an encrypted string in a .config file promotes using an identical connection string and security.

Listing 1 also shows the protocol for proper connection use:

  1. Connect.
  2. Open a connection.
  3. Start a try block.
  4. Manipulate the data.
  5. Ensure the connection is closed in a finally block.

The example in Listing 1 manipulates the data all in the Try part of the exception handler. Even if you were returning a dataset from the method, you would do so in the Try part of the block rather than assign the dataset to a temporary variable and return the temporary variable after the Try..Except.Finally block. You would do this because the Finally statements are always executed—even if you have a Return statement in the Try block, and the rule of Refactoring suggests you reduce or eliminate temporary variables.

ADO.NET DataSets allow you to make several modifications while disconnected and then, when you connect, they enable ADO.NET to perform a compendium of modifications as long as it knows how each kind of modification—INSERT, DELETE, and UPDATE—should be made. That is, ADO.NET needs to know the SQL to perform each operation.

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.

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

If you are interested in joining or sponsoring a .NET Users Group, check out

Copyright © 2004 by Paul Kimmel. All Rights Reserved.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories