I’m a bit behind with my "5 things I’ve learned" series, and for that I apologize. I’ve been a bit busy at a client site. Will get back to that a.s.a.p.
What I want to chat to you about today is MySQL and how well it works with .Net(so far, it works well, touch wood)…
First, how did I come about using MySQL instead of MS SQL Server? Initially we’ve planned to use SQL Express, but we’ve quickly realized that with the amount of data, that will be moving through the system will probably be more than what SQL Express can handle(4 GB).
Sooo…I figured the client would have to go the full MS SQL Server direction, but working on a contract and already costing the client, I did not want to burden them with any additional cost if it could be avoided.
Enter MySQL.
I’ve always known about it, but never really had a good look at it, all I knew was that it was free, and for the moment that was all that mattered.
There is 2 what you could call editions of MySQL, one is the Enterprise edition and the other the Community edition, the latter is the one that is free. There are 3 downloads with a combined size of about 80MB. After installing that I read the Migrating From Microsoft SQL Server to MySQL pdf document and was delightfully surprised to see that the SQL syntax between MySQL and MS SQL Server is not all that much different. Nothing you could not get used to within a week or so of actively writing some SQL.
Ok, that done, I wanted to start storing and retrieving my data from the MySQL db by using VB.Net. One way is ODBC, but I did not really want to go the ODBC route, considering any performance and ODBC issues that I could possibly encounter. The other, and the option I used, is to use the MySQL Connector for .Net.
It’s a quick download, and you then add it to your project references. You then have access to the following:
MySqlCommand
MySqlConnection
MySqlDataAdapter
MySqlDataReader
and more…
I set forth to write a quick little app that use a datareader to pull data from a table, using normal .Net techniques, and it worked brilliantly. So from a .Net point of view I did not need to learn any new objects, Great!
The tools you get to manage your MySQL is not too shabby, the Query Browser(The MySQL equivalent of Query Analyzer), is not the friendliest app but serves it’s purpose. The MySQL Administrator does a good job at the administrave part, and is easy to use.
All and all an enjoyable experience and I would recommend MySQL to any .Net developer that needs a decent datastore for their application.
Until next time, keep coding!