Chapter 19. Working with ADO.NET Data Providers

IN THIS CHAPTER

• Introduction to ADO.NET Data Providers
• Using Provider Factories
• Working with Connection Strings
• Enumerating Data Sources
• Obtaining Additional Provider Information
• Creating a Custom ADO.NET Data Provider

This chapter gives you an in-depth look at the power and functionality provided by the ADO.NET data providers. You’ll see how to use provider factories and the factory pattern to create provider-agnostic code. This chapter also provides you with samples of additional functionality exposed by data providers such as data source enumeration, as well as features exposed by the ADO.NET SQL provider specifically.

Introduction to ADO.NET Data Providers

Throughout the last two chapters, you have been using data providers but might not have known it. A data provider is a collection of classes that implement standard interfaces and inherit from predefined abstract base classes to provide a standard means by which a developer can access relational data. For example, ADO.NET comes with a SQL provider, an Oracle provider, an OLE DB provider, and an ODBC provider. All of them, by virtue of their compliance to data provider standards, have a similar list of classes, methods, and properties. This allows developers to reuse their knowledge of one provider to develop against a different provider.

These providers all contain connections, commands, command parameters, various data readers, and data adapters, all of which are specialized to their specific back end. So, although both the Oracle provider and the SQL provider have connections, each one uses a different means for communicating with their respective databases.

As you will see throughout the course of this chapter, data providers are easy to use, flexible, and extremely powerful.

Using Provider Factories

One of the problems of working with data providers is that they are not all the same. Prior to version 2.0 of ADO.NET, developers often had to do a lot of work on their own to get their applications to work with an OLE DB data source as well as a SQL Server data source without having to create two different data layers. In these situations, developers often created their own provider-agnostic wrappers that abstracted the work of allowing code to access both data sources seamlessly.

With ADO.NET 2.0, such wrappers are no longer necessary. The .NET Framework provides several classes that allow you to use a factory pattern to access data providers without specifically tying your code to a single implementation such as the DbProviderFactories class and the DbProviderFactory class. Using the factory pattern, you can create code that will execute commands on multiple providers without having to create complex conditional logic or multiple layers of abstraction.

Data providers are a lot more powerful under ADO.NET 2.0 than they were in previous versions. For example, when a data provider is installed, it can modify a computer’s machine.config file to indicate that it has installed a provider factory. This allows developers to query the list of installed provider factories and handle different situations accordingly. In addition, new ADO.NET providers have the ability to provide multiple types of metadata to further assist developers in coding generically against multiple types of data without excessive or redundant code.

Obtaining the List of Installed Provider Factories

You can use the ProviderFactories class to obtain a list of installed factories. The list of installed factories comes in the form of a DataTable that contains the provider name, its invariant name (fully qualified namespace), a long description, and the fully qualified assembly strong name.

The code in Listing 19.1 iterates through the list of installed provider factories and displays their names and invariant names. You will need a provider’s invariant name to create an instance of that provider’s factory.

Listing 19.1 Enumerating the List of Installed Provider Factories

The preceding code produces the following output on the author’s machine:

The following data provider factories are available:
Odbc Data Provider (System.Data.Odbc)
OleDb Data Provider (System.Data.OleDb)
OracleClient Data Provider (System.Data.OracleClient)
SqlClient Data Provider (System.Data.SqlClient)
SQL Server CE Data Provider (Microsoft.SqlServerCe.Client)

Using a Provider Factory to Establish a Connection

Using a data provider factory to establish a connection is actually a pretty simple task. First, you create an instance of a specific factory using the DbDataFactories.GetFactory() method. When you have an instance of the factory, you can use any of the following DbProviderFactory methods to create a provider-agnostic instance of an object:

