MySQL probably is the most popular open source database server. To learn more about MySQL Database server, please visit MySQL Website.

MySQL library comes with some API's. You may download MySQL Library and its source code from MySQL Website. The MySQL OLE DB Provider is a thin wrapper of those MySQL library Version 5.0 API's. This provider is being implemented with ATL OLE DB Provider templates so that the provider itself has small footprint even though the provider does not have many dependencies. It only depends on MySQL library along with Windows OS runtime. You may use ADO or ADO.NET 2.0 to access data with MySQL OLE DB Provider

MySQL OLE DB Provider can be used to access MySQL database in your native code and .NET code as well. It has few dependency and is is very easy to use and very easy to deploy as well.

Most importantly, Cherry City Software MySQL OLE DB Provider is the best OLE DB Provider for MySQL database and it does NOT require you to open your source code.

The following are the features that this MySQL OLE DB Provider supports:

• Transaction
• Record scroll
• Parameters - input and output 
• Bookmarks
• Database Schema - tables and their columns
• Extended error information
• Multiple queries in one single execution*
• Transparency - all scripts are transparent to MySQL engine
• Command Preparation and Column Information on command object
• IColumnsRowset**
• Record Add/Delete/Update on the fly**
• MySQL as linked server in SQL Server 2005**

The current release was tested with MySQL 4.1.x and 5.0 and only support Western European charset. 

MySQL TIME is mapped to string. MySQL ENUM, SET and GEOMETRY data types are not supported.

*   - If the SQL statements have parameters, each statement must be executed seperately.
** - Not applicable if MySQL version is 4.1.x or older 

Provider=MySQL Provider; Data Source=MySQLServer; User ID =MyID; Password=MyPassword; Initial Catalog=DatabaseName;

Where

• MySQLServer is the name or IP address of MySQL database server. If MySQL Server is listening on a port other than the default port (3306), you may specify the port number after the server name with a comma delimitor. Here is an example: localhost,6306
• MyID/MyPassword are the credentials for the user to gain access to MySQL databases
• DatabaseName is the database name on the MySQL Server that you want to connect to

Using MySQL OLE DB Provider is simple if you have the knowledge of ADO or ADO.NET. The sample code is intended to demostrate how to use MySQL OLE DB Provider. You may find sample code below that demostrate how to use MySQL OLE DB Provider with ADO and ADO.NET. The sample will show you how to use the following features:

• Transaction
• Parameter input and output
• Database schema
• Record scroll
• Bookmark

		
'define connection used through out in the form
Private mConn As ADODB.Connection 

'//Synposis:
'//1. Create an ADO connection
'//2. Enable Read Data button if the table exists
Private Sub Form_Load()
    On Error GoTo trap
    Set mConn = New Connection
    'connect to MySQL server (localhost, port 3306), Database Name: test
    mConn.Open "Provider=OleMySql.MySqlSource.1; " _
        & " Data Source=localhost,3306; Initial Catalog=test", _
		"root", "myPassword"
    If DoesTableExist("AllFields") Then
        Me.Command3.Enabled = True
    End If
    Exit Sub
trap:
    MsgBox Err.Description
End Sub


