C

From Hashmysql
Jump to: navigation, search

There are actually several ways to connect to MySql from C#. The two discussed in this article are through the MySql Connector/Net and through ODBC.

Connecting through Connector/Net

The Connector/Net way of connecting is described in detail here. A quick rundown follows:

 using MySql.Data.MySqlClient;
 
 MySqlConnection conn;
 string myConnectionString;
 
 myConnectionString = "server=127.0.0.1;uid=root;" +
   "pwd=12345;database=test;";
 
 try
 {
     conn = new MySqlConnection();
     conn.ConnectionString = myConnectionString;
     conn.Open();
 }
 catch (MySqlException ex)
 {
     MessageBox.Show(ex.Message);
 }
 
 conn.close();

The above code sets up the connection string, which includes the server's ip/hostname, the username and password to use and the database to select upon connection, and then it closes the connection. To actually do something useful with this connection, you'd use something like this:

 MySqlCommand cmd = new MySqlCommand();
 MySqlDataReader results;
 
 cmd.CommandText = "SELECT VarCharField, IntField FROM mytable WHERE SomeCondition = 'Value';";
 cmd.Connection = conn;
 
 results = cmd.ExecuteReader();
 
 while (results.Read())
 {
     string f1 = results.GetString(0);
     int f2 = results.GetInt32(1);
 }
 
 results.Close();

Note that the MySqlCommand object does not need to be closed, as it does not create a permanent connection. MySqlCommand also supports prepared statements, which are useful if you have to execute the same query many times in a row, only with different parameters. An example of using Connector/Net with prepared statements can be found here. It may also be worthy to note that you can also retrieve fields by their names through this construct:

 string f1 = results["VarCharField"].ToString();

however, this involves manipulating variable types for anything other than strings, like so:

 int f2 = int.Parse(results["IntField"].ToString());

Connecting through ODBC

Using the built-in ODBC interface is very similar to using the Connector/Net interface, the difference is the object names and the way you define your connection string on the OdbcConnection object.

 using System.Data.Odbc;
   
 OdbcConnection conn = new OdbcConnection("dsn=MyDSNName; option=35");
 
 // or alternatively
 // string myConnectionString = "dsn=MyDSNName; option=35";
 // OdbcConnection conn = new OdbcConnection();
 // conn.ConnectionString = myConnectionString;
 
 try
 {
     conn.Open();
 }  
 catch (OdbcException ex)
 {
     MessageBox.Show(ex.Message);
 }
 
 conn.Close();

Looks pretty similiar to the other way of doing things, doesn't it? Reading data isn't all that different either:

 OdbcCommand cmd = new OdbcCommand;
 OdbcDataReader results;
 
 cmd.CommandText = "SELECT VarCharField, IntField FROM mytable WHERE SomeCondition = 'Value';";
 cmd.Connection = conn;
 
 results = cmd.ExecuteReader();
 
 while (results.Read())
 {
     string f1 = results.GetString(0);
     int f2 = results.GetInt32(1);
 }
 
 results.Close();

That's it in a nutshell. Don't forget to checkout the MySql Connector/Net documentation for more information, or Odbc Namespace documentation on Microsoft's website.

External Links

MySql - Connector/Net documentation

Microsoft - Odbc Namespace documentation