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'.

Lobbyist

So far I haven't found how to use MVC Mini Profiler with Enterprise Library too... – Genseric

@goalie7960, Please see my updated answer for a solution to this problem. – Microsecond

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);
   

Jahdal

That works great. I couldn't figure out the providers model. One thing though, is there a way to remove DbCommand cmd = new ProfiledDbCommand(dbCommand, dbCommand.Connection, MiniProfiler.Current); and have the db.GetStoredProcCommand() return a profiled command? I know I can do via helper method, but a configuration solution would be so much nicer. – Lobbyist

Answer 2

This is quite and old post, but I think its worth to mention how I have sorted this out.

Due to the way our application was implemented it wasn't easy to apply the solution by Jonas, so I went down the road of changing the EntLib Datablock (which I have already modified in our project).

The thing was as easy as just modify the methods DoExecuteXXX in the Database class so they call the miniprofiler, which previously I have changed to expose the SqlProfiler as public.

Just doing:

if (_miniProfiler != null)
    _miniProfiler.ExecuteStart(command, StackExchange.Profiling.Data.ExecuteType.NonQuery);

at the begining of each method and

if (_miniProfiler != null)
    _miniProfiler.ExecuteFinish(command, StackExchange.Profiling.Data.ExecuteType.NonQuery); 

in the finally block did the trick. Just changing the Execution type to the appropriate for the method being changed.

The miniprofiler instance was obtained in the constructor of the DatabaseClass

if (MiniProfiler.Current != null)
    _miniProfiler = MiniProfiler.Current.SqlProfiler;

Disallow

Answer 3

You can try something like this.

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);

DbConnection dbc = MvcMiniProfiler.Data.ProfiledDbConnection.Get(dbCommand.Connection, MiniProfiler.Current);
DataSet ds = db.ExecuteDataSet(dbc);

---

Update :

After a lot of searching though the EntLib Data source code, The SqlDatabase, it's doesn't look like there is really an easy way to achive this. Sorry. I know I am.

This actually seems to be an issue with Mvc-Mini-Profiler. If you look at this Issues 19 Link on MVC-Mini-Profiler Project Home, you will see other people are having the same Issue.

I've spent quite a while going though System.Data.SqlClient with Reflector, and ProfiledDbProviderFactory, trying to see what the Issue is and it seems like the following is were the problem is. This is found in ProfiledDbProviderFactory class.

 public override DbDataAdapter CreateDataAdapter()        
 {
     return tail.CreateDataAdapter();        
 }

Now the problem isn't exactly with this code but the fact that when Microsoft.Practices.EnterpriseLibrary.Data.Database class calls an intance of DbDataAdapter (tail in our case is SqlClientFactory) it creates SqlClientFactory Command, and then tries to set the Connection of this command to ProfiledDbConnection and this is were it breaks.

I've tried creating my own Factory classes to get around this but I just get too lost. Maybe when I have some free time I will try and sort this out, unless SO team beats me to it. :)

Microsecond

How is this different from what I posted? – Lobbyist

Please see my updated Answer. Was also wonder about how to use Mini Profiler with EntLib. Will need to go though this in detail some time. – Microsecond

This code does not compile. You can't pass in a DbConnection to the ExecuteDataSet method. Furthermore, the Connection property of DbCommand is null. – Lobbyist

Your updated answer is effectively not using Enterprise Library. – Genseric

@Regent, are you looking for a solution without changing EntLib? As you can dload the source code. I found what the Issue is just want to know if you will accept if I change the EntLib Source code slightly? – Microsecond

The preferred solution would involve just wrapping the EntLib command and the magic of the profilier would take care of the rest. For me, having to add extra code to CreateCommand() and close the connection invalidates the usage of EntLib. I would also have to change code in every data access method. – Lobbyist

@goalie7960, without creating a new class that inherits from the abstract class Database, in Microsoft.Practices.EnterpriseLibrary.Data there is no way of doing it. – Microsecond

@Jethro, ideally I would use ProfiledSqlDatabase : Database which will provide with all profiler's magic. – Genseric

@Regent, I had the exact same thought. Even started implementing the same functionallity as the SqlDatabase. I'll see what I can do about implementing this. – Microsecond

Is it better to have ProfiledSqlDatabase : SqlDatabase? – Lobbyist

@goalie7960, It would be better but it's not possible, you see the issue comes in that when you use SqlDatabase, specific methods are run that only belong to Sql Server, were ProfiledDbProviderFactory uses the generic methods of DbProviderFactory. To be honest I'm sitting here pulling my hair out trying to get this to work, can't believe how difficult it is. I think I may have a solution, but It means overriding most of the abstract Database methods. Will try carry on with this tomorrow. – Microsecond

@Microsecond maybe it's better to just post this to the issue tracker for this project? – Lobbyist

@goalie7960, please check my updated post, there already is an issue 19, that should sort this out. I've tried but I just ended up in circles, that System.Data namespace is scary. I also now after quite a few hours understand EntLib Data namespace a lot more. OH well. – Microsecond