Basic Tutorial for MySQL, C# and VB.Net

I'm a senior software architect/developer who usually prefers working with Microsoft technologies such as C#, VB.Net and Microsoft SQL Server. But with the high cost of using SQL Server with hosting companies ($100+/year), I usually opt-out for using a MySQL database if cost is an issue. This includes whether I'm using Open Source technologies such as PHP or .Net technologies such as VB.Net or C#.

After searching on the internet, most examples I found of C# connecting to MySQL used an ODBC MySQL database connection. Because most hosting companies won't set this up for you, I needed to find an another way of using MySQL with C#.Net and VB.Net.

After looking around, I found that MySQL offers a ADO.Net connector for MySQL databases. This .Net connector makes it really easy to connect to MySQL. It's just as if you were connecting to a SQL Server database. This includes using data adapters, connections and command objects... everything you're used to using with VB.Net or C#.Net.

Basic Tutorial to Connect to MySQL using C#

• Download and install the MySQL .Net Connector [download MySQL .Net connector]
• Open Visual Studio and your C# or VB.Net solution
• Add reference to MySQL .Net Connector (MySQL.Data). This dll is automatically registered in the GAC when you install the package.
  
  
  
  
• Copy the dll from the folder C:\Program Files\MySQL\MySQL Connector Net 5.0.9\Binaries\.NET 2.0\MySql.Data.dll (replace version with the version you have installed) to your bin directory of your C# or VB.Net project. This will make sure that the file is available once you publish your web project.
• Setup MySQL connection string in web.config or app.config
  
  <connectionStrings>
  

  <clear/>
  <add name="wisemen.ConnectionString" connectionString="Data Source=127.0.0.1; Database=2wisemen; User ID=username; Password=123456;" />
  

  </connectionStrings>
  
  Please note: replace IP address, database, username and password which you use for your database.
• Create new back end class project which will be responsible for connecting to MySQL database (this is not required, but recommended to separate your front end from your backend classes.
• Create class and add "using MySql.Data.MySqlClient;"
• Create method to execute query on MySQL database and return dataset
  
  public static DataSet GetDataSet(string Query, string TableName, string ConnectionString)
  {
  

  MySqlConnection myConnection = new MySqlConnection(ConnectionString);
  MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(Query, myConnection);
  DataSet ds = new DataSet();
  
  //Open connection
  myConnection.Open();
  
  try
  {

  //Fill dataset
  myDataAdapter.Fill(ds, TableName);
  
  //Close connection
  myConnection.Close();
  

  }
  catch (Exception ex)
  {
  

  //Close connection
  myConnection.Close();
  
  throw ex;
  

  }
  

  
  

  return ds;

  }
  
  
• Create method to execute queries such as insert, update and delete on a MySQL database
  
  public static void ExecuteNonQuery(string Query, string ConnectionString)
  {
  

  MySqlConnection myConnection = new MySqlConnection(ConnectionString);
  MySqlCommand myCommand = new MySqlCommand(Query, myConnection);
  
  //Open connection
  myConnection.Open();
  
  try
  {
  

  //Execute query
  myCommand.ExecuteNonQuery();
  
  //Close connection
  myConnection.Close();
  

  }
  catch (Exception ex)
  {
  

  //Close connection
  myConnection.Close();
  
  throw ex;
  

  }
  

  }
• Compile project and now you can execute queries on a MySQL database using C# or VB.Net.