.NET Data Providers

for RuBoard

The prefix on the database classes and methods indicates the data provider used to access the data source. For example, the OleDb prefix applies to the OleDb data provider. The Sql prefix applies to the SqlServer data provider.

The SqlServer data provider uses the native SQL Server wire protocol. The OleDb data provider goes through the COM interop layer to talk to the various OleDb providers. For example, you could talk to SqlServer through the OleDb data provider to the OLEDB provider for SQL Server. Nonetheless, the performance of going through the SqlServer data provider will be superior . The advantage of the OleDb and the ODBC data providers is that you can work with ADO.NET against most data sources that you work with today.

There are some interfaces that define common functionality, and some base classes that can be used to provide common functionality, but there is no requirement for a data provider to fit a specification that does not correspond to the way the underlying data source works.

For example, the SqlDataAdapter class and the OleDbDataAdapter class both use the abstract base classes DbDataAdapter and DataAdapter that are found in the System.Data.Common namespace. SqlTransaction and the OleDbTransaction classes both implement the IDbTransaction interface. The OleDbError class and the SqlError class do not resemble each other at all. Server-side cursors are not in the ADO.NET model because some databases (such as Oracle and DB2) do not have native support for them. Any support for them in the SQL Server data provider would be as an extension. ^[5]

^[5] Besides, server-side cursors are rarely appropriate so it is not surprising that databases do not support them. Scrolling through the output is usually the result of a user interaction. Holding state on the server while the user interacts with the data is not the way to build a scalable application.

As Table 9-1 shows, the Connection , Command , DataReader , DataAdapter , and DataParameter classes of the data providers do have some parallels that are defined by the IDbConnection , IDbCommand , IDataReader , IDbDataAdapter , and IDataParameter interfaces. Nothing, of course, prevents an implementation of these classes from having additional methods beyond those specified in the interfaces.

Table 9-1. Comparison of Parallel Classes in the OleDb and SQL Server Data Providers

Interface	OleDb	SQL Server
IDbConnection	OleDbConnection	SqlConnection
IDbCommand	OleDbCommand	SqlCommand
IDataReader	OleDbDataReader	SqlDataReader
IDbDataAdatpter	OleDbDataAdapter	SqlDataAdapter
IDbTransaction	OleDbTransaction	SqlTransaction
IDataParameter	OleDbDataParameter	SqlDataParameter

Classes such as the DataSet or the DataTable , which are independent of any data provider, do not have any prefix.

If database scalability is important, it is important to suppress finalization on any of your classes that do not require it. You will get vastly improved performance because you will reduce the amount of time the finalizer thread runs.

for RuBoard