A key architectural element in the ADO.NET infrastructure is the managed provider, which can be considered the .NET counterpart of the OLE DB provider. A managed data provider enables you to connect to a data source and retrieve and modify data. Compared to the OLE DB provider, a .NET managed provider has a simplified data-access architecture made of a smaller set of interfaces and based on .NET Framework data types.
A COM-based OLE DB provider is a monolithic component; in contrast, a managed provider is made of a suite of classes, each of which represents a particular step in the connection model that a data-aware application implements. A managed provider comprises classes for connecting to a particular data source, managing transactions, executing commands, and accessing result sets. For each of these steps, the managed provider supplies a particular class with its own set of properties, methods, and events. The union of these classes form the logical entity known as the managed provider for that data source.
In the .NET Framework, each managed provider is characterized by a prefix that qualifies the various classes of the same type. For example, the class that manages connections to SQL Server is called SqlConnection, while the class that opens a connection to an Oracle database is OracleConnection. In the former case, the prefix is Sql; in the latter, it is Oracle.
The classes in the managed provider interact with the specific data source and return data to the application using the data types defined in the .NET Framework. Objects contained within a managed provider include Connection, Transaction, Command, Parameter, Data adapter, and Data reader.
The Connection object creates a connection with the specified data source, be it SQL Server, Oracle, or any data source for which you can indicate either an OLE DB provider or an ODBC driver. Strictly dependent on the connection is the Transaction object. It represents a transaction to be made in the source database server. While connection pooling is sometimes implemented within the ADO.NET infrastructure, any transaction related features are delegated to the underlying database server.
The Command object represents a command-typically, a SQL statement or a stored procedure name-that hits the data store. The command may be further characterized by using parameters. A Parameter object simply represents a parameter that you can pass to the Command object. The Data adapter is a particular type of a database command. It executes on the specified database server and returns a disconnected set of records. Internally, a Data adapter makes use of a Data reader, which emulates the behavior of a read-only, forward-only cursor created on the database. You normally use a Data adapter when you need to cache the result set for further processing. You use a Data reader when you are going to consume the records immediately without storing them.
You should note that, as of .NET Framework 1.1, ADO.NET managed providers don't support server cursors. If your application can't be reworked to do without server cursors, the best way of proceeding is just sticking to the ADO library, which is easily callable from within a .NET application.
Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant based in Rome, Italy. Dino is a contributing editor to Windows Developer Magazine and MSDN Magazine, and the author of several books for Microsoft Press including Building Web Solutions with ASP.NET and ADO.NET and Applied XML Programming for .NET. Contact Dino at [email protected].
