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.   Shaw G — September 26, 2009 @ 12:18 pm    Reply

    I can’t believe that MS would have made it so trivial to code for multiple database types.

    In Java, we will have to use an elaborate DAO pattern using one of Sun’s blueprint, like this one:

    http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html

    Or use DAO facility by one of the leading frameworks, Spring’s DAO comes to mind.

    Or use an ORM like Hibernate.

    Anyway, is this provider method widely used in MS. I mean is it widely used if you are not using ORM (like nHibernate) or other open source frameworks like Spring.NET?

  2.   Masoud — September 27, 2009 @ 3:48 am    Reply

    very nice post.

    thats my always problem with different dbms

    thanks

    😉

  3.   Conrad — September 28, 2009 @ 10:02 am    Reply

    What do we do with exceptions? Are the exceptions raised provider-specific?

  4.   HoyaBaptiste — September 28, 2009 @ 10:05 am    Reply

    Won’t the use of SqlParameter… ParamArray arrParam() As SqlParameter… still bind this approach to Sql Server provider?

  5.   DeborahK — September 28, 2009 @ 11:32 am    Reply

    Hi HoyaBaptiste –

    Oops! I did not correctly handle the parameters in this code. I will edit the post to correct it.

    Thanks!

  6.   DeborahK — September 28, 2009 @ 11:57 am    Reply

    Hi Conrad –

    This is from the msdn documentation:

    “The DbException class is the base class for all exceptions thrown on behalf of a data source. You can use it in your exception handling code to handle exceptions thrown by different providers without having to reference a specific exception class.”

    Here is the link:

    http://msdn.microsoft.com/en-us/library/9hy8csk1.aspx

    Hope this helps.

  7.   John Wright — October 7, 2009 @ 2:12 pm    Reply

    I created code similar to this in 2005 when the data factory came out. I created a class that I can drop into any project and used shared functions that I call. I account for returned datatables, return an object for ExecuteScalar functions, and I also have an execute nonquerySQL that returns nothing. The class has a parameter object with typed parameters, and so far has been tested successfully with SQL Server, Oracle, text files, Excel, and ODBC against a mainframe. It will even account for the oracle refCursor returns as well for returning cursors from oracle.

    In addition, I created a slick little application that goes against any SQL Server database on the network, lists all the stored procedures, and generates the calling code for me, complete with all return types, and typed parameters. Cut my programming time down by 80% and eliminated quite a bit of debugging since the code has been tested and is quite effective. I think the best thing .NET 2.0 put in was the data factory.

    John
    riley_wright@hotmail.com

  8.   Volker Schaak — October 15, 2009 @ 6:08 am    Reply

    Hi Deborah,

    let me first thank you for your work, esp. your “Doing objects…” books. Really good intro for OOP in VB(.net)!

    Unfortunatly I’ve got to put some salt in the generic approach to data access:
    1) MS-DBProvider approach doesn’t support Oracle-return parameters, that do return a cursor, which is THE way for Oracle-SPROCs to return resultsets. The DBType enumeration doesn’t contain a DBType.Cursor or something similar. So, one has to implement something more special inside the DAL…
    2) The Oracle Parameter-Format (at least in Ora 9.2) isn’t neccessarily the name with : before! Doing ad hoc queries like “SELECT * FROM table WHERE field = :Value” this works, but not with SPROCs! (The ad hoc-query approch counters the generic approch, since it implies NOT to write provider specific SQL-code!)
    So that sadly reduces the DBFactory to a semi-generic issue, esp. when using a SPROC approach.

    IMHO MS should finish it’s work in those areas instead of walking hundreds of new paths, only to realize those are dead ends…

    Best wishes and take care

    Volker from Germany

  9.   SHIVAJI — October 26, 2009 @ 5:54 am    Reply

    VB.NET APPLICATION DEMO WITH SOURCE HAVING BO, DAL, AND UI.

  10.   DeborahK — October 26, 2009 @ 11:26 am    Reply

    Hi Shivaji –

    I have an example here:

    http://www.insteptech.com/techLibrary/samplecode.htm

    Hope this helps.

RSS feed for comments on this post. TrackBack URI

Leave a comment

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