Proposed solution for InfoMessage Event handling

Topics: Building and extending application blocks, Data Access Application Block
Feb 22, 2011 at 8:26 PM

The Problem

Informational messages from a database are not easily available when using the DAAB. For example, the output from a SQL PRINT statement is not easily available.


.Net provides access to these informational messages if you add an InfoMessage handler to the database connection. However, when using the DAAB, the connection is not directly available, so we are not able to add the handler. Connections are pooled behind the scenes and are not exposed to us. Although there is a Connection property in the DbCommand class, it cannot be used to solve this problem since the Database class will get a connection from the pool when you execute the DbCommand with a method call like Db.ExecuteDataSet( dbCommand ).


The DAAB does provide a good solution to this problem. The solution is not difficult, just a little hard to figure out. This solution does NOT require modifying any of the Enterprise Library code, which was very important to me. The steps required to implement this solution are:

  1. Add a DbProviderFactory class (full sample implementation below)
  2. Add a DbProviderFactories element to your config file(s)
  3. Set the providerName attribute in the config file's connection string to your new DbProvider factory.
  4. Instantiate the Database using the appropriate factory.


1. Add a DbProviderFactory class:

Adding a DbProviderFactory class is easy, so here's an example implementation. For most of the required methods that implement the methods from the abstract class, DbProviderFactory, you can simply call one of the already provided methods from a concrete class. In my implementation, I relied on the DbProviderFactory that is obtainable from this call:

factory = DbProviderFactories.GetFactory( "System.Data.SqlClient" );

Here's the full class:

using System.Diagnostics;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;

using Foo.EnterpriseLibrary.Logging;

namespace Foo.EnterpriseLibrary.Data {
  /// <summary>
  /// Special factory that we need in order to expose the CreateConnection method. We need this
  /// so that we can add the InfoMessage handler to every newly created connection. This allows
  /// access to the print statement output from executing sql.
  /// </summary>
  /// <remarks>
  /// We want the behavior of System.Data.SqlClient.SqlClientFactory, but it is sealed,
  /// so we cannot derive from it. Instead, we will instantiate it, call it, and modify
  /// its behavior only as necessary.
  /// This requires implementing every method, but there aren't that many, so it's ok.
  /// </remarks>
  public class DbProviderFactoryFoo : DbProviderFactory {
    /// <summary>
    /// Singleton instance. Required by the Enterprise Library.
    /// </summary>
    public static DbProviderFactoryMgam Instance = new DbProviderFactoryMgam();

    private static DbProviderFactory _factory;

    private DbProviderFactoryMgam() : base() {
      // Note: this deviates from completely following the configuration / dependency injection model.
      // So, for example, if a config file specified an oracle or mySql database, the rest of
      // our code would work, but it would fail here.
      _factory = DbProviderFactories.GetFactory( "System.Data.SqlClient" );
      Log.Write( "Based on config, using our custom provider: DbProviderFactoryMgam", Log.CategoryMisc, TraceEventType.Start, Log.PriorityDefault );

    public override DbConnection CreateConnection() {
      DbConnection dbConnection = _factory.CreateConnection();
      if ( dbConnection is SqlConnection ) {
        SqlConnection sqlConn = dbConnection as SqlConnection;
        // This is the whole point of this class - the ability to add this handler.
        sqlConn.InfoMessage += InfoMessageEventHandler;
      return dbConnection;

    #region infoMessage handler
    /// <summary>
    /// Handles messages from the connection by logging them.
    /// </summary>
    /// <remarks>Each line of print output results in a separate call to this method,
    /// so the outputs cannot be combined.
    /// </remarks>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void InfoMessageEventHandler( object sender, System.Data.SqlClient.SqlInfoMessageEventArgs e ) {
      StringBuilder msg = new StringBuilder();

      foreach ( System.Data.SqlClient.SqlError err in e.Errors ) {
        if ( !IgnoreErrorNumber( err.Number ) ) {
          // Console.WriteLine( String.Format( "{0} (errNo: {1})", err.Message, err.Number ) );
          msg.Append( "sp: " ).Append( e.Errors[0].Procedure ).Append( " print output:" );
          msg.Append( Log.NewLinePlusTab );
          msg.Append( err.Message );

        Log.Verbose( msg, Categories.Database );

        // Some additional info is available if desired:
        // Console.WriteLine("The {0} has received a severity {1}, state {2} error number {3}\n" +
        // "on line {4} of procedure {5} on server {6}:\n{7}",
        // err.Source, err.Class, err.State, err.Number, err.LineNumber,
        // err.Procedure, err.Server, err.Message);

    // Returns true if the error should be ignored. 5701 and 5703 get sent
    // by the server upon login.
    private static bool IgnoreErrorNumber( int errorNumber ) {
      switch ( errorNumber ) {
        // case 5701:
        // "Changed database context ..."
        // case 5703:
        // "Change language setting ..."
        // return true;
          return false;
    #endregion infoMessage handler

    public override DbCommand CreateCommand() {
      return _factory.CreateCommand();

    public override DbCommandBuilder CreateCommandBuilder() {
      return _factory.CreateCommandBuilder();

    public override DbConnectionStringBuilder CreateConnectionStringBuilder() {
      return _factory.CreateConnectionStringBuilder();

    public override DbDataAdapter CreateDataAdapter() {
      return _factory.CreateDataAdapter();

    public override DbDataSourceEnumerator CreateDataSourceEnumerator() {
      return _factory.CreateDataSourceEnumerator();

    public override bool CanCreateDataSourceEnumerator {
      get {
        return _factory.CanCreateDataSourceEnumerator;

    public override DbParameter CreateParameter() {
      DbParameter param = _factory.CreateParameter();
      return param;

    public override System.Security.CodeAccessPermission CreatePermission( System.Security.Permissions.PermissionState state ) {
      return _factory.CreatePermission( state );


2. Add a DbProviderFactories element to your config files

To use the DbProviderFactoryFoo that we just added, we need to register it. Simply put this element in your config file (machine.config, app.config or web.config as appropriate). You will need to substitute your own namespace, class name, version # and publicKeyToken into the type attribute below. Also note, this element must be after, not before, the <configSections> element (at least in the app.config file. I did not work with machine.config or web.config).

      <add name="Foo Data Provider"
           description="Foo .Net Framework Data Provider for SqlServer"
           type="Foo.EnterpriseLibrary.Data.DbProviderFactoryFoo, Foo.EnterpriseLibrary.Data Net 3.5, Version=, PublicKeyToken=4514b30c41e86a32"


3. Set the providerName attribute in the config file's connection string to your new DbProvider factory.

So in the "add" element of the <connectionStrings> element, set the providerName attribute to the value of the invariant attribute from Step 2 just above.

    <add name="DataAccessTest on TestServer" connectionString="Data Source=;Initial Catalog=DataAccessTest;Persist Security Info=True;User ID=DataAccessTestGuest;Password=BlahBlah;Min Pool Size=0;Max Pool Size=1;Connect Timeout=5;Load Balance Timeout=120;Application Name=DataRunner"
      providerName="Foo.EnterpriseLibrary.Data.SqlClient" />

4. Instantiate the Database using the appropriate factory.

Instantiate the DAAB Database like you normally would. The simplest way is to use the static factory like this:

Database db = DatabaseFactory.CreateDatabase();

This will instantiate a Database using the default database specified in your configuration. Following the example above, since we specified the providerName to be our own custom provider, this call will instantiate a Generic Database with the DbProviderFactory set to our DbProviderFactoryFoo. The only downside to this, for us, is that our Database is now a Generic Database instead of a SqlDatabase. (It will be a SqlDatabase if we have set providerName="System.Data.SqlClient" in the connection string.)

The only feature we lose that I am aware of is parameter discovery. Since I considered this important, in my application, I also instantiate a SqlDatabase which is a subclass of Database. When I need parameter discovery, I use this database. As it happens, I only need this when I create a DbCommand. So, I create the command using the SqlDatabase instance but I execute the command using the Generic Database instance. It is simpler than it sounds and it is entirely hidden from the users of my Data layer. Here's how I instantiate the SqlDatabase:

SqlDatabase sqlDb = new SqlDatabase( connectionString );

I get a command by calling the Database method whose signature is this:

public virtual DbCommand GetStoredProcCommand( string storedProcedureName, params object[] parameterValues );

So it looks like:

DbCommand command = sqlDb.GetStoredProcCommand( "spDoSomethingImportant", "abc", 123 );

I then execute this command on the Generic Database:

db.ExecuteDataSet( command );


Well, I had wanted this for a long time. This proposed solution seems to work very well and is pretty simple. It does not require any modification to any of the Enterprise Library code. Rather, it leverages the dependency injection built into the Enterprise Library. Surprisingly, googling this problem does not yield anything that met my needs, so I decided to try to document my approach in hopes it helps the community. Good luck.

Feb 22, 2011 at 11:55 PM
Edited Feb 23, 2011 at 10:58 AM

To clarify, the only problem you have why you wanted this is that DAAB internaly creates and manages the database connection which you want to have access to so you can attach an event handler, am I correct?  If yes, then this can be easily accomplished by creating and opening connection prior to executing a command:

Database db = DatabaseFactory.CreateDatabase();
DbConnection connection = db.CreateConnection();
        //execute command

EntLib polls if there's currently an open connection and if there is, it uses that connection.  So in this case, it uses the connection variable you opened prior to executing the command.  Does this accomplishes the requirement you need? 


Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Feb 23, 2011 at 5:46 PM

Although that potentially seems like a viable alternative, I do not believe it solves the problem because it places undesirable restrictions on the execution. For example, a DataSet is not available with this approach because this only works when you call one of the execution methods on the command like ExecuteNonQuery(). It does not work if you call db.ExecuteDataSet( command ) because the db does not use the command's Connection in this code path.

This restriction is undesirable, and not acceptable in our applications. We must be able to call ExecuteDataSet().

I tested with this code, from your pseudo-code:

      using ( DbConnection dbConnection = db.CreateConnection() ) {

        if ( dbConnection is SqlConnection ) {
          SqlConnection sqlConn = dbConnection as SqlConnection;
          sqlConn.InfoMessage += InfoMessageEventHandler;

        DbCommand cmd = db.GetStoredProcCommand( "spSomeProcedure", 123, "abc" );

        cmd.Connection = dbConnection;

        // Using the db to execute here will NOT use the supplied connection.
        // db.ExecuteDataSet( cmd );
        // db.ExecuteNonQuery( cmd );

        // so for this to work, we must use one of the command's methods. No DataSet available.

Feb 24, 2011 at 2:01 AM

My apologies, what I missed was you need to associate the connection to a transaction, not sure if this is approach is ok with you:

using ( DbConnection dbConnection = db.CreateConnection() ) {

        if ( dbConnection is SqlConnection ) {
          SqlConnection sqlConn = dbConnection as SqlConnection;
          sqlConn.InfoMessage += InfoMessageEventHandler;

        DbCommand cmd = db.GetStoredProcCommand( "spSomeProcedure", 123, "abc" );

        using(DbTransaction trans = dbConnection.BeginTransaction())
                db.ExecuteDataSet( cmd, trans );
                db.ExecuteNonQuery( cmd, trans );

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Feb 24, 2011 at 9:32 PM

Thank you. Adding the transaction, as in your supplied code, does result in the InfoMessage handler getting called.

My main concern with this approach is that explicitly calling db.CreateConnection() appears to bypass the connection pool. (Stepping through in the debugger, it appears to bypass the pool). If that's the case, then I would not choose this approach for our purposes due to the (likely) performance hit. Our applications require high performance and the database is often the bottleneck, so frequently creating connections would be undesirable. If you think this does in fact use a connection from the connection pool, please let me know.

For an application with only a few database commands to execute and where performance would not be affected, your (connection, transaction approach) would be preferable to my approach since it requires fewer, simpler steps.

For my needs, fewer, simpler steps is not an issue since I provide a facade that sits in front of the Data library as a convenience for my users. The facade hides the details of such things as connections (and transactions) from the user. So, I would only have to put your code in one place.

The big advantage of your approach is that it does not require the users to make any change to their config file. That is a big plus. If not for the performance / connection pool issue, I might prefer this (connection, transaction) approach to the DbProviderFactory approach.

Feb 24, 2011 at 11:12 PM

I'm curious, how did you come up with the idea that it bypasses the connection pool?  DAAB doesn't do the actual pooling, it's still the responsibility of the underlying ADO.NET.   In addition, the CreateConnection method is also what the Execute methods eventually call to create a connection when there's no open transaction.


Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.

Feb 25, 2011 at 4:46 PM


Thank you for the discussion of this - it's been educational and interesting. The reason I thought the connection pool was bypassed is that the "normal" path when calling execute-like database methods are called, goes through GetOpenConnection(). However, when we call CreateConnection() directly, the code path goes directly to creating the connection and does not call any methods like GetOpenConnection(). That's how I came up with the idea. However, based on your question, I stepped through the code again and better understand what you are saying. I see your point that CreateConnection method eventually gets called anyway if there's no open transaction. So, I'm happy to be wrong about the connection pool :)

My current conclusion then is something like this:

  • the (connection, transaction) approach is preferable to the DbProviderFactory approach
  • to avoid having the duplicate (connection, transaction) code surrounding every database call, implement it in a facade that shields the database users from the details. (This is what I plan to do now).


Sarah, thanks very much. You've been extremely helpful.


Mar 3, 2011 at 7:37 PM

I have now implemented this approach and am happy with it, but I wanted to point out there is a slight performance hit. I think the performance hit is probably from creating the DbTransaction for every command.

I tested the performance by executing 1000 identical stored procedure calls. Using the code above in the "connection, transaction" approach, the 1000 commands take about 1.75 seconds. Using simply database.ExecuteDataSet( command ), the 1000 commands take about 1.35 seconds. The "connection, transaction" approach is about 30% longer. Although this is only an additional .4 milliseconds per command, in some of our applications that difference might matter. So, I added a property that can be used to turn this (connection, transaction) approach off. This makes it easy to test the performance difference in a real application which I have not yet done.

(One other detail about my performance test - I commented out adding the InfoMessage handler to the connection in order to eliminate that variable from the test and make it more directly comparable to the simple database.ExecuteDataSet command execution. Thus, the performance test is only testing the difference caused by using the DbTransaction.)

Mar 30, 2011 at 4:21 PM

Important (if you've followed this discussion)! - the above (connection, transaction) approach has a HUGE drawback which I did not consider. (Yes, I feel dumb for not considering it.) This approach creates an explicit database transaction for each of your database calls. If any of your database calls also involve transactions thus creating nested transactions, then you almost certainly do NOT want to use the (connection, transaction) approach. ( See for one discussion of the complexities of nested transactions.)

So my conclusion now is to use the DbProviderFactory approach outlined in the first post of this thread. Only use the (connection, transaction) approach if you are completely sure that your database calls will not explicitly create any database transactions.