Fundamentally, almost all business applications need some type of access to data stored in a database. They need access to this data quickly, and in a number of different formats. This is where ADO.NET comes into the picture. In this series of two articles, Vaijayantee explains exactly what ADO.NET is, including discussions on scalability, architecture, etc. She also shows us how to use ADO.NET with VB.NET and Visual Studio.NET.
All You Need To Know About ADO.NET: Part 1/2 - Creating a master/detail app (Page 4 of 5 )
Most commercial applications use data in some way or the other. Many times, related data from two or more tables has to be used in these applications. We can define relations between two tables easily by using a DataSet.
Let's create a Master / Detail relation application using the Northwind Database that comes with the SQL Server. We will use the Customers and Orders tables for this example. The Customers table contains information about the customers and the Order table contains information about orders. Orders are placed by customers and hence each order must be related to a customer.
In our example, we will show the customers in one DataGrid and orders placed by a selected customer in the second DataGrid.
Steps to create a Master/Detail Application
1. Start a new Windows application.
2. Place 2 DataGrids and a command button on the form.
3. Configure 2 DataAdapters for the Customers table and Orders table respectively with the help of the DataAdapter Configuration wizard. If you want to try out something different, use the Server Explorer instead.
If the Server explorer is not open, open it from View menu.
Expand the servers.
Expand appropriate SQL server instance.
Expand Northwind Database.
Select Customers table and drag it on the form.
Select Orders table and drag it on the form
This will create the two required DataAdapters.
5. Generate a DataSet named DS11 for both the adapters:
Select first adapter and click on Generate DataSet.
Call the DataSet DS11.
Select Customers table from the wizard.
Select second Adapter and click on Generate DataSet.
Select the same DataSet DS11.
Select the Orders table.
6. Write following code in the click event of the Load Button:
Private Sub BtnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Fill Data Sets Me.SqlDataAdapter1.Fill(DS11, "Customers") Me.SqlDataAdapter2.Fill(DS11, "Orders") Me.SqlDataAdapter3.Fill(DS11, "Order Details")
The application is now ready to run. Click the Load button. Click any Customer from the first grid and you will notice the second grid displaying records only for the selected Customer.
Allowing Updates via Bound Controls A DataSet is an in memory copy of the data, updating is a two step process with the DataSet. The process of writing data to the table is distinctly separate from writing to the DataSet.
The first step is to update the DataSet with new information such as modifications, deletions etc. The DataSet does not automatically pass these changes to the underlying database. That has to be done explicitly. This is the second step in updating the database. Typically, this is achieved by calling the Update method of the DataAdapter. This method loops through the database checking every record for modification requirements such as Update, Insert or Delete. On finding modified records, the DataAdapter executes the appropriate update.
RowState Property For finding out if a record has been modified or not, the RowState property of the DataSet is used. It has 4 values:
Update method of the DataAdapter Action is taken depending upon the value of RowState. If the value of the RowState is let us say "Modified", then the Update method automatically transmits the SQL Update statement. The transmitted SQL statement also consists of the appropriate WHERE clause so that the changes are made only to that particular record.
Create a new Windows application. Add an SqlDataAdapter, DataSet (DS11) and a form with a DataGrid and Load, Clear as well as Update buttons. In the Load buttons code, use the fill method of the DataAdapter to fill DS11 and set the DataSource property of the DataGrid to DS11.
In the clear buttons code, just set the DataSource of the DataGrid to Nothing. In the update button, call the Update method of the DataAdapter that writes the changes to the table. The code could looks something like this:
Private Sub BtnUpd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click