indows® platforms have for years included an API for data access based on a generic provider-consumer paradigm. Originally, this consisted of the relational data-source-centric Open Database Connectivity (ODBC) API. ODBC is nearly a one-to-one mapping to the ANSI SQL standards committee's SQL CLI (command language interface). ODBC drivers encapsulate database-specific protocols like the SQL Server tabular data stream (TDS) or layer over vendor-specific APIs like the Oracle Call Interface (OCI). This concept expanded to include nonrelational data sources using a common set of COM interfaces and types known as OLE DB. Because OLE DB contained provisions for nonrelational data sources, producers of any kind of data—from hierarchical to multidimensional to flat files—were encouraged to adapt the common OLE DB object model.
      The Microsoft® .NET platform will ship with a System.Data.dll assembly containing a set of namespaces reminiscent of OLE DB and ODBC. This assembly contains the System.Data namespace, which includes the definition of a common set of data access interfaces and System.Data.Common, which contains a few abstract base classes that implement common functionality for use by provider writers. System.Data.dll also contains two namespaces (System.Data.OleDb and System.Data.SqlClient) that expose parallel series of classes and interfaces, known in the documentation as data providers. These encapsulate access to databases or other data access APIs through a set of managed types and interfaces. The types and interfaces exposed by each data provider derive from the common subset, and expose mostly equivalent (but not identical) functionality. Because the types and interfaces need not be identical for each provider, provider writers are freer to expose functionality that is unique to the data source. In addition, System.Data includes provisions for a disconnected object model, based around the DataSet type, which is a more feature-rich version of the ODBC client cursor library and the OLE DB disconnected Recordset.
      The documentation for the .NET SDK Beta 2 explains how to build a data provider, what types and interfaces comprise a standard provider, and how the interface methods should act to encourage standardization and interoperability among custom data providers. In addition, a sample data provider that exposes a custom data store is included. While it's not a complete specification, the information codifies how a data provider should behave. This information shows how to implement the standard types and interfaces defined in System.Data and use the helper base classes in System.Data.Common.
      In this article, I'll cover writing a minimal example of a .NET data provider that exposes the standard set of types and interfaces. I'll use this as a vehicle to examine the data provider object model, separating base functionality from extended functionality, and explore some provider-specific extensions. In addition, I'll look at the reasons for exposing data through a data provider and the other available alternatives. Please note that the code in this article is based on .NET Beta 2.

Why Write a Data Provider?

      In the days of OLE DB and ODBC, drivers and providers were implemented for a variety of reasons. Sets of data could be exposed through a common set of GUI controls when a provider was implemented. Reporting programs, data exchange programs, and other data consuming products used the common API. The ADO Recordset and Remote Data Services (RDS) were used as a de facto standard for data exchange, so some teams wrote providers that only produced ADO Recordsets. There was even the promise of "write-once, use with everything" generic data clients. Providers and drivers were implemented even if the data access method corresponded little or not at all to the object model. Examples included the ODBC driver for text that exposed text files through SQL and the OLE DB Simple Provider architecture.
      The various Windows-based visual development environments, such as Visual Basic®, exposed data through a set of data-bound controls. In order to be used with the data-bound controls, providers and drivers had to adhere to an additional set of rules and expose standard objects and interfaces. The latest of these specifications, for OLE DB providers and control writers, was the ActiveX® Control Writers specification for OLE DB.
      ODBC and OLE DB providers offered the advantage of being hooked into other Microsoft and third-party products directly. Microsoft Access, for example, can use any ODBC driver to directly expose data as though it came from an Access database for use by Access applications. SQL Server and DB/2 (on Windows NT®) permit you to use OLE DB data sources in distributed queries. Beginning with SQL Server 7.0 you can also use any OLE DB provider to import, export, and transform data with the Data Transformation Services (DTS) feature. Crystal Reports can use ODBC drivers or ADO Recordsets for input to reporting. Other products produce OLAP data or XML schemas from existing data sources through ADO or ODBC. These and other examples make writing OLE DB providers or ODBC drivers attractive.

