Connect MySQL Database with .NET Core and C#

In this post, we are going to look at how to connect our code to a database, in our case MySQL.

To do this, it is an essential requirement to have MySQL installed. If you don't have it, you can download it from the official website.

1 - Connecting to MySQL with .NET

First of all, let's see how to connect to MySQL with C# in a "pure" way, without using any framework or ORM.

For this, from Nuget we need to install the MySql.Data package, and of course, we need to know the connection string to the database. In my case: Server=127.0.0.1;Port=3306;Database=ejemplo-conexion;Uid=root;password=test;

To run queries, all we need to do now is instantiate the connection.

For this, we instantiate the MySqlConnection class, passing as a parameter the connection string that we specified earlier.

string connectionString = "Server=127.0.0.1;Port=3306;Database=personal;Uid=root;password=test;";
MySqlConnection conexion = new MySqlConnection(connectionString);

Remember that after you finish a call to the database, you have to clean up the connection. For this, we need to call the .Dispose() method of MySqlConnection.

conexion.Dispose();

Or, as I prefer, use a using block inside our method, which automatically calls the .Dispose() method when finished.

using (MySqlConnection conexion = new MySqlConnection(connectionString))
{
    //Code
}

The reason I prefer to use using is because it is easier to maintain.

With this simple step, we are now able to make queries and inserts to the database.

1.2 - Opening the database connection

First of all, we need to have content inside our database.

In this example, I have created two tables, Articulo and Autor, and between these two tables, the author has a direct relationship with the article. Along with the tables, I have created two rows so we can see some result.

To run any query or insert, the first thing we need to do is open a connection to the database.

We do this with the open method of the connection

conexion.Open();

Now we only need to create the command to query the database.

We need to use the MySqlCommand object, which allows us to create a command to run queries, inserts, or updates. Inside MySqlCommand we need to specify the connection and the command to execute.

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conexion;
cmd.CommandText = "select/insert/update here";

As we see, inside .CommandText we can write text, and it will accept any kind of text. We must be careful, since if we concatenate variables within the query, we could suffer from SQL Injection.

string articuloId; //Element passed from the main controller through httpPost/httpGet
cmd.CommandText = $"select * from Articulo where id = {articuloId}"; //WARNING SQL INJECTION

As you can see, that code can lead us to be exposed to SQL Injection, because if an attacker intercepts the call from the browser to the server and changes the article id to something like: 1'; drop table articulo;

The value of our query to the database would be

"select * from Articulo where id = 1'; drop table articulo;";

Which would delete our table.

1.3 - Preventing SQL Injection in C#

We can avoid the SQL injection scenario if in our query command we do not insert values directly, but pass them as parameters.

To do this, we must indicate the parameters used in the SQL command by prefixing the parameter name with a question mark ?.

In our previous example, the articuloId variable is no longer a variable as such and is now part of the command as ?articuloId.

cmd.CommandText = $"select * from Articulo where id = ?articuloId";

Right now, the value of articuloid to query is ?articuloId, which is not correct, since we need to assign the value of the article to query.

The MySqlCommand object provides us with a list of parameters into which we can insert. For this, we access .Parameters and inside use the .Add method

cmd.Parameters.Add("?articuloId", MySqlDbType.Int32).Value = articuloId;

We must pass inside .Add the parameter name from our query and the type it will be. In this way, we avoid possible injections. Also, if we select string, it will check the input and help avoid SQL injection.

Finally, with the Value property, we assign the value we are going to use.

2 - Querying database information with .NET (select)

To query a MySQL database from .NET, we just need to call the cmd.ExecuteReader() method. It returns a MySqlDataReader type that has a .Read() method, which returns a boolean indicating that we should continue reading.

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conexion;
cmd.CommandText = $"select * from Articulo where id = ?articuloId";
cmd.Parameters.Add("?articuloId", MySqlDbType.Int32).Value = articuloId;
Articulo articulo = new Articulo();
using (var reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        articulo.Id = Convert.ToInt32(reader["id"]);
        articulo.Titulo = reader["Titulo"].ToString();
    }
}
return articulo;

We need to return an object instead of the reader, so we must instantiate the type we want to return and assign the values when reading. To assign the values, we must read the content from the reader using reader["field"] and convert it to the required type, since by default, when reading, it is always a string.

If the query returns a list, each while loop represents a row.

3 - Inserting values into a database with .NET (insert)

To insert values, we need to create our connection, as we did before, but this time, we need to call the .ExecuteNonQuery() method. We must pass the parameters in the same way as when we run queries.

using (MySqlConnection conexion = new MySqlConnection(connectionString))
{
    conexion.Open();

    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = conexion;
    cmd.CommandText = "INSERT INTO `ejemplo-conexion`.`Articulo` (`Titulo`, `Contenido`, `AutorId`, `Fecha`) VALUES (?titulo, ?contenido, ?autorid, ?fecha);";

    cmd.Parameters.Add("?titulo", MySqlDbType.VarChar).Value = $"nuevo titulo";
    cmd.Parameters.Add("?contenido", MySqlDbType.VarChar).Value = $"nuevo contenido";
    cmd.Parameters.Add("?AutorId", MySqlDbType.Int32).Value = 1;
    cmd.Parameters.Add("?fecha", MySqlDbType.DateTime).Value = DateTime.Now;

    cmd.ExecuteNonQuery();
}

3.1 - Retrieve the inserted ID

By default, when we execute .ExecuteNonQuery(), it returns the number of rows accepted, but if we are inserting data, many times we want the ID of the element we have inserted. For this, there is the LastInsertedId property inside MySqlCommand.

cmd.ExecuteNonQuery();
var ultimoId = cmd.LastInsertedId;

To retrieve the inserted id, the query must have been executed.

4 - Updating values in a database with .NET (update)

Exactly the same as when inserting, once we have our command with the parameters to update and, of course, our filter in the SQL query where clause.

using (MySqlConnection conexion = new MySqlConnection(connectionString))
{
    conexion.Open();

    MySqlCommand cmd = new MySqlCommand();
    cmd.Connection = conexion;
    cmd.CommandText = "update `ejemplo-conexion`.`Articulo` SET `Titulo` = ?titulo where ID = ?id;";

    cmd.Parameters.Add("?titulo", MySqlDbType.VarChar).Value = $"updated title";
    cmd.Parameters.Add("?id", MySqlDbType.Int32).Value = 3;

    cmd.ExecuteNonQuery();
}

Conclusion

• In this post we focused on connecting to a MySQL database from an application written in .NET. It doesn't matter if it is ASP.NET or a console application, since the code is the same
• Preventing SQL Injection in an application written in .NET
• Running queries to a database from .NET
• Inserting records into a database from .NET
• Updating records in a database from .NET