TableAdapter Part3

This is something I found just the other day while working with the Fill method. If you want to follow along, add a datasource and drag a table to your form (see earlier posts if you don't know how). I am working with the Customers table from the Northwinds.mdb.

This is a short post but very beneficial. Say you have a table you need to work with but you are doing it on a pretty puny device, or the table is really huge, I will show you how to change the TableAdapters Fill method to be dynamic so you can pull only what you need.

In the old days you could change the select command and pass it right into the database to get what you want, while TableAdapters still have all of the commands, they are not exposed without jumping through hoops. Whoever decided that this was a good idea....well nevermind, we work with what we have.

Right-clicking the dataset in your Data Sources screen, click "Edit DataSet with Designer". Now you should have a .xsd tab with a little diagram of your table and the tableadapter. Right-click where it says "Fill, GetData()" and select configure, you will see the current select statement. You can type the next part manually, or to be sure its right, you can use the Query Builder button.

The Query Builder is just like the query builder in Access, do what you want and the SQL changes automatically. In this scenario my program will need data for the country it is running in. To set this up, in Query Builder I would navigate to Country and place a '?' in the Filter column:
Click Finish, and then notice that the "Fill,GetData()" has been replaced with "Fill,GetData(Country)" .

Go to your code where the Fill method is and you'll see that you now have an error. The '?' told the program that you will be passing in data, so you need to do that. You can do this by typing in the information:

Me.CustomersTableAdapter.Fill(Me.NorthwindDataSet.Customers, "Germany")

Or you could make it dynamic by tying it to a textbox, dropdowb, My.Settings etc.

By using this method you keep things dynamic while maintaining full access to the database. Instead of bogging the program down with thousands or millions of unnecessary records in a dataset and then populated into a view, you are only pulling what you need at that particular time.