Access MySQL Server Database using ODBC data provider

If you are new to the ODBC data provider, please try Understanding ODBC .NET Data Provider.

 

In this article, I will show you how to access the MySQL server database using ODBC data provider. There are two ways to access the MySQL Server database using ODBC data providers. First, using the ODBC data source name and second bypassing connection string direct in the OdbcConnection object.

 

The following text is taken from Chapter 11: Working with ODBC .NET Data Provider of A Programmer's Guide to ADO.NET in C#. This chapter teaches you how to work with various data sources such as Oracle, MySQL, Excel, Access, Text, and Sybase using ODBC data provider.

 

Using MySQL Database

 

As I've been saying, working with different data sources is only a matter of changing the connection string. You can access a MySQL database either using a DSN or using the direct database name in the connection string. You can use a database name directly as shown in the following code:

string connectionString = @"Driver= MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;";

Or you can use an ODBC DSN, as you can see from the following code that I've used TestDSN DSN to connect to the data source:

OdbcConnection conn= new OdbcConnection("DSN=TestDSN");

To test this code, create a Windows application and add a DataGrid control to the form and write code listed in Listing 11-2 on the form load. As you can see in Listing this code is similar to the code you saw earlier. It creates a connection, a data adapter, fills the dataset from the data adapter, and sets the dataset's DefaultViewManager as the DataGrid control's DataSource property.

 

Listing 11-2. Accessing a MySQL database

private void Form1_Load(object sender, System.EventArgs e) {
    string connectionString = @ "Driver= MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;";
    OdbcConnection conn = new OdbcConnection(connectionString);
    conn.Open();
    OdbcDataAdapter da = new OdbcDataAdapter("SELECT CustomerID, ContactName, ContactTitle FROM Customers", conn);
    DataSet ds = new DataSet("Cust");
    da.Fill(ds, "Customers");
    dataGrid1.DataSource = ds.DefaultViewManager;
    conn.Close();
}

Note:

In this code, I have converted the Northwind Access database to a MySQL database. You make sure you replace your database name, table, user ID, and password.