Home arrow ADO.NET arrow Page 4 - All You Need To Know About ADO.NET: Part 1/2
ADO.NET

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


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.

Author Info:
By: Vaijayantee Sateesh Kamat
Rating: 4 stars4 stars4 stars4 stars4 stars / 104
April 14, 2002
TABLE OF CONTENTS:
  1. · All You Need To Know About ADO.NET: Part 1/2
  2. · Data binding in Windows application
  3. · Binding Controls to Data Sources
  4. · Creating a master/detail app
  5. · Conclusion

print this article
SEARCH DEVARTICLES

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
ADO.NET ARTICLES

- Datasets in Microsoft.Net
- Latest Developments in the .Net World
- Introduction to .NET
- Automatic Generation of Single Table SQL Sta...
- Data Access in .NET using C#: Part 1
- All You Need To Know About ADO.NET: Part 2/2
- All You Need To Know About ADO.NET: Part 1/2
- Easing Transition From ASP and ADO to ASP.NE...
- A Practical Comparison of ADO and ADO.NET

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials