October 1, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

ADO.NET Generates SQL for You Via the SqlCommandBuilder

  • November 30, 2004
  • 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)
        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.



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.

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.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel