Over view of ADO.NET Classes and Objects

The diagram in Figure 24-1 shows the basic classes in ADO.NET. Note that this is not an inheritance diagram but rather shows the relationships between the most commonly used classes.

Figure 24-1

Here the classes are divided into .NET data provider objects and consumer objects.

• Provider objects are specific to each type of data source — the actual reading and writing to and from the data source is done with the provider-specific objects.

• Consumer objects are what you use to access and manipulate the data once you have read it into memory.

The provider objects require an active connection. You use these first to read the data, then depending on your needs, you may work with the data in memory using the consumer objects. You can also update the data in the data source using the provider objects to write the changes back to the data source. Thus, the consumer objects operate in a disconnected fashion; you can work with the data in memory even if the database connection is down.

Provider Objects

These are the objects defined in each .NET data provider. The names are prefaced with a name unique to the provider; so for example the actual connection object for the OLE DB provider is OleDbConnection; the class for the SQL Server .NET provider is SqlConnection.

Connection Object

The connection object is typically the first object that you will use, before using most of the other ADO.NET objects — it provides the basic connection to your data source. If you are using a database that requires a user and password or one on a remote network server, the connection object takes care of the details of establishing the connection and logging in.

Note

If you are familiar with classic ADO, you'll note that Connection and other objects that serve a similar function in classic ADO have similar names in ADO.NET.

Command Object

You use this object to give a command to a data source, such as SELECT * FROM Customers to query the data in the Customers table.

The provider-specific names include SqlCommand for SQL Server, OdbcCommand for ODBC, and OleDbCommand for OLE DB.

CommandBuilder Object

This object is used to build SQL commands for data modification from objects based on a single-table query. You look at this object in more detail when you study how to update data.

The provider-specific names include SqlCommandBuilder for SQL Server, OdbcCommandBuilder for ODBC, and OleDbCommandBuilder for OLE DB.

DataReader Object

This is a fast, simple-to-use object that reads a forward-only read-only stream of data (such as the set of customers found) from a data source. This object gives the maximum performance for simply reading data; the first example will demonstrate how to use this object.

The provider-specific names include SqlDataReader for SQL Server, OdbcDataReader for ODBC, and OleDbDataReader for OLE DB.

DataAdapter Object

This is a general-purpose class that performs various operations specific to the data source, including updating changed data, filling DataSet objects (see the "DataSet Object" section) and other operations, which you see in the following examples.

The provider-specific names include SqlDataAdapter for SQL Server, OdbcDataAdapter for ODBC, and OleDbAdapter for OLE DB.

Consumer Objects

These are the objects defined for the disconnected, consumer side of ADO.NET. These aren't related to any specific .NET data provider and live within the System.Data namespace.

DataSet Object

The DataSet is the king of consumer objects. the DataSet represents a set of related tables referenced as one unit in your application. For example, Customers, Orders, and Products might all be tables in one DataSet representing each customer and the products he or she ordered from your company. With this object, you can get all the data you need from each table quickly, examine and change it while you're disconnected from the server, and then update the server with the changes in one efficient operation.

The DataSet has features that let you access lower-level objects that represent individual tables and relationships. These objects, the DataTable object and the DataRelation object, are covered in the following sections.

DataTable Object

This object represents one of the tables in the DataSet, such as Customers, Orders, or Products.

The DataTable object has features that allow you to access its rows and columns:

• DataColumn object: This represents one column in the table, for example OrderID or CustomerName.

• DataRow object: This represents one row of related data from a table; for example, a particular customer's CustomerID, name, address, and so on.

DataRelation Object

This object represents the relationship between two tables via a shared column; for example, the Orders table might have a CustomerID column identifying the customer who placed the order. A DataRelation object might be created representing the relationship between Customers and Orders via the shared column CustomerID.

You now have an idea of the overall structure of the objects in ADO.NET. There are more objects than the ones just listed, but let's skip the details for now and get into some examples that show how this all works.

Using the System.Data Namespace

The first step in using ADO.NET within your C# code is to reference the System.Data namespace, in which all the ADO.NET classes are located. Put the following using directive at the beginning of any program using ADO.NET:

 using System.Data; 

Next, you need to reference the .NET data provider for the specific data source you'll be using.

SQL Server .NET Data Provider

If you are using the SQL Server database (version 7 or greater), including the desktop engine (SQL Express or MSDE), the best performance and most direct access to the underlying features is available with the native (SQL Server–specific) .NET data provider, referenced with this using directive:

 using System.Data.SqlClient; 

Oracle .NET Data Provider

If you are using the Oracle database, a native Oracle .NET driver is the best choice; one is provided with the .NET Framework, referenced with this using directive:

 using System.Data.OracleClient; 

Oracle itself also provides a .NET data provider, referenced as Oracle.DataAccess.Client. This is a separate download that you have to get from Oracle. Whether you use a .NET data provider from the database vendor or just use the one provided with the .NET Framework is your choice. Generally, a provider from the vendor will make more use of the database product's special features, but for basic or beginning usage this is unlikely to matter much. Either provider will work; use the one that you prefer.

OLE DB .NET Data Provider

For data sources other than SQL Server or Oracle (such as Microsoft Access), you can use the OLE DB .NET data provider, referenced with this using directive:

 using System.Data.OleDb; 

This provider uses an OLE DB provider DLL for the specific database you are accessing; OLE DB providers for many common databases are installed with Windows, notably Microsoft Access, which you will use in the following examples.

ODBC .NET Data Provider

If you have a data source for which no native or OLE DB provider is available, the ODBC .NET data provider is a good alternative because most databases provide an ODBC interface. It is referenced with this using directive:

 using System.Data.Odbc; 

Other Native .NET Data Providers

If there is a native .NET data provider available specifically for your database, then you may want to use that .NET data provider instead. Many other database vendors and third-party companies provide native .NET data providers; the choice between using the native provider and using something generic like the ODBC provider will depend on your circumstances. If you value portability over performance, then go generic. If you want to get the best performance or make the best use of a particular database's features, go native.