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

Print Add To Favorites Email To Friend Rate This Article
All You Need To Know About ADO.NET: Part 1/2

Published: 08 Oct 2003

Unedited - Community Contributed

Abstract
ADO.NET for businesses. Use the Visual Studio.NET wizards to generate and map data to controls on the fly. Set up relationships between two tables and use these relationships to display orders related to one specific customer.

by Devarticles.com
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 37992/ 77

Article Contents:

Introduction
Data Binding in Windows Applications
Navigation Does Not Update the Data
Using the DataAdapter Configuration Wizard
Binding Controls to Data Sources
Creating a Master/Detail App
Allowing Updates via Bound Controls
Update Method of the DataAdapter
Conclusion

Introduction

[ Back To Top ]

Fundamentally, almost all business applications need some type of access to data. In addition, many a time, business components need to interact with each other using XML and XML based Internet services. This is where ADO.NET comes into picture.

Microsoft has provided us with several high level data access API's.

The data access method called Data Access Objects or more popularly known as DAO was primarily intended for jet databases and was optimized for it although it could be used for ODBC based data sources.

In order to deal with server based databases more efficiently, Microsoft built a wrapper on top of ODBC. It was called Remote Data Objects (RDO), and it provided an object model similar to DAO. Unfortunately, as the objects could not be marshalled across the net, it was of limited use.

DAO and RDO both used the API approach for accessing data. Microsoft wanted to use a COM based approach to data access, and the result was OleDB and ADO. ADO is the OLE automation implementation built on top of OleDB. ADO was first introduced with the first version of ASP. The primary advantage with ADO was improved support for disconnected data and the ability for a disconnected recordset to marshall over the network.

Microsoft decided to create a database access model to go with the .NET Framework. It mainly focuses on the following key goals:

XML integration
Disconnected data model
Scalability

[NOTE] Although similar in concept, ADO.NET has different functionality from ADO. Experienced VB programmers will find that more often than not, they will not be using the recordets they are familiar with. ADO.NET introduces quite a few objects used for data access in various manners and conditions. [End Note]

ADO.NET is broken down into 3 namespaces in the .NET framework. Namespaces are used to organize components into groups based on organizational and logical reasons.

In previous versions of ADO, an attempt was made to create as generic methods as possible, whereas with ADO.NET, the data provider classes for SQL server are now optimized. Optimized data providers for specific database products can now be created also.

From a developers point of view, objects provided by OleDB managed providers and SQL server managed providers are quite similar, the only difference being they are prefixed with SQL rather than OleDB.

Let's now see how to use ADO.NET in an application. We will use the Pubs sample database that is included with SQL server 2000. We will use the Authors, Publishers, Titles and TitleAuthor tables. Although we will use OleDB managed providers, the same code can be used for SQL managed providers very easily. You can even use access tables in place of SQL tables if you do not have SQL tables.

Data Binding in Windows Applications

[ Back To Top ]

There are several ways to use ADO.NET in a Windows application. One of the easiest is by using a data form. Let's now see how to use a data form to access data from Authors table.

Using the Data Form Wizard The data form wizard is a handy tool. It helps you create data bound controls on windows form or web pages. The wizard itself writes code for loading the controls with data or updating the data.

Running the Data Form Wizard is like adding an item to a project, but here, we have to keep in mind that a Data Form can be added to a project that supports forms. The task of creating Data Forms can be accomplished with following few steps:

1. Start a new project. Select Add New Item from File menu.

2. Select Data Form from the dialog box.

3. The wizard opens up automatically with a welcome screen. Just press next and it starts working.

4. On the next page of the wizard, you have to select an existing DataSet or create a new one. Here, we'll create a new one and use the Pubs database for that. The next few steps will explain how to do that. Enter "DSet1" for your dataset's name and select "New Connection" from the next screen.

5. This opens up the Data Link dialog box. You can connect to the server and set a link to the required database here.

6. Once the link is established, the Data Form wizard takes over again showing tables from the database. Select the required table (authors ) and add it to the "Selected Items" by clicking on the ">" button:

Data Form Wizard.

7. From the "choose tables and columns to display on the form" dialog box, select the fields that you need. The Data Form Wizard is about to complete its job. It will now show the final screen asking us if we want all the rows to be displayed at a time in the DataGrid control, or a single row at a time, bound to controls. Let's select all the rows in a grid control for this example.

