Connect to MySQL database using C#
problem
Connect to MySQL database using C# and Visual Studio. This blog post is about using the connection string to connect to a local MySQL database and read data and print them in the console.
The tutorial was written using the official IDE from Microsoft –Visual Studio.
No longer available. Check out this link for alternatives.
create project
Create a Console Application
add dependency
MySql.Data package is needed
connect to database
Now let’s put the code to connect to the MySQL db instance and read some data. A local MySQL instance is running with the database and table already created.
using System;
using MySql.Data.MySqlClient;
namespace mysqlTest
{
public class MySQLReader
{
public static void Main()
{
string connectionString = "server=localhost;user=root;database=test;";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
Console.WriteLine("Connecting to MySQL...");
connection.Open();
string sql = "SELECT * FROM users";
MySqlCommand cmd = new MySqlCommand(sql, connection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[0] + " -- " + reader[1]);
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
connection.Close();
Console.WriteLine("Done.");
}
}
}
The code above connects to a database named test and reads all the rows from the table users. It’s printing in the console the first two fields/ columns of each record. Also, the connection string above has no password provided for simplicity. The user: root didn’t setup password at installation phase of MySQL (which is discouraging).
output
Current db with user table and a record having 4 columns (fields)
running
Executing the code above shows the following output in the console.
Correct!
Crash test
The reader[0] + ” — ” + reader[1] is shown above. If we were to run it with reader[5] or any index greater than 3 it should crash and the catch exception clause should catch it and print the error.
Let’s try updating the line below:
Console.WriteLine(reader[0] + " -- " + reader[1]);
to
Console.WriteLine(reader[3] + " -- " + reader[4]);
and by running it..breaks!
As expected with IndexOutOfRangeException (4 columns are indexed from 0 to 3 inclusive).
conclusion
In this post, the code simply connects and reads data from the table specified in a local database. This code can be adapted to your database and table names, also the server can be a remote IP address instead of localhost.
Share it!
Ellion
Professional IT consultant, writer, programmer enthusiast interested in all sorts of coding.
Eats all cookies 🍪