Referencing Connector/Net on a remote machine

Recently I’ve read lots of forum posts from frustrated users claiming that their is something wrong with the Connector/Net installer.  They reference the connector in their web app, then deploy the app to their remote host only to find that the app no longer works.  I’ll walk through a simple web app sample and show where the confusion comes from and what they should be doing instead.  This sample assumes you have a recent build of Connector/Net installed with Visual Studio integration enabled.

First, we’ll create a web app using Visual Studio 2008.

The next thing that many developers do is create some type of page that references the connector.  Often this is a SqlDataSource that they will be connecting to a datagrid.  To do that you drop a SqlDataSource control and a GridView control on the page.  After setting the grid view to use the sql data source you now need to configure the data source control.  Normally  to do that you would select ‘Configure Data Source…’ from the smart tasks popup and walk through adding a data connection to your app (as well as Server Explorer).  Due to a bug in our implementation currently you need to add the connection to Server Explorer first and then pick that connection in the Configure Data Source dialog.  Once done with this you can find the new connection string in your web.config and see how the data source control is tied to it.   Here is the connection string added to my web.config.

<connectionStrings>
    <add name="testConnectionString"
     connectionString="server=localhost;user id=root;database=test"
     providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Here is how my SqlDataSource is configured now.

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
     ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
     ProviderName="<%$ ConnectionStrings:testConnectionString.ProviderName %>"
     SelectCommand="select * from t1"></asp:SqlDataSource>

At this point you can hit F5 in VS and the web app will run and show you the data you specified in your query.  However, if you deploy this application to a remote server that does not have Connector/Net installed then it will fail and you may see something like this:

What’s going on?  The first thing to understand is how the SqlDataSource connects to a data provider.  If you refer back to our connection string you’ll see the attribute providerName have the value ‘MySql.Data.MySqlClient’.  That is the invariant name of Connector/Net.  However this name is not enough to locate the right assembly to load.  This mapping is done in either the machine.config or web.config files.  The Connector/Net installer makes the proper registrations in the machine.config file.  Here is the DbProviderFactories section from my machine.config (I’ve omitted all entries but Connector/Net for brevity).

<system.data>
  <DbProviderFactories>
    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
         description=".Net Framework Data Provider for MySQL"
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
               Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

With this entry in machine.config then the system can match the invariant name to a fully qualified assembly name and also identify the client factory class that should be used.  You can see that by examining the type attribute above.

So what do you do if you are deploying this app to a remote server that you can’t install Connector/Net on?  Simple.  Add this registration to your web.config.  Just drop it in right outside the system.web block like this:

<system.data>
  <DbProviderFactories>
    <clear/>
    <add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient"
         description=".Net Framework Data Provider for MySQL"
         type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
               Version=5.2.1.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
  </DbProviderFactories>
</system.data>

So why the <clear/> tag?  You can’t doubly add provider factories to the config system so if you have Connector/Net installed on your dev system and you register the provider factory in your web.config then you will be doubly adding them.  The clear tag clears out all the provider factories pulled in from machine.config and then adds in  yours.  Now if you are mixing providers then you’ll need to adjust this to your situation.

I hope this blog post has cleared up some of the reasons why people have working web apps on their dev systems but then are confused when it doesn’t work after deploy.