What is a CData ADO.NET Provider for MySQL Driver?

The CData ADO.NET Provider for MySQL is a powerful tool that allows developers to connect .NET applications to live MySQL data. The ADO.NET Provider allows users to use MySQL Data Provider objects to connect and access data just as you would access any traditional database. You will be able to use the MySQL Data Provider through Visual Studio Server Explorer, in code through familiar classes, and in data controls like DataGridView, GridView, DataSet, etc.

This article reviews the core features of The CData MySQL ADO.NET Provider. It explores how to use the ADO.NET Provider to connect to MySQL, query data from a specific table, update data in MySQL, and execute a stored procedure.

Core features

Comprehensive support for CRUD (Create, Read, Update, and Delete) connectivity to MySQL Server 5.0 or later.
Supports ADO.NET Entity Framework (EF 5 & 6), LINQ to Datasets, etc.
Support for 32-bit and 64-bit operating systems.
Supports .NET Framework 4.0+ and .NET Standard 2.0 (.NET Core 2.1+, .NET 6.0).
DataBind to MySQL using standard Visual Studio wizards.

Connecting to MySQL

The CData ADO.NET Provider for MySQL supports authentication to MySQL via the following types of authentication schemes:

Standard (Password)
NT Lan Manager (NTLM)
LDAP
SSL
SSH
Azure
AWS

The following section reviews an example of using the Password authentication scheme to authenticate your connection to MySQL:


using (MySQLConnection connection = new MySQLConnection("User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;"))
{
connection.Open();
}

VB.NET


Using connection As New MySQLConnection("User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;")
    connection.Open
End Using

Read more about the CData MySQL ADO.NET Provider authentication schemes and connection properties on the CData Documentation page: CData MySQL ADO.NET Provider Documentation

Using the ADO.NET Provider

READ

The following code examples connect to MySQL and run a SELECT SQL query to return ShipName and ShipCity columns of the Orders table:


string connectionString = "User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;";

using (MySQLConnection connection = new MySQLConnection(connectionString)) {
    MySQLDataAdapter dataAdapter = new MySQLDataAdapter(
    "SELECT ShipName, ShipCity FROM `sakila`.Orders", connection);

    DataTable table = new DataTable();
    dataAdapter.Fill(table);

    Console.WriteLine("Contents of Orders.");

    foreach (DataRow row in table.Rows) {
        Console.WriteLine("{0}: {1}", row["ShipName"], row["ShipCity"]);
    }
}

VB.NET


Dim connectionString As String = "User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;"

Using connection As New MySQLConnection(connectionString)
    Dim dataAdapter As New MySQLDataAdapter("SELECT ShipName, ShipCity FROM `sakila`.Orders", connection)

    Dim table As New DataTable()
    dataAdapter.Fill(table)

    Console.WriteLine("Contents of Orders.")

    For Each row As DataRow In table.Rows
        Console.WriteLine("{0}: {1}", row("ShipName"), row("ShipCity"))
    Next
End Using

WRITE

The following code examples connect to MySQL and run an UPDATE SQL Statement to update the ShipCity of one of the `sakila`.Orders entries:


string connectionString = "User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;";

using (MySQLConnection connection = new MySQLConnection(connectionString)) {
    MySQLDataAdapter dataAdapter = new MySQLDataAdapter(
        "SELECT ShipName, ShipCity FROM `sakila`.Orders", connection);

    dataAdapter.UpdateCommand = new MySQLCommand(
        "UPDATE `sakila`.Orders SET ShipCity = @ShipCity " + 
        "WHERE Id = @Id", connection);

    dataAdapter.UpdateCommand.Parameters.Add(new MySQLParameter("@ShipCity", "ShipCity", DbType.String ));
    dataAdapter.UpdateCommand.Parameters.Add(new MySQLParameter("@Id", "Id", DbType.String ));
    dataAdapter.UpdateCommand.Parameters[1].SourceVersion = DataRowVersion.Original;

    DataTable table = new DataTable();
    dataAdapter.Fill(table);

    DataRow firstrow = table.Rows[0];
    firstrow["ShipCity"] = "Raleigh";

    dataAdapter.Update(table);

    Console.WriteLine("Rows after update.");

    foreach (DataRow row in table.Rows) {
        Console.WriteLine("{0}: {1}", row["ShipName"], row["ShipCity"]);
    }
}

VB.NET


Dim connectionString As String = "User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;"

Using connection As New MySQLConnection(connectionString)
    Dim dataAdapter As New MySQLDataAdapter(
        "SELECT ShipName, ShipCity FROM `sakila`.Orders", connection)

    dataAdapter.UpdateCommand = New MySQLCommand(
        "UPDATE `sakila`.Orders SET ShipCity = @ShipCity " +
        "WHERE Id = @Id", connection)

    dataAdapter.UpdateCommand.Parameters.Add(new MySQLParameter("@ShipCity", "ShipCity" DbType.String ))
    dataAdapter.UpdateCommand.Parameters.Add(new MySQLParameter("@Id", "Id", DbType.String))
    dataAdapter.UpdateCommand.Parameters(1).SourceVersion = DataRowVersion.Original

    Dim table As New DataTable()
    dataAdapter.Fill(table)

    Dim firstrow As DataRow = table.Rows(0)
    firstrow("ShipCity") = "Raleigh"

    dataAdapter.Update(table)

    Console.WriteLine("Rows after update.")

    For Each row As DataRow In table.Rows
        Console.WriteLine("{0}: {1}", row("ShipName"), row("ShipCity"))
    Next
End Using

Stored procedure

The following code examples execute a Stored Procedure from your MySQL connection:


string connectionString = "User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;";

using (MySQLConnection connection = new MySQLConnection(connectionString)) {
    MySQLCommand cmd = new MySQLCommand("SelectEntries", connection);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new MySQLParameter("@ObjectName", "Account"));
    // Add other parameters as needed ...

    MySQLDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read()) {
        for (int i = 0; i < rdr.FieldCount; i++) {
            Console.WriteLine(rdr.GetName(i) + " --> " + rdr[i]);
        }
        Console.WriteLine();
    }
}

VB.NET


Dim connectionString As String = "User=myUser;Password=myPassword;Database=NorthWind;Server=myServer;Port=3306;"

Using connection As New MySQLConnection(connectionString)
    Dim cmd As New MySQLCommand("SelectEntries", connection)
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add(New MySQLParameter("@ObjectName", "Account"))
    ' Add other parameters as needed ...

    Dim rdr As MySQLDataReader = cmd.ExecuteReader()
    While rdr.Read()
        For i As Integer = 0 To rdr.FieldCount - 1
            Console.WriteLine(rdr.GetName(i) + " --> " + rdr(i))
        Next
        Console.WriteLine()
    End While
End Using

Data type mapping

Most MySQL data types map directly to the same names in the CData ADO.NET Provider. A few exceptions are listed in the table below:

MySQL Schema	CData Schema
TIMESTAMP()	datetime
DOUBLE, REAL	float
FLOAT	real
BOOLEAN	tinyint
TEXT()	tinytext

Unlock seamless MySQL connectivity in your .NET applications

Are you looking to unlock the full potential of your MySQL data? The CData MySQL ADO.NET Provider can seamlessly integrate your .NET applications with your MySQL data. With the CData ADO.NET Provider for MySQL Community License, you get free-forever libraries to access your data in personal .NET projects, all through familiar SQL. Request a license and start building MySQL-connected apps today!

Feedback