This section, deals with the representation of database data, in a disconnected, memory-resident database by taking advantage of ADO.NET DataSets.
The DataSet, which is an in-memory cache of data retrieved from a database, is a major component of the ADO.NET architecture. The DataSet consists of a collection of DataTable objects that can be related to each other with DataRelation objects. Data integrity can be enforced in the DataSet by using the UniqueConstraint and ForeignKeyConstraint objects.
While DataTable objects contain the data, the DataRelationCollection allows navigating through the table hierarchy. The tables are contained in a DataTableCollection accessed through the Tables property. When DataTable objects are accessed it is important to know that they are conditionally case-sensitive.
For example, if one DataTable is named "mydatatable" and another is named "Mydatatable", a string used to search for one of the tables is regarded as case-sensitive. However, if "mydatatable" exists and "Mydatatable" does not, the search string is regarded as case-insensitive.
In the previous section of ADO.NET, uses of Command and DataReader classes, to modify and represent the database data were examined. This section, will examine a separate set of classes, that enables to work with database data.
One can ask, as to why two sets of classes are used?
The two sets of classes are used in different applications. If some database records are to displayed on a Web page, the Command and DataReader classes are used. If, on the other hand, work needs to be done with a disconnected and memory-resident representation of Database records, DataSets are used.
In the section of ADO.NET, the use of DataReader to represent database records was learned. However, the DataReader represents only one database record at a time. To get a new record from the underlying database table into memory, the Read() method is used. But each time the Read() method is used, the previous record is lost.
On the whole, the DataReader cannot be used to work with the results of a database query. For example, no sorting, filtering, or retrieving of the number of records can be done in a DataReader, because at a time, only one record from a database query is represented.
Further, to be able to work, the DataReader must remain connected to a database table. A DataReader is tied down to its underlying data source. This means, that a DataReader cannot be cached in the server's memory, so that the same DataReader can be used for multiple pages or multiple requests of the samepage.
On the whole, a DataSet enables to represent the results of a database query in the server's memory, because it provides a memory-resident representation of data. For example, a DataSet uses sorting, filtering, and retrieving a count of the records from a database query.
Unlike a DataReader, a DataSet represents a disconnected set of records. When a DataSet is populated with records, the connection can be broken to its underlying data source.
This means that a DataSet can be easily cached in the server's memory to achieve dramatic performance benefits. They can be used to improve the performance of the Web applications.
If the same set of records needs to be represented over and over again in multiple pages or across multiple requests of the same page, the records can be represented in a DataSet and cached in the server's memory. Caching the database records, avoids the connection to the database server, which is a resource-intensive task.
One of the disadvantage of DataSets is that, it occupies a lot of memory.
If a DataSet is used in an ASP.NET page to represent a table with 10,000 database records, then 10,000 records are represented in a memory. If 100 people together request the same page, 1 million records are represented in the server's memory.
So, it is advisable that whenever a different set of records are requested to be retrieved, a DataReader must be used.
On the other hand ,If the same set of records are required to be displayed, a DataSet should be used.
Elements of Datasets
To work with the DataSets, the following ADO.NET classes are used:
It can contain one or more DataTables, that represents database tables.
These classes define the relationship between the tables. (such as parent/child relationships)
It represents a particular view, that is filtered or sorted of a DataTable.
The DataSet, DataTable, DataRelation, and DataView classes, all live in the System called the Datanamespace. There's one more class that need to used when working with DataSets: the DataAdapter.
It represents the bridge between a DataSet and the data source.
DataAdapter is used, to populate a DataSet from an existing database table. It is also used, to update an existing database table with the changes made to DataSet.
While working with a Microsoft SQL Server (version 7.0 or higher) database, SqlDataAdapter is used, which can be found in the System. Called Data. SqlClient namespace. Whereas, working with other databases, such as Oracle or Microsoft Access, the OledbDataAdapter, is used, which can be found in the System. Data. oleDb namespace.