Tuesday, 16 March 2004
Howto: use MySql with .Net
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.
- Download & Install MySQL 4.0 (16 Mb)
- Download & Install ODBC.NET Data Provider (800 k)
- Download & Install MyODBC 2.5 (1.5 Mb)
Note: the newer versions do not work with .NET at time of writing
- Optional: Download & Install DBManager, GUI for MySQL tables, highly recommended (3 Mb)
- 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'
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";
DataSet ds = new DataSet();
OdbcDataAdapter dba = new OdbcDataAdapter();
dba.SelectCommand = command;
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
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
Tuesday, 16 March 2004 23:27:39 (GMT Standard Time, UTC+00:00) Database