Monday, April 7, 2008

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.

Create a web app dialog
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.

53 comments:

  1. Hi there, I have been following ramblings and I must say I tend to agree with your interesting post

    ReplyDelete
  2. Same with this really nice codes. thanks alot.

    ReplyDelete
  3. this the tutorial i need..
    thanks

    ReplyDelete
  4. great tutorial, thank you

    ReplyDelete
  5. Intimnih DnepropetrovskJanuary 28, 2009 at 4:47 PM

    You are a real master, body!
    Thanks!

    ReplyDelete
  6. thanks for teaching this.its really useful.

    ReplyDelete
  7. This is exactly what I was looking for. Thanks for sharing this great article! That is very interesting :) I love reading and I am always searching for informative information like this! You are bookmarked!
    Thx,

    ReplyDelete
  8. thank u very much
    lol

    ReplyDelete
  9. Thank you so much! I needed this and I've been searching for days!

    ReplyDelete
  10. This is exactly what I was looking for. Thanks for sharing this great article! That is very interesting Smile I love reading and I am always searching for informative information like this! You are bookmarked!
    Thx,

    ReplyDelete
  11. tnomeralc web design toysApril 1, 2009 at 1:15 AM

    This is really great.. I enjoy reading this articles.

    ReplyDelete
  12. Connector/Net 5.1 represents a change in how we package our products. Until now, we've shipped our core provider and the Visual Studio integration bits as separate downloads. This has become a bit of a problem. Often we would fix a bug that involved changing code both in the VS package and in the core provider. This provided a versioning problem where users would need to upgrade both products to see the benefit of the bug fix. To solve this, we've decided to discontinue Tools for Visual Studio as a separate product and have, instead, integrated it into a new Connector/Net installer. We hope this provides a better "out of box" experience for our users.

    ReplyDelete
  13. Free Background CheckApril 2, 2009 at 4:04 PM

    Very informative post..

    ReplyDelete
  14. Invisible Pet FencingApril 19, 2009 at 12:34 AM

    On Windows, installation is supported either through a binary installation process or by downloading a Zip file with the Connector/NET components.

    Before installing, you should ensure that your system is up to date, including installing the latest version of the .NET Framework.

    ReplyDelete
  15. low cost franchisesApril 23, 2009 at 4:22 AM

    Heya - Just looking through some blogengine.net blogs, seems to be a fairly nice platform, certainly better than blogger but still playing with the idea of wordpress. Any major plus points you have found over WP at all?

    Thanks

    Matthew

    ReplyDelete
  16. MySQL Connector/NET runs on any platform that supports the .NET framework. The .NET framework is primarily supported on recent versions of Microsoft Windows, and is supported on Linux through the Open Source Mono framework developed by Novell

    ReplyDelete
  17. great
    thank u sir
    love it all

    ReplyDelete
  18. Net Connector works invisibly to open the connection for you, ... When client caching is used, the client machine contains a local memory cache and a local ... It allows you to use three different types to connect to remote computer. ...

    ReplyDelete
  19. Cirurgia PlasticaMay 4, 2009 at 5:03 AM

    thanks for sharing this.its really useful.

    ReplyDelete
  20. Thats really something i didnt knew about. thanks

    ReplyDelete
  21. synthetic wicker furnitureMay 9, 2009 at 4:06 PM

    I am trying to install Acquia Drupal on Windows Server 2003 SP2. I am getting the message

    MySQL Connector/Net 5.2

    This product did not install successfully. Fatal error during installation.

    ReplyDelete
  22. i was looking for this for so many days.thank you!

    ReplyDelete
  23. On Windows, installation is supported either through a binary installation process or by downloading a Zip file with the Connector/NET components.

    ReplyDelete
  24. Replica LV HandbagsMay 20, 2009 at 3:25 PM

    I am trying to install Acquia Drupal on Windows Server 2003 SP2. I am getting the message

    MySQL Connector/Net 5.2

    This product did not install successfully. Fatal error during installation.

    ReplyDelete
  25. I'm not such an asp'er myself, but i find this very usefull. Thnaks!

    ReplyDelete
  26. Franchises for saleJune 9, 2009 at 3:16 AM

    It's interesting, the blog engine platform seems very variable in form. My design skills are not so good as my C coding though, I would be interested in seeing what additional skins you can get for it. Nice blog btw, best wishes for it and keep up the posts. :) Kind regards, Peter sims.

    ReplyDelete
  27. MySql.Web acctualy creates the database structure. So the reson we could not log in is because there were no users in the database.

    ReplyDelete
  28. This sample assumes you have a recent build of Connector/Net installed with Visual Studio integration enabled.

    ReplyDelete
  29. I read the articles and all the things here inside this blog .,I got many information that
    I really need .,Thanks for sharing.,

    ReplyDelete
  30. Its quite easy to understand through this tutorial..

    ReplyDelete
  31. Chanel Replica handbagsJune 30, 2009 at 11:49 PM

    good tutorial, make it easier to understand

    ReplyDelete
  32. Descriptive and clear post, easy to understand.


    <a href="http://www.bestweddingdresses.net">Wedding Dresses</a>

    ReplyDelete
  33. I am quite interesting in this topic hope you will elaborate more on it in future posts.

    ReplyDelete
  34. Hoodia SlankepillerJuly 18, 2009 at 8:12 PM

    Good post, I will mention it on my blog.. Cheers

    ReplyDelete
  35. запознанстваJuly 19, 2009 at 4:09 AM

    10x for the code :) Cheers!

    ReplyDelete
  36. Nice way of summing it up.. cheers

    ReplyDelete
  37. yeah! you gotta big mind to think!

    ReplyDelete
  38. You are so true on that! http://www.mrstiff.com

    ReplyDelete
  39. Louis vuitton handbagsSeptember 2, 2009 at 1:54 PM

    Give us many informations please?

    louis vuitton handbags

    ReplyDelete
  40. Now this is hghly recommeded post for me. I will surely email this to my friend.


    Regards

    Matthews

    ReplyDelete
  41. cheap aion online itemSeptember 3, 2009 at 10:13 PM

    I posted your blog to my facebook group


    Regards

    hanitz

    ReplyDelete
  42. I added your post to my college Report


    Regards

    saniths

    ReplyDelete
  43. that;s true...

    ReplyDelete
  44. that;s really useful

    ReplyDelete
  45. swimming pools designsSeptember 7, 2009 at 2:14 PM

    I digged this for more news from you.



    Regards and respect

    realman

    ReplyDelete
  46. I don’t think that all the articles will remain in the mind without missing a single word whichever we read. I think this article done a great job.What a best way to describe your view. Thanks for sharing with us. Really like your informative article.
    Hopefully we will get more interesting topic from you in future.

    ReplyDelete
  47. Louis vuitton handbagsSeptember 10, 2009 at 2:51 PM

    Thanks for sharing this cool stuffs.


    louis vuitton handbags

    ReplyDelete
  48. I try to connect MySQL via sqlDataSource. I use VWD 2008 Express, MySQL 5.0.87-community-nt and MySQL Connector/Net 6.2.2. In my web project, I not find MySQL Database in Data Source. Why! Help me!

    ReplyDelete
  49. Right now we don't integrate into Visual Web Developer. This is something we are trying to fix

    ReplyDelete
  50. I'm trying to use the newest version of the Connector/Net on GoDaddy. I've followed you instructions and also read other posts that link to this one. I'm still getting:

    Parser Error Message: Unable to initialize provider. Missing or incorrect schema.

    Does it matter which version of the provider I use as long as I put the correct MySql.Data.dll and MySql.Web.Dll in the App_Code and/or /Bin dirs?

    ReplyDelete
  51. No, it doesn't matter as long as the version you use serves your needs. Are you using the 'autogenerateschema=yes' option? Did you use the wizard to set it up?

    ReplyDelete