First, we'll create a web app using Visual Studio 2008.
|Create a web app dialog|
<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=184.108.40.206, 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=220.127.116.11, 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.