To connect to a MySQL database in a C# project, you need to install the MySQL Connector/NET data provider. Installing this provider is a simple process, and can be done using NuGet, a package manager built into Visual Studio.
Getting Started
To get started, open your C# project in Visual Studio and navigate to the Solution Explorer. Right-click on your project and select “Manage NuGet Packages” from the context menu. In the NuGet Package Manager, select the “Browse” tab and type “mysql.data” in the search box. The MySQL Connector/NET data provider, provided by Oracle, should be the first result that appears. Click the “Install” button to install the package.
Once the MySQL Connector/NET data provider is installed, you can start writing code to connect to your MySQL database.
Connecting to MySQL
The code down below shows an example of how to connect to a MySQL database using the MySQL Connector/NET data provider in C#.
using MySql.Data.MySqlClient;
using System.Data;
public class App
{
public static void Main()
{
Dictionary<string, string> config = new()
{
{ "server" , "localhost" },
{ "user" , "root" },
{ "password", "" },
{ "database", "scratchpad" }
};
using(MySqlHelper helper = new(config))
{
try
{
MySqlConnection? connection = helper.OpenAndGetConnection();
MySqlCommand command = new("SELECT ALL * FROM users", connection);
using MySqlDataReader reader = command.ExecuteReader();
Console.WriteLine("{0} {1} {2}", "ID", "NAME", "EMAIL");
while (reader.Read())
{
/* 0 is the first column, 1 the second, 2 the third and so on */
Console.WriteLine("{0} {1} {2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
}
}
catch(Exception ex)
{
Console.WriteLine("Opening database failed: {0}", ex.Message);
}
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey(true);
}
}Creating a MySqlHelper Class
The MySqlHelper class is a custom class that encapsulates the details of opening and closing a connection to the database. It implements the IDisposable interface and overrides the Dispose(bool) method to ensure that the connection is properly closed and resources are released.
class MySqlHelper : IDisposable
{
private readonly string connectionString;
private MySqlConnection connection;
public MySqlHelper(Dictionary<string, string> config)
{
this.connectionString = String.Format("server={0};user={1};password={2};database={3}",
config["server"], config["user"], config["password"], config["database"]);
this.connection = new MySqlConnection(connectionString);
}
public MySqlConnection OpenAndGetConnection()
{
if (this.connection != null && (this.connection.State != ConnectionState.Open))
{
this.connection.Open();
}
return this.connection;
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
if (disposing && this.connection != null)
{
/* close database connection, and release resources */
this.connection.Dispose();
this.connection = null;
}
}
~MySqlHelper()
{
Dispose(false);
}
}Overall, this code provides a simple example of how to connect to a MySQL database using C# and retrieve data from it. I hope you find the code helpful. If you wish to learn more about C#, please subscribe to our newsletter today and continue your C# learning journey with us!
