Most of the classes in the ADO.NET Framework have two versions. One version is designed to work with databases using OLE DB providers, and one version is designed to work specifically with Microsoft SQL Server (version 7.0 or later).

For example, there are actually two Connection classes in the ADO.NET Framework. There is an OleDbConnection class that is used for connecting to a database that has an OLE DB provider and a SqlConnection class for connecting to Microsoft SQL Server.

The OleDb classes can be used with any database that has a native OLE DB provider. Most major databases—including Microsoft Access, Oracle, and Microsoft SQL Server—have an OLE DB provider.

The SqlClient classes can be used only with Microsoft SQL Server (version 7.0 or later). These classes are optimized for SQL Server. They work directly at the network protocol layer and do not use any intermediate layers, such as OLE DB or ODBC to communicate with a database. The SqlClient classes represent the fastest database access technology that Microsoft has ever developed.

So, if you are building an application that is designed to work specifically with Microsoft SQL Server, you should use the SqlClient classes. On the other hand, if you need to communicate with another type of database or there is a possibility that you will need to work with another type of database in the future, you should use the OleDb classes.

The classes in the ADO.NET Framework support two different data access models. You can use the ADO.NET classes to represent data by using a DataSet or a DataReader.

A DataSet represents an in-memory database. When you represent database data with a DataSet, you copy the data from the physical database to the in-memory database represented by a DataSet.

A DataReader, on the other hand, represents only a single record from a database at a time. When you use a DataReader, you loop through the records from the database to retrieve the records into your application.

There are two primary advantages that DataSets have over DataReaders. First, the Visual Studio .NET development environment better supports DataSets. You can use the tools provided by Visual Studio .NET to generate and work with DataSets.

Second, DataSets, unlike DataReaders, can be cached in memory. You can store a single DataSet in your server’s memory and reuse the same DataSet across multiple requests to the same or different pages. Caching data in memory can significantly improve the performance of your application.

However, DataSets also have two significant disadvantages. First, when you represent database records with a DataSet, all the records must be copied into the Web server’s memory. If you need to represent a database table with two billion records, all two billion records must be copied into memory. In contrast, because a DataReader only represents a single record at a time, you can use a DataReader to work more efficiently with large sets of records.

A second, and perhaps more important, disadvantage of DataSets is that they are significantly slower than DataReaders. If you simply need to display the contents of a database table one time, retrieving database data with a DataSet can be significantly slower than retrieving the same data with a DataReader.

You can use the classes in the ADO.NET Framework to represent data by using a relational or XML model. In a relational model, data is represented as rows in a table. In an XML model, data is represented as nodes in a tree.

For example, you can copy the contents of a database table into a DataSet and represent the database data in an XML document. Alternatively, you can load the contents of an XML document into a DataSet and represent the contents of the document as rows in a table.

In this section, you will learn how to add the Authors database table to a DataSet and display the contents of the DataSet with the DataGrid control.

To create and display a DataSet, you’ll need to complete each of the following tasks:

If you haven’t already created a connection to the Pubs database in the Server Explorer window, you’ll need to create one before doing anything else by doing the following:

After you complete these steps, a new connection to the Pubs database is added to the Server Explorer window.

Next, you need to create the DataAdapter by performing the following steps. The DataAdapter is used to retrieve the data from the Authors database table.

The DataAdapter retrieves all the records from the Authors database table.

Next, you need to create the DataSet and fill the DataSet with data from the Authors database table by using the DataAdapter by doing the following:

In the Page_Load event handler, the SqlDataAdapter’s Fill() method is called to fill the DataSet1 DataSet with records from the Authors table.

The final step is to display the DataSet in a DataGrid control. (The DataGrid control is discussed in detail in Chapter 13.) To do so, perform the following steps:

When the DisplayAuthors.aspx page is opened, all the rows from the Authors database table should be displayed (see Figure 8.2). The DataGrid control displays the contents of the DataSet.

Figure 8.2. Displaying the Authors database table.

dataSet1.Tables("Authors")

DataSet1.Tables["Authors"]

dataSet1.Authors

DataSet1.Authors.

A DataView provides you with a particular view of the data contained in a DataSet. You can use a DataView to represent the records in a DataSet sorted in a particular order, or you can use a DataView to represent the records in a DataSet filtered by a particular criterion (or both).

A DataView has two important properties:

For example, suppose that you want to display a list of records from the Titles database table. However, suppose that you want to display the rows sorted by the publication date, and that you don’t want to display any book that costs less than $10.00.

First, let’s create a new Web Form Page that contains all the necessary database objects and controls:

Previously, we’ve bound the DataGrid directly to a DataSet. This time, however, we want to bind the DataGrid to a DataView so that we can sort and filter the contents of the DataSet by doing the following:

Finally, we need to add the necessary code to the page to sort and filter the DataView and bind the DataGrid to the DataView. We do this by performing the following:

When the page opens, the titles should appear in order of the publication date. Furthermore, you shouldn’t see any book that costs less than $10.00 (see Figure 8.3).