'//Synposis:
'//1. Create a table named AllFields if it does not exist. If the table exits,
'//delete all records from the table.
'//2. Insert some records into the table
'//3. Update the records using parameters which makes it easy for
'//binary data manipulation
Private Sub Command1_Click()
    On Error GoTo trap
    On Error GoTo trap
    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = mConn
    
    'delete all data from the table if table exists
    If DoesTableExist("AllFields") Then 
        cmd.CommandText = "DELETE FROM AllFields;"
    Else  'create AllFields table if it does not exists
        cmd.CommandText = "CREATE TABLE AllFields" _
            & "(ID INTEGER PRIMARY KEY, Name VARCHAR(20), " _
            & "LongDoc MEDIUMTEXT, Image BLOB, Date5 DATE, " _
            & "datetime6 DATETIME, time6 TIME, " _
            & "timestamp7 TIMESTAMP, Year8 YEAR);"
    End If
    cmd.Execute
    cmd.CommandText = ""
     
    'insert data using multiple query statements
    Dim n As Long
    Dim I As Long
    For I = 0 To 300
        cmd.CommandText = cmd.CommandText _
            & "INSERT INTO AllFields (ID, Name, LongDoc, Date5, " _
            & "datetime6, time6, timestamp7, Year8) VALUES (" _
            & I & ", 'Sean D', 'this field can hold long long" _
            & " long long ... text string',  '2005-6-10', " _
            & "'2005-6-12 09:01:10', '6:05:00', '2005-6-15 1:22:11', 2005);"
    Next I
    mConn.BeginTrans
    cmd.Execute n
    mConn.CommitTrans
	 
    'update records with Parameters
    Dim BA(999) As Byte
    For I = 0 To 999
        BA(I) = (I + 1) Mod 256
    Next I
    Dim params(2) As Parameter
    Set params(0) = New ADODB.Parameter
    params(0).Type = adInteger  'integer
    params(0).Value = 1
    Set params(1) = New ADODB.Parameter
    params(1).Type = adBSTR      'string
    params(1).Value = "Sean Deng"
    Set params(2) = New ADODB.Parameter
    params(2).Type = adBinary
    params(2).Value = BA
    params(2).Size = 1000
    cmd.CommandText = "UPDATE AllFields SET Name=?, Image=? WHERE ID=?;"
    'add parameters according to commend text
    cmd.Parameters.Append params(1)
    cmd.Parameters.Append params(2)
    cmd.Parameters.Append params(0)
    cmd.Execute
     
    'using transaction
    params(0).Value = 2
    mConn.BeginTrans
    cmd.Execute
    mConn.RollbackTrans  'the Name is NOT updated when read back
    params(0).Value = 3
    mConn.BeginTrans
    cmd.Execute
    mConn.CommitTrans  'the Name is updated when read back
    
    Me.Command3.Enabled = True
    Set cmd = Nothing
    MsgBox "Done"
    Exit Sub
trap:
    MsgBox Err.Description    
End Sub


'//Synposis:
'//Create a recordset to get all data from the table and present the
'//data in a data grid.
'//To view the binary data read back from the database, you may open
'//open a watch window in the debug mode
Private Sub Command3_Click()

    On Error GoTo trap
    Dim BA As Variant
    Me.MSHFlexGrid1.Clear
    Dim rs As New ADODB.Recordset
    rs.Open "SELECT * FROM AllFields", mConn, adOpenStatic, adLockReadOnly
    If (rs.RecordCount > 3) Then
        rs.Move 3
        BA = rs("image").Value 'add BA to the watch window to view data
    End If
    
    Set Me.MSHFlexGrid1.Recordset = rs
    Exit Sub
trap:
    MsgBox Err.Description
    
End Sub


'//Synposis:
'//Clear all data from the data grid   
Private Sub Command2_Click()
	Me.MSHFlexGrid1.Clear
End Sub

'//Synposis:
'//Check if the specified table exists. Return true if it does. Return false
'//otherwise.  
Private Function DoesTableExist(ByVal sTable As String) As Boolean
    Dim rs As ADODB.Recordset
    Set rs = mConn.OpenSchema(adSchemaTables)
    While (Not rs.EOF)
        If UCase(rs("table_name")) = UCase(sTable) Then
            DoesTableExist = True
            rs.Close
            Set rs = Nothing
            Exit Function
        End If
        rs.MoveNext
    Wend
    rs.Close
    Set rs = Nothing
End Function


'//Synposis: Create a stored procedure and call it
'
Private Sub SPCall()
    On Error GoTo trap
    Dim cmd As ADODB.Command
    Set cmd = New Command
    cmd.ActiveConnection = mConn
    
    cmd.CommandText = "DROP PROCEDURE IF EXISTS MyProc;CREATE PROCEDURE MyProc" 
    cmd.CommandText = cmd.CommandText + "(param INT, OUT param1 INT)" _
                    & " BEGIN SET param1 = param*param; END;"
    cmd.Execute
    
    Dim param(1) As New ADODB.Parameter
    param(0).Direction = adParamInput
    param(0).Type = adInteger
    param(0).Value = -1000
    param(1).Direction = adParamOutput
    param(1).Type = adBSTR
    param(1).Value = ""
    param(1).Size = 40

    
    cmd.Parameters.Append param(0)
    cmd.Parameters.Append param(1)
    
    cmd.CommandText = "call MyProc(?, ?)" 
    cmd.CommandType = adCmdText 'must use text command type
    cmd.Execute
    
    Exit Sub
trap:
    MsgBox Err.Description


End Sub
				

   
//OLEDB connection used throughout this class
private System.Data.OleDb.OleDbConnection m_conn 
            = new System.Data.OleDb.OleDbConnection();   

