C# MySQL
last modified October 17, 2026
In this article we show how to program MySQL in C#. We use ADO.NET.
MySQL
MySQL is a leading open source database management system. It is a multi-user, multithreaded database management system. MySQL is especially popular on the web. It runs on Linux, Windows, macOS, and BSD Unix.
ADO.NET
ADO.NET is a specification that unifies access to relational databases, XML files and other application data. MySql.Data (Connector/NET) is the official implementation of the ADO.NET specification for the MySQL database from Oracle. It is a driver written in C# and is available for all .NET languages. An alternative is the community-maintained MySqlConnector package, which has a very similar API.
$ dotnet add package MySql.Data
We add the package to our project.
The MySqlConnection, MySqlCommand,
MySqlDataReader, DataSet, and
MySqlDataAdapter are the core elements of the .NET data provider
model. The MySqlConnection creates a connection to a specific data
source. The MySqlCommand object executes an SQL statement against a
data source. The MySqlDataReader reads streams of data from a data
source.
The DataSet object is used for offline work with a mass of data. It
is a disconnected data representation that can hold data from a variety of
different sources. The MySqlDataAdapter fills a
DataSet with data from the database. Both
MySqlDataReader and DataSet are used to work with
data; they are used under different circumstances. If we only need to read the
results of a query, the MySqlDataReader is the better choice. If we
need more extensive processing of data, or we want to bind a Windows Forms
control to a database table, the DataSet is preferred.
C# MySQL version
In the following program we check the version of the MySQL server.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
Console.WriteLine($"MySQL version: {con.ServerVersion}");
We connect to the database and get some info about the MySQL server.
using MySql.Data.MySqlClient;
We import the elements of the MySQL data provider.
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
This is the connection string. It is used by the data provider to establish a connection to the database. We specify the host name, user name, password and a database name. In a real application, the connection string should not be hard-coded in the source code; it is usually read from a configuration file or an environment variable.
using var con = new MySqlConnection(cs);
A MySqlConnection object is created. This object is used to open a
connection to a database. The using declaration closes the
database connection when the variable goes out of scope.
con.Open();
This line opens the database connection.
Console.WriteLine($"MySQL version: {con.ServerVersion}");
Here we print the version of MySQL using the ServerVersion
property of the connection object.
$ dotnet run MySQL version: 8.4.11
C# MySQL SELECT statement
The following example determines the version of MySQL with a
SELECT statement.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
var stm = "SELECT VERSION()";
using var cmd = new MySqlCommand(stm, con);
var version = cmd.ExecuteScalar()?.ToString();
Console.WriteLine($"MySQL version: {version}");
We check for the version of the MySQL database. This time using an SQL query.
var stm = "SELECT VERSION()";
This is the SQL SELECT statement. It returns the version of the
database. The VERSION is a built-in MySQL function.
using var cmd = new MySqlCommand(stm, con);
The MySqlCommand is an object which is used to execute a query on
the database. The parameters are the SQL statement and the connection object.
var version = cmd.ExecuteScalar()?.ToString();
There are queries which return only a scalar value. In our case, we want a
simple string specifying the version of the database. The
ExecuteScalar is used in such situations. It returns the first
column of the first row, or null if the result set is empty; hence
the null-conditional operator.
$ dotnet run MySQL version: 8.4.11
C# MySQL create table
In the following example, we create a database table and fill it with data.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
using var cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "DROP TABLE IF EXISTS cars";
cmd.ExecuteNonQuery();
cmd.CommandText = """
CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL, price INT NOT NULL)
""";
cmd.ExecuteNonQuery();
cmd.CommandText = """
INSERT INTO cars(name, price) VALUES
('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000),
('Volvo', 29000), ('Bentley', 350000), ('Citroen', 21000),
('Hummer', 41400), ('Volkswagen', 21600)
""";
int n = cmd.ExecuteNonQuery();
Console.WriteLine($"Table cars created with {n} rows");
In the example, we create a cars table with eight rows.
cmd.CommandText = "DROP TABLE IF EXISTS cars"; cmd.ExecuteNonQuery();
First we drop the table if it already exists. We use the
ExecuteNonQuery method if we do not want a result set, for
example for DROP, INSERT, or DELETE
statements.
cmd.CommandText = """
CREATE TABLE cars(id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL, price INT NOT NULL)
""";
cmd.ExecuteNonQuery();
The cars table is created. The AUTO_INCREMENT keyword
makes the column auto-incremented in MySQL. We use a raw string literal for the
multi-line SQL statement.
cmd.CommandText = """
INSERT INTO cars(name, price) VALUES
('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000),
('Volvo', 29000), ('Bentley', 350000), ('Citroen', 21000),
('Hummer', 41400), ('Volkswagen', 21600)
""";
int n = cmd.ExecuteNonQuery();
We insert eight rows into the table with a single multi-row
INSERT statement. The ExecuteNonQuery method returns
the number of affected rows.
$ dotnet run Table cars created with 8 rows
We run the program.
$ mysql -u dbuser -p testdb
We connect to the testdb database with the mysql
tool.
mysql> SELECT * FROM cars; +----+------------+--------+ | id | name | price | +----+------------+--------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | | 7 | Hummer | 41400 | | 8 | Volkswagen | 21600 | +----+------------+--------+ 8 rows in set (0.00 sec)
We verify the data. The cars table was successfully created.
C# MySQL prepared statements
Prepared statements increase security and performance. When we write prepared statements, we use placeholders instead of directly writing the values into the statements.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
var sql = "INSERT INTO cars(name, price) VALUES(@name, @price)";
using var cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@name", "BMW");
cmd.Parameters.AddWithValue("@price", 36600);
cmd.Prepare();
cmd.ExecuteNonQuery();
Console.WriteLine($"row inserted, id: {cmd.LastInsertedId}");
We add a new car to the cars table. We use a parameterized command.
var sql = "INSERT INTO cars(name, price) VALUES(@name, @price)"; using var cmd = new MySqlCommand(sql, con);
The @name and @price are placeholders, which are
going to be filled later. Parameters are sent to the server separately from the
SQL text, so their values can never change the structure of the statement. This
guards against SQL injection attacks. Never build SQL statements by
concatenating or interpolating user input.
cmd.Parameters.AddWithValue("@name", "BMW");
cmd.Parameters.AddWithValue("@price", 36600);
cmd.Prepare();
Values are bound to the placeholders with the AddWithValue method.
The Prepare method creates a prepared version of the command on
the server, which pays off when the same command is executed many times.
cmd.ExecuteNonQuery();
Console.WriteLine($"row inserted, id: {cmd.LastInsertedId}");
The prepared statement is executed. We use the ExecuteNonQuery
method of the MySqlCommand object when we don't expect any data to
be returned. The LastInsertedId property gives us the
auto-generated id of the new row.
$ dotnet run row inserted, id: 9
C# MySqlDataReader
The MySqlDataReader is an object used to retrieve data from the
database. It provides fast, forward-only, read-only access to query results. It
is the most efficient way to retrieve data from tables.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
var sql = "SELECT id, name, price FROM cars";
using var cmd = new MySqlCommand(sql, con);
using MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
}
We get all rows from the cars table and print them to the console.
using MySqlDataReader rdr = cmd.ExecuteReader();
To create a MySqlDataReader, we call the
ExecuteReader method of the MySqlCommand object.
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
}
The Read method advances the data reader to the next record. It
returns true if there are more rows; otherwise false.
We can retrieve the value using the array index notation, or use a specific
method to access column values in their native data types. The latter is more
efficient.
$ dotnet run 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 9 BMW 36600
C# MySQL column headers
In the following example we print column headers with the data from a database table.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
var sql = "SELECT id, name, price FROM cars";
using var cmd = new MySqlCommand(sql, con);
using MySqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine($"{rdr.GetName(0),-4} {rdr.GetName(1),-10} {rdr.GetName(2),10}");
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} {rdr.GetString(1),-10} {rdr.GetInt32(2),10}");
}
In the example, we select all rows from the cars table with their
column names.
Console.WriteLine($"{rdr.GetName(0),-4} {rdr.GetName(1),-10} {rdr.GetName(2),10}");
We get the names of the columns with the GetName method of the
reader. The numbers after the commas set the width of the fields; negative
values align the text to the left.
while (rdr.Read())
{
Console.WriteLine($"{rdr.GetInt32(0),-4} {rdr.GetString(1),-10} {rdr.GetInt32(2),10}");
}
We print the data that was returned by the SQL statement to the terminal.
$ dotnet run id name price 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600 9 BMW 36600
C# MySQL async
The data provider has asynchronous versions of its methods. They do not block the calling thread while waiting for the database, which matters in web applications and GUI programs.
using System.Data;
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
await using var con = new MySqlConnection(cs);
await con.OpenAsync();
var sql = "SELECT name, price FROM cars WHERE price < @max ORDER BY price";
await using var cmd = new MySqlCommand(sql, con);
cmd.Parameters.AddWithValue("@max", 30000);
await using var rdr = await cmd.ExecuteReaderAsync();
while (await rdr.ReadAsync())
{
string name = rdr.GetString("name");
int price = rdr.GetInt32("price");
Console.WriteLine($"{name}: {price}");
}
We select cars cheaper than 30000 asynchronously.
await using var con = new MySqlConnection(cs); await con.OpenAsync();
The await using declaration disposes the connection
asynchronously. The OpenAsync opens the connection.
await using var rdr = await cmd.ExecuteReaderAsync();
while (await rdr.ReadAsync())
{
string name = rdr.GetString("name");
int price = rdr.GetInt32("price");
Console.WriteLine($"{name}: {price}");
}
The ExecuteReaderAsync returns a general DbDataReader.
The GetString and GetInt32 overloads that take a
column name are extension methods from the System.Data namespace.
Accessing columns by name makes the code independent of the column order.
$ dotnet run Skoda: 9000 Citroen: 21000 Volkswagen: 21600 Volvo: 29000
C# MySQL transaction
A transaction is an atomic unit of database operations against the data in one or more databases. Either all of the statements in a transaction are committed to the database, or none of them are.
using MySql.Data.MySqlClient;
string cs = "server=localhost;userid=dbuser;password=s$cret;database=testdb";
using var con = new MySqlConnection(cs);
con.Open();
using var tr = con.BeginTransaction();
try
{
using var cmd = new MySqlCommand();
cmd.Connection = con;
cmd.Transaction = tr;
cmd.CommandText = "UPDATE cars SET price = 10000 WHERE name = 'Skoda'";
cmd.ExecuteNonQuery();
cmd.CommandText = "UPDATE carz SET price = 30000 WHERE name = 'Volvo'";
cmd.ExecuteNonQuery();
tr.Commit();
Console.WriteLine("transaction committed");
}
catch (MySqlException e)
{
tr.Rollback();
Console.WriteLine($"transaction rolled back: {e.Message}");
}
using var cmd2 = new MySqlCommand("SELECT price FROM cars WHERE name = 'Skoda'", con);
Console.WriteLine($"Skoda price: {cmd2.ExecuteScalar()}");
We update two rows in a transaction. The second statement contains a typo in the table name, so it fails and the whole transaction is rolled back.
using var tr = con.BeginTransaction();
The BeginTransaction method starts a transaction.
cmd.Connection = con; cmd.Transaction = tr;
The command must be associated with the transaction.
catch (MySqlException e)
{
tr.Rollback();
Console.WriteLine($"transaction rolled back: {e.Message}");
}
In case of an error, we roll back the transaction; the first update is undone.
$ dotnet run transaction rolled back: Table 'testdb.carz' doesn't exist Skoda price: 9000
The price of the Skoda car was not changed.
Source
MySQL Connector/NET Developer Guide
In this article we have shown how to program MySQL databases in C#.
Author
List all C# tutorials.