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

Based on Sam's feedback below I merely implemented Dapper instead and took 50 lines of code down to around 20 and significantly reduced complexity. I was able to implement a solution for SqlDataReaders by using `DbDataReader` and `DbType.Guid` for the parameter collection (replacing all the MS SQL specific bits with the `System.Data.Common` equivelant). As Sam mentioned it's a lot more verbose and you end up writing a lot of boilerplate code and you might be better served (imo) implementing Dapper than trying to shoehorn it into an existing SqlDataAdapter — TodK, Jun 28 '11 at 20:14

score 4 · Accepted Answer · answered Jun 28 '11 at 12:19

4

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.

answered Jun 28 '11 at 12:19

Sam Saffron

128,308
78
326
506

Dapper was easier tbh. Thanks! – TodK Jun 28 '11 at 20:15
There's also a `ProfiledDbDataAdapter` class you can use to wrap a `SqlDataAdapter` with profiling. See http://stackoverflow.com/a/13793409/8479 – Rory Dec 10 '12 at 00:26

score 3 · Answer 2 · answered Apr 25 '12 at 10:57

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.

Using mvc-mini-profiler with ADO.NET SqlConnection

2 Answers2