TableAdapters are one of the many ways in .NET of accessing/manipulating data from different sources — usually a database — and are relatively easy to use; all that's needed is some understanding of how it (and ADO.NET) works.

TableAdapters provide communication between your application and a database. More specifically, a TableAdapter connects to a database, executes queries or stored procedures, and either returns a new data table populated with the returned data or fills an existing DataTable with the returned data. TableAdapters are also used to send updated data from your application back to the database.^[1]

That's a roundabout way of saying that you don't directly touch the data in your database (or whatever); it's a disconnected architecture, which means when an application interacts with the database, the connection is opened to serve the request of the application and is closed as soon as the request is completed.^[2] It achieves this through the use of the DataSet and Data Provider components. For our purposes at this time, we are most concerned with the DataSet, which is a disconnected, in-memory representation of data. It can be considered as a local copy of the relevant portions of the database. The DataSet is persisted in memory and the data in it can be manipulated and updated independent of the database. When the use of this DataSet is finished, changes can be made back to the central database for updating. The data in DataSet can be loaded from any valid data source like Microsoft SQL server database, an Oracle database or from a Microsoft Access database.^[3]

So where does TableAdapter come in? Well, it's with the TableAdapter that you can populate a disconnected DataSet with data, and perform updates. Meaning, we use TableAdapters to query our data stored in the DataSet. In this post, I will demonstrate the use of a TableAdapter in executing a simple search query.

Let's start by creating a very simple MS Access database table called users (or you can download it here).

Next, we connect that database to our Visual Basic project, and edit it in the DataSet Designer like so:

At this point, we want to now create the SELECT query we need to be able to do our searches. For this, we need some SQL to basically tell the DataSet we only want to see data based on what we're searching for. Our SQL query would look like this: SELECT ID, fname, lname FROM users WHERE lname = ?. Try reading it aloud; you'll see that it's very straightforward. Follow the numbered steps in the TableAdapter Query Configuration Wizard below:

1. Right-click the TableAdapter section of the table we want to query, and choose 'Add Query' from the context menu.
2. Choose the 'Use SQL Statements' option
3. Choose the 'SELECT which returns rows' option
4. Under 'Specify a SQL SELECT Statement', enter the following: SELECT ID, fname, lname FROM users WHERE lname = ?
5. Enter the name of the method you want to save this as; I used SearchFillByLName and SearchGetDataByLName.

Some notes: the ? in the query indicates that we are passing a value to this method as a parameter; in this case, a text box we'll be setting up in a little bit. Also, at this point, we only really need the Fill method.

With that done, we can now finally move on to the form. I've laid it out below, with the element names in hot pink:

Double click on cmdSearch to bring up the Code View, and enter the following lines:

Dim search_string As String = txtSearch.text
UsersTableAdapter.SearchFillByLName(DemoDataSet.users, search_string)

Here we are now using the Fill method we created earlier: SearchFillByLName. This method accepts two arguments: a DataTable (in this case DemoDataSet.users) and the value we want to match the lname field in the users table to, which as you can see, we are getting from txtSearch. And you're done! Run the project to see how it goes:

Taking it Further

Such a search functionality is a bit limited, since you need to type in the excact last name of the person you are searching for. Let's extend it a bit, and allow for partial matching of the search. To do this, we need to use the SQL LIKE operator, and some minor edits to our code.

First, change the SQL query in the TableAdapter to this:

SELECT ID, fname, lname FROM users WHERE lname LIKE ?

As you can see, we merely changed the equality operator to the LIKE operator. Next, we need to update the call in cmdSearch to look like this:

Dim search_string As String
search_string = "%" + txtSearch.Text + "%"
UsersTableAdapter.SearchFillByLName(DemoDataSet.users, search_string)

The only significant change is the addition of the two % operators to both ends of the search_string variable; that's because the LIKE operator needs it. Running the same query in the program now produces this:

Notice that both Juan dela Cruz and Lukas Cruz are now returned.

Download the source for: the project | the sample MS Access database.

References

• TableAdapter Overview — Data Access in Client and Middle-Tier Programming, MSDN Developer Center
• ADO.NET Architecture — StartVBdotnet.com
• ibid