Moving from SQL Server to MySQL for Web Development

I am a Web Developer who is quite comfortable using Visual Studio 2005 and 2008 to build web sites based on SQL Server. Today, I am trying to transfer this knowledge to creating sites based on three new technologies:

• Visual Studio 2010 Web developer
• MVC 3 for developing sites that are partially or completely based on the Model / View / Controller design pattern. This seems to be the wave of the future and also seems to be the basis for using DHTML5 with AJAX. Hey – maybe this isn’t necessary, but it looks to me like the most probably future direction for Microsoft based development.
• MySQL. Narrative 1 databases are currently based on MySQL and this will continue. This last transition is the one I will talk about here.

Connecting to MySQL

In my experience, connecting the plumbing is always the hardest part of any new technology and connecting to MySQL from Visual Studio is no exception. Here is what I think is necessary.

1. You need something called the MySQL Connector for .Net which I got from http://dev.mysql.com/downloads/connector/net/. Somewhere I saw that I was supposed to uninstall previous versions of this. I did so and that made my Narrative 1 workbooks stop connecting to the MySQL databases. Hopefully, reinstalling Narrative 1 will correct this,but I have not yet tried this.
2. Once you have successfully installed MySQL Connector, open the VS 2010 project and add a reference to this. Right click the References section under the Project in the Solution Explorer, Select the .NET tab, and sort by Component Name. Scroll down and click on MySQL.Data. Then click OK. For me, this component was on my system at

For easier access, I added the following to the top of the file:

The first step in using the program to connect to a MySQL database is setting up the connection. Here is the the connect string format that I ended up using:

At this point, one would think that all our problems would be solved; if you can do it in SQL Server, you should be able to do it in MySQL, right?  WRONG! It ain’t that easy.

Accessing a table seems to work as one would expect. You set the Command Text property to the name of the table, and set the Command Type to Table and you get back the entire table. No Problem.

Invoking a normal SQL Command also seems to not be a problem. Set the Command Text property to a string holding the command, set the Command Type to Text and it runs the the stored procedure.  No Problem.

Stored Procedures, however, are a problem. Set the Command Text Property to a string holding the procedure name, set the Command Type property to Stored Procedure and Execute Reader says that it cannot find the Stored Procedure. I have tried this with and without “`” characters, with and without the database name in front, with and without the “( )” characters surrounding it all to no avail. I suspect that there is some stupid incantation that I am missing, but I have no idea what that would be.

I did find a workaround. Instead of setting the Command Type to Stored Procedure, I set it to Text. I was successful when I set:

The next issue is the passing of parameters. One might think that if you are going to pass the procedure name as text, you might just as well pass the parameters as text as well. That sounds find to me. But there are two reasons that you might not want to do this.

For me, the first reason is that I have code that has to work with both SQL Server and MySQL. It is all set up to loop through the parameters and add them to the parameter list. I want to be able to structure the code the same way in MySQL.

The way you do this is to add the parameter names to the Command Text, preceding the parameter name with a “@”. Note that MySQL procedures would not take arguments starting with “@”; I used “_” instead. When passing the parameters, the “@” character works fine. Because MySQL does not accept default values, it seems that it cares only about the order of the parameters and not the names. This means that all parameters must be passed; you can’t default them to null and skip them as I often do (or did) in SQL Server. Also, I discovered that if one wants to pass a null value to a parameter, one should use “dbNull.Value”. Of course, if you are putting this into a Command Text string, you just put in “null” as shown in the previous code snip.

Here is some sample code that shows how to work with parameters.

 

Note the Prepare statement above. This causes a sort of compile to occur. In the code above, this would improve performance if the command is to be executed multiple times.

All this sure sounds simple enough. Why did it take me half the day to figure it all out??? I wish it had been in the manuals. But that is what this blog is for, isn’t it?