• CreateConnection—Creates a new connection instance. Under the hood, this connection will be specific to the provider you chose at the factory level, but you will receive a basic DbConnection instance in return.
• CreateCommand—Creates an instance of a new command.
• CreateCommandBuilder—Creates a new command builder.
• CreateConnectionStringBuilder—Creates an instance of a connection string builder (covered in the next section).
• CreateDataAdapter—Creates a new data adapter.
• CreateDataSourceEnumerator—If the underlying provider supports it, this will create a new data source enumerator.
• CreateParameter—Creates a new DbParameter instance.

Take a look at the code in Listing 19.2, as it provides an illustration of how to establish a connection to a data source and execute a command without ever hard-coding a reference to a SqlConnection instance or an OleDbConnection instance.

Listing 19.2 Establishing a Provider-Agnostic Connection

The important thing to note about the preceding code is that the only indications of the underlying provider are the connection string, and the invariant name of the provider. Both of those pieces of information could have come from a configuration file, from user input, or from another data source. The preceding code will work just as well on any underlying data provider that has a Customers table with the "LastName", "FirstName", and "MiddleInitial" columns.

Working with Connection Strings

Connection strings are typically specific to the provider for which they are intended. This makes it difficult to create a truly provider-agnostic application, as you’ll have to have multiple connection string formats to manage all of the different types of providers to which your application can connect.

This dilemma is partially solved by the DbConnectionStringBuilder class. Each provider has its own specific connection string builder class that provides the developer with a standardized interface for creating a connection string. When the ConnectionString property is accessed, the output is specific to the underlying provider. The DbConnectionStringBuilder class provides the basic mechanism for adding name/value pairs of information to a connection string. Classes like SqlConnectionStringBuilder provide additional methods and properties that allow you to configure settings specific to the provider.

When you add a key and value to a connection string builder, it takes the key and interprets it in a way that is specific to the underlying provider. When you examine the connection string property of the builder, the key name might have been changed to reflect the needs of the provider. For example, if you set the “server” key on the SQL client connection string builder, it won’t include that key in the output. But the same key on an ODBC or an OLE DB connection string builder will be included in the connection string.

The code in Listing 19.3 illustrates using DbConnectionStringBuilder to use provider-agnostic keywords to create connection strings and output them.

Listing 19.3 Using the DbConnectionStringBuilder

The output from the preceding code is as follows:

[System.Data.Odbc] server=localhost;user=sa;password=secret;data source=SampleDB
[System.Data.OleDb] Data Source=SampleDB;server=localhost;user=sa;password=secret
[System.Data.OracleClient] Data Source=SampleDB;User ID=sa;Password=secret
[System.Data.SqlClient] Data Source=SampleDB;User ID=sa;Password=secret

As you can see, the connection string builder for each data provider produced a connection string specific to the needs of that particular provider.

Enumerating Data Sources

Some data providers now give you the ability to enumerate a list of data sources. For the SQL Server provider, this is a list of available SQL servers. Each data provider will return a DataTable containing different information about the available data sources.

The code in Listing 19.4 shows sample code to determine whether a data provider supports the ability to enumerate data sources and how to examine that list of data sources.

Listing 19.4 Enumerating Data Sources

The preceding code takes a few seconds to start, as the GetDataSources() method is fairly lengthy. After it completes, however, you will have a fully populated DataTable containing a list of data sources available for the given data provider (in this case, System.Data.SqlClient).

Obtaining Additional Provider Information

Many of the data providers expose additional information that might be useful for debugging, tracing, and diagnosing purposes. Depending on the provider, you might be able to get information about the database server version or any number of other pieces of useful information, even metadata about columns, tables, and databases contained within the server.

Using the RetrieveStatistics Method

One of the useful methods exposed by the SqlConnection class for debugging, tracing, and diagnosing purposes is the RetrieveStatistics() method. This method returns a name/value pair collection of statistics about the connection itself. You can use this information to obtain statistics before and after executing commands to compare and examine the impact of each of your commands. The code in Listing 19.5 illustrates how to use this method.

Listing 19.5 Using the RetrieveStatistics Method