Figure 8.3. Displaying records with a DataView.

When representing database data with a DataReader, you need to work with the following three objects:

Like the majority of the other ADO.NET objects, there are two versions of each of these objects. There is an SQL version for working with Microsoft SQL Server (version 7.0 or later) and an OleDb version, for working with any database that has a native OLE DB Provider (all major databases including Oracle and Microsoft Access). For example, there is both a SqlDataReader and an OleDbDataReader.

Perform the following steps to add the database objects that you’ll need to create a SqlDataReader that represents the records from the Titles database table:

Next, we can create the SqlDataReader in the Code Editor by doing the following:

The Page_Load() handler creates a SqlDataReader by calling the ExecuteReader() method of the SqlCommand object. Notice that the database connection, represented by the SqlConnection object, must be explicitly opened before the ExecuteReader method is called.

To test our SqlDataReader, we’ll display its contents in a DataGrid control. (The DataGrid control is covered in detail in Chapter 13.) First we add the DataGrid:

Next, we can bind the DataGrid to the DataReader:

The additional statements in the Page_Load() handler copy the contents of the SqlDataReader into the DataGrid control. When the page is opened, all the records from the Titles database table are displayed in the DataGrid control (see Figure 8.4).

Figure 8.4. Displaying records with a DataReader.

If you add an item to the Cache object in a Web Form Page, the item remains in the cache across multiple page requests. By taking advantage of the Cache object, you need to retrieve a set of records from the database only once.

For example, suppose that you are building an online store that sells a couple thousand products. You could load the list of products into a DataSet and store the DataSet in the Cache. That way, whenever someone wants to view a list of products, the list of products can be quickly retrieved from memory instead of from the database.

Because you can sort and filter records in a DataSet, by using the DataView object, you can even present different views of the items in the Cache. For example, you can display all the products related to Beanie Babies on one page and display all the products related to TeleTubbies on another.

The Cache object has two important methods:

For example, you would use the following code to add the string Hello World! to the Cache object:

C# Code

Cache.Insert( "myString", "Hello World!" );

VB.NET Code. 

Cache.Insert( "myString", "Hello World!" )

This statement adds a new item to the cache named myString that has the value Hello World!. You can abbreviate the previous Insert statement as follows:

C# Code

Cache[ "myString" ] = "Hello World!";

VB.NET Code. 

Cache( "myString" ) = "Hello World!"

You would use the following statement to remove the myString item from the Cache:

C# Code

Cache.Remove( "myString" );

VB.NET Code. 

Cache.Remove( "myString" )

There are two important warnings that you must heed that concern the Cache object. First, the Cache object is case sensitive (even in VB.NET). Consequently, adding an item named Item1 to the Cache is different than adding an item named ITEM1.

Second, items that you add to the Cache are not guaranteed to be there when you attempt to retrieve them. The Cache object automatically drops items from the cache when your Web server’s memory gets too low.

For this reason, you should always check to make sure that an item still exists after you retrieve it from the Cache. You can check whether something exists by comparing it to the value null when using C# or the keyword Nothing when using Visual Basic .NET.

Typically, when you want to retrieve an item from the Cache, you use logic that looks like the following:

C# Code

string strMessage = "";

strMessage = (string)Cache["message"];
if (strMessage == null)
{
  strMessage = "Hello World!";
  Cache["message"] = strMessage;
}
Response.Write(strMessage);

VB.NET Code. 

Dim strMessage As String
strMessage = Cache("message")
If strMessage = Nothing Then
  strMessage = "Hello World!"
  Cache("message") = strMessage
End If
Response.Write(strMessage)

In this example, an item named message is stored in the Cache. After the item is retrieved from the Cache, the item is compared against either null or Nothing. If the item doesn’t have a value, the item is recreated and added to the Cache. In either case, the value of the item is displayed.

In this case, we added a string to the Cache. However, you’ll use the same logic with any item, such as a DataSet, that you might add to the Cache.

In this section, you will learn how to store the Products database table in the Cache object. You will load the Products database into a DataSet and add the DataSet to the Cache.

You’ll create a page that displays the cached Products table in a DataGrid. To create this page, you’ll need to complete the following steps:

The first step is to create the connection to the Northwind database. The Northwind database is a sample database that is automatically created when you install SQL Server. Perform the following steps to create the connection:

Completing these steps will add a new connection to the Server Explorer window.

Next, we need to create a new Web Form Page and add the necessary database objects and controls to the page by doing the following:

Finally, we need to fill the DataSet with data from the Products database table by using the DataAdapter, add the DataSet to the Cache, and display the cached data in a DataGrid.

The first time you open the CachedProducts.aspx page, the Products database table will be retrieved from the database and the message “Retrieving data from database!” will appear in the Label control (see Figure 8.5). However, if you refresh the page (by selecting Refresh from the View menu), the Products table will be retrieved from the Cache object instead of the database.

Figure 8.5. Displaying cached records.