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.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read