ADO.NET Generates SQL for You Via the SqlCommandBuilder

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 _
      SqlConnection(connectionString)
    connection.Open()
    Try
       Dim command As SqlCommand = _
          New SqlCommand("SELECT * FROM Customers", connection)

       Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)

       Dim data As DataSet = New DataSet
       adapter.Fill(data)

       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))
          Next
          Console.WriteLine(New String("*", 40))
       Next

       Catch ex As Exception
          Debug.WriteLine(ex.Message)
          Throw
       Finally
          connection.Close()
       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.

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.

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

Biography

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.



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

  • With 81% of employees using their phones at work, companies have stopped asking: "Is corporate data leaking from personal devices?" and started asking: "How do we effectively prevent corporate data from leaking from personal devices?" The answer has not been simple. ZixOne raises the bar on BYOD security by not allowing email data to reside on the device. In addition, Zix allows employees to maintain complete control of their personal device, therefore satisfying privacy demands of valued employees and the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds