LWDbComponents - Another Database Component

• Download Library and demo project - 33.6 Kb

Introduction

In this article, I'm going to try to explain how to use the database components that I've been creating and extending over the past years. I'll try to explain, of course, why I think they are useful and better, at least for my needs, than other DB Components out there... even better than the MS Enterprise Library 2.0. Of course, talking only about DB access. The Enterprise Library brings a lot of good functionality and practices all in one, not covered by this library.

You'll notice, in fact, that the DB Provider concept is, somehow, the same that you'll find in the Enterprise Library.

Here you have a short summary with the functionalities covered by this library. Over the next sections, I'll explain in detail each one of them:

• DB Engine Independence: You can use the same source code, without any change, to run you queries against any DB engine.
• Connection Auto-close: You don't need to worry about non-closed DB connections anymore.
• In-Context & Nested Transactions: You can open nested transactions or execute queries inside an opened transaction without the need to specify it before executing the DB command. This functionality is the same as the one implemented within the Enterprise Services.
• DBCommandAdapters: You can define your own DBCommand class, to control the query execution process.
• Configurable at Runtime: All of this is defined in the configuration file, so you can change everything without the need to recompile your application.

DB Engine Independence

The first functionality to highlight of these components is that they are independent from the DB engine that you need to use.

This is, of course, essential to any good DB component. And this is something that you could find in the Enterprise Library too. The only difference here is the approach used to solve this problem. This library is provided with two providers, one for SQL Server and another one for Oracle, but you can define any other provider that you need without the need to change your application code.

To do so, you must define a class that implements IDbProvider, that is defined by these two methods:

• GetDBConnection: This method returns a new instance of the IDbConnection associated to the DB engine. In the two provided providers, SqlClient.SqlConnection and OracleClient.OracleConnection instances are created. This will be the base to create the IDbCommand and IDbTransactions instances.
• GetParameterCorrector: This method returns a IDbParameterCorrector, which provides the queries and parameters translated from a DB engine specification to another. With this approach, you could use the SQL Server query syntax ('@' symbol before parameter names) into (i.e.,) Oracle syntax. This is one of the differences with the Enterprise Library approach.  

DB Providers must be declared in the application configuration file. This is the related configuration section needed to use the two included providers.

<providers> 
  <add name="sql" 
    type="LWComponents.Datalayer.Providers.SqlProvider, 
          LWComponents.Datalayer"/>
  <add name="oracle"
    type="LWComponents.Datalayer.Providers.OracleProvider, 
          LWComponents.Datalayer"/>
</providers> 

IDbParameterCorrector

