Connecting to a MySQL database with C# express via ODBC

Question

Hi,

I need to connect to a MySQL database via C# express 2008. I think I got the code right apart from the connection string. I obtained this code from a forum but the connection string was for SQLExpress 2005. Can someone please help me on how can I fix this? Here is the code with the SQL Express connection string:

//string connectionString = "Driver={SQL Native Client}; Server=localhost\\sqlexpress;" + "Database=oshahsdb;Trusted_Connection=yes;";

using (OdbcConnection odbcCon = new OdbcConnection(connectionString))
using (OdbcCommand odbcCom = new OdbcCommand("Select * FROM Product", odbcCon))
using (OdbcDataAdapter odbcDA = new OdbcDataAdapter(odbcCom))
using (DataSet ds = new DataSet())
{
   odbcCon.Open();
   odbcDA.Fill(ds);

   this.dataGridView1.DataSource = ds.Tables[0];
}

I also need to add a username and password to the connection string.

Any help would be greatly appreciated.

Many thanks in advance.

Chris

Answer 1

http://www.connectionstrings.com is a great resource for connecting to all sorts of databases.

Answer 2

Check out the MySQL Connector for .NET below is an example found here.

MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString;

myConnectionString = "server=127.0.0.1;uid=root;pwd=12345;database=test;";

try
{
    conn = new MySql.Data.MySqlClient.MySqlConnection();
    conn.ConnectionString = myConnectionString;
    conn.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
    MessageBox.Show(ex.Message);
}

Answer 3

Do you really need to connect using ODBC?

I'd recommend using MySQL Connector/Net instead, if you're able to. It's a fully managed provider and is taylor-made for .NET, whereas the ODBC driver is more general-purpose.

(Of course, you'll still need a suitable connection string, regardless of how you choose to connect.)