Profiling Subsonic SQL with MvcMiniProfiler

Question

Anybody had any success in profiling their Subsonic sql queries with MvcMiniProfiler? I can't seem to find exactly where in Subsonic to hook into the SqlConnection creation process.

• http://code.google.com/p/mvc-mini-profiler/#Database_profiling
• http://subsonicproject.com/

Answer 1

I never did find a really great way to do this, but I did find something that worked. I couldn't subclass the SubSonic.DataProviders.DbDataProvider class since the constructor was "internal" (not awesome). So I just copied the source code into my project and made a few changes.

The main line of code that has to be changed is in the "CreateConnection" method, it needs to return a ProfiledDbConnection.

public DbConnection CreateConnection(string connectionString)
        {
            DbConnection conn = Factory.CreateConnection();
            conn.ConnectionString = connectionString;
            if(conn.State == ConnectionState.Closed) conn.Open();
            return conn;
        }

Since the Connection is no longer a SqlConnection so this caused a casting error in some of the existing code. To fix these I changed the "ExecuteDataSet" method to use the Connection from the scope instead of the factory.

public DataSet ExecuteDataSet(QueryCommand qry)
{
            if (Log != null)
                Log.WriteLine(qry.CommandSql);
#if DEBUG
            //Console.Error.WriteLine("ExecuteDataSet(QueryCommand): {0}.", qry.CommandSql);
#endif
            using (AutomaticConnectionScope scope = new AutomaticConnectionScope(this))
            {
                DbCommand cmd = scope.Connection.CreateCommand();
                cmd.CommandText = qry.CommandSql;
                cmd.CommandType = qry.CommandType;
                DataSet ds = new DataSet();
                cmd.Connection = scope.Connection;
                AddParams(cmd, qry);
                DbDataAdapter da = Factory.CreateDataAdapter();
                da.SelectCommand = cmd;
                da.Fill(ds);

                return ds;
            }
        }

I also changed the "ExecuteScalar" method to use the scope Connection.

public object ExecuteScalar(QueryCommand qry)
        {
            if (Log != null)
                Log.WriteLine(qry.CommandSql);

#if DEBUG
            //Console.Error.WriteLine("ExecuteScalar(QueryCommand): {0}.", qry.CommandSql);
            //foreach (var param in qry.Parameters) {
            //    if(param.ParameterValue==null)
            //        Console.Error.WriteLine(param.ParameterName + " = NULL");
            //    else
            //        Console.Error.WriteLine(param.ParameterName + " = " + param.ParameterValue.ToString());
            //}
#endif

            object result;
            using (AutomaticConnectionScope automaticConnectionScope = new AutomaticConnectionScope(this))
            {
                DbCommand cmd = automaticConnectionScope.Connection.CreateCommand();
                cmd.Connection = automaticConnectionScope.Connection;
                cmd.CommandType = qry.CommandType;
                cmd.CommandText = qry.CommandSql;
                AddParams(cmd, qry);
                result = cmd.ExecuteScalar();
            }

            return result;
        }

Everything seems to be working now. I am currently using IOC to determine whether the database class itself uses this ProfilingDbDataProvider or the existing DbDataProvider. I changed this in the code generation of the Context class to pull from IOC instead of using the ProviderFactory.

Hope that helps someone else.