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.
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 messageIf 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 QueriesThis 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
BTW, if you're looking for .NET web hosts that support MySQL, check out www.webhost.ie
© Copyright 2018 Tim Mackey