The wizard now generates the DataForm and adds it to the project. The form contains a button for loading the data in the grid, and also buttons for canceling all and updating. Code for the click events of these buttons is written by the wizard making our lives easier.

To run the project you either have to write some appropriate code or make the DataForm a StartUp object. You can see the DataForm in action once you click the "Load" button.

Similarly, if you had selected individual records, the wizard will ask if you need controls for editing and navigation. If you select these controls, then it will generate the form using controls for every field and create the navigation and other buttons for you.

Navigation Does Not Update the Data

[ Back To Top ]

An important thing to note here is unlike the previous versions of data access objects, ADO.NET uses disconnected data. Older versions used to update the data once we made changes and navigated through the data, but now the navigation doesn't update the data to the underlying SQL table unless we click the update button manually.

Using the DataAdapter Configuration Wizard

[ Back To Top ]

The DataAdapter is an object that's supported by the managed providers for ADO.NET. It acts like a bridge between the DataSet and the data source. DataAdapter's are used to exchange data between a DataSet and a data source, and in most of the applications, this means reading data from the database into a DataSet and writing back from the DataSet to the database.

Database applications can be created with relative ease using the Data Adapter Configuration Wizard. The first step is to start a new project. Drag the sqlDataAdapter from the Data panel of the toolbox on to the form. This will start the wizard. Once the DataAdapter Configuration wizard is invoked, we have to establish a connection just like we did with the Data Form Wizard. Having established an existing connection, or after creating a new connection, the wizard lets us choose the type of query we want it to execute.

The option to use SQL statements will make the wizard generate Insert, Update and Delete statements, depending upon the select statement given by us. It will use the same filter that we have used in the where condition.

This time around, instead of SQL statements, the wizard generated stored procedures because they are precompiled and therefore they run faster. Stord procedures should be your primary choice for maximum performance.

If we wanted to, we could also use an existing stored procedure:

Data Adapter Configuration Wizard.

The screen above generates SQL statements. We can either enter our own select statement like we have done above, or the query builder can be used. It lets us select tables and the fields that we require to be displayed.

The advanced options allow us to specify following options:

1. Generate SQL statements for Insert/Update/Delete.
2. Use optimistic concurrency: This will make the wizard to include logic to test whether a record has changed since you read it from the database.
3. Refresh the DataSet: This will make the record read the updated records once again and give a refreshed view of the data.

Finally, the wizard presents us with the view of everything it has done before finishing the task.

Now that we can use the configured data adapter in our project the DataAdapter object that we just created will be added to a separate component tray which doesn't show up on the form.

One thing we have to remember here is that the DataAdapter assumes that it will be reading and writing to the same table ,and if this is not the case we will have to change the table mappings.

The next step is to create the DataSet. Selecting "Generate DataSet" with the red arrow creates it. After selecting Generate DataSet, we are present with the options for its name and such details.

Once the DataSet is created, we are ready to work with the data. The data can be viewed with the Preview Data context menu of the form (This option is also available on the Data menu). The preview data context menu brings up a preview form and clicking the "Load"’ button displays it.

Now that the DataSet is ready, let's see how we can manipulate it. I have selected the same authors' table from the pubs database for my DataSet. You can use whatever table you like for this. Place a DataGrid control on the form and set its DataSource property to the DataSet we have just created. Add a button to the form for loading the data; we only need one line of code to fill our grid with data!

The code for the click event of the button looks like this:

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoad.Click

SqlDataAdapter1.Fill(DataSet11, "Authors")

End Sub

Run and test the program. When you click the Load button, the output should be similar to that shown above.

To summarize what we've done so far, both the wizards (the Data form wizard and DataAdapter wizard) have definitely made our life as a database programmer a little easier. We have to remember one thing however: ADO.NET by default uses disconnected data and we have to specifically write update code if we want to make changes to the underlying table. Instead of changing the data in the table every time, this feature allows us to make all the changes to the table with just one stroke.

Binding Controls to Data Sources

[ Back To Top ]

With the DataAdapter Wizard, we have seen how to bind a DataGrid Control to the table. We can also bind other different types of controls to the database. If we want to view all the records at a time, generally we would use a DataGrid control. To view individual records, we normally bind controls like a Textbox to the table.

Types of Controls to Bind
Unlike VB6, data binding has a much broader sense in VB.NET. Any property of any control can be bound to any structure that contains data. This is achieved by using the DataBindings property. This property contains a collection of Binding objects for the control. Traditionally, we typically bind the display property of a control to a data source (e.g. Text property of a Textbox control). But in VB.NET we have the option to bind other properties also. We can bind properties like background/foreground color, size, image etc. for a control to a data source, making for a totally flexible setup.

VB.NET supports different types of User Interfaces and each of them requires different controls. The Web and Windows are the two different User Interfaces supported by VB.NET. Although many of the controls for both of the interfaces look similar, there are some differences in the way that they are used.

Data binding in windows forms
Simple Data Binding: Generally, a control's ability to bind to an element like a value in a table column is thought of as Simple Binding. Binding the Text property of a Textbox to a single value in a column of a table is a good example. The Textbox, Label, ListBox, ComboBox and CheckedListBox controls are all good examples of controls that are frequently bound to data in a windows application.

Complex Data Binding: Data binding is said to be complex when more than one element from a record can be bound to a given control. The DataGrid is a good example of this where we mostly bind entire records to the control.

Data binding in web forms Often web forms use data from database, XML documents, streams or some other data source. Due to the nature of web applications, data access in such applications differs from that of a windows application. The data binding architecture assumes that most of the time, data requirements for a web application are read only. Therefore, web form data architecture does not perform updates. We have to provide the logic ourselves if we want to do that. The logic will largely depend upon the type of the data set as well as the control being used, and the concurrency requirements.

Just like windows forms however, you can bind the controls to any structure that provides data, although normally they are bound to a DataSet control. Some Web Form controls display values from multiple records at once. These controls include the Repeater, DataList, DataGrid, ListBox, CheckBoxList, and RadioButtonList Web server controls, as well as the HtmlSelect control. Other controls, such as the Label, TextBox, CheckBox, and Hyperlink controls display single values, which in data-binding terms means values from a single record.

Types of properties to bind
We have already seen that VB.NET allows almost all properties of a control to be bound to a data source. Let us see how we can bind these properties to controls right now.

We can create simple data bound controls using the following steps:

1. Start a new Windows application. Connect to a data source. Creating a DataAdapter and generating a DataSet will be one of the quickest ways to start.
2. Design the form, laying out the controls to be bound to data. Select a control to be bound (say a Textbox) and display the properties.
3. Expand the DataBindings property of the selected control. Properties most frequently bound for that control are displayed in this collection. (For a Textbox, the Text property will be displayed here).
4. Expand the list of available data sources for that property. It will display the data sources available for binding. Select the one you want to use. (Say the DataSet created in the last example. Suppose, your DataSet contains records from Authors table, Select the DataSet and expand it. It will show the list of fields available for binding. Select the field you want to bind to the control.
5. If the property to be bound is not found under the DataBindings, then we can click the Ellipsis (...) button to display the advanced data binding properties. This opens up an Advanced Data Binding Dialogue Box that displays all the properties. We can select the required property and data source to be connected from the dialog box.

For controls like the ComboBox, ListBox etc, their DisplayMember property is normally bound to the data source.

Since simple-bound data controls show only one value from a column at a time, it is typical to write navigation logic for such controls. Complex Data Binding is the ability of a control to bind to more than one single element, typically more than one record at a time. The DataGrid and ListBox controls are good examples of this. We have bound a DataGrid control to a data source when we used the DataAdapter configuration wizard earlier.

Creating a Master/Detail App

[ Back To Top ]

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:

[code="xml"]

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

[/code]

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

[ Back To Top ]

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

[ Back To Top ]

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

Conclusion

[ Back To Top ]

In this article we've seen how to use the Visual Studio.NET wizards to generate and map data to controls on the fly. We've also seen how to setup relationships between two tables and how to use these relationships to displays orders related to one specific customer.

In part two of this article we'll discuss the purpose of ADO.NET and also look at scalability, XML and ADO.NET, the DataAdapter object, the DataSet object, typed versus un-typed datasets and more.

User Comments

Title: PC Analyst
Name: Eddie Mathews
Date: 2006-11-27 6:23:17 PM
Comment:
I am new to Visual Studio after many years of employment in other industries. Now I want to come but I am having problem locating the Data Form Wizard. I know my installation is correct.

Thanks buddy

Title: Joe
Name: Joseph David
Date: 2006-05-04 5:32:24 AM
Comment:
Click any Customer from the first grid and you will notice the second grid displaying records only for the selected Customer

how this work

Product Spotlight