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();