Writing a Data Provider

      In Visual C++® 6.0, Microsoft introduced a set of classes, based on the ActiveX Template Library (ATL), enabling programmers to write OLE DB providers. The ATL OLE DB provider templates shipped with a wizard that used the ATL infrastructure and produced an example provider that exposed the WIN32_FIND_DATA structure (which consists of information about files and directories) using OLE DB cotypes and interfaces. I used this provider in demos so often that I gave it a standard name: DirProv, the OLE DB provider for directory information. Since .NET includes a set of classes that expose similar functionality I'll reproduce this example as a data provider and call it "managed dirprov."
      Writing a data provider for ADO.NET is much more straightforward than writing the equivalent OLE DB provider. ADO.NET interfaces are well-defined; if you implement a class to expose certain functionality, you should implement the corresponding interface. The four major classes to implement, at a bare minimum, are Connection, Command, DataReader, and DataAdapter. Let's look briefly at each of these classes.
Connection This is a required class even if you don't actually connect to a data source. Other classes, such as the Command class, require a Connection class for basic functionality. DataAdapters will call the Open and Close methods on the Connection class in the process of filling a DataTable in the DataSet.
Command The Command class serves at least two purposes. Commands, in the command language of your choice, are supported to directly affect the data store. Examples of this would be the SQL INSERT, UPDATE, and DELETE commands. In addition, you can submit commands that return results. An example of this is the SQL SELECT command.
DataReader The DataReader class is used for processing results from a Command. Its methods allow the consumer to iterate, in a forward-only manner, through rows of one or more sets of results. It also provides methods to get the data in the columns of those rows into variables of .NET types.
DataAdapter The DataAdapter class fills the DataSet with results from the Command class. In addition, it can provide command-based updates and an event model for fine-tuning updates.
      Figure 1 shows the required classes and some relationships among them.


Figure 1 Some Relationships of Data Provider Classes

Not all relationships are shown. For example, a Command class can be created not only by Connection.CreateCommand, but by specifying the Connection in an overload of the Command's constructor. As some of the classes depend on the existence of other classes, this is the smallest number of classes that can realistically be implemented. For a start, I've implemented a read-only provider that exposes these classes. Once the base functionality has been developed (see Figure 1), additional services can be layered on top of the existing required classes or defined through ancillary classes, as I'll describe later in this article.

The Connection Class

      To implement the Connection class, you start by implementing IDbConnection. The IDbConnection interface (see Figure 2) has six public methods, the most obvious of which are Open and Close. Because the .NET runtime does not include the concept of deterministic destruction, the user must explicitly call Close rather than just releasing all of the interface pointers as you would do with OLE DB. You use ChangeDatabase if your data source includes the concept of connecting to a different database.
      The BeginTransaction method is used to start a local transaction. BeginTransaction returns an IDbTransaction interface through which the user calls Commit or Rollback. There is an overload of BeginTransaction that takes a transaction isolation level. If your data source does not support local transactions, you do not need to support BeginTransaction. Finally, IDbCreateCommand creates your provider's Command object and returns an IDbCommand interface reference.
      There are four public properties on IDbConnection. ConnectionString, ConnectionTimeout, and Database are the most commonly used properties pertaining to connections. The fourth public property, State, is critical. It is a read-only property returning a ConnectionState-enumerated value, as shown in Figure 3. The meaning of Open and Closed are obvious. Open and Closed can be used if your data source supports asynchronous initialization, and Fetching can be exposed if the data source supports asynchronous fetch at connection time. Broken represents the state of a connection that was opened but is not operational because, for example, the database it is using was shut down by a system administrator.

Implementing Connection

      To implement IDbConnection, decide what you are connecting to and what information you need. If the information you need corresponds to the information used in a DBMS system, so much the better. If it doesn't, you must add additional properties and fields. For a bare-bones "directory information" connection, I have implemented simple Open and Close methods (which do nothing but set the correct ConnectionState), but I have not implemented a connection string, current database, or transaction.
      Your Connection class should be a publicly createable class. You should have at least a no-arg constructor and one constructor that takes a String (representing the connection string). Because my data provider does not use a connection string, I implemented only the no-arg constructor. A newly created Connection should be initialized to the state ConnectionState.Closed; this is accomplished by initializing the private _ConnectionState variable.
      Throughout the implementation, I use some simple coding conventions. Properties provide access to private member fields that begin with an underscore. Exceptions will be thrown when functionality is either NotSupported or NotImplemented, by calling the appropriate private method. The MDirConnection implementation is included as part of the provider code, which is downloadable from the link at the top of this article.

