ProfiledDbProviderFactory doesn't work with System.Data.SqlClient.SqlClientFactory

[GoogleCodeExporter]

This is a follow on from issue 19...

ProfiledDbProviderFactory works for a SqlCeProviderFactory, but not for a 
SqlClientFactory.

CreateCommand() wraps the command in a ProfiledDbCommand:

        public override DbCommand CreateCommand()
        {
            return new ProfiledDbCommand(tail.CreateCommand(), null, profiler);
        }

However CreateDataAdapter() just returns the underlying data adapter:

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

The problem is that means that I have a SqlDataAdapter but a ProfiledDbCommand.

So this line throws an error:

        da.SelectCommand = cmd;

The exception is:

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

This same line doesn't throw an error when the wrapper factory is a 
SqlCeProviderFactory - I think the implementations must be different. However 
that means that the unit test passes.

Original issue reported on code.google.com by keithhe...@gmail.com on 13 Jul 2011 at 8:59

[GoogleCodeExporter]

I get another issue where it throwns an NRE when calling CreatingConnection()

My fix is to call InitProfiledDbProviderFactory explicity, it looks like 
something does not initialise in time, anyone seeing this?

My init:

ProfiledDbProviderFactory.Instance.InitProfiledDbProviderFactory(MiniProfiler.Cu
rrent, SqlClientFactory.Instance);
Database.DefaultConnectionFactory = new ProfiledDbConnectionFactory(new 
SqlConnectionFactory());

Original comment by stefan.s...@gmail.com on 22 Jul 2011 at 1:56

[GoogleCodeExporter]

I think that's a different issue - my bug here is that some implementations of 
DbDataAdapter throw an exception if assigned a DbCommand that's not of the same 
sub-type. 

There is no way to fill a DataSet if you're using SqlDataAdapter.

The fix is to add a wrapper (a ProfiledDbDataAdapter) that can handle the 
ProfiledDbCommand type but then assigns the SqlCommand that wraps to the 
SqlDataAdapter that the new ProfiledDbDataAdapter wraps.

The problem then is that you also need to implement the rest of 
ProfiledDbDataAdapter.

Original comment by keithhe...@gmail.com on 22 Jul 2011 at 9:53

[GoogleCodeExporter]

does this technique work for you? 
http://blog.fearofaflatplanet.me.uk/mvcminiprofiler-and-nhibernate-take-2

Original comment by sam.saff...@gmail.com on 29 Jul 2011 at 12:04

• Changed state: Started

[GoogleCodeExporter]

Hi Sam, that has the same problem - I end up with this error:

  Unable to cast transparent proxy to type 'System.Data.SqlClient.SqlCommand'.

Original comment by keithhe...@gmail.com on 16 Aug 2011 at 9:26

[GoogleCodeExporter]

Hi Keith, I've put a fix up for that cast error at 
http://www.adverseconditionals.com/2011/08/fixing-miniprofiler-with-nhibernate.h
tml - at least on my machine :)

Original comment by mcintyre...@gmail.com on 27 Aug 2011 at 3:20

[GoogleCodeExporter]

Not sure, that's a lot of workaround. 

It seems to me that a ProfiledDbDataAdapter (i.e. a simple wrapper of the 
actual DataAdapter but adding the profiling) would be a much better fix.

I'll try this workaround too if I get the chance, but I don't want to be adding 
too many workarounds on top of the NuGet release.

Original comment by keithhe...@gmail.com on 31 Aug 2011 at 3:33

[GoogleCodeExporter]

In the end worked around this, by replacing:

    using (var da = factory.CreateDataAdapter())
    {
        da.SelectCommand = com;
        da.Fill(returnData);
    }

With:

    var dr = com.ExecuteReader();

    // Using DataTable.Load rather than DataAdapter.Fill as it can be profiled
    int i = 0;
    while (!dr.IsClosed)
    {
        // Create a table
        var table = returnData.Tables.Add("table" + i++);

        // Load the results into the table. DataTable.Load also auto-moves onto the next result set
        table.Load(dr, LoadOption.OverwriteChanges);
    }

It's not ideal, but it does let us replace the Fill method with something 
that's profiled.

Original comment by keithhe...@gmail.com on 13 Oct 2011 at 1:38