In this, the final part of her two part series on ADO.NET, Vaijayantee talks about the purpose of ADO.NET, scalability, XML, the ADOCommand and SQLCommand objects and more. If you're just new to .NET then Vaijayantee's ADO.NET tutorial series will have you up and running with databases in a short time.
All You Need To Know About ADO.NET: Part 2/2 - Building An In-Memory Schema (Page 3 of 6 )
A DataSet is not directly tied to a data source. It's a temporary storage area for data coming from multiple data sources. Yet, we still can work with it using a consistent object model, regardless of its original source.
Data Is Persisted as XML Data needs to be moved from the data source to the DataSet and from there to various components. In ADO.NET, the format for transferring data is XML. Similarly, if data needs to be persisted (for example, into a file), it is stored as XML. If you have an XML file, you can use it like any data source and create a DataSet out of it.
In ADO.NET, XML is a fundamental format for data. The ADO.NET data API's automatically creates XML files from information in the DataSet and sends them to another component. The second component can invoke similar API's to read the XML back into a DataSet. Basing data protocols around XML offers a number of advantages as we have already seen. For most scenarios, we do not need to know XML in order to use data in ADO.NET. ADO.NET automatically converts data into and out of XML as needed; you interact with the data using ordinary programming methods.
Schemas Define Data Structures Although most of the time we don't need to know about XML or how to read or write the XML data, there are some times when that is precisely what we look for. These are the situations when we have to design data while the application is running. To put it another way, in ADO.NET we can use XML directly when we are working with metadata.
Datasets are represented as XML. The structure of the DataSet, the definition of tables, columns, data types, constraints, and so on are in the DataSet which is defined using an XML schema based on the XML schema definition language (XSD). Just as data contained by a DataSet can be loaded from and serialized as XML, the structure of the DataSet can be loaded from and serialized as XML Schema.
Schema For most of the work done with data in ADO.NET, you don't have to delve deeply into schemas. When you use the tools to create a DataSet representing tables in your database, Visual Studio .NET generates an XML Schema describing the structure of the DataSet. The XML Schema is then used to generate a typed DataSet, in which data elements (tables, columns, and so on) are available as first-class members.
However, there are times when we have to create or edit schemas ourselves. A typical example is developing a schema in conjunction with a partner or client. One will often have to map the schema elements to the structure of one's own database. VB provides us with visual tools for working with XML files/schemas/ADO.NET Datasets. It's called the XML Designer. The XML Designer is used to create and edit XML Schema files, ADO.NET Datasets, XML data files, and XML source code. The XML Designer has three views.
DataTable Objects We use RecordSet objects as tables with ADO. Each RecordSet object corresponds to a database table. With Datasets, a similar function is handled by the DataTable object. A DataTable object is like a database table. It belongs to the datatables collection of the DataSet. If the DataSet contains data from multiple tables, one DataTable corresponds to one table or view. This way, the DataSet can mimic the structure of its underlying database. When we say that the DataSet is a container for data, we must keep in mind that the data is actually contained in the DataTable objects. In other words, a DataTable is an in memory cache of the data from one table. A DataTable can be instantiated just like any other object in VB.NET with the "New" keyword.
DataRelation Object Many a time an application has to work with related data. A DataSet does not have a database's inherent capacity to maintain relations, so we have to create a DataRelation object that can maintain these relations.
If we continue with the Northwind example, a DataSet might have some data from the Customers table and the Orders table. Even if the tables contain a key in common (CustomerID), the DataSet itself does not keep track of the records in one table that relate to those in another. You can create a DataRelation object that references the parent and child tables (and their keys), and then use the object to work with the related tables.
The DataRelation object can perform two functions:
It can provide either with the child record or with the parent record depending upon what we are working with.
It can enforce constraints for referential integrity, such as deleting orphaned records.
It is important to understand the difference between a true join and the function of a DataRelation object. In a true join, records are taken from parent and child tables and put into a single, flat recordset. A DataRelation object maintains the synchronization between the related tables and no new recordset is created. Instead, the relation tracks the relationship between tables and keeps parent and child records in synch.
Accessing Related Records If tables in a DataSet have a logical relationship, a DataRelation object can make the related records in another table available to us. We can create a DataRelation object whose properties are set to reflect the common keys. We can then use the DataRelation object to get related records. Instead of joining the tables, we call the GetChildRows method of a data row in the parent table, passing the DataRelation object that defines the parent/child relationship to it. The method returns an array of related child records.
The following code illustrates a simple example of getting related records. In this example, the array DRelnArray is set to the child records of the first row in the Customers table. This assumes that the relation CustomesrOrders is added to the DataSet: