Profiling when using DataAdapter.Fill to populate a DataSet.

[GoogleCodeExporter]

The standard way to fill a DataSet is to use a DataAdaper either specific to 
the connection type or instantiated from a factory:

 var factory = DbProviderFactories.GetFactory(connectionSettings.ProviderName);
 using (var da = factory.CreateDataAdapter())
 {
  da.SelectCommand = command;
  da.Fill(returnData);
 }

But this throws an error because the adapter will be a specific implementation 
(say SqlDataAdapter) while the command is a ProfiledDbCommand.

I think it may need a ProfiledDataAdapter that wraps the underlying specific 
adapter.

Original issue reported on code.google.com by keithhe...@gmail.com on 14 Jun 2011 at 9:39

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

+1 from me, definitely. 
We have legacy code that's trying to fill a DataTable, and I can't find a way 
to profile these db calls.
We have even older legacy code that's using SqlConnections everywhere.

Original comment by steve.wi...@gmail.com on 23 Jun 2011 at 10:09

[GoogleCodeExporter]

FYI, for now I've put together a workaround that gets me the timing I need 
until miniprofiler is enhanced. It works best if you have a single fetchtable() 
type of function that returns a DataTable:

a) wrap this around the query:
  var sqlprofiler = MiniProfiler.Current.SqlProfiler;
  sqlprofiler.ExecuteStart(command, ExecuteType.Scalar);
  // run query, clean up
  sqlprofiler.ExecuteFinish(command, ExecuteType.Scalar);
b) change MiniProfiler.SqlProfiler to be public
c) add the tag [ScriptIgnore] to MiniProfiler.SqlProfiler so you don't get a 
circular reference error when serializing

That's it, pretty simple. One drawback is that it's reported as a scalar query 
rather than a reader-type query, but you need a DbDataReader to track that.

Original comment by steve.wi...@gmail.com on 24 Jun 2011 at 4:31

[GoogleCodeExporter]

I just made the factory public which means this now works: 

 [TestMethod]
        public void TestDataAdapter()
        {
            var prof = new TestDbProfiler();
            var factory = new ProfiledDbProviderFactory( prof, new System.Data.SqlServerCe.SqlCeProviderFactory());

            using (var da = factory.CreateDataAdapter())
            {
                var cmd = factory.CreateCommand();
                cmd.CommandText = "select 1 as A, 2 as B";
                da.SelectCommand = cmd;
                da.SelectCommand.Connection = connection;

                DataSet ds = new DataSet();
                da.Fill(ds);

                Assert.That(((int)ds.Tables[0].Rows[0][0]) == 1);
            }

            Assert.That(prof.ExecuteStartCount == 1);
            Assert.That(prof.ReaderFinishCount == 1);
            Assert.That(prof.ExecuteFinishCount == 1);
        }

Original comment by sam.saff...@gmail.com on 12 Jul 2011 at 4:20

• Changed state: Fixed

[GoogleCodeExporter]

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

I think the problem is in ProfiledDbProviderFactory.

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.

Original comment by keithhe...@gmail.com on 13 Jul 2011 at 8:55