Jonathan's Blog

• DbConnection
• DbCommand
• DbProviderFactory

Basically, the DbProviderFactory allows for us to relativly easily implement a provider independent data access layer in our application. The advantage of this methodology is that it gives you the option of (almost) seamlessly working with multiple providers like OleDb and ODBC without having to lock onto to a specific implementation like SQLClient. With the obvious advantage and seemingly simple implementation. DbProviderFactory itself is an abstract base class and every provider must provide an implementation for this. The provider to use at runtime is obtained by calling the GetFactory static method of the DbProviderFactories class by passing a string uniquely representing that provider. This string is called Provider Invariant Name and is registered by each provider in machine.config. For e.g. for ODBC provider, it is System.Data.Odbc. For some nice reading, check out this article and some nifty ideas and samples from Binary Intellect.

Basically there are 4 default providers out of box in .NET 2.0 (SqlServer, OleDb, Oracle, and ODBC), in our case, i added a default ConfigDefined which will pull the connection string from web.config, and in most cases result in an SQLServer connection object being made.

The DBHelper object in Enterprise Library is pretty much the same, except added a little idea regarding paramater caching for some occasional performance tweaks. We also have a couple different overloads to cator to RB support I suppose.

This provides us with the ability to create Oracle, MySQL and other version of RB (I will not be doing this) but it would be possible. The class could also be extended to provide an XML layer for example.

My intention is that by the time Alpha 4 is ready with the membership and roles stuff a few developers are working on, I will also be able to replace all the core data access with the new db helper, I doubt the modules will get done in time, but the core will be nice to see working and test in these early alpha stages.

This should also provide some cleaner and more consisten code, as any core data access will now run thorugh a unified layer, which eventulaly we cna extend to otehr web service and windows layers.

Check out the following code sample to see how the database helper class replaces some of the standard data code, in this example, i am replacing one of the main DBHelper.ExecuteScript(....) overloads.

1: object returnValue;

2: //using (SqlConnection myConnection
                = Config.SqlConnectionString)

3: //{

4: // using (SqlCommand sqlCommand
                = new SqlCommand(sql, myConnection))

5: // {

6: DatabaseHelper db = new DatabaseHelper();

7: try

8: {

9: returnValue = db.ExecuteScalar(sql);

10: //sqlCommand.Connection.Open();

11: //returnValue = sqlCommand.ExecuteScalar();

12: }

13: catch (Exception e)

14: {

15: throw new DatabaseUnreachableException("Error in DBHelper
                    - ExecuteSQLScalar", e);

16: }

17: finally

18: {

19: db.Dispose();

20: //sqlCommand.Dispose();

21: //myConnection.Close();

22: //myConnection.Dispose();

23: }

24: //}

25: //}

26: return returnValue;

For simplicity sake I also put some statics in so you can do one liners like

1: return DatabaseHelper.GetDataSet(selectCmd, CommandType.StoredProcedure, false);

1: objConnection = objFactory.CreateConnection();

2: objCommand = objFactory.CreateCommand();