Deborah's Developer MindScape

Whether it be SQL Server, Access, Oracle, or mySql, most applications write to one kind of database. But what if your application requirements are such that you have to support multiple database types? Then the DbProviderFactory is for you.

For example, say you are building a product to sell to HR departments. Some of your customers want to leverage their existing SQL Server infrastructure and will only buy the product if it supports SQL Server. Other customers want your application to work in an all Oracle environment, still others would prefer mySql. So what do you do? Build multiple versions of the product? No need!

Part of ADO.NET, the DbProviderFactory allows you to have one set of code that works with any type of database that supports a data provider such as ODBC, OleDb, or SQL Server.

The code below takes a stored procedure and a set of parameters and returns a DataTable. You could easily adjust this code to take a sql string instead of a stored procedure or to return a DataReader instead of a DataTable.

NOTE: This post assumes you already have a stored procedure defined to select the required data from your database. For more information on stored procedures, see this link.

The code is first shown in both VB and C#. It is then described in detail below.

NOTE: Be sure to import the System.Data,  System.Data.Common and System.Collections.Generic namespaces.

NOTE: If you plan to cut and paste this code, be sure to define two application settings: ARConnectionString to specify a connection string appropriate for your database and ARProviderName to specify an appropriate provider name for your database (such as System.Data.SqlClient or System.Data.OleDb)

In C#:

public static class Dac
{

public static DataTable ExecuteDataTable(string storedProcedureName,
                          Dictionary<string,object> paramDictionary) 
  {
    DataTable dt = new DataTable();

    // Get the data factory
    DbProviderFactory df = DbProviderFactories.
               GetFactory(Properties.Settings.Default.ARProviderName);

    // Open the connection
    using (DbConnection cnn = df.CreateConnection())
    {
        cnn.ConnectionString =
                      Properties.Settings.Default.ARConnectionString;
        cnn.Open();

        // Define the command
        using (DbCommand cmd = cnn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = storedProcedureName;

            // Handle the parameters
            if (paramDictionary != null)
            {
                DbParameter param;
                foreach (string key in paramDictionary.Keys)
                {
                    param = cmd.CreateParameter();

                    // The parameter name depends on the provider
                    switch (df.GetType().Name)
                    {
                        case "SqlClientFactory":
                            param.ParameterName = "@" + key;
                            break;
                        case "OracleClientFactory":
                            param.ParameterName = ":" + key;
                            break;
                        case "OleDbFactory":
                        case "OdbcFactory":
                            param.ParameterName = "?";
                            break;
                        default:
                            break;
                    }
                    param.Value = paramDictionary[key];
                    cmd.Parameters.Add(param);
                }
            }

            // Define the data adapter and fill the dataset
            using (DbDataAdapter da = df.CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                da.Fill(dt);
            }
        }
    }
    return dt;
}
}

In VB:

Public Class Dac

Public Shared Function ExecuteDataTable( _
     ByVal storedProcedureName As String, _ 
     ByVal paramDictionary As Dictionary(Of String, Object)) _
                                                     As DataTable

    Dim dt As New DataTable

    ' Get the data factory
    Dim df As DbProviderFactory = DbProviderFactories. _
                        GetFactory(My.Settings.ARProviderName)

    ' Open the connection
    Using cnn As DbConnection = df.CreateConnection
        cnn.ConnectionString = My.Settings.ARConnectionString
        cnn.Open()

        ' Define the command
        Using cmd As DbCommand = cnn.CreateCommand
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = storedProcedureName

            ' Handle the parameters
            If paramDictionary IsNot Nothing Then
                Dim param As DbParameter
                For Each key As String In paramDictionary.Keys
                    param = cmd.CreateParameter()
                    ' The parameter name depends on the provider
                    Select Case df.GetType.Name
                        Case "SqlClientFactory"
                            param.ParameterName = "@" & key
                        Case "OracleClientFactory"
                            param.ParameterName = ":" & key
                        Case "OleDbFactory", "OdbcFactory"
                            param.ParameterName = "?"
                    End Select
                    param.Value = paramDictionary(key)
                    cmd.Parameters.Add(param)
                Next
            End If

            ' Define the data adapter and fill the dataset
            Using da As DbDataAdapter = df.CreateDataAdapter
                da.SelectCommand = cmd
                da.Fill(dt)

            End Using
        End Using
    End Using

    Return dt
End Function
End Class

The ExecuteDataTable function is in my Dac (data access component) class and defined to be public and static (shared in VB). It is public so it can be called from any other code. It is static/shared because it does not use or retain any state. That allows the function to be called without creating a instance of the class containing the function.

The function has two parameters. The first is the name of a stored procedure. Since this code returns a DataTable, it assumes that the stored procedure contains one Select statement. Here is an example of a SQL Server stored procedure:

CREATE PROCEDURE dbo.CustomerRetrieveAll
AS
    SELECT    
                CustomerId,
                LastName,
                FirstName
    FROM       
                Customer
    ORDER BY
                LastName + ', ' + FirstName

NOTE: The structure and syntax of stored procedures in other database platforms may differ.

The second parameter is a Dictionary that defines any parameters required by the stored procedure. The dictionary key is the name of the parameter and the value is the value of the parameter. In the CustomerRetrieveAll example, there are no parameters. But your stored procedure may have parameters to filter the data.

The first line of code defines a variable for the DataTable. It is used as the return value from the function.

The next statement sets up the data provider factory. The GetFactory method returns an appropriate strongly typed data factory based on a string value representing the provider name. For SQL Server, this value is "System.Data.SqlClient", for an OleDb database, it is "System.Data.OleDb". Once you create a data factory, you can use its methods to define other data access objects.

In this example, the name of the provider is stored in a configuration file using the Settings feature in Visual Studio. (See this for information on settings in C# and this for information on settings in VB.) This allows you to change the configuration file to change the database provider used by the application. You will need to replace the property setting with a setting or provider name appropriate for your database.

You can then use the data factory to create a connection object of the appropriate type as shown in the first Using statement.

The code then defines the connection string. In this example, the connection string is also stored in the configuration file using the Settings feature in Visual Studio. You will need to replace the connection string in the example with a setting or connection string appropriate for your database.

The connection is then opened.The connection is automatically closed at the end of the Using block.

The second Using statement creates the command. In this case, the command is a stored procedure and the CommandText property of the command defines the stored procedure name.

If parameters were passed in, the parameters are added to the command’s Parameters. The format of name of the parameter is dependent on the provider, so a switch/select statement is used to set the appropriate name.

The third Using statement defines a DataAdapter, again using the data factory. The Fill method of the DataAdapter fills the DataTable with the data returned from the stored procedure.

Since the code uses the Using statement, all of the objects are appropriately closed and disposed.

The resulting filled DataTable is then returned.

As an example, this is how you call this method.

In C#:

DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveAll", 
                                            null);

In VB:

Dim dt As DataTable = Dac.ExecuteDataTable("CustomerRetrieveAll", _
                                            nothing)

If the stored procedure had parameters, you would call it like this:

In C#:

Dictionary<string, object> paramDictionary =
                    new Dictionary<string, object>();
paramDictionary.Add("CustomerId", custId);
DataTable dt = Dac.ExecuteDataTable("CustomerRetrieveById",
                                            paramDictionary);

In VB:

Dim paramDictionary As New Dictionary(Of String, Object)
paramDictionary.Add("CustomerId", custId)
Dim dt As DataTable = Dac.ExecuteDataTable("CustomerRetrieveById", _
                                                  paramDictionary)

EDITED 9/28/09:

The code did not handle DbParameters correctly in the first draft of this post. This error was corrected in both the ExecuteDataTable method and in the calling examples.

EDITED 12/23/09:

When I made the above edit, I neglected to update the C# function signature. Corrected it above.

Enjoy!