The first article in this series, A First Database Centered Application for Beginners, was designed to show how the latest controls and tools packaged with Visual Studio could make creating and using a database as easy as the traditional sequential file. I picked the application, a "recipe file", for it's simplicity. The only real downside to using these advanced tools is that since most don't ship with Microsoft's free Express versions, I have to use the commercial versions of both Visual Studio and SQL Server for the article. But the application used only a couple of lines of VB.NET code to create a basic recipe file that even included the ability to add, update, and delete.
In the first article, most of the features were due to the DataGridView control and the ability of that control to "bind" directly to a database. While it's fast and convenient (and even efficient), it's not easy to see what's actually happening so it doesn't help you learn and understand. For that reason, I reversed course in the second article, Adding To Your First Database Centered Application and showed the detailed code required to access a database. A new twist was added by accessing the data from two related tables using a SQL JOIN statement.
That was good as far as it went, but it didn't go far enough. Database programmers refer to the four operations that can be done with a database as CRUD. No, really. Create-Read-Update-Delete. We only did one of those in the second article: Read. The reason is that the other three involve much more code to handle the relationship between the two tables. But in this article, we dig into that problem.
Recall from the second article that we established a one-to-many relationship between the Recipes table and the Ingredients table by adding a foreign key to the Ingredients table. Here's a slightly smaller version of the important fields from those two tables:
Recipe ID Recipe Name
--------- -----------
1 Gruel
Ingredient ID Recipe ID Ingredient Name Amount
------------- --------- --------------- ------
1 1 Floor Sweepings 1 pound
2 1 Mud 1 quart
3 1 Grass Clippings 2 bags
Joining these tables using the Recipe ID lets us retrieve the right ingredients for the recipe.
But, how do you do the other three CRUD operations? Just as before, there are design questions to be answered first.
The first question is what should happen if a row in one table is added when there is no related row in the other one. Put another way, should an ingredient be added to the Ingredients table when it's not part of any recipe? This question, and others like it, are what the "analysis" phase of a project is all about. The way you answer this question makes a huge difference is how well your database works and most of this article is about showing why. Even a database this simple can get you into trouble with a design that doesn't work.
First, however, let's modify the app from the second article to include a new form just for updating the Ingredients table. To do this, I added a MainMenu form and call the different sub forms as dialogs. (Remember to make MainMenu the Startup form in the project properties!)
Public Class MainMenu
Private Sub btnGetIngredients_Click(
ByVal sender As System.Object, ByVal e As System.EventArgs
) Handles btnGetIngredients.Click
GetIngredients.ShowDialog()
End Sub
Private Sub btnAddIngredients_Click(
ByVal sender As System.Object, ByVal e As System.EventArgs
) Handles btnAddIngredients.Click
AddIngredients.ShowDialog()
End Sub
End Class
After creating a simple form with just two textbox controls and a button, the code to add a row to the Ingredients table looks fairly straightforward. (This code is designed to illustrate what's actually happening and is not intended to be an example of production database code.)
Dim connectionString As String =
"Data Source=MUKUNTUWEAP;" &
"Initial Catalog=RecipeToIngredients;" &
"Integrated Security=True;Pooling=False"
Dim linkToDB As New SqlConnection(connectionString)
Dim insertStatement As String =
"INSERT INTO Ingredients (Ingredient, Amount) " &
"VALUES('" & IngredientIn.Text &
"', '" & AmountIn.Text & "')"
Dim commandWrapper As New SqlCommand(
insertStatement, linkToDB)
linkToDB.Open()
Dim count As Integer =
commandWrapper.ExecuteNonQuery
linkToDB.Close()
One final adjustment has to be made to make this work. Since we're adding an ingredient without adding the recipe that it's in, there is no recipe ID (the RID field). So we have to change the database definition to allow this field to accept a null value. You can do this is Visual Studio's Server Explorer tool. The process is shown below.
--------
Click Here to display the illustration
--------
This program works and allows ingredients to be added, but there are problems now that have to be solved as a result. Consider the updated Ingredients table shown below:
--------
Click Here to display the illustration
--------
When you're adding a recipe, you can't just add the right ingredient because there may be several (or none) in the database with different Amount fields. We solve this problem on the next page!