//On form load, establish an connection to MySQL database
//When a connection is established, enable button to
//allow user populate data
private void Form1_Load(object sender, EventArgs e)
{
    try
    {
        //establish a db connection
        this.m_Read.Enabled = false;
        this.m_SP.Enabled = false;
        m_conn.ConnectionString = @"Provider=OleMySql.MySqlSource.1;"+
            " Data Source=localhost,3306; Initial Catalog=test;" + 
            " User ID=root; Password=MySQL";
        m_conn.Open();
        this.m_Create.Enabled = true;
        
        return;
    }
    catch (System.Data.OleDb.OleDbException ex)
    {
        MessageBox.Show(
            "Failed to open connection to MySQL database. Error Details:"
            + ex.Message);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    this.m_Create.Enabled = false;
    this.m_Read.Enabled = false;
}

/// <summary>
/// Find if a table has been created by checking the table schema.
/// </summary>
/// <param name="sTable">sTable is the table name</param>
/// <returns>return true if found, false otherwise</returns>
private bool TableExist(string sTable)
{
    System.Data.DataTable schema = 
        m_conn.GetOleDbSchemaTable(
        System.Data.OleDb.OleDbSchemaGuid.Tables, null);
    foreach (DataRow r in schema.Rows)
    {
        if (r.ItemArray.GetValue(2).ToString().ToLower() == 
            sTable.ToLower() &&
            r.ItemArray.GetValue(3).ToString().ToLower() == "table") 
            return true;

    }
    return false;
}

//Create a test table if it does not exist. If it does exist,
//delete data if any. Then insert data into the test table.
private void m_Create_Click(object sender, EventArgs e)
{
    try
    {
        //create command object
        System.Data.OleDb.OleDbCommand cmd
            = new System.Data.OleDb.OleDbCommand();
        cmd.Connection = m_conn;

        //query string to create table 
        cmd.CommandText = @"CREATE TABLE FieldTest" +
             "(ID INTEGER PRIMARY KEY, Name VARCHAR(20), " +
             "LongDoc MEDIUMTEXT, Image BLOB, Date5 DATE, " +
             "datetime6 DATETIME, time7 TIME, " +
             "timestamp8 TIMESTAMP)";

        //if test has been created, delete all data
        if (this.TableExist("FieldTest"))
            cmd.CommandText = "DELETE FROM FieldTest";
        cmd.ExecuteNonQuery();

        //insert data into FieldTest table
        cmd.CommandText = "INSERT INTO FieldTest " +
            "(ID, Name, LongDoc, Image, Date5, datetime6, Time7, TimeStamp8)"
            + " VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
        System.Data.OleDb.OleDbParameter[] oParams
            = new System.Data.OleDb.OleDbParameter[8];
        for (int i = 0; i < 8; i++)
        {
            oParams[i] = new System.Data.OleDb.OleDbParameter();
            cmd.Parameters.Add(oParams[i]);
        }

        
        //start transaction
        System.Data.OleDb.OleDbTransaction tran
            = m_conn.BeginTransaction(); 
        cmd.Transaction = tran;
        byte[] ba = new byte[1000]; 
        for (int i = 0; i < 260; i++)
        {
            oParams[0].Value = 100 + i;
            oParams[0].DbType = DbType.Int32;
            oParams[1].Value = "MyName " + i.ToString();
            oParams[2].Value = "A long long long long long long text";
            ba[i] = (byte)i;
            oParams[3].Value = ba;
            oParams[4].Value = System.DateTime.Now - new TimeSpan(i, 0, 0, 0);
            oParams[4].DbType = DbType.Date;
            oParams[5].Value = System.DateTime.Now
               - new TimeSpan(i, i % 24, i % 60, i % 60);
            oParams[5].DbType = DbType.DateTime;
            oParams[6].Value = new TimeSpan(i % 24, i % 60, i % 60);
            oParams[6].DbType = DbType.Time;
            oParams[7].Value = System.DateTime.Now;
            oParams[7].DbType = DbType.DateTime;
            cmd.ExecuteNonQuery();
        }
        //at last commit transaction
        tran.Commit();

        //test all time related fields
        DateTimeTest();
        
        MessageBox.Show("Done");
        m_Read.Enabled = true;
        m_SP.Enabled = true;
    }
    catch (System.Data.OleDb.OleDbException ex)
    {
        MessageBox.Show(ex.Message);
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    
}

 //Test date and time related data types
private void DateTimeTest()
{
    System.Data.OleDb.OleDbCommand cmd = 
        new System.Data.OleDb.OleDbCommand();
    cmd.Connection = m_conn;
    if (!this.TableExist("TimeTest"))
    {
        cmd.CommandText = 
            "CREATE TABLE TimeTest (date0 DATE, time1 TIME," + 
            " dateTime2 DATETIME, ts3 TIMESTAMP)";
    }
    else
    {
        cmd.CommandText = "delete from timeTest";
    }
    cmd.ExecuteNonQuery();

    System.Data.OleDb.OleDbParameter[] oParams = 
        new System.Data.OleDb.OleDbParameter[4];
    for (int i = 0; i < 4; i++)
    {
        oParams[i] = new System.Data.OleDb.OleDbParameter();
        oParams[i].Value = DateTime.Now;
        cmd.Parameters.Add(oParams[i]);
    }
    oParams[0].DbType = DbType.Date;
    oParams[1].Value = new TimeSpan(-80, 6, 2);
    oParams[1].DbType = DbType.Time;
    oParams[2].DbType = DbType.DateTime;
    oParams[3].DbType = DbType.DateTime;
    cmd.CommandText =
        "INSERT INTO TimeTest (date0, time1, dateTime2, ts3) values (?,?,?,?)";
    cmd.ExecuteNonQuery();

}
/// <summary>
/// Create an stored procedure and test input and output parameters
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void m_SP_Click(object sender, EventArgs e)
{
    try
    {
        //Create a stored procedure
        System.Data.OleDb.OleDbCommand cmd =
            new System.Data.OleDb.OleDbCommand();
        cmd.CommandText =
            "DROP PROCEDURE IF EXISTS MyProc;CREATE PROCEDURE MyProc" +
            " (param INT, OUT param1 INT, OUT Param2 DATE, OUT param3" + 
            " DATETIME, OUT param4 TIME) BEGIN SET param1 = param*param;" + 
            " SET param2='2000-10-10', param3='2001-12-31 01:02:03'," +
            " param4='9:8:7'; END;";
        cmd.Connection = m_conn;
        cmd.ExecuteNonQuery();

        //call stored procedure with input and output parameters
        cmd.CommandText = "call MyProc(?, ?, ?, ?, ?)";

        System.Data.OleDb.OleDbParameter[] oParams = 
            new System.Data.OleDb.OleDbParameter[5];
        for (int i = 0; i < 5; i++)
        {
            oParams[i] = new System.Data.OleDb.OleDbParameter();
            oParams[i].Direction = ParameterDirection.Output;
            cmd.Parameters.Add(oParams[i]);
        }

        //setup input parameters
        oParams[0].Value = 10;
        oParams[0].DbType = DbType.Int32;
        oParams[0].Direction = ParameterDirection.Input;

        //setup output parameter types
        oParams[1].DbType = DbType.Int32;
        oParams[2].DbType = DbType.Date;
        oParams[3].DbType = DbType.DateTime;
        oParams[4].DbType  = DbType.Time;

        cmd.ExecuteNonQuery();
        
        //run debug to view output values
        System.Diagnostics.Debug.WriteLine(oParams[1].Value.ToString());
        System.Diagnostics.Debug.WriteLine(oParams[2].Value.ToString());
        System.Diagnostics.Debug.WriteLine(oParams[3].Value.ToString());
        System.Diagnostics.Debug.WriteLine(oParams[4].Value.ToString());

        MessageBox.Show("Done");
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    
}

/// <summary>
/// read data back from a FieldTest table and present it on a data grid
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void m_Read_Click(object sender, EventArgs e)
{
    try
    {

        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
        cmd.Connection = m_conn;
        cmd.CommandText = "select * from FieldTest";
        System.Data.OleDb.OleDbDataAdapter da =
            new System.Data.OleDb.OleDbDataAdapter();
        da.SelectCommand = cmd;
        DataTable dt = new DataTable();
        da.Fill(dt);
        HexDecBinding.Util.SetupGVColumns(m_DGVResult, dt);
        this.m_DGVResult.DataSource = dt;
    }
    catch (System.Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    
}
                   
                

Using MySQL OLE DB Provider with Visual Studio 2005 IDE is very handy. You can readily add MySQL OLE DB Provider into Visual Studio 2005 IDE by adding Data connections via the OLE DB provider.

Refer to Using MySQL Provider to transfer MySQL data into SQL Server.

MySQL OLE DB Provider must be activated before you can use it. To activate it, you must download the provider and get your passcode from the Activator included in the package. Click here to get activation code.

Update History:

Date	Changes
2009-01-26	In beta release, fixed a defect that misinterprets empty strings as db nulls
2009-01-04	In the beta release, fixed an issue in Linked Server after Microsoft released SQL Server 2005 SP2; also fixed an SSIS issue.
2008-05-18	Implemented IRowsetChange and IColumnsRowset interfaces so that the provider can support Linked Server and .NET dynamic Insert/Delete/Update commands. Features avialable in Beta release.
2008-04-10	Fixed a bookmark issue introduced in the previous beta release on 3/15/2008
2008-03-15	Fixed a parameter binding issue with non-English characters. Added backward compatibility for table schema. Fixed Column schema information for MySQL 5.+
2007-01-20	Unlocked all features as long as it is activated; Fixed an activation defect; Improved performances.
2006-10-04	Implemented schema rowsets for catalogs and views, ICommandPrepare interface, GetColumnInfo on command, and IDBInfo interface as well.
2006-09-16	Integrated with MySQL library 5.0.24a.
2006-05-08	Fixed an issue on MySQL datetime to MS datetime conversion.
2006-03-05	Fixed a critical activation bug.
2006-01-02	Upgraded to support stored procedure calls with in/out parameters.
2005-11-27	Upgraded to support ADO.NET 2.0.
2005-11-20	Added default port number if missing from connection string.
2005-07-07	Intial Release.

MySQL OLE DB Provider (Beta)
MySQL OLE DB Provider
Provider VB Test source
C# Sample source code

This whole post here including the downloads is provided 'as-is', without any express or implied warranty. In no event will the author be held liable for any damages arising from the use of this software.

To send me feedback, click here