An IDbProvider can define an IDbParameterCorrector. Its function is to translate the application queries from a known base syntax to the one needed by the specific DB Engine. The source base syntax selected for the two included providers is the SQL Server syntax (that's the reason because the SQL Server provider does not need a corrector), but you could use whatever syntax you want in your providers. 

This diagram explains how this "Correction" works:

Then, with the provided OracleParameterCorrector code:

Public Function CorrectParameters(ByVal command As DbCommand) _ 
         As ParametersCorrection _ 
         Implements IDbParameterCorrector.CorrectParameters 
  Dim sql As String = command.CommandText 
  Dim res As New ParametersCorrection(command) 
  For Each par As IDbDataParameter In command.Parameters 
    Dim parName As String = par.ParameterName  
    'This will replace @ParamName by :ParamName in the query 
    sql = sql.Replace(parName, GetQueryParameterName(parName)) 
    'And the IDbDataParameter name is changed form @ParamName to ParamName
    res.AddParameterCorrection(par, GetParameterName(parName)) 
  Next 
  res.NewSql = sql 
  Return res 
End Function

We could have this code in our application:

Dim cmd As DBCommand = DataBaseFacade.CreateQueryCommand("oracleTest", _ 
             "SELECT Count(*) FROM Customers WHERE Country = @Country") 
cmd.AddParameter("@Country", "Spain") 
Dim res As String = cmd.ExecuteScalar().ToString

And it will be translated to this one at runtime (of course, only when running against an Oracle database):

query: "SELECT Count(*) FROM Customers WHERE Country = :Country" 
cmd.AddParameter("Country", "Spain") 

So, you only need to create your Provider for your preferred DB engine and you have a really DB independent application.

Note: Of course, proprietary SQL functions will not be translated. This is one more reason to use Stored Procedures instead of inline queries in your applications.

Connection Auto-close

One common source of problems in every application comes from a problem, very difficult to detect at design-time, of non-closed connections. 

With this library, this problem will end... well, "will almost end" will be more exact.

You will not need to take care of the DbConnection open and close process. You only need to get a DBCommand and execute it. The DBCommand class is responsible for opening the connection when needed and to close it after finishing. There are only two exceptions to this rule:

1. ExecuteReader: When a DataReader is generated, it is always created with the CommandBehavior.CloseConnection. So the connection is closed when the IDataReader is closed.
2. When running inside a Transaction: When a DBCommand is created inside a BeginTransaction-Commit/Rollback block, the connection is kept open until the Commit/Rollback methods are executed.  

So, these are the common patterns to execute database queries:

Private Sub NoCloseNeededPatterns() 
    Dim cmd As DbCommand = DataBaseFacade.CreateCommand("somequery...", _
                                                        CommandType.Text) 
    cmd.ExecuteNonQuery() 
    ' or ... 
    cmd.ExecuteScalar() 
    ' or ... 
    cmd.ExecuteDataSet()
End Sub 

Private Sub DataReaderPattern() 
    Dim cmd As DbCommand = DataBaseFacade.CreateCommand( _
                                              "SELECT * FROM Customers", _
                                              CommandType.Text) 
    Using rs As IDataReader = cmd.ExecuteReader() 
      Do While rs.Read 
        'Here we'll do something whit the records 
      Loop 
    End Using 
End Sub 

Private Sub TransactionPattern() 
     Using t As IDbTransaction = DataBaseFacade.BeginTransaction() 
       Dim cmd As DbCommand = DataBaseFacade.CreateCommand("somequery...", _
                                                           CommandType.Text) 
       '... 
       t.Commit() 
     End Using
End Sub  

Note: In the TransactionPattern sample, you will notice that Rollback is not directly called. In fact, Rollback will be automatically executed if the Commit method is not called (i.e., in the case of  an exception.)

In-Context & Nested Transactions

Standard design rules say that a method or a class must not know what its callers do. Instead. it must focus on its own mission. 

This common-sense rule becomes not true when we talk about DB transactions. If we have a method in our data layer that opens a DB command and executes a simple query, we must know if our caller has opened a transaction, because the DB command must use it in order to work, or we can open a new DB connection to execute this query, but then we can be reading non-updated data or, even worse, creating an inter-lock error.

Note: In ADO.NET 2.0, Microsoft has introduced the TransactionScope concept that solves these problems (System.Transactions.TransactionScope). In fact, this new namespace implements distributed transactions, and works perfectly within a Windows Communication Foundation (.NET 3.0) context. Even then, I maintain this functionality for backwards compatibility. Besides, to provide a simpler (and lighter) implementation, good enough for most applications. In any case, the TransactionScope is fully compatible with the rest of the functionalities of this library, so it's up to you to decide the method to use. The concepts and diagrams used to explain the in-context transactions are, moreover, applicable to TransactionScope too.

This is what In-Context transactions provided by LWDbComponents solves.

With this LWDbComponents, you only need to take care of a transaction in the method that creates it. The following calls to DataBaseFacade.CreateCommand, until the transaction is closed using Commit/Rollback, will know that there is an active transaction and create the DBCommand accordingly.

Besides, there is another problem with transactions, and that is Nested Transactions. In this case, you have a method that needs a transaction and that calls other methods, one of whom needs another transaction. Some DB engines do not support this (e.g., SqlClient).

LWDbComponents supports creating a NestedTransaction when a RealTransaction is already active. This can be easily understood using this sample diagram.

This, translated to code, could be something like this (without creating the methods, in order to shorten the code).

'There is no active transaction, so a RealTransaction is created
Using t1 As IDbTransaction = DataBaseFacade.BeginTransaction()
  'This DBCommand is created using the active transaction T1
  Dim cmd As DbCommand = DataBaseFacade.CreateCommand("somequery...", _
                                                      CommandType.Text)
  ' Do something....
  'Now, there is an active transaction,
  'so a NestedTransaction is created
  Using t2 As IDbTransaction = DataBaseFacade.BeginTransaction()
    'This DBCommand is created using the active transaction T2, that relys on T1.
    Dim cmd2 As DbCommand = DataBaseFacade.CreateCommand("somequery...", _
                                                         CommandType.Text)
    ' Do something....
    t2.Commit()
  End Using
  'This DBCommand is created using the active
  'transaction T1, because T2 has already been closed
  Dim cmd3 As DbCommand = DataBaseFacade.CreateCommand("somequery...", _
                                                       CommandType.Text)
  ' Do something....
  t1.Commit()
End Using

Active Transaction Counters

In a very complex application, it's very easy that some developer forgets a transaction commit/rollback, driving to register locks and poor DB performance. The problem with this is that they will appear probably only in a production environment.

In order to help during the task of finding those kind of problems as soon as possible, and during the development phase, there are some properties in the DataBaseFacade class (ActiveTransactions and TotalActiveTransactions) that will return the number of active transactions.

Using t1 As IDbTransaction = DataBaseFacade.BeginTransaction()
  Using t2 As IDbTransaction = DataBaseFacade.BeginTransaction()
    Using t3 As IDbTransaction = _
             DataBaseFacade.BeginTransaction("oracleTest")
      'This should return 2: 1 Real SQL Server + 1 Real Oracle
      Debug.WriteLine("Total transactions:" & _
                      DataBaseFacade.TotalActiveTransactions())
      'This one would return 3, because of the nested t2 transaction
      Debug.WriteLine("Total transactions + nested:" & _
                      DataBaseFacade.TotalActiveTransactions(True))
      'This should return 1
      Debug.WriteLine("Oracle transactions:" & _
                      DataBaseFacade.ActiveTransactions("oracleTest"))
      'This should return 1
      Debug.WriteLine("SQLServer transactions:" & _
            DataBaseFacade.ActiveTransactions())
      'This should return 2, because of the nested t2 transaction
      Debug.WriteLine("SQLServer transactions + Nested:" & _
                      DataBaseFacade.ActiveTransactions(True))
    End Using
  End Using
End Using

Note: It's a good idea to use those counters in an ASP.NET application in the HttpApplication.EndRequest event handler to ensure that there are no non-closed transactions after each request.

DBCommandAdapter

When you execute a CreateCommand in the DataBaseFacade class, you receive an instance of the LWComponents.Datalayer.DbCommand class. This class implements the IDbCommand interface, and will provide the standard Execute (NonQuery/Scalar/Reader) methods to run your queries. Although this class has all you need in a standard application, you could need to do something else. For example, monitor or log every DB query you run.

For these cases, you can expand the DBCommand functionality, defining a DBCommandAdapter. This class must inherit from DBCommand and implement its default constructor. This could be the source code for a DBCommandAdapter that implements this logging requirement:

Public Class DBCommandAdapterExample 
      Inherits LWComponents.Datalayer.DbCommand 
  Public Sub New(ByVal con As IDbConnection, _ 
                 ByVal paramCorrector As _
                         LWComponents.Datalayer.IDbParameterCorrector) 
    MyBase.New(con, paramCorrector) 
  End Sub 
  Public Overrides Sub OnBeforeExecute(ByVal ev As _
                       LWComponents.Datalayer.DBCommandEventArgs) 
    MyBase.OnBeforeExecute(ev) 
    'TODO: Log queries 
  End Sub 
End Class

And the needed configuration section to define it:

<commandAdapters> 
  <add name="standard" 
       type="LWComponents.Datalayer.DbCommand, LWComponents.Datalayer"/>
  <add name="MyDBAdapter" 
       type="DBComponentsTest.DBCommandAdapterExample, DBComponentsTest"/>
</commandAdapters> 

Configuration

And all of this behavior is controlled by the application configuration file. Here we can see the included sample configuration file:

<lwDB> 
  <providers> 
    <add name="sql"
        type="LWComponents.Datalayer.Providers.SqlProvider,
              LWComponents.Datalayer"/> 
    <add name="oracle"
        type="LWComponents.Datalayer.Providers.OracleProvider,
              LWComponents.Datalayer"/> 
  </providers> 
  <commandAdapters> 
    <add name="standard"
        type="LWComponents.Datalayer.DbCommand,
              LWComponents.Datalayer"/> 
    <add name="MyDBAdapter"
        type="DBComponentsTest.DBCommandAdapterExample,
              DBComponentsTest"/> 
  </commandAdapters> 
  <connections default="sqlTest"> 
    <add name="sqlTest" provider = "sql" 
         shareTransactionByConnectionString="true" 
         commandAdapter = "standard"
         connectionString = "conStringSqlServer"/> 
    <add name="oracleTest" provider = "oracle" 
         shareTransactionByConnectionString="false" 
         commandAdapter = "MyDBAdapter"
         connectionString = "conStringOracle"/> 
  </connections> 
</lwDB> 

<connectionStrings> 
  <add name="conStringSqlServer"
       connectionString="... a SQL Server connection string..."/> 
  <add name="conStringOracle"
       connectionString="...an Oracle connection string..."/> 
</connectionStrings>

• lwDB/providers: DB Providers defined. Each provider must implement IDbProvider.
• lwDB/commandAdapters: DBCommand adapters to use. This section is optional. Each adapter must inherit from DBCommand and implement a constructor with the signature (ByVal con As IDbConnection, ByVal paramCorrector As IDbParameterCorrector).
• lwDB/connections: DB connection definitions. We use its name in the calls to the DatabaseFacade provider. If no name is specified, the name set in the "default" attribute is used instead. 
• connectionStrings: This is the standard .NET connection strings section.

Note: Every section and attribute is explained in the included configuration file.

Conclusion

I've found this library very useful in all my projects, but its far from being perfect. So, if you like it and find something that could be done better, some new functionality that could be useful, or simply you find it useful and are using it in your projects... I'd like to hear about it.

Update

• 12/11/2006: Some code updates and tweaks.
  • Updated the transaction classes code (RealTransaction and NestedTransaction) to allow the "using" pattern.
  • Updated the code samples to use the "Using" statement.
  • Added some functions to check the number of active transactions. Very useful to detect unclosed transactions.
  • Added some diagrams to explain better some patterns.
  • Some other minor code fixes and tweaks.