Working with Oracle in .NET – part III : DbManager

After successfully compiled our generated DAL, now it is time to use the DAL to actually access our Oracle database from our .NET applications.

Add reference to BLToolkit Oracle data provider

Modify your configuration file

This is what mine looks like :

<configuration>
  <configSections>
    <section name="bltoolkit" type="BLToolkit.Configuration.BLToolkitSection,   BLToolkit.4" />
  </configSections>
  <bltoolkit>
    <dataProviders>
      <add type="BLToolkit.Data.DataProvider.OdpDataProvider,     BLToolkit.Data.DataProvider.Oracle.4" />
    </dataProviders>
  </bltoolkit>
  <connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=localhost/xe;User ID=user;Password=password;" providerName="Oracle.DataAccess.Client" />
  </connectionStrings><!-- other entries
...
...
...
-->

Tips : you can easily accomplish both step by simply installing the library from NuGet.

Usage example

A typical usage of the DAL would be something like this :

using (var db = new MyDbManager("MyConnectionString"))
            {
                db.BeginTransaction();
                try
                {
                    /*
                     * do something with the database
                     */
                    db.CommitTransaction();
                }
                catch 
                {
                    db.RollbackTransaction();
                    throw;
                }
            }

Simplifying the usage

Do you notice the parameter supplied to the DbManager’s constructor? It is the name of the connection string we’ve added to the web.config in the earlier step. It has to be done every time we instantiate the DbManager, which is very often in my case. To me, magic string like this is not a good thing.

To avoid supplying an arbitrary string like this every time we need to access the database, we can add a parameterless constructor to the DbManager. But since this class is auto-generated, modifying the class directly is not a good option. Instead, as the DbManager is declared as partial, we can add another partial class to achieve this. This is what my class looks like :

public partial class MyDbManager : DbManager
    {
        public MyDbManager()
            : base("MyConnectionString")
        {
        }
    }

Ensure you specify this class in the same namespace with the generated DAL.

With this addition, we can now create the DAL without supplying any parameter.

using (var db = new MyDbManager())
            {
                db.BeginTransaction();
                try
                {
                    /*
                     * do something with the database
                     */
                    db.CommitTransaction();
                }
                catch 
                {
                    db.RollbackTransaction();
                    throw;
                }
            }

Another advantage of this approach is, if the connection string name needs to be changed in the future, we only need to modify a single string in our code. Handy, isn’t it?

On the next article I will cover about doing basic CRUD operation against our Oracle database.