Note that the connection won’t gather statistics unless indicated by the StatisticsEnabled property due to the overhead involved in maintaining metrics. The output from the preceding code on a freshly created connection is as follows:

NetworkServerTime : 0
BytesReceived : 0
UnpreparedExecs : 0
SumResultSets : 0
SelectCount : 0
PreparedExecs : 0
ConnectionTime : 0
ExecutionTime : 109
Prepares : 0
BuffersSent : 0
SelectRows : 0
ServerRoundtrips : 0
CursorOpens : 0
Transactions : 0
BytesSent : 0
BuffersReceived : 0
IduRows : 0
IduCount : 0

Obtaining Schema Information from Data Providers

Another extremely valuable piece of information that you can get from a data provider is a schema. The GetSchema() method of a DbConnection class is the means by which you can interrogate a data provider of an enormous amount of valuable data.

When you invoke this method with no arguments, it provides you with the list of schemas that you can query. For example, when you call GetSchema() on a SQL Server 2005 connection, you get the following list of schemas contained in a table called MetaDataCollections:

MetaDataCollections
DataSourceInformation
DataTypes
Restrictions
ReservedWords
Users
Databases
Tables
Columns
Views
ViewColumns
ProcedureParameters
Procedures
ForeignKeys
IndexColumns
Indexes
UserDefinedTypes

You can experiment with the results for querying each of the above metadata collections. For example, a query to the data provider for the Databases metadata collection resulted in the following list of databases:

master
tempdb
model
msdb
ReportServer
ReportServerTempDB
SampleDB

The database list also included the database creation date and the dbid of the database.

Creating a Custom ADO.NET Data Provider

Creating a custom ADO.NET data provider is definitely a long process that needs to be designed thoroughly before proceeding. However, the use of standard interfaces and base classes makes the task slightly easier.

There aren’t all that many reasons to create your own custom ADO.NET data provider. If you find that you need to access data that you want to expose through the connection/command/reader pattern, there is an extremely good chance that the underlying data can be accessed using either ODBC or OLE DB. Remember that you can use OLE DB to access data sources like Excel spreadsheets and even text files. However, if the data is proprietary, or can’t be accessed through ODBC, OLE DB, Oracle, or SQL Server, you might find yourself in a situation where you want to create a data provider to expose your data to the .NET Framework in a relational manner.

To do this, you’ll need to create a class library that contains implementations of several interfaces. To create a minimal data provider, you will need to create the following:

• A connection—To expose data via a data provider, the data provider must follow a connection model. A connection represents some live connection with a source of data, whether that connection is a reserved file handle, a network socket, or some other representation of a database connection.
• A data reader—You will need to create a class that provides a read-only, forward-only traversal over the underlying data contained within your provider
• A data adapter—Data adapters are responsible for executing commands in order to exchange data between the underlying data source and DataSets.
• A command—The command is a unit of instruction sent to the underlying data source. Traditional commands are typically in the form of stored procedures or SQL statements, but your data source can take any type of parameterized command that you feel appropriate. Commands can return both scalar data and data readers.

In the past, some example data providers have been created for ADO.NET that provide exposure for Microsoft Message Queues (MSMQ), Active Directory (AD), and even runtime type information provided by a Reflection data provider.

It’s beyond the scope of this chapter to walk you through the implementation of a custom provider. In addition to providing implementations of a command, a data reader, a connection, and a data adapter, your data provider would also have to take into account things like transactional support and whether or not you want your provider to register itself with the list of data provider factories.

Summary

This chapter has provided you with a guide to some of the extra features that are exposed by the ADO.NET data providers that come with the .NET Framework. You saw how to create provider-agnostic code using provider factory lists and provider factories. In addition, you saw how you can use the DbConnectionStringBuilder class to create connection strings without knowing the specifics of the underlying provider at compile time. Finally, you saw how to squeeze a lot of extra functionality out of the data providers by enumerating data sources, querying metadata collections, and obtaining tracing and monitoring statistics for a given connection.