An Overview of ADO.NET

We'll begin with a quick tour of ADO.NET. The .NET framework contains several namespaces with dozens of classes devoted to database access. However, if you examine the classes contained in these namespaces, you'll notice that the classes are very similar. There is a simple reason for this. Microsoft has created separate namespaces that are optimized for working with different data providers (different types of databases).

The following data provider specific namespaces are included with ADO.NET:

• System.Data.SqlClient ” Contains classes for connecting to Microsoft SQL Server version 7.0 or higher

• System.Data.OleDb ” Contains classes for connecting to a data source that has an OLE DB provider

• System.Data.Odbc ” Contains classes for connecting to a data source that has an ODBC driver

• System.Data.OracleClient ” Contains classes for connecting to an Oracle database server

NOTE

It is expected that additional data provider-specific namespaces will be released over time. Microsoft has already released a separate set of classes for working with Microsoft SQL Server in the Compact Framework, and a separate set of classes for working with XML generated from SQL Server 2000. For more information on these additional namespaces, see the msdn.microsoft.com Web site.

Oracle has also released their own namespace for working with Oracle databases. You can download the Oracle provider for .NET (ODP.NET) from the Oracle Web site.

The System.Data.SqlClient namespace includes the following three classes:

• SqlConnection

• SqlCommand

• SqlDataReader

If you plan to build your ASP.NET application with Microsoft SQL Server (version 7.0 or higher), you'll use these classes most often. These classes enable you to execute SQL statements and quickly retrieve data from a database query.

The SqlConnection class represents an open connection to a Microsoft SQL Server database. The SqlCommand class represents a SQL statement or stored procedure. Finally, the SqlDataReader class represents the results from a database query. The next section of this chapter goes into the details of using each of these classes.

ASP CLASSIC NOTE

If you have used ActiveX Data Objects (ADO), these three classes should be familiar to you. The SqlConnection and SqlCommand classes are similar to the ActiveX Data Objects Connection and Command objects, with the important exception that they work only with Microsoft SQL Server.

The SqlDataReader class is similar to an ActiveX Data Objects Recordset object opened with a fast, forward-only cursor. However, unlike Recordset , SqlDataReader does not support alternative cursor types, and it works only with Microsoft SQL Server.

The first group of classes, from the System.Data.SqlClient namespace, works only with Microsoft SQL Server. If you need to work with another type of database, such as an Access or Oracle database, you need to use the classes from one of the other data provider-specific namespaces.

For example, if you want to connect to a Microsoft Access database, then you'll need to use the classes from the System.Data.OleDb namespace. The System.Data.OleDb namespace includes the following classes:

• OleDbConnection

• OleDbCommand

• OleDbDataReader

Notice that these classes have the same names as the ones in the previous group, except that these class names start with OleDb rather than Sql .

The OleDbConnection class represents an open database connection to a database, the OleDbCommand class represents a SQL statement or stored procedure, and the OleDbReader class represents the results from a database query.

Why did Microsoft duplicate these classes, creating different versions for different types of databases? By creating separate sets of classes, Microsoft was able to optimize the classes.

For example, the OleDb classes use OLE DB providers to connect to a database. The SQL classes, on the other hand, communicate with Microsoft SQL Server directly on the level of the Tabular Data Stream (TDS) protocol. TDS is the low-level proprietary protocol used by SQL Server to handle client and server communication. By bypassing OLE DB and ODBC and working directly with TDS, you get dramatic performance benefits.

NOTE

You can use the classes from the System.Data.OleDb namespace with Microsoft SQL Server. You might want to do so if you want your ASP.NET page to be compatible with any database. For example, you might want your page to work with both Microsoft SQL Server and Oracle. However, you lose all the speed advantages of the SQL- and Oracle-specific classes if you use the System.Data.OleDb namespace.

In general, the classes in the different namespaces duplicate the same functionality. Just remember to use the appropriate classes for your data provider. We'll be concentrating on the classes from the System.Data.SqlClient and System.Data.OleDb namespaces in this chapter.

NOTE

You examine another important set of ADO.NET classes in Chapter 12, "Working with DataSets." The classes in this group are used to represent memory-resident, disconnected database data. These classes include DataSet , DataTable , and DataAdapter .