MVC Mini Profiler with Microsoft Enterprise Library

Question

I'm sure it's possible to profile the Enterprise Library SQL commands, but I haven't been able to figure out how to wrap the connection. This is what I have come up with:

Database db = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = db.GetStoredProcCommand(PROC);

ProfiledDbCommand cmd = new ProfiledDbCommand(dbCommand, dbCommand.Connection, MvcMiniProfiler.MiniProfiler.Current);
db.AddInParameter(cmd, "foo", DbType.Int64, 0);

DataSet ds = db.ExecuteDataSet(cmd);

This results in the following exception:

Unable to cast object of type 'MvcMiniProfiler.Data.ProfiledDbCommand' to type 'System.Data.SqlClient.SqlCommand'.

Answer 1

The exception comes from this line in Entlib Database.DoLoadDataSet

    ((IDbDataAdapter) adapter).SelectCommand = command; 

In this case the adapter is of type SqlDataAdapter and it expects a SqlCommand, the command that is created by the ProfiledDbProviderFactory is of type ProfiledDbCommand as you see in the exception.

This solution will provide EntLib with a generic DbDataAdapter by overriding CreateDataAdapter and CreateCommand in the ProfiledDbProviderFactory. It seems to work as it should but I apologize if I've overseen any unwanted consequenses this hack might have (or sore eyes it might have caused ;) . Here it goes:

1. Create two new classes ProfiledDbProviderFactoryForEntLib and DbDataAdapterForEntLib

   public class ProfiledDbProviderFactoryForEntLib : ProfiledDbProviderFactory
   {
       private DbProviderFactory _tail;
       public static ProfiledDbProviderFactory Instance = new ProfiledDbProviderFactoryForEntLib();
   
       public ProfiledDbProviderFactoryForEntLib(): base(null, null)
       {
       }
   
       public void InitProfiledDbProviderFactory(IDbProfiler profiler, DbProviderFactory tail)
       {
           base.InitProfiledDbProviderFactory(profiler, tail);
           _tail = tail;
       }
   
       public override DbDataAdapter CreateDataAdapter()
       {
           return new DbDataAdapterForEntLib(base.CreateDataAdapter());
       }
   
       public override DbCommand CreateCommand()
       {
           return _tail.CreateCommand(); 
       }        
   }
   
   public class DbDataAdapterForEntLib : DbDataAdapter
   {
       private DbDataAdapter _dbDataAdapter;
       public DbDataAdapterForEntLib(DbDataAdapter adapter)
       : base(adapter)
      {
           _dbDataAdapter = adapter;
      }
   }
   

2. In Web.config, Add the ProfiledDbProviderFactoryForEntLib to DbProviderFactories and set ProfiledDbProviderFactoryForEntLib as providerName for your connectionstring

   <configuration>
       <configSections>
           <section name="dataConfiguration" type="..."  />
       </configSections>
       <connectionStrings>
           <add name="SqlServerConnectionString" connectionString="Data Source=xyz;Initial Catalog=dbname;User ID=u;Password=p"
     providerName="ProfiledDbProviderFactoryForEntLib" />
       </connectionStrings>
       <system.data>
           <DbProviderFactories>
             <add name="EntLib DB Provider"
              invariant="ProfiledDbProviderFactoryForEntLib"
              description="Profiled DB provider for EntLib"
              type="MvcApplicationEntlib.ProfiledDbProviderFactoryForEntLib,     MvcApplicationEntlib, Version=1.0.0.0, Culture=neutral"/>
           </DbProviderFactories>
       </system.data>
       <dataConfiguration defaultDatabase="..." />
       <appSettings>... <system.web>... etc ...
   </configuration>
   

   (MvcApplicationEntlib is the name of my test project)

3. Set up the ProfiledDbProviderFactoryForEntLib before any calls to the DB (readers sensitive to hacks be warned, this is where it gets ugly)

   //In Global.asax.cs 
       protected void Application_Start()
       {
   
           ProfiledDbProviderFactoryForEntLib profiledProfiledDbProviderFactoryFor = ((ProfiledDbProviderFactoryForEntLib)DbProviderFactories.GetFactory("ProfiledDbProviderFactoryForEntLib"));
           DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient"); //or whatever predefined factory you want to profile
           profiledProfiledDbProviderFactoryFor.InitProfiledDbProviderFactory(MiniProfiler.Current, factory); 
       ...
   

   This could probably been done in a better way or in another place. MiniProfiler.Current will be null here because nothing is profiled here.

4. Call the stored procedure just as you did from the beginning

   public class HomeController : Controller
       {
           public ActionResult Index()
           { 
               Database db = DatabaseFactory.CreateDatabase();
               DbCommand dbCommand = db.GetStoredProcCommand("spGetSomething");
               DbCommand cmd = new ProfiledDbCommand(dbCommand, dbCommand.Connection, MiniProfiler.Current);
               DataSet ds = db.ExecuteDataSet(cmd);
               ...
   

Edit: Ok wasn't sure exactly how you wanted to use it. To skip the manual creation of a ProfiledDbCommand. The ProfiledDbProviderFactory needs to be initiated with the miniprofiler for every request.

1. In Global.asax.cs, Remove the changes you made to Application_Start (the factory setup in step 3 above), add this to Application_BeginRequest instead.

   ProfiledDbProviderFactoryForEntLib profiledProfiledDbProviderFactoryFor = ((ProfiledDbProviderFactoryForEntLib) DbProviderFactories.GetFactory("ProfiledDbProviderFactoryForEntLib"));
   DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
   profiledProfiledDbProviderFactoryFor.InitProfiledDbProviderFactory(MvcMiniProfiler.MiniProfiler.Start(), factory);
   

2. Remove the method CreateCommand from ProfiledDbProviderFactoryForEntLib to let the ProfiledDbProviderFactory create the profiled command instead.

3. Execute your SP without creating a ProfiledDbCommand, like this

   Database db = DatabaseFactory.CreateDatabase();
   DbCommand dbCommand = db.GetStoredProcCommand("spGetSomething");
   DataSet ds = db.ExecuteDataSet(dbCommand);