All You Need To Know About ADO.NET: Part 1/2

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")

DS11.Relations.Add("CustOrders",_
DS11.Tables("Customers").Columns("CustomerID"),_
DS11.Tables("Orders").Columns("CustomerID"))
DataGrid1.DataSource = DS11
DataGrid2.DataSource = DS11

DataGrid1.SetDataBinding(DS11, "Customers")
DataGrid2.SetDataBinding(DS11,"Customers.CustOrders")

End Sub

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:

• UnChanged
• Modified
• Deleted
• Inserted

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

Me.SqlDataAdapter1.Update(DS11)
MsgBox("Database Updated",_ msgBoxStyle.Information, "Done")

End Sub

To test this code, follow these steps:

• Run the application
• Load the data and make some changes to the data
• Click the update button
• After the message box is displayed, clear the grid
• Load it once again and you will see that the changes are written to the table

blog comments powered by Disqus