Using mvc-mini-profiler with ADO.NET SqlConnection

Question

I'm attempting to utilize the (awesome) mvc-mini-profiler with some pre-existing SqlConnection stored procedure code (we don't use EF or L2S, just ADO.NET to SQL Server 2008). I'm looking for some guidance on how to integrate the inherited ProfiledDb types into this kind of code.

var con = new SqlConnection("connectionstring");  
var cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.CommandText = "SP_STORED_PROCEDURE_NAME";
cmd.Paramters.Add("recordsetid",SqlDbType.UniqueIdentifier).Value = recordsetid;
var dSet = new DataSet();
var da = new SqlDataAdapter(cmd);
da.fill(dSet);
<parse DataSet>

Any help here for us legacy ADO.NET users would be great because on the surface it appears that the SQL profiler should be applicable to this situation

Answer 1

You will need to do is wrap up your connection and use the DbConnection CreateCommand factory.

Similarly to pass params you will need to use the base interface methods, and avoid stuff like SqlParameter cause it is not wrapped.

So:

var cnn = MvcMiniProfiler.Data.ProfiledDbConnection.Get(new SqlConnection(str));
var cmd = cnn.CreateCommand();
var param = cmd.CreateParameter(); 
...

I have not tested DataSets and DataAdapters, honestly I use Dapper for this kind of stuff these days as it is much less verbose. If it plays up, be sure to report on Google code.

Answer 2

I have a similar situation where all our SQL is in stored procedures and we simply have ADO.NET code that calls into them. I also have a data access layer that I am happy with so do not like the idea of having to rewrite chunks of it simply to accommodate MiniProfiler.

So the compromise I settled on was to use the standard MiniProfiler.Step() calls around the procedure calls. It helps in my case that all calls to ExecuteReader() et al are part of a base class so I know all SqlCommands get executed within a few base methods, so it was easy to change my existing code to look similar to this:

protected SqlDataReader ExecuteReader()
{
    SqlDataReader reader = null;

    // sqlComm is a member of a base class which this method is part of. I also 
    // happen to know that sqlComm.CommandText will always refer to a stored
    // procedure name so it makes it easy to view in the results.
    using (MiniProfiler.Current.Step(sqlComm.CommandText))
    {
        try
        {
            sqlConn.Open();
            reader = sqlComm.ExecuteReader();
        }
        catch (SqlException exception)
        {
            sqlConn.Close();
            // Error handling removed for brevity...
        } 
    }

    return reader;
}

I'm sure that this isn't as good as Sam's answer as I'm sure some details will be missing in the results tab, but it works well enough for me to analyse database calls now with very little changes to my data access code structure.