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:

con.Close();