Specialization in a Connection

      The OLE DB spec allowed providers to implement custom provider-specific interfaces. You can also do this when using .NET data providers. In addition, you may implement provider-specific instance methods or extra overloads. As with OLE DB, generic clients may choose not to use your provider-specific methods. Runtime discovery by casting for interfaces/classes is the most dependable way to find capabilities. Marker interfaces could be used for this.
      In addition, specialized clients may discover your provider-specific methods, overloads, and properties at runtime through reflection without explicit prior knowledge, subject to permission considerations. Because these actions are all done at runtime, neither method gives the consumer any insight as to the semantics of your specializations.
      An example of specialization in a Connection is the connection string parameter series supported by the SqlClient. In addition to the standard connection string parameters such as Data Source, User ID, and Password (taken from the OLE DB spec), the SqlClient supports SQL Server-specific parameters such as Network Library and TDS buffer size.
      A more complex specialization would be to provide an implementation of connection pooling to enable better sharing of connections in a three-tier environment, such as a Web server scenario. Both the OleDb and SqlClient provide connection pooling, using different semantics.

The Command Class

      Commands must implement the IDbCommand interface, which is shown in Figure 4. IDbCommand's main purpose is to submit commands or queries to the data store. Commands that affect the data store but do not produce results are submitted through IDbCommand.ExecuteNonQuery, which returns the total number of rows affected by the command. Commands that are queries return resultsets through a DataReader class. The method IDbCommand.ExecuteReader returns an IDataReader interface on your DataReader class. ExecuteScalar returns the first column of the first row, but can also be used to return a scalar result. A possible use might be to retrieve a resultset that is actually a single object instance or document.
      There is an overload of IDbCommand.ExecuteReader that takes a CommandBehavior parameter. The CommandBehavior indicates whether a DataReader provides data, metadata, or whether some additional behavior is desired. The CommandBehavior enumeration is shown in Figure 5.
      Commands need a CommandText (the command itself) and a CommandType. These are exposed as public properties. In addition, a CommandText string and a Connection instance can be specified in overloaded constructors, as noted in the documentation. The only CommandType that must be supported is CommandType.CommandText. Other types support stored procedures and the use of simple table names instead of commands (the SqlClient does not support this because using the table name is only specific to OLE DB). Commands can be cancelled directly (through the Cancel method) or they can time out (exposed through the CommandTimeout property).
      Command types integrate and contain references to other types within the object model. A Command must have an associated Connection (as an IDbConnection reference), which can be set in the constructor or directly as a property. There is also an optional associated Transaction (as an IDbTransaction reference), which can either be set in the constructor or inherited from the underlying Connection object.
      Stored procedures or parameterized queries use sets of Parameters. This is exposed as a .NET type and a collection. The Parameters collection is a property of the Command, and the Command includes the CreateParameter method. Finally, the UpdatedRowSource indicates which version of a DataRow is used when updates are performed through a DataAdapter.

Implementing Command

      In my example data provider, a directory name can be used as input to the FileSystemInfos API. This can be specified only as the CommandText type of command. Since transactions, parameterized queries, updates, and cancellable commands are not supported, the majority of the implementation is in the command execution methods. Both ExecuteNonQuery and ExecuteReader instantiate a DataReader, call its internal GetDirectory method, and return either the number of directory entries returned (number of rows affected) or a DataReader over the set of entries.
      The most complex method to code is the overload of ExecuteReader that takes a CommandBehavior. Since some of the behaviors can be logically or'd together, individual bits must be checked in the implementation. Although it is possible to request KeyInfo or SchemaOnly, schema info is returned (through the DataReader's GetSchemaInfo method) each time, as is data. The only difference in implementation occurs when CloseConnection is specified. This invokes a different overloaded constructor of DataReader, which stashes the associated Connection. Then, when Close is called on the DataReader, Close is also called on the associated Connection, as the CloseConnection bit mandates. The implementation of ExecuteReader is shown in Figure 6.

