The Managed Provider and Generic Data Set Classes

To provide both connected and disconnected database access, ADO.NET defines two sets of classes: managed provider and generic data.

You use objects of the managed provider classes to directly connect to a database and to synchronize your locally stored data with the database. You can use the managed provider classes to read rows from the database in a forward-only direction. You use a different set of managed provider classes depending on the database you use.

You use objects of the generic data classes to store a local copy of the information retrieved from the database. This copy is stored in the memory of the computer where the C# program is running. The main generic data class is the System.Data.DataSet class. The generic data classes, as their name suggests, are not specific to any database, and you always use the same classes regardless of the database you use. The generic data classes represent information retrieved from the database as XML.

The Managed Provider Classes

The managed provider objects allow you to directly access a database, and you'll be introduced to the classes that allow you to create these objects in this section. You use the managed provider objects to connect to the database and read and write information to and from the database.

Figure 5.1 illustrates some of the managed provider objects and how they relate to each other.

click to expand
Figure 5.1: Some of the managed provider objects

There are currently three sets of managed provider classes, and each set is designed to work with different database standards:

  • SQL Server Managed Provider Classes You use the SQL Server managed provider classes to connect to a SQL Server database.

  • OLE DB Managed Provider Classes You use the OLE DB (Object Linking and Embedding for Databases) managed provider classes to connect to any database that supports OLE DB, such as Access or Oracle.

  • ODBC Managed Provider Classes You use the ODBC (Open Database Connectivity) managed provider classes to connect to any database that supports ODBC. All the major databases support ODBC, but ODBC is typically slower than the previous two sets of classes when working with .NET. You should use the ODBC managed provider classes only when there aren't any alternative OLE DB managed provider classes.

    These three sets of classes all implement the same basic functionality.

Note 

Whenever you see Sql at the start of a managed provider class name, you know that class is used with a SQL Server database. For example, SqlConnection allows you to connect to a SQL Server database. Similarly, OleDb is for databases that support OLE DB. For example, OleDbConnection allows you to connect to a database using OLE DB. Finally, Odbc is for databases that support ODBC. For example, OdbcConnection allows you to connect to a database using ODBC. I refer to all of these classes as the Connection classes.

You'll see some of the various managed provider classes in the following sections.

The Connection Classes

There are three Connection classes: SqlConnection, OleDbConnection, and OdbcConnection. You use an object of the SqlConnection class to connect to a SQL Server database. You use an object of the OleDbConnection class to connect to any database that supports OLE DB, such as Access or Oracle. You use an object of the OdbcConnection class to connect to any database that supports ODBC. Ultimately, all communication with a database is done through a Connection object.

The Command Classes

There are three Command classes: SqlCommand, OleDbCommand, and OdbcCommand. You use a Command object to run a SQL statement, such as a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a Command object to call a stored procedure or retrieve rows from a specific table. You run the command stored in a Command object using a Connection object.

The Parameter Classes

There are three Parameter classes: SqlParameter, OleDbParameter, and OdbcParameter. You use a Parameter object to pass a parameter to a Command object. You can use a Parameter to pass a value to a SQL statement or a stored procedure call. You can store multiple Parameter objects in a Command object through a ParameterCollection object.

The ParameterCollection Classes

There are three ParameterCollection classes: SqlParameterCollection, OleDbParameterCollection, and OdbcParameterCollection. You use a ParameterCollection object to store multiple Parameter objects for a Command object.

The DataReader Classes

There are three DataReader classes: SqlDataReader, OleDbDataReader, and OdbcDataReader. You use a DataReader object to read rows retrieved from the database using a Command object.

DataReader objects can only be used to read rows in a forward direction. DataReader objects act as an alternative to a DataSet object. You cannot use a DataReader to modify rows in the database.

Tip 

Reading rows using a DataReader object is typically faster than reading from a DataSet.

The DataAdapter Classes

There are three DataAdapter classes: SqlDataAdapter, OleDbDataAdapter, and OdbcDataAdapter. You use a DataAdapter object to move rows between a DataSet object and a database. You use a DataAdapter object to synchronize your locally stored rows with the database. This synchronization is performed through a Connection object. For example, you can read rows from the database into a DataSet through a DataAdapter, modify those rows in your DataSet, and then push those changes to the database through a Connection object.

The CommandBuilder Classes

There are three CommandBuilder classes: SqlCommandBuilder, OleDbCommandBuilder, and OdbcCommandBuilder. You use a CommandBuilder object to automatically generate single-table INSERT, UPDATE, and DELETE commands that synchronize any changes you make to a DataSet object with the database. This synchronization is performed through a DataAdapter object.

The Transaction Classes

There are three Transaction classes: SqlTransaction, OleDbTransaction, and OdbcTransaction. You use a Transaction object to represent a database transaction. A database transaction is a group of statements that modify the rows in the database. These statements are considered a logical unit of work. For example, in the case of a banking transaction, you might want to withdraw money from one account and deposit it into another. You would then commit both of these changes as one unit, or if there's a problem, roll back both changes.

Namespaces for the Managed Provider Classes

The managed provider classes for SQL Server (SqlConnection and so on) are declared in the System .Data.SqlClient namespace. The classes for OLE DB-compliant databases (SqlDbConnection and so on) are declared in the System.Data.OleDb namespace. The classes for ODBC-compliant databases (OdbcConnection and so on) are declared in the System.Data.Odbc namespace.

Note 

At time of writing, you have to download the ODBC managed provider classes from Microsoft's Web site at http://msdn.microsoft.com/downloads. This download is separate from the .NET SDK. Look for "ODBC .NET Data Provider" in the MSDN table of contents.

In the following section, you'll learn about the generic data classes.

The Generic Data Classes

As you learned in the previous section, you can use the managed data provider objects to connect to the database through a Connection object, issue a SQL statement through a Command object, and read retrieved rows using a DataReader object; however, you can read rows only in a forward only direction and you must be connected to the database.

The generic data objects allow you to store a local copy of the information stored in the database. This allows you to work the information while disconnected from the database. You can read the rows in any order, and you can search, sort, and filter those rows in a flexible manner. You can even make changes to those rows and then synchronize those changes with the database

Figure 5.2 illustrates some of the generic data set objects and how they relate to each other. The bridge between the managed provider and generic data set objects is the DataAdapter, which you use to synchronize changes between your DataSet and the database.

click to expand
Figure 5.2: Some of the generic data set objects

The following sections outline some of the generic data classes.

The DataSet Class

You use an object of the DataSet class to represent a local copy of the information stored in the database. You can make changes to that local copy in your DataSet and then later synchronize those changes with the database through a managed provider DataAdapter object. A DataSet object can represent database structures such as tables, rows, and columns. You can even add constraints to your locally stored tables to enforce unique and foreign key constraints.

You can also use a DataSet object to represent XML data. In fact, all information stored in a DataSet is represented using XML, including information retrieved from the database.

The DataTable Class

You use an object of the DataTable class to represent a table. You can store multiple DataTable objects in a DataSet through a DataTableCollection object. A DataSet object has a property named Tables, which you use to access the DataTableCollection containing the DataTable objects stored in that DataSet.

The DataRow Class

You use an object of the DataRow class to represent a row. You can store multiple DataRow objects in a DataTable through a DataRowCollection object. A DataTable object has a property named Rows, which you use to access the DataRowCollection containing the DataRow objects stored in that DataTable.

The DataColumn Class

You use an object of the DataColumn class to represent a column. You can store multiple DataColumn objects in a DataTable through a DataColumnCollection object. A DataTable object has a property named Columns, which you use to access the DataColumnCollection containing the DataColumn objects stored in that DataTable.

The Constraint Class

You use an object of the Constraint class to represent a database constraint that is to be enforced on one or more DataColumn objects of a DataTable. You can store multiple Constraint objects in a DataTable through a ConstraintCollection object. A DataTable object has a property named Constraints, which you use to access the ConstraintCollection containing the Constraint objects stored in that DataTable.

The DataView Class

You use an object of the DataView class to view only specific rows in a DataTable object using a filter, which specifies the criteria to restrict the rows.

The DataRelation Class

You use an object of the DataRelation class to represent a relationship between two DataTable objects. You can use a DataRelation object to model parent-child relationships between two database tables. You can store multiple DataRelation objects in a DataSet through a DataRelationCollection object. A DataSet object has a property named Relations, which you use to access the DataRelationCollection containing the DataRelation objects stored in that DataSet.

The UniqueConstraint Class

You use an object of the UniqueConstraint class to represent a database constraint that enforces that the value stored in a DataColumn object is unique. The UniqueConstraint class is derived from the Constraint class. You can store multiple UniqueConstraint objects in a DataTable through a ConstraintCollection object.

The ForeignKeyConstraint Class

You use an object of the ForeignKeyConstraint class to specify the action performed when the column values in the parent table are updated or deleted.

The ForeignKeyConstraint class is derived from the Constraint class. You can either have the child rows deleted (cascading action), set the child columns to null, or set the child columns to a default value. You can store multiple ForeignKeyConstraint objects in a DataTable through a ConstraintCollection object.

Namespaces for the Generic Data Classes

The DataSet, DataTable, DataRow, DataColumn, DataRelation, Constraint, and DataView classes are all declared in the System.Data namespace. This namespace contains other classes that you can use in your programs. You can view the full set of classes declared in the System.Data namespace using the .NET documentation. Chapter 1 explains how you access this documentation.

In the next section, you'll see a simple example that illustrates how to issue a SQL SELECT statement that retrieve rows from the Customers table, and then stores the returned rows in a DataSet object. This program will give you a basic understanding on how to use some of the managed provider and generic data classes previously outlined. In Part II, you'll see the details of the various classes used in this example.




Mastering C# Database Programming
Mastering the SAP Business Information Warehouse: Leveraging the Business Intelligence Capabilities of SAP NetWeaver
ISBN: 0764596373
EAN: 2147483647
Year: 2003
Pages: 181

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net