MySQL Data Provider
There are two ADO.NET providers in Mono
for a MySQL database:
- ByteFX.Data.MySQLClient
- Written in 100% C#
- Does not require a client library
- Works on Mono and Microsoft .NET
- Requires at least Mono 0.18 and MySQLNet 0.65 for it to work on Mono
- Works in the SQL# command-line and GTK# GUI version
- Mono.Data.MySql (deprecated)
- Deprecated in favor of ByteFX.Data.MySQLClient
- Written in C# and uses
the MySQL C Client Library
- Exists in namespace Mono.Data.MySql and assembly Mono.Data.MySql
- Works on Windows and Linux via the MySQL client shared library
(libmySQL.dll on Windows and libmysqlclient.so on Linux).
- Started by Daniel Morgan using
C# Bindings to MySQL from Brad Merill
- Bugs with Mono or the data provider should be reported
in Mono's Bugzilla here. If you
do not have Bugzilla user account, it is free
and easy to
create one here.
Exploring MySQL
in the Microsoft .NET Environment is an article
by Mr. Venu who is a MySQL AB developer.
Testing for Mono's Mono.Data.MySql and ByteFX's ByteFX.Data.MySqlClient is below.
Current Status
Current Status of the MySQL providers:
- ByteFX.Data.MySqlClient
- Build and Runs on Microsoft .NET and Mono
- Works with SQL# (command-line and GTK# GUI versions)
- MySQLCommandBuilder now implemented
- Transaction support now implemented (not all table types support this)
- GetSchemaTable fixed to not use xsd (for Mono)
- Driver is now Mono-compatible
- TIME data type now supported
- More work to improve Timestamp data type handling
- Changed signatures of all classes to match corresponding SqlClient classes
- Protocol compression using SharpZipLib
- Named pipes on Windows now working properly
- Work done to improve Timestamp data type handling
- Implemented IEnumerable on DataReader so DataGrid would work
- Speed increased dramatically by removing bugging network sync code
- Driver no longer buffers rows of data (more ADO.Net compliant)
- Conversion bugs related to TIMESTAMP and DATETIME fields fixed
- Mono.Data.MySql (deprecated)
- Currently, only works with version 3.23.x of MySQL
- can connect
- can execute non-queries via ExecuteNonQuery()
- can execute aggregates via ExecuteScalar() and retrieve the
single row/single column result
- can execute queries and retrieve results using a data reader.
- a schema DataTable has been partially
implemented which is returned from GetSchemaTable() in MySqlDataReader.
- a DataTable in a DataSet can be filled via a MySqlDataAdapter
- The shared client libraries
between windows version and linux are different: windows has libmySQL.dll
while linux has libmysqlclient.so. This is handled by the
file etc/mono/config which is mapped by the mono runtime in knowing
which native shared library to load. In cvs, this file is mono/config.in and
can be modified with a text editor.
- Works in the SQL# command-line and GTK# GUI version
Action plan
The current plan for the MySQL data providers:
- ByteFX.Data.MySqlClient
- Testing and fixes
- Implement missing features
- Only fixes for bugs to build and run MySQLClient on Mono
will be accepted in mono-cvs. Most bugs and any new features will
go into sourceforge cvs. Anytime there is a release of MySQLClient,
the source code will be copied from sourceforge cvs to mono-cvs
- Releases of MySQLClient are determined by Reggie Burnett and releases
of Mono are determined by Miguel de Icaza
- Implement any missing features or fix any bugs in Mono to get new
features all of MySQLClient to work on Mono
- Mono.Data.MySql (deprecated)
- Testing and bug fixes
- Mono.Data.MySql is deprecated and therefore maybe removed
at a later date. It will stay in Mono for now because other
programs maybe using it now.
Testing for MySQLNet provider (ByteFX.Data.MySQLClient)
- Have access to a MySQL database or download it from
- MySQLNet can be gotten from here and the
binary assembly ByteFX.Data.dll needs to be installed
in the same place as the mono class libraries.
- MySQLNet requires SharpZipLib which is
a Zip Library written in 100% C#. This is used for compression/decompression of data
sent/received over the network. The SharpZipLib binary assembly SharpZipLib.dll should
be installed in the same place as the mono class libraries.
- Has a ConnectionString format:
"Server=hostname;" +
"Database=database;" +
"User ID=username;" +
"Password=password"
- C# Example:
using System;
using System.Data;
using ByteFX.Data.MySqlClient;
public class Test
{
public static void Main(string[] args)
{
string connectionString =
"Server=localhost;" +
"Database=test;" +
"User ID=myuserid;" +
"Password=mypassword;";
IDbConnection dbcon;
dbcon = new MySqlConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
// requires a table to be created named employee
// with columns firstname and lastname
// such as,
// CREATE TABLE employee (
// firstname varchar(32),
// lastname varchar(32));
string sql =
"SELECT firstname, lastname " +
"FROM employee";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read()) {
string FirstName = (string) reader["firstname"];
string LastName = (string) reader["lastname"];
Console.WriteLine("Name: " +
FirstName + " " + LastName);
}
// clean up
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
}
}
- Building C# Example:
- Save the example to a file, such as, TestExample.cs
- Build on Linux:
mcs TestExample.cs -r System.Data.dll \
-r ByteFX.Data.dll
- Build on Windows via Cygwin:
mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
TestExample.cs \
-lib:C:/cygwin/home/MyHome/mono/install/lib \
-r System.Data.dll -r ByteFX.Data.dll
- Running the Example:
mono TestExample.exe
Testing for Mono's MySQL provider (Mono.Data.MySql)
- Have access to a MySQL database or download it from
- Take a look at MySqlTest.cs in mcs/class/Mono.Data.MySql/Test
- On Linux, you may need to make a symbolic link from libmySQL.dll to libmysqlclient.dll
- Has a ConnectionString format:
"Server=hostname;Database=database;User ID=username;Password=password"
(or)
"Host=hostname;Dbname=database;User=username;Passwd=password"
- C# Example:
using System;
using System.Data;
using Mono.Data.MySql;
public class Test
{
public static void Main(string[] args)
{
string connectionString =
"Server=localhost;" +
"Database=test;" +
"User ID=myuserid;" +
"Password=mypassword;";
IDbConnection dbcon;
dbcon = new MySqlConnection(connectionString);
dbcon.Open();
IDbCommand dbcmd = dbcon.CreateCommand();
// requires a table to be created named employee
// with columns firstname and lastname
// such as,
// CREATE TABLE employee (
// firstname varchar(32),
// lastname varchar(32));
string sql =
"SELECT firstname, lastname " +
"FROM employee";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read()) {
string FirstName = (string) reader["firstname"];
string LastName = (string) reader["lastname"];
Console.WriteLine("Name: " +
FirstName + " " + LastName);
}
// clean up
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
}
}
- Building C# Example:
- Save the example to a file, such as, TestExample.cs
- Build on Linux:
mcs TestExample.cs \
-r System.Data.dll \
-r Mono.Data.MySql.dll
- Build on Windows via Cygwin:
mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
TestExample.cs \
-lib:C:/cygwin/home/MyHome/mono/install/lib \
-r System.Data.dll \
-r Mono.Data.MySql.dll
- Running the Example:
mono TestExample.exe
|