Specialization in a Command

      The SqlClient and OleDb data providers implement almost all the functionality of IDbCommand. Since singleton results are often faster for databases to produce, both providers make use of the command behavior SingleRow. Both providers implement a Prepare method, which allows you to submit the command to the database for parsing and query plan preparation separately. The SqlClient extends the Command in a unique way. Since SQL Server 2000 can return streamed results in XML format, the SqlClient exposes an ExecuteXmlReader method, which returns an XmlReader rather than an IDataReader.

The DataReader Class

      DataReader classes do not have a public constructor. They must implement both the IDataReader and IDataRecord interfaces. The DataReader class allows the user to read forward only, one row at a time, and get typed data or generic types from the columns in each row. The IDataReader and IDataRecord interface definitions are shown in Figure 7.
      IDataReader can iterate over results in a resultset with the Read method, which returns false when it runs out of rows. Multiple resultsets can be returned in a single command execution. A NextResult method moves to the next resultset.
      Some consumers require a description of the information contained within the resultset, known as resultset metadata. IDataReader can return a single DataTable of metadata about each resultset through the GetSchemaTable method. The SchemaTable exposes some standardized information and some provider-specific information.
      IDataRecord exposes methods that allow providers to return a strongly or loosely typed data item for every column. There is a series of strongly typed getters that take a zero-based column ordinal, as well as generic GetValue and GetValues methods that return values of type object. IDataRecord always returns .NET managed types; therefore it is this interface that encapsulates mapping the data source type system to the .NET type system. In addition, IDataRecord contains column iterators (exposed as two overloads of the property Item) that can get a specific column by name or zero-based ordinal.

Implementing DataReader

      The DataReader class contains the only provider-specific implementation method, GetDirectory. GetDirectory calls the managed API System.IO.DirectoryInfo.GetFileSystemInfos. The data provider exposes a subset of the information retrieved: Name, Size, Type (file or subdirectory), and CreationDate. Name and Type are type String, Size is Int64, and CreateDate is a DateTime. Because the metadata is fixed (each resultset returns the same metadata) the results are encoded into four arrays: types (the managed type of a single column), sizes (the sizes of those types), names (the column name), and cols (values of type object). Although cols is an array of type object, each column is an instance of the correct type—that is, they are not all exposed as Variant, as in ADO. Because the metadata is always the same, GetSchemaTable is hardcoded. The protected implementation for the DataReader class and the Read method is shown in Figure 8.
      The strongly typed getters are implemented by simply type casting. Casting to a different type (in other words, using GetInt32 on a String column) produces an InvalidCastException, as it does in the SqlClient and OleDb data providers. Because all data values are already managed types, no conversion from data store-specific to managed types is required. The Item properties are implemented through C# indexers. The provider does not expose multiple resultsets from a single command.
      DataReaders should implement IEnumerable to be usable with data-bound controls. I've chosen to implement IEnumerable by using the DbEnumerator class in System.Data.Common. This class is used by both the SqlClient and OleDb data providers. In order to implement IEnumerable's GetEnumerator method, I return a new DbEnumerator instance obtained by passing my DataReader instance to the DbEnumerator constructor, as shown here:

  IEnumerator System.Collections.IEnumerable.GetEnumerator()
  

  {
  

    return ((IEnumerator) new DbEnumerator(this));
  

Specialization in DataReader

      The OleDb and SqlClient providers wind up with managed types in different ways. The SqlClient provider has a SqlTypes enumeration as well as a System.Data.SqlTypes namespace that exposes SQL Server-specific types. In addition to the strongly typed getters (such as GetInt32) that map SQL Server data types to .NET types, the SqlClient also has a series of getters (such as GetSqlInt32) that expose the native System.Data.SqlTypes without any conversion. The .NET SDK documentation says this is faster than converting to managed types.
      The OleDb data provider maps OLE DB DBTYPEs to managed types, since the data store's types are mapped to OLE DB DBTYPEs in the provider. The only interface of particular interest here is IDataRecord.GetData, which returns an IDataReader. In OLE DB this maps to the chapter concept, in which hierarchical data is exposed through a special column of type chapter that relates the parent and child rowsets. IDataRecord.GetData would return a DataReader positioned over the child row based on a particular parent row. The same concept could have been used in the MDirProv data provider to recurse into subdirectories in the file system.

The DataAdapter Class

      DataAdapter is one of the few data provider classes that is implemented through a common base. Provider-specific DataAdapters inherit from DbDataAdapter, which inherits from DataAdapter. These classes implement IDataAdapter (which defines Fill and Update methods that interact with the DataSet), and IDbDataAdapter. IDbDataAdapter exposes a set of four Commands (SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand) to define the provider-DataSet interaction. There is also a standard set of events and delegates to enable the consumer to receive notification and possibly modify behavior, both before and after update (Insert, Delete, or Update in the data store are considered updates). This hierarchy of classes and interfaces is shown in Figure 9.


Figure 9 Inheritance Hierarchies

Implementing DataAdapter

      The DataAdapter in the DirProv data provider is a lightweight implementation because the provider exposes read-only data. It supports only the SelectCommand method because it does not need any updating or command-submitting logic nor does it need Updating events or delegates, since they'll never be used.
      The base class, DbDataAdapter, provides almost all of the methods that are needed (Fill, FillSchema, and others) for MDirDataAdapter. The class works as specified, allowing the consumer to Fill the DataSet and exposing the SelectCommand. In tracing through the provider, it can be noted that DbDataAdapter's implementation of Fill calls Command.ExecuteReader(commandBehavior) with the behavior of SequentialAccess, and its FillSchema implementation uses the behavior CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly.

Specialization

      Both the SqlClient and OleDb data provider implement subclasses of the Updating/UpdatedEventArgs and delegates. As a final specialization, the OleDb data provider implements a special overload of Fill that allows you to fill a DataSet or DataTable from a classic ADO Recordset.

Adding Enhanced Functionality

      That covers the required common objects of the data provider object model. As a final note, I should add that the DirProv data provider is fully instrumented and can be extended to serve as a trace of higher-level calls inside the ADO.NET managed data stack. The entire source code and a sample program are downloadable from the MSDN Magazine Web site. This provider implements only the base set of types and interfaces and, as with OLE DB providers, extended functionality could be added. To complete my tour through the model, I will briefly describe the extended types available and common base definitions and implementations: transaction types, parameter and parameter collection classes, the CommandBuilder class, error-handling types, and permission types. Note that the SqlClient and OleDb data providers implement provider-specific variations of these types, as well as provider-specific data type enumerations.
Transaction types Transaction types encapsulate local transaction semantics that can be invoked from outside the data provider. Both OleDb and SqlClient have Transaction types that implement IDbTransaction.
Parameter and parameter collection These classes implement collections of parameters used in stored procedures and parameterized queries. Parameters must map .NET types to database types in a method similar to IDataReader. SqlParameter and OleDbParameter implement the common IDataParameter interface. The collection classes implement the IDataParameterCollection interface as well as the collection interfaces IEnumerable, ICollection, and IList.
CommandBuilder This class is implemented to assist in building default InsertCommand, UpdateCommand, and DeleteCommand members on a DataAdapter from database metadata. Both SqlClient and OleDb providers implement a version of this, but there is no common interface or base class.
Error-handling types A difficulty in any data access API is mapping the flow of control based on error handling and accounting for multiple errors. Both SqlClient and OleDb implement the Error and Error collection types. These types do not extend a common base. They do, however, implement the collection interfaces. Both providers throw a provider-specific exception OleDbException/SqlException and also implement an event/delegate for warnings and informational messages—Sql/OleDbInfoMessageEventArgs/Delegate—so that warnings need not interrupt the flow of control in the calling program.
Permission types To be consistent with the .NET security architecture and because clients should not be allowed to perform secure functions just because they are using a high-level API, a data provider should implement Permissions and PermissionsAttribute types. System.Data.Common contains a set of base classes, DBDataPermission and DBDataPermissionAtribute that serve these security requirements.

Implementation Alternatives

      The MDirProv data provider was implemented in this article to clarify and explore the .NET data provider model. A lot of functionality available in the API was not exposed in this provider. So what are the alternatives? Is the data provider the best choice? The reasons for exposing data through a data provider are to facilitate data exchange, to integrate with (or be useable by) GUI components, and to enable access to your data by third-party products (like Crystal Reports).
      Today, data provider writers have at least four choices to hook into the .NET compatibility space:
Write a .NET data provider This is a good choice for a data source (usually a relational or other database) that uses a proprietary protocol that can be parsed using a set of managed classes. The immediate example would be SqlClient, which includes a TDS parser or the OleDb data provider, which encapsulates a COM-based API. Your data source should be updateable for maximum benefit and support local transactions, parameterized queries, customizable commands, and nonstandard error collections. The ideal data source would support multiple rectangular resultsets, batching of commands, and command-based updates. Note that the model currently has no intrinsic support for in-place (non-command-based) updates or server cursors.
Write a custom XmlReader or XPathNavigator This is especially good if your data is hierarchical (or more correctly, non-rectangular) and uses navigation-based access (cursors) as opposed to set-based access. Data sources that can be optimized by subsetting (reading only a portion of the hierarchy) benefit greatly from a custom XPathNavigator implementation. In addition, besides navigation, XPath queries and XSLT transforms can be used directly with your data. Aaron Skonnard has written a series of XPathNavigators (over the file system, .NET assemblies, and others) that illustrate this technique. These are available at https://staff.develop.com/aarons.
Write a "legacy" OLE DB provider or ODBC driver Although these will be phased out eventually, this is still your best bet for doing distributed queries with SQL Server, using SQL Server DTS, direct enlistment in MSDTC transactions, and other integration processes. Both APIs also expose an update-in-place cursor model. With an OLE DB provider, you have almost 100 percent integration into the new .NET world through the OleDb data provider.
Programmatically populate the DataSet or XmlDocument Do so using the appropriate data-specific APIs. If all you want is a set of data, both APIs are directly programmable. You need not expose navigation, updateability, or connections and commands unless you need them.
      The .NET DataSet can be used for data exchange, providing functionality similar to an ADO Recordset. It is supported directly for use in Web Services and it marshals by value. But the key point of interest is how the DataSet marshals by value. Unlike the ADO Recordset, which used a proprietary binary format (advanced data tablegram, ADTG), the DataSet marshals as XML, as does almost everything else in the .NET Framework. XML, rather than ADTG or the DataSet, is the universal marshaling and data exchange format in .NET. So there is no need to implement a data provider to Fill the DataSet to facilitate data exchange.
      The same functionality could be implemented using a custom XmlReader or XPathNavigator that access an XML infoset rather than a DataSet. An infoset is an in-memory opaque representation of the data in an XML document. The infoset is often a more efficient repesentation of an XML document. The XML serialization format need not even be used, as long as the data can be exposed using the XML infoset model. In addition, the XML infoset is a better fit for nonrelational data such as homogeneous and heterogeneous hierarchies and semi-structured data (structured document data). Keep in mind that besides directly exposing data as XML, the DataSet can be filled programmatically, as could the ADO Recordset, without the need of a Connection-Command-DataReader model.
      Visual Studio® makes it is easy to integrate controls, not only with data providers, but also with any data source. .NET data-bound controls work with any type that implements IEnumerable or ICollection. The designers of Visual Studio also support this integration. A data provider need not be written for the purpose of control support.
      Some third-party products are beginning to support .NET directly. For example, Crystal Reports .NET, which will ship with Visual Studio .NET, supports using DataSets for reporting, in addition to ODBC drivers and ADO Recordsets. This will most likely become a continuing trend.

Conclusion

      Although there was no direct correlation of Connection, Transaction, or Parameters or of mapping data source types to managed types in my example, my data provider provides a hook to fill the DataSet. Other choices are to use an XPathNavigator or directly fill the DataSet. It was, however, a useful exercise to walk through the data provider architecture. The provider I wrote to do this has tracing hook facility to use with third-party products and user-written programs. With more flexibility come more choices. I hope that this exercise was useful in helping you explore how to expose your custom data.