Extending TableAdapters for Dynamic SQL

Introduction

This article discusses how to extend ADO.NET TableAdapter functionality in Visual Basic .NET. TableAdapters were introduced in ADO.NET 2.0. Basically, a TableAdapter lets you connect to a database, run queries or stored procedures, return a new table or fill existing DataTables. At the end, it allows you to send updated data from your application back to the database. All of this happens with a minimal amount of fuss and bother!

TableAdapter review

TableAdapters are created inside strongly typed DataSets using the Dataset Designer. There are a couple of ways to create TableAdapters in Visual Studio. Probably the easiest is to build both the DataSet and the TableAdapter(s) as by-products of adding a Data Source to your project. In the Data Source window, just click the link "Add New Data Source..." or click the toolbar button for this purpose.

This will invoke the Data Source Configuration Wizard and, after answering a few short questions such as which database, which objects, etc., Visual Studio will add not only a new Data Source, but also a new DataSet with one or more TableAdapter objects inside. You can double-click the XSD file in Solution Explorer to get a "picture" of these objects. Here's the one used in this article's example:

Figure 1

As you can see, we are using the good old NorthWind database. For the record, Orders is the ADO.NET DataTable and of course the TableAdapter is OrderTableAdapter.

Another way to create a TableAdapter is to use the TableAdapter Configuration Wizard. Simply open an existing DataSet in the DataSet Designer. Drag a TableAdapter from the DataSet tab of the Toolbox onto the design surface. This opens up the TableAdapter Configuration Wizard. Again, simply answer the prompts of the wizard and it will dutifully add a new TableAdapter and DataTable to the DataSet. Note that once it is created, the auto-generated DataSet will appear in Solution Explorer as an XSD file. See the Figure below.

Figure 2

At this point, you could build a form and put that TableAdapter to good use. Just drag and drop the table or columns from the Data Sources window onto a form's design view. As you drop the table onto your form, this is what you might see appear in the form's component tray:

Left to right, these components are: the strongly typed DataSet, a Binding Source, the TableAdapter and a Binding Navigator, which is a visual component that provides toolbar buttons for row navigation and for adding, removing and saving rows in the DataTable. Note that you can add more queries to your TableAdapter beyond the one that the Wizard gives you. Just right click on TableAdapter in the component tray and choose to Edit queries in DataSet Designer. When the designer opens up, you'll see the box representing your TableAdapter. Right click it and select Add Query

Each query will have its own generated Fill method. Typically, additional queries are "parameter-driven" and your code is responsible for passing the appropriate parameter value(s) when you call the TableAdapter's Fill method. For example, suppose your query is against an Employee table and selects based on a range of Last Names. Your SELECT statement, stored in CommandCollection, might be:

SELECT * FROM dbo.EMPLOYEES WHERE LASTNAME > @LNAME1 AND LASTNAME < @LNAME2 

Your call to the TableAdapter's Fill method might look like this:

Me.EmployeesTableAdapter.Fill(Me.HRDBDataSet.Employees,_
    "AAAAA","HHHHH")

This technique works well as long as your query in the TableAdapter is always the same. You simply add parameters to the select statement when you configure the TableAdapter in the DataSet Designer and you're ready to rock-and-roll. Be sure to use named parameters beginning with @ for the SQL Server data provider.

The generated Fill and GetData methods in the TableAdapter will use these parameters and you will be able to pass values on the method calls from your code. Of course, this technique often leads to adding "yet another query" to the TableAdapter. In short, it isn't terribly "dynamic." So, if you want to have more control over your TableAdapter and avoid adding 100 queries to it, you have to hack it a little.

Looking again at the DataSet in DataSet Designer, we see that even a very simple TableAdapter incorporates several objects as well as a couple of standard methods like Fill and GetData

Figure 3

Each TableAdapter encapsulates the following objects:

Object	Based on
a Connection	an SQL Connection
an Adapter	an SQL DataAdapter
a Command Collection	an array of SQL Commands

All of these objects are more-or-less built-in and private. However, these generated objects and methods can be used to fetch data -- even update data -- from the database. At this point, you may well ask yourself:

OK. Where have they hidden the code?

To dig into the bowels of TableAdapter, have a look at the VB file for the DataSet Designer. In the figures above, this would be NorthWindDBDataSet.Designer.VB. By examining the internals of TableAdapter, you will find:

• DataAdapter contains objects for the three standard SQL "update" commands, i.e. the update, insert and delete commands. See the class OrdersTableAdapter and the private method InitAdapter.
• CommandCollection is an array of SQL Command objects. Tthere will be one or more SELECT statements depending on the number of queries you have configured for the table within the DataSet.

How to get TableAdapter to process dynamic SQL SELECT Queries

The problem with what we've seen of TableAdapter so far is that all this configuration and the resulting code generation is strictly a design-time activity. What if you want to construct your SELECT query at run-time, based upon certain criteria entered by the user on your form? Is there a way to do this and then pass the dynamic SQL to TableAdapter? The short answer is, "No." That is, unless you're willing to hack the auto-generated code in the DataSet Designer VB file. Take a look at the generated Fill method below. You'll see that DataAdapter's SelectCommand gets set to the first occurrence in CommandCollection:

Public Overridable Overloads Function Fill(ByVal dataTable As _
      NorthWindDBDataSet.OrdersDataTable) As Integer
   Me.Adapter.SelectCommand = Me.CommandCollection(0)
   If (Me.ClearBeforeFill = True) Then
      dataTable.Clear()
   End If
   Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
   Return returnValue
End Function

Clearly, if we want to change DataAdapter's select command on the fly, we have to gain access to CommandCollection. Unfortunately, CommandCollection is a Protected ReadOnly property with a private field behind it. The good news, however, is that the TableAdapter itself is implemented as a Partial Public Class that inherits from System.ComponentModel.System.

A "partial class" means that you can add your own code to the class in a separate file and just "extend" its functionality. A partial class lets you split the definition of a class -- its properties, methods, etc. -- over two or more source files. Thus, you can add your own class file and put your hack in this file.

Step-by-step example

As mentioned before, our examples are all using the Orders Table from the NorthWind database. So begin by adding a simple TableAdapter to a Windows form. Do this by dragging the entire Orders table from the Data Sources window onto the form's design surface. The wizard automatically adds the components, including the TableAdapter, to the component tray as well as a Data Grid View control to the form.

Now make room at the top of the form by moving the grid down a bit. At the top of the form add a couple of text boxes, one labeled Customer ID Like: and the other labeled Ship Country Like:. Give the text boxes appropriate names such as uxCustID and uxShipCntry. Finally, again at the top of the form, add a Button control and change its Text Property to the word Fill. The top of the form above the grid should look something like what's shown in Figure 4.

Figure 4

Preparing to hack

Now we can add our hack. From Solution Explorer, begin by opening up the file NorthWindDBDataSet.Designer.vb. In the code, find the namespace statement for the TableAdapter. This might require a few clicks of the Find Next button. The namespace naming convention is simply: DataSetName + TableAdapter. Since I called my DataSet "NorthWindDBDataSet," my namespace is:

Namespace NorthWindDBDataSetTableAdapter

Copy and paste this namespace statement or, if you're old-fashioned like me, copy the name down on a Post-it sticky. Next, find the declaration for the TableAdapter class. It will be named using the naming convention: Object + TableAdapter. So in our example, the TableAdapter class would be named OrdersTableAdapter.

Now, add a new Class file to your Project! In the class file, add a namespace statement with a name that is identical to that used in the auto-generated DataSet. Next, change the class declaration so it exactly matches that of the TableAdapter class in NorthWindDBDataSet.Designer.vb. In other words:

Option Strict Off
Option Explicit On

Imports System

Namespace NorthwindDBDataSetTableAdapters
    Partial Public Class OrdersTableAdapter
        Inherits System.ComponentModel.Component

    End Class
End Namespace

Now we can hack the code in the class adding to our own file. Let's add a new property called SelectCommand. Make it a public read/write property that controls an array of SqlClient.SqlCommands. The property code is pretty simple. It just lets us get at CommandCollection with a Getter and a Setter as shown below:

Public Property SelectCommand() As SqlClient.SqlCommand()

    Get
        If (Me._commandCollection Is Nothing) Then
            Me.InitCommandCollection()
        End If
        Return Me._commandCollection
    End Get

    Set(ByVal value As SqlClient.SqlCommand())
        Me._commandCollection = value
    End Set

End Property

The Set property procedure here means that code calling TableAdapter can pass in a properly formed SELECT command and get at the same private field used by the auto-generated TableAdapter – Me._commandCollection. Now we need a new Fill method. Let's call it FillByWhere. This method is modeled after the standard Fill method, but has a signature that lets us pass in a WHERE condition. For example:

Public Function FillByWhere(ByVal dataTable As _ 
     NorthWindDBDataSet.OrdersDataTable, ByVal WhereExp As String) _ 
     As Integer
 
   Dim stSelect As String
 
   stSelect = Me._commandCollection(0).CommandText
   Try
      Me._commandCollection(0).CommandText += " WHERE " + WhereExp
      Return Me.Fill(dataTable)
   Catch ex As Exception
 
   Finally
       Me._commandCollection(0).CommandText = stSelect
   End Try
 
   End Function

To complete the picture, simply write the necessary code behind the form's Fill button. See the code snippet sample below. You can start by moving the auto-generated code that calls the Fill method out of the form's Load event and into the Fill button Click event. In the button's Click event, you will write code to accomplish the following:

• Populate the TableAdapter SelectCommand object's CommandText property
• Construct a string containing the WHERE criteria based on the user input
• Call the FillByWhere method of the TableAdapter

See the sample button Click event code below.

Private Sub btnFill_Click(ByVal sender As System.Object, 
    ByVal e As System.EventArgs) Handles btnFill.Click

        Dim stCustID As String = Trim(uxCustID.Text)
        Dim stShipCntry As String = Trim(uxShipCntry.Text)

        ' Pass in a SELECT with no WHERE that conforms to the the DataTAble 
        '   used by the TableAdapter's DataSet 
        Me.OrdersTableAdapter.SelectCommand(0).CommandText = _
        "SELECT * FROM Orders"

        ' Build a string containing WHERE criteria (without the WHERE) 
        Dim stWhere As String = ""
        If stCustID <> "" Then
            stWhere = "CustomerID LIKE '" + stCustID + "%' AND "
        End If
        If stShipCntry <> "" Then
            stWhere = "ShipCountry LIKE '" + stShipCntry + "%' "
        Else
            stWhere = Replace(stWhere, " AND ", "")
        End If
        If stWhere = "" Then
            Me.OrdersTableAdapter.Fill(Me.NorthwindDBDataSet.Orders)
        Else
            Me.OrdersTableAdapter.FillByWhere( _
                Me.NorthwindDBDataSet.Orders, stWhere)
        End If

    End Sub

That's about it, Ladies and Gentlemen!

History

• 25 January, 2007 -- Original version posted
• 9 July, 2007 -- Article updated