Connecting C# to MySQL database / C# connection String for MySQL

In many cases it becomes imperative to connect C# to MySql, the prominent reason being the inherent simplicity of MySql. We have generally used the usual connection string to connect PHP to MySql. Connecting C# to MySql is similar.
We first need to use the connection string to establish the connection with MySql. Then, we use the SQL queries to carry out the Creation, Insertion, Update & Delete operations.

Before we do anything, first you need to download and install mysql-connector. You can get this connector from MySQL :: Download Connector/Net.

Once you install this connector, you need to add it as a reference in your C# project. You can do this by navigating to Project -> Add Reference. Then go to the .Net tab, and search for MySql.Data, and add it as a reference.

Now you need to include MySql.Data.MySqlClient at the start of the code. Add this at the start:

using MySql.Data.MySqlClient;

Now, you can use this connection string for establishing the connection:

string MyConString = "SERVER=localhost;" +    "DATABASE=dbname;" +    "UID=root;" +    "PASSWORD=\"\";";
MySqlConnection con = new MySqlConnection(MyConString);

con.Open();

Now, you can use 'con' when you need to fire any query.

Now, lets get into details of using sql queries.

1. CREATE TABLE:

   string query = "CREATE TABLE table_name";
   MySqlCommand cmd = new MySqlCommand(query, con);
   try
   {
     cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
     Console.WriteLine(e);
   }

2. INSERT:

   string query = "INSERT INTO table_name (attributes) VALUES(values)";
   MySqlCommand cmd = new MySqlCommand(query, con);
   try
   {
      cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
      Console.WriteLine(e)
   }

3. UPDATE:

   string query = " UPDATE table_name
   SET column1=value1, column2=value2, ...
   WHERE some_column=some_value";
   MySqlCommand cmd = new MySqlCommand(query, con);
   try
   {
     cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
     Console.WriteLine(e)
   }

4. DELETE:

   string query = "DELETE FROM table_name WHERE some_column=some_value";
   MySqlCommand cmd = new MySqlCommand(query, con);
   try
   {
     cmd.ExecuteNonQuery();
   }
   catch(Exception e)
   {
     Console.WriteLine(e)
   }

5. SELECT:

   MySqlCommand query = new MySqlCommand("Select * FROM `table_name` where
   CONDITION, con);
   MySqlDataReader reader = con.ExecuteReader();
   while (reader.Read())
   {
     Console.WriteLine(reader.GetString("column_name"));
   }

Finally, you need to close the connection, after you finish executing the queries. You can do this by writing: