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!
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:
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
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.
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:
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.
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
TableAdapter encapsulates the following objects:
||an SQL Connection
||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
SelectCommand gets set to the first occurrence in
Public Overridable Overloads Function Fill(ByVal dataTable As _
NorthWindDBDataSet.OrdersDataTable) As Integer
Me.Adapter.SelectCommand = Me.CommandCollection(0)
If (Me.ClearBeforeFill = True) Then
Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
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
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.
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
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.
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:
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
Partial Public Class OrdersTableAdapter
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()
If (Me._commandCollection Is Nothing) Then
Set(ByVal value As SqlClient.SqlCommand())
Me._commandCollection = value
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) _
Dim stSelect As String
stSelect = Me._commandCollection(0).CommandText
Me._commandCollection(0).CommandText += " WHERE " + WhereExp
Catch ex As Exception
Me._commandCollection(0).CommandText = stSelect
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)
Me.OrdersTableAdapter.SelectCommand(0).CommandText = _
"SELECT * FROM Orders"
Dim stWhere As String = ""
If stCustID <> "" Then
stWhere = "CustomerID LIKE '" + stCustID + "%' AND "
If stShipCntry <> "" Then
stWhere = "ShipCountry LIKE '" + stShipCntry + "%' "
stWhere = Replace(stWhere, " AND ", "")
If stWhere = "" Then
That's about it, Ladies and Gentlemen!
- 25 January, 2007 -- Original version posted
- 9 July, 2007 -- Article updated