1. Computing

Data Sources, VB.NET, and SQL Server

Data Source Generated Objects

By

There are usually at least five controls generated by Visual Studio when you use the wizard to generate a data source. Here an analysis of what they are. (I'll use the names generated in my system. Your generated system will use your names.)

RecipeIngredientsMToMDataSet

This is the DataSet that was created by the wizard discussed above.

RecipesBindingSource

This is an instance of the BindingSource .NET object. This object, through the DataSource property, identifies the data that is bound to the control, in this case, the Recipes table. The BindingSource object has been used to bind Windows Forms controls to data sources since Framework 3.5.

RecipesTableAdapter

A TableAdapter isn't part of the .NET Framework library; it's created by the wizard in Visual Studio. It's even in a unique namespace created at the same time. The TableAdapter contains all the methods needed to send and receive data from the database. Microsoft has a useful description:

"Users of previous versions of Visual Studio can think of a TableAdapter as a DataAdapter with a built-in connection object and the ability to contain multiple queries. Each query added to a TableAdapter is exposed as a public method that is simply called like any other method or function on an object."

If you check the Dataset xsd diagram, you can see these queries listed below each TableAdapter. The query for this one is:

SELECT RID, RecipeName FROM dbo.Recipes

You can see it in the CommandText property for the Fill method of RecipesTableAdapter.

TableAdapterManager

The main job of the TableAdapterManager is to make sure that referential integrity is maintained in two or more tables as the data is written to the database. Again, it's generated by the wizard.

RecipesBindingNavigator

An instance of the .NET BindingNavigator object is created and provides the user interface that you can see in the form to move forward and backward in the data source and to add and delete data.

Although you haven't written any VB.NET code yet, there is code written for you by Visual Studio in the project. One of the best ways to learn how to write your own code is to understand this generated code. But as we look at it, keep in mind what's missing in this code too:

  • No connection strings
  • No SQL commands
  • No direct calls to the database such as ExecuteNonQuery

All of this detail is abstracted away (contained inside objects) so you don't have to work directly with it anymore.

Let's look first at the code in the Load event of the form, along with the helpful comment:


'TODO: This line of code loads data into the 
'RecipeIngredientsMToMDataSet.Recipes' table. 
'You can move, or remove it, as needed.
Me.RecipesTableAdapter.Fill(
     Me.RecipeIngredientsMToMDataSet.Recipes)

The Fill method of the generated TableAdapter is used to load data into the Recipes object in the DataSet defined earlier. Since all three tables were selected when the DataSet was created in the wizard, there is also an Ingredients object and a RecipeIngredient object in the DataSet that could be loaded by specifying them as parameters to the Fill method. An IngredientsTableAdapter has to be generated for this to work. Since TableAdapters are Visual Studio generated objects, you can't just instantiate a .NET class to create one. There are several ways to create TableAdapters, but they all involve triggering the generation process in Visual Studio. Dragging a control to the form as we did here is the easiest way.

The wizard also generates a RecipesBindingNavigatorSaveItem.Click event. (The other methods, such as MoveNext and MoveFirst, are internal to the BindingNavigator and no generated event code is present unless you explicitly create the event subroutine.) This code also helps you understand how the objects work:


Me.Validate()
Me.RecipesBindingSource.EndEdit()
Me.TableAdapterManager.UpdateAll(
     Me.RecipeIngredientsMToMDataSet)

The Validate method of the Form object isn't necessary, but it's a very good idea so Visual Studio includes it automatically. The EndEdit method applies any changes to the underlying data source, in this case, the Recipes table in the Dataset. And UpdateAll actually applies the changes to the database.

Although the creation of a data source makes it unnecessary to work with the details of SQL and direct connections to the database in many cases, if you need this level of detail it's still available. Earlier, I mentioned that these details were in the generated TableAdapter object. If you want to work with the SQL statements in a TableAdapter ...

  • Open the Dataset Designer (double click the xsd schema)
  • Select the Properties for the TableAdapter that you want to change
  • Select the DeleteCommand, InsertCommand, SelectCommand, or UpdateCommand property - the SQL statement used by that property is shown
  • Click the elipsis button to open the same Query Builder that was shown in the previous article

Hopefully, this series has completed the circle for you now so you understand both the details of working directly with the database and you know how to get started using the higher level the high-level Dataset objects. But this is only the beginning. There are many more high-level database tools available. The next segment, Using Data Sources in VB.NET goes into more detail.

  1. About.com
  2. Computing
  3. Visual Basic
  4. Using VB.NET
  5. Using Data Sources in VB.NET - A Visual Basic Database Course for Beginners

©2014 About.com. All rights reserved.