As you saw earlier, a Data Source can deliver a straightforward view of the data very easily. But we need to display the result of a "chain" of links starting with the recipe ID key to the Recipes table (RID) to the RecipeIngredient junction table which contains both the Recipes key (RID) and the Ingredients key (IID) and finally to the Ingredients table with the corresponding Ingredients key (IID).
The Microsoft VB.NET guru Beth Massi calls this "one-to-many chaining". (I'm indebted to Beth's explanations for what follows here.)
Step one is to add DataGridView controls to the project to set up the chaining. The main goal here is to get Visual Studio to generate the correct code for what we want, so it has to be done in a specific way.
Start with a blank form and a Data Source that includes all three tables in our Recipe Ingredients database. If you have done the examples in the previous parts, you should be able to generate a Data Source for this database in about thirty seconds using the wizard. (I can ... now!) The completed Data Source and blank form should look like this. Notice that there is nothing in the component tray below the form ... yet.
--------
Click Here to display the illustration
--------
With the DataGridView control selected in the Data Source, drag each table to the form.
--------
Click Here to display the illustration
--------
A binding navigator component (RecipesBindingNavigator) is generated for the Recipes table because it's dragged to the form first.
No other table generates one. But each table does generate a binding source and a table adapter. Dragging these components to the form creates nine new entries in the component tray. After you complete this, your form should look something like this:
--------
Click Here to display the illustration
--------
If you run your application now, you get what you want for the first two grids, but not the third (Ingredients). Everything is displayed in that grid, and the navigator toolbar doesn't change what's in it. This happens because there is only one-to-many chaining built into the binding sources. In other words, if you look at the properties of RecipesBindingSource, you see that the DataSource is the DataSet itself and the DataMember is the Recipes table. The binding source for RecipeIngredient, however, is RecipesBindingSource and the DataMember is the foreign key for Recipes to RecipeIngredients. This is one-to-many chaining. Like Recipes, Ingredients uses the DataSet directly as the DataSource again.
To get the effect you need, you have to filter the results for Ingredients based on a change in the selected recipe. This is where the coding comes in. Here's the code that works:
Recalling that we're working with the set of all of the DataRows in IngredientsBindingSource, we construct a Filter string that consists of an OR concatenated string of all of the Ingredients keys found in a specific Recipes selection. Microsoft explains the Filter property of BindingSource like this:
Typically used in complex data-binding scenarios, the Filter property allows you to view a subset of the DataSource.
The Filter string might look like this:
The string is a constant to select no rows in the Ingredients table if there are no DataRows in the junction table:
This gives you what you need. Whenever a selection of a recipe in the first DataGridView changes (that is, the ListChanged event) then the complete results returned for the Ingredients table are filtered. You get only the ingredients and amounts for a specific recipe. It's not very pretty, but at least it's the right result.
--------
Click Here to display the illustration
--------
Customizing this form so it's a little closer to a finished result is traditional programming again (as opposed to database specific programming). You can edit the columns in the DataGridView just as you normally would.
One additional help that Visual Studio gives to you is the ability to match the .NET types to database types, and more exactly control how updates are done. (Neither of these situations apply in this example.) To do this, select the (Advanced) property for the control and then select the specific formatting or updating that you need. This illustration shows the sequence of windows.
--------
Click Here to display the illustration
--------
Much of the history of software is a story of low-level software (such as assembler language) being replaced by much more productive high-level software (such as Cobol, Fortran, and Algol). It's easy to assume that anything "high-level" will automatically be better and that's often true. But if you find yourself spinning your wheels trying to get the result you need, then you might want to consider simply "going lower" to get the job done.
The next lesson, Parameter Queries and Data Sources in VB.NET, continues the discussion of using a DataSource, but a new topic, parameterized queries, is introduced. Again, the limitations and a way around them are demonstrated.
The Microsoft VB.NET guru Beth Massi calls this "one-to-many chaining". (I'm indebted to Beth's explanations for what follows here.)
Step one is to add DataGridView controls to the project to set up the chaining. The main goal here is to get Visual Studio to generate the correct code for what we want, so it has to be done in a specific way.
Start with a blank form and a Data Source that includes all three tables in our Recipe Ingredients database. If you have done the examples in the previous parts, you should be able to generate a Data Source for this database in about thirty seconds using the wizard. (I can ... now!) The completed Data Source and blank form should look like this. Notice that there is nothing in the component tray below the form ... yet.
--------
Click Here to display the illustration
--------
With the DataGridView control selected in the Data Source, drag each table to the form.
--------
Click Here to display the illustration
--------
A binding navigator component (RecipesBindingNavigator) is generated for the Recipes table because it's dragged to the form first.
No other table generates one. But each table does generate a binding source and a table adapter. Dragging these components to the form creates nine new entries in the component tray. After you complete this, your form should look something like this:
--------
Click Here to display the illustration
--------
If you run your application now, you get what you want for the first two grids, but not the third (Ingredients). Everything is displayed in that grid, and the navigator toolbar doesn't change what's in it. This happens because there is only one-to-many chaining built into the binding sources. In other words, if you look at the properties of RecipesBindingSource, you see that the DataSource is the DataSet itself and the DataMember is the Recipes table. The binding source for RecipeIngredient, however, is RecipesBindingSource and the DataMember is the foreign key for Recipes to RecipeIngredients. This is one-to-many chaining. Like Recipes, Ingredients uses the DataSet directly as the DataSource again.
To get the effect you need, you have to filter the results for Ingredients based on a change in the selected recipe. This is where the coding comes in. Here's the code that works:
Private Sub RecipeIngredientBindingSource_ListChanged(ByVal sender As Object,ByVal e As System.ComponentModel.ListChangedEventArgs) Handles RecipeIngredientBindingSource.ListChangedIf Me.RecipeIngredientBindingSource.Count > 0 ThenDim filter As New System.Text.StringBuilder()For Each recipeIngredient As DataRowViewIn CType(Me.RecipeIngredientBindingSource.List,DataView)If filter.Length0 Then' Adding criteriafilter.Append(" OR ")End Iffilter.Append(String.Format("IID = {0}", recipeIngredient!IID))NextMe.IngredientsBindingSource.Filter =filter.ToStringElse' display no rowsMe.IngredientsBindingSource.Filter = "1 = 0"End IfEnd SubRecalling that we're working with the set of all of the DataRows in IngredientsBindingSource, we construct a Filter string that consists of an OR concatenated string of all of the Ingredients keys found in a specific Recipes selection. Microsoft explains the Filter property of BindingSource like this:
Typically used in complex data-binding scenarios, the Filter property allows you to view a subset of the DataSource.
The Filter string might look like this:
IID = 29 OR IID = 30 OR IID = 31The string is a constant to select no rows in the Ingredients table if there are no DataRows in the junction table:
1 = 0This gives you what you need. Whenever a selection of a recipe in the first DataGridView changes (that is, the ListChanged event) then the complete results returned for the Ingredients table are filtered. You get only the ingredients and amounts for a specific recipe. It's not very pretty, but at least it's the right result.
--------
Click Here to display the illustration
--------
Customizing this form so it's a little closer to a finished result is traditional programming again (as opposed to database specific programming). You can edit the columns in the DataGridView just as you normally would.
One additional help that Visual Studio gives to you is the ability to match the .NET types to database types, and more exactly control how updates are done. (Neither of these situations apply in this example.) To do this, select the (Advanced) property for the control and then select the specific formatting or updating that you need. This illustration shows the sequence of windows.
--------
Click Here to display the illustration
--------
Much of the history of software is a story of low-level software (such as assembler language) being replaced by much more productive high-level software (such as Cobol, Fortran, and Algol). It's easy to assume that anything "high-level" will automatically be better and that's often true. But if you find yourself spinning your wheels trying to get the result you need, then you might want to consider simply "going lower" to get the job done.
The next lesson, Parameter Queries and Data Sources in VB.NET, continues the discussion of using a DataSource, but a new topic, parameterized queries, is introduced. Again, the limitations and a way around them are demonstrated.
Source...