Click here to Skip to main content
Click here to Skip to main content

LWDbComponents - Another Database Component

, 10 Dec 2006
Rate this:
Please Sign up or sign in to vote.
A full featured and easy to use DB component.

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:

IDbParameterCorrector diagram

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.

In-Context transactions diagrams

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Luis Angel R.C.
Web Developer
Spain Spain
My first computer was a MSX when I was twelve years old. I liked to develop simple games in Basic (you know... these old sprites...)
My first PC was a 8Mhz with two 5 1/4 floppy disks. The first language that I programed for it was assembler, and I liked to develop graphic demos. About that time Future Crew were my heros.
 
Now I have a computer engineer degree at Deusto University (Bilbao - Spain) since 1996, and I've been a Microsoft specialist since. Firstly with VB and now with VB.NET. In fact, I have three MCP Certifications (Win-VB6, Win-VB.NET and ASP.NET).
 
As a senior architect my work involves designing the application architecture for my team. For this work Rational XDE is, I think, the best tool ever made.
 
I like, development apart, travelling, watching movies, reading, and, of course, playing computer games, mainly MMOPRGs (currently WOW, and Vanguard:SoH as soon as it becomes available).

Comments and Discussions

 
QuestionLWDbComponents - Optimized for SQL Server 2005 Pro/Express? PinmemberBarryGSumpter10-Feb-07 10:44 
AnswerRe: LWDbComponents - Optimized for SQL Server 2005 Pro/Express? PinmemberLuis Angel R.C.10-Feb-07 15:08 
GeneralRe: LWDbComponents - Optimized for SQL Server 2005 Pro/Express? PinmemberBarryGSumpter10-Feb-07 17:32 
GeneralRe: LWDbComponents - Optimized for SQL Server 2005 Pro/Express? PinmemberLuis Angel R.C.11-Feb-07 5:21 
GeneralRe: LWDbComponents - Optimized for SQL Server 2005 Pro/Express? PinmemberBarryGSumpter11-Feb-07 10:32 
Generaloracle and returning cursors Pinmemberroberto galbiati6-Aug-06 22:28 
GeneralRe: oracle and returning cursors PinmemberLuis Angel R.C.7-Aug-06 6:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 10 Dec 2006
Article Copyright 2006 by Luis Angel R.C.
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid