DIY ADO.NET Profiler

In a professional project, we are using Entity Framework as an ORM provider for our application. On several occasions we wanted to know what SQL query some LINQ query produced. This mostly is the case when a particular query is very slow, returns unexpected results, etc. To ‘debug’ a LINQ query, we could just use the cool new feature of VS 2010 (Ultimate), IntelliTrace. With this toolwindow, we can inspect which query is run at a given point in time. This is great when we would have an Ultimate license for VS2010, which we unfortunately do not have.

The second negative point about IntelliTrace is that is doesn’t tell us much about the executed query’s performance. Did it take 500ms to run, or 5ms? We just don’t know.

Wait a sec! Why don’t you just use the SQL Profiler software then? Well, I suggest you all try to debug a EF Query using the SQL-Profiler. It’s just a pain in the ****.

Now, that’s why I decided to look for a solution myself. I tried to keep it as basic, and simple as I could, so don’t look for over-engineering here!

This will be a series of articles, I will try to keep an index here so you can find the different parts easily.

Using the Code

Using the code is as simple as this:

/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main()
{
    //First and only entry point of the profiler
    ProxyGenerator.Init();

    Application.Run(new Form1());
}

As you can see, this has a minimal impact on how your application should be designed, etc. This makes the profiler ideal for plugging into existing projects. In this first article, we will only use one event to catch all the profiling info, this will eventually change by implementing a WCF service to do the communication between the server (ProxyDbxxx, etc.) and clients (VS2010 add-in). But for now, we can catch all the debugging info using these commands:

public void SomeMethod()
{
    ProxyGenerator.DbProviderInfo +=
      new EventHandler<DbProviderInfoEventArgs>(ProxyGenerator_DbProviderInfo);
}

void ProxyGenerator_DbProviderInfo(object sender, DbProviderInfoEventArgs e)
{
    MessageBox.Show(e.Message);
}

For now, we only show informative messages using the DbProviderInfo event. Later on, when we will discuss the approach to creating a profiling system, we will add real statistics to the game. So for now, only debug messages. There is a sample program included in the download, of which the output will be the following:

In the image below, you can see the Class Diagram of the ProxyGenerator:

Next up, I will discuss the technique of creating proxy DbProviders, and how we can use them to intercept all method calls to DbCommand, DbConnection, etc. classes.

DbProviderFactory

Fist off, a quick intro to DbProviderFactory (Concept on MSDN). This ‘Factory-pattern’ implementation is designed to abstract all database access away from the code. Each ‘big’ database provider (MSSQL, Oracle, etc.) has an implementation of the DbProviderFactory. The DbProviderFactory itself is an abstract class which contains everything we need to connect to, and query any database. All implementations of the DbProviderFactory are stored by the .NET Framework in the DbProviderFactories class. By using this class, we can execute the following code:

DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);

This will get you an instance of the DbProviderFactory matching the providerName specified. By using this DbProviderFactory pattern, no dependencies are needed to MSSQL, Oracle specific implementations.

How are these implementations stored

The DbProviderFactories class contains three public members, of which one is particularly interesting to us: ‘GetFactoryClasses‘. This method returns a DataTable which contains all the implementations of DbProviderFactory in the loaded assemblies. The first thing that skipped my mind was, can we edit this DataTable so we can provide a custom implementation class for the providers? By using the public method GetFactoryClasses, we can only access a copy of the original DataTable stored in-memory in the DbProviderFactories class. To access the original DataTable, I used a disassembler to inspect the .NET Framework class. I noticed that there is a private static method which returns a reference to the original DataTable created by the DbProviderFactories class.

Finding a way to access the original DataTable containing the combinations of ProviderNames and AQN [^] was key to the success of this approach. It may not seem right to this, but as a friend of mine says, ‘there’s nothing wrong with being stupid’.

//DbProviderFactories has a private static method
//(thanks jetBrains decompiler!) which gives us
//a non copy of the available DbProviderFactories
var dbProvidersFactoriesDataTable = typeof(DbProviderFactories).GetMethod(
  "GetProviderTable",
  BindingFlags.NonPublic | BindingFlags.Static).Invoke(null, null)
  as DataTable;

Here is a diagram of how the default DataTable could look like:

Overriding default DbProviderFactory implementations

The second way of doing it is by creating the proxy class ourselves. In .NET 1.0 times, this would mean that for every possible DbProvider, we would have to write a proxied version (ProxyOracleDbProviderFactory, etc). Luckily for us, we’ve got generics since .NET 2.0! So we could go and write a class like this:

ProxyDbProviderFactory<T> where T : DbProviderFactory

Simple, yet very powerful! Why is this powerful? Well, we can use this for literally every DbProviderFactory (damn that’s a long word to write all the time) that will ever be made. The following snippet demonstrates how we can create a new type using this generic type.

var DbProviderFactoryTypes =
  dbProvidersFactoriesDataTable.Rows.OfType<datarow>().Select(
  x => x["InvariantName"].ToString()).ToList();

foreach (string providerInvariantName in DbProviderFactoryTypes)
{
    //get an instance of the DbProviderFactory in this loop.
    var factory = DbProviderFactories.GetFactory(providerInvariantName);

    //create new generic type ProxyDbProviderFactory<{TypeOfFactoryToProxy}>
    var factoryType = typeof(ProxyDbProviderFactory<>).MakeGenericType(
                      new Type[] { oringalFactoryType });

    //call static method Initialize to construct the instance
    factoryType.GetMethod("Initialize", BindingFlags.FlattenHierarchy |
                BindingFlags.Public | BindingFlags.Static).Invoke(null, null);

    ... (see below)
}

Before we continue, you should notice that all DbProviderFactory implementations must use the Singleton pattern. In this case, that means all Factories have a static field ‘Instance‘ which contains the only instance used for the type. This is very useful to know, because we have to follow this pattern. As we can see in the last line of code in the previous snippet:

//call static method Initialize to construct the instance
factoryType.GetMethod("Initialize", BindingFlags.FlattenHierarchy |
            BindingFlags.Public | BindingFlags.Static).Invoke(null, null);

We call the static (that’s why the Invoke method is called with the double null parameter) method Initialize to setup the newly created type. This is the Initialize method:

...
//static field containing the singleton instance
public static ProxyDbProviderFactory<tfac> Instance;
...
public static void Initialize()
{
    Instance = new ProxyDbProviderFactory<tfac>();
}
...
public ProxyDbProviderFactory()
{
    //access the singleton instance of the type that is proxied
    FieldInfo field = typeof(TFac).GetField("Instance",
                      BindingFlags.Public | BindingFlags.Static);
    this.ProxiedDbProviderFactory = (TFac)field.GetValue(null);
}

In this Initialize method, we instantiate the singleton instance of our proxied DbProviderFactory. In the above code snippet, we also see the constructor, which extracts the Instance field of the proxied type. We store a reference to this instance named ProxiedDbProviderFactory. So when is the proxied DbProviderFactory (e.g., OracleDbProviderFactory) intialized (= creation of a singleton instance)? This happens in this line of code:

//get an instance of the DbProviderFactory in this loop.
var factory = DbProviderFactories.GetFactory(providerInvariantName);

By getting a factory, we make sure it will be properly initialized. We do not actually need this variable ‘factory‘ in the rest of our program.

Next up is actually overriding the type in the DataTable discussed earlier. The following code snippet demonstrates how we can override the default implementations of DbProviderFactories. Note that theDataTable‘s columns are read-only, so I had to manually override this to replace the field. See it for yourself below:

foreach (string providerInvariantName in DbProviderFactoryTypes)
{
    ... (see above)

    //retrieve the datarow in which we will alter
    //the Type to the newly contructed ProxyType
    var dataRowToEdit =
        dbProvidersFactoriesDataTable.Rows.OfType<datarow>().FirstOrDefault(
        dt => ((string) dt["InvariantName"]) == providerInvariantName);

    if (dataRowToEdit == null)
        return;

    //set readOnly false to edit table
    dbProvidersFactoriesDataTable.Columns["AssemblyQualifiedName"].ReadOnly = false;

    //map the DbProvider to the new proxy
    dataRowToEdit["AssemblyQualifiedName"] = factoryType.AssemblyQualifiedName;

    //set readOnly back to original
    dbProvidersFactoriesDataTable.Columns["AssemblyQualifiedName"].ReadOnly = true;
}

After executing this code, the DataTable now looks like this:

All that is left now to conclude this first part is implementing all the necessary overrides of the abstract class DbProviderFactory in the ProxyDbProviderFactory<TFac> class. For reference, I will include some samples of this implementation here:

...
public override DbCommandBuilder CreateCommandBuilder()
{
    return ProxiedDbProvider.CreateCommandBuilder();
}

public override DbConnection CreateConnection()
{
    return ProxiedDbProvider.CreateConnection();
}

public override DbConnectionStringBuilder CreateConnectionStringBuilder()
{
    return ProxiedDbProvider.CreateConnectionStringBuilder();
}
...

Obviously, what happens is, we just call the proxied instance’s members, so all default functionality is preserved. Later on, we will also create Proxy classes for DbConnection, DbCommand, etc., to enable real profiling.

IServiceProvider interface

One last thing we should briefly discuss is the implementation of the IServiceProvider interface. All DbProviderFactory implementations also implement IServiceProvider. According to MSDN, this interface ‘Defines a mechanism for retrieving a service object; that is, an object that provides custom support to other objects’. While debugging and doing some research on the web, I discovered that the only service that is requested, is of type DbProviderServices. To support this IServiceProvider interface, we implement the service method as follows:

public object GetService(Type serviceType)
{
    ProxyGenerator.OnQueryExecuted(string.Format(
         "GetService requested {0}", serviceType.Name));

    if (serviceType == GetType())
    {
        return ProxiedDbProvider;
    }

    DbProviderServices service;

    if (serviceType == typeof(DbProviderServices))
    {
        if (ProxiedDbProvider is IServiceProvider)
        {
            service = (ProxiedDbProvider as IServiceProvider).GetService(serviceType)
                       as DbProviderServices;
        }
        else
        {
            service = null;
        }
    }

    if (service != null)
    {
        return new ProxyDbProviderServices(service);
    }

    return null;
}

As you can see, we already encapsulate this default return value of the proxied DbProviderFactory GetService() method in a new class called ProxyDbProviderServices. This enables us to do some more initial logging of what’s going on in the base classes.

Disadvantages of this approach

• Using undocumented features of the .NET Framework (private DataTable)

Advantages of this approach

• No need for custom ObjectContext / DbContext (Entity Framework).
• No need for custom DbProvider per SQL technology.
• ‘Single point of intrusion’ => One line of code gives the lib access to the runtime.
• All implementors of DbProviderFactory covered, even exotic custom made ones.

Round up

So what have we done after this first part? To round it up, this summation:

• Get a reference to the internally used DataTable containing all DbProviderFactory implementors
• Create a generic proxy-type to encapsulate the default DbProviderFactory – types
• Implement abstract methods of DbProviderFactory in ProxyDbProviderFactory<TFac>

Question for the reader

What do you think of this mechanism to alter an internally defined DataTable to change what DbProviders are intialized and used?

Do you see other disadvantages of this approach?

Is there a way to use IOC frameworks such as Castle, Unity, Lin.Fu to create a proxy for a sealed class?

Source code

The source code can be found on my github repo