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:
[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.
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:
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.
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 Application1. 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:
6. Write following code in the click event of the Load Button:
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.
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