Tim Mackey's Weblog

MySQL is a great alternative to SQL Server because it is open source and nearly has the performance to rival SQL Server. Applications using Microsoft Access databases can suffer seriously if the transaction load is anything above minimal. This article is my collection of instructions and tips for getting up and running with MySQL and .NET.

Downloads

1. Download & Install MySQL 4.0 (16 Mb)
   
   
2. Download & Install ODBC.NET Data Provider (800 k)
   
   
3. Download & Install MyODBC 2.5 (1.5 Mb)
   Note: the newer versions do not work with .NET at time of writing
   
   
4. Optional: Download & Install DBManager, GUI for MySQL tables, highly recommended (3 Mb)

Testing

• Make sure the MySQL service is running in Windows.
  
  
• Load up the WinMySQLAdmin.exe tool in the bin folder of your MySQL install. This program controls the MySQL server. Feel free to customise any settings you wish, e.g. change the Server Name, Port Number, username & password etc.
  
  
• Load up DBManager and connect to the Server. The default user is root with a blank password. You can create your own users or keep using the root. Create a test database and then right-click the 'Tables' node under the database you just created. Use the 'Table Editor' to define the fields for the table. Click the 'Save' icon to save your changes. Users from MS Access backgrounds may want to read below.

To connect to the database through .NET code:
Add a reference to Microsoft.Data.Odbc.dll, this should be in 'C:\Program Files\Microsoft.NET\Odbc.Net'

using Microsoft.Data.Odbc;
...
stringconnString="'driver={MySql};uid=root;pwd=;server=127.0.0.1;database=test;OPTION=17923";
string sql = "select * from table1 where ShortName = ?";

// declare an OdbcCommand object with a new OdbcConnection
OdbcCommand command = new OdbcCommand(sql, new OdbcConnection(connString));
// add a paramter to the command corresponding to the ? in the sql statement
OdbcParameter dbParam = new OdbcParameter('ShortName", OdbcType.VarChar, 50);
dbParam.Value = "bob";
command.Parameters.Add(dbParam);

DataSet ds = new DataSet();
OdbcDataAdapter dba = new OdbcDataAdapter();
dba.SelectCommand = command;
try
{
  command.Connection.Open();
  dba.Fill(ds);
}
catch(Exception ex)
{
  throw ex;
}
finally
{
  command.Connection.Close();
}

Unfortunately, MS never provided OdbcDataAdapter & OdbcCommand GUI support for Visual Studio so you have to do all that manually, as in the code above. However, there are commercial components that solve this.

The 'System Error' ODBC message
If you get a 'System Error' message thrown in your code using MySQL, with no useful information, you need to catch the line of code causing the error and throw the exception explicitly (i have no idea why, but MS have a kb article about it).

Note on DBManager Queries
This version of MySQL doesn't support Stored Procedures, but you can still save queries to the database. Click the 'Create new query' button in DBManager and enter the SQL you want. There is a query designer but the intellisense doesn't work well and it can be annoying to use. Save the query and it appears as an extra tab after 'DataSheet' in the lower right corner. When you close DBManager, next time you open it, that tab won't be there and it will look like the query was only temporary. They are actually stored permanently, and accessible via the 'Tools' tab on the lower right of the screen.

Notes on the MySQL field types for anyone from an MS Access background

• MySQL uses the single apostrophe character for quoting values, e.g. select * from table where name = 'bob'. the double quote character causes a syntax error.
  
• there is no 'bool' field type as such, use 'TinyInt' and change the size to 1 bit, then use 1 to represent true and 0 for false.
  
• the varchar field type is limited to 255 characters, in MS Access you would use a Memo to go beyond this. MySQL has the tinytext, mediumtext and longtext field types to support larger max characters.
  
• to force the input to match a list of values, e.g. Categories: Animal / Vegetable / Mineral, use the 'Set' field type, with comma separated values in single quotes: 'Animal', 'Vegetable', 'Mineral'
  
• the 'Now()' function is supported in MySQL as a default value for a DateTime or Date field, but when you enter this in DBManager, the value appears as 0000-00-00 00:00:00 in the Table Editor, this seems unusual but the function works as expected.

BTW, if you're looking for .NET web hosts that support MySQL, check out www.webhost.ie