Using MySQL with ASP.Net
One year ago
For the first six months of 2009 I have started a European Union funded project to create a website for local livestock farmers to publish information relevant to them. It's quite a simple website with a few interactive features and there will be a database behind the scenes storing all the information on the site.
My programming language of choice is ASP.Net C# and naturally I would choose Microsoft SQL Server as the backend database, which we had to buy. When I got a quote for a single CPU license, I was amazed at the price... just over £5,000! Obviously I can't afford that for my own projects and SQL Express has some limitations I cannot live with.
I needed an inexpensive or free alternative solution for my new project which was flexible enough for our short and long term needs. In short, we decided to use MySQL and although there are "better" open source databases, MySQL is more than capable and is well supported by the community.
I've never used MySQL with ASP.Net, only with the LAMP stack, and so I had to research how to access the database from my code. Conveniently, the MySQL community have written an ADO.Net driver for the .Net framework. Simple download and add the DLL to your project and a new MySql.Data.MySqlClient namespace will become available.
The good news is they have kept all the same method and property names, all that has changed is the class names, making converting your existing code easier. So lets get down to some examples... Here's how to execute a select statement using the built-in SqlClient class:
String sSQL = "Select Top 1 * From Posts ";
sSQL += "Where PO_Date > GetDate()-7 ";
SqlCommand cSQL = new SqlCommand(sSQL);
SqlConnection cConnection = _
new SqlConnection("server=local;database=Blog;uid=X;password=X");
cSQL.Connection = cConnection;
SqlDataAdapter oDataAdapter = new SqlDataAdapter(cSQL);
DataTable oDataTable = new DataTable();
oDataAdapter.Fill(oDataTable);
And here's how to do the same statement using the MySqlClient namespace.
I have highlighted the code that has changed.
String sSQL = "Select * From Posts ";
sSQL += "Where PO_Date > Now()-7 ";
sSQL += "Limit 1; ";
MySqlCommand cSQL = new MySqlCommand(sSQL);
MySqlConnection cConnection = _
new MySqlConnection("server=local;database=Blog;uid=X;password=X");
cSQL.Connection = cConnection;
MySqlDataAdapter oDataAdapter = new MySqlDataAdapter(cSQL);
DataTable oDataTable = new DataTable();
oDataAdapter.Fill(oDataTable);
As you can see, the class names have changed and some parts of the SQL statement have changed also to take into account some of the differences between the databases. To be honest, the differences between the database languages will take you the most time to learn. The changes in class names can be sorted with a quick find and replace.
So go away and get MySQL installed on a Linux or Windows box and start enjoying an open source database... It really is that simple and to help you in other areas not covered in this post, here are some useful resources.
- MySQL Gotchas
- Differences between MySQL and MS SQL
- Official MySQL Online Reference
- HeidiSQL, a MySQL GUI Client for Windows
Previous Comments
-
Mike Griffiths Monday 26 January 2009 1:48 PM
-
Stephen Hill Monday 26 January 2009 2:49 PM
-
Robert Hanlon Tuesday 27 January 2009 7:21 PM
-
-
trendbender Wednesday 28 January 2009 10:57 AM
-
Ivan Dimitrov Thursday 05 February 2009 10:02 AM
-
Ian Tebbutt Thursday 05 February 2009 10:13 PM
-
How to play the game of craps Monday 29 June 2009 11:07 AM