Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

September 25, 2009

DAL: Using a Data Provider Factory

Filed under: C#,Data,VB.NET @ 6:59 pm

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!

19 Comments

  1.   Shiv — November 1, 2009 @ 2:08 am    Reply

    Thanks for your post. It was very useful. Just would like to share that, I could also create parameter from DbProviderFactory.CreateParameter() and it created param specific to my provider. This way, I could avoid below switch case…
    switch (df.GetType().Name)
    {
    case “SqlClientFactory”:
    ………..

  2.   Volker Schaak — November 3, 2009 @ 10:46 am    Reply

    Hi Shiv,

    the switch/case statement in the DAL-snippet is mainly due to the different naming conventions for DBParameters. The DBFactory.CreateParameter() method won’t supply the Parametername with the ‘@’, ‘?’ or (sometimes, not always) ‘:’.
    For that reason a DAL needs to make a difference, here done by switch/case. Another attempt could be to read the DB’s Schema, where’ll find ‘ParameterMarkerFormat’…

    Best wishes

    Volker

  3.   Wagner — November 17, 2009 @ 1:19 pm    Reply

    the oracle database gives back a RefCursor.. how to populate dataset with RefCursor

  4.   Wagner — December 21, 2009 @ 5:25 am    Reply

    where is the paramDictionary coming from ? Is it the 2nd argument ? if yes, how is this being populated ?

  5.   DeborahK — December 23, 2009 @ 5:09 pm    Reply

    Hi Wagner –

    If you are looking at the C# code, it was confusing because I had not modified the signature appropriately. See my edit on 12/23/09.

    As for how it is populated, see the example at the end of the post.

    Sorry for any confusion.

  6.   oakley discount — September 10, 2015 @ 8:33 pm    Reply

    certainly like your website however you have to check the spelling on quite a few of your posts. A number of them are rife with spelling issues and I find it very troublesome to inform the reality then again I?ll definitely come back again.

  7.   bml — March 25, 2016 @ 1:48 am    Reply

    hi all

    is this still a good approach to achieve that functionality?

    thanx
    B

    •   deborahk — April 14, 2016 @ 9:36 am    Reply

      As far as I know, yes!

  8.   brass — May 4, 2017 @ 9:43 am    Reply

    Good info. Lucky me I came across your website by accident (stumbleupon).

    I have saved as a favorite for later!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs