How to connect to MySQL and MariaDB in .NET with C#

A connection between your C# application and a MySQL or MariaDB database is essential for any data-driven solution on the .NET platform. Whether you're creating a web app, desktop software, or backend service, a reliable database connection serves as the starting point for executing queries, handling data, and supporting dynamic functionality.

In this guide, you'll learn how to create a stable and efficient link between your .NET app and MySQL/MariaDB using dotConnect for MySQL, Devart's high-performance ADO.NET provider. We'll cover the full setup process — from initializing your project and installing the required NuGet package to configuring secure access with SSL/TLS. You'll also discover how to integrate seamlessly with modern ORM tools like Entity Framework Core using Scaffold-DbContext.

Why dotConnect for MySQL?

dotConnect for MySQL is the data provider that suits ideally for all MySQL and MariaDB-related operations. Its numerous features enhance functionality, performance, and ease of development for .NET developers working with MySQL databases.

Prerequisites

The following prerequisites are necessary to follow this guide:

• Visual Studio 2022: This is our IDE of choice. If you don't have it on your machine, you can visit the official website to download and install the free Community Edition.
• dotConnect for MySQL: A high-performance ADO.NET data provider for MySQL with enhanced ORM support and database connectivity features.
• Entity Developer: An ORM designer for .NET ORM Frameworks with powerful code generation (more on this later).
• MySQL test database: A sample database provided by MySQL for learning and testing.

Download and activate dotConnect for MySQL

30-day free trial version

You can start using dotConnect for MySQL immediately with a 30-day free trial. Choose one of the following installation options:

• Installer: Download and install dotConnect for MySQL directly on your machine.
• NuGet: Add the Devart.Data.MySql package to your project.

No license key is required for the trial version — just install and explore.

Full version

After you've purchased the full version, follow these steps:

1. Go to the Products section in your Devart profile.

2. Click the product name, or hover over it and click Details. The License details page opens, where you can find the
Activation Key.

3. To activate dotConnect for MySQL in your application, copy the Activation Key and paste it into your connection string as the License Key parameter.

Create a .NET project

To begin working with dotConnect for MySQL, you first need to create a simple console application that connects to a MySQL database in Visual Studio. The process involves initializing a new project and installing the required NuGet packages.

1. Launch Visual Studio and select Create a new project.

2. Choose Console App (.NET Core) or Console App (.NET Framework), depending on your preference, and click Next.

3. Name the project, specify the path to its folder, and click Create. In this tutorial, we'll use MySqlTest as the project name.

4. In the Solution Explorer, right-click the created project and select Manage NuGet Packages.

5. Go to the Browse tab, find and install the Devart.Data.MySql package. Optionally, you can run the following command in the terminal:

dotnet add package Devart.Data.MySql

Connect using C#

This example demonstrates a basic connection to a MySQL database. Depending on your requirements, you might want to add more functionality, such as executing queries or handling different types of exceptions.

static void Main(string[] args) {
  string connectionString = "" +
	"Host=127.0.0.1;" +
	"User Id=TestUser;" +
	"Password=TestPassword;" +
	"Database=TestDb;" +
	"License Key=**********";

  try {
	using(MySqlConnection connection = new MySqlConnection(connectionString)) {
  	connection.Open();
  	Console.WriteLine("Connection successful!");
	}
  } catch (Exception ex) {
	Console.WriteLine("An error occurred: " + ex.Message);
  }
}

The connection string contains the necessary information to connect to the database, such as the host, user ID, password, and database name.

Connect using the SSL/TLS connection

To establish the SSL connection to the MySQL database with the specified connection string parameters, modify your code as follows:

static void Main(string[] args) {
    string connectionString = "" +
      "Host=127.0.0.1;" +
      "User Id=TestUser;" +
      "Password=TestPassword;" +
      "Database=TestDb;" +
      "Protocol=SSL;" +
      "SSL CA Cert=file://C:\\CA-cert.pem;" +
      "SSL Cert=file://C:\\SSL-client-cert.pem;" +
      "SSL Key=file://C:\\SSL-client-key.pem;" +
      "License Key=**********";

    try {
        using (MySqlConnection connection = new MySqlConnection(connectionString)) {
            connection.Open();
            Console.WriteLine("SSL/TLS Connection successful!");
        }
    }
    catch (Exception ex) {
        Console.WriteLine("An error occurred: " + ex.Message);
    }
}

Connect using Server Explorer

You can also connect to the database using Visual Studio's built-in Data Explorer:

1. Go to the Server Explorer window, right-click Data Connections, and then select Add Connection.

2. In the Add Connection dialog, click Change to specify the data source.

3. In the Change Data Source dialog, select dotConnect for MySQL from the list of installed data providers and click OK.

4. In the Add Connection dialog, select MySQL as the data source and provide your server credentials.

5. To verify the connection, click Test Connection.

Once connected, you can use Data Explorer to browse tables, run queries, and manage data directly within Visual Studio.

Connect with EF Core using Entity Developer

If you need to create an EF Core model from the MySQL database, the right way is to use a visual ORM builder — Entity Developer.

1. Right-click the project and navigate to Add > New Item.

2. Go to Installed > C# Items > Data, select Devart EF Core Model, and then click Add.

3. In the Entity Developer Model wizard, select Database First and click Next.

4. Provide the details of your MySQL database connection and click Test Connection.

5. To proceed further, click Next. Then, select Generate From Database and click Next.

6. Choose the database objects you want to scaffold and click Next.

7. Next, define the naming rules you want the property names in the database object to follow. We'll leave it as the default and click Next.

8. Specify model properties such as the namespace and entity container, then click Next.

9. After that, you will be asked to choose the contents of the model diagram. You can use all entities, split the entities by database, or do a custom selection.

10. Define code generation templates for the model.

11. After the model has been created, you can choose whether to download the Devart.Data.MySql.EFCore package. Then
click Finish.

12. Check the created model and context classes.

All steps in this guide are performed in Visual Studio, but if you prefer VS Code, JetBrains Rider, or another similar tool, you can use ED as a standalone application to work with the models efficiently.

To create a simple example that checks a database connection using Entity Framework Core (EF Core) with MySQL, follow
these steps.

class Program {
  static void Main(string[] args) {
	// Assuming the SakilaContext class is generated by Entity Developer
	using(var context = new SakilaContext()) {
  	try {
    	context.Database.CanConnect();
    	Console.WriteLine("Connection successful!");
  	} catch (Exception ex) {
    	Console.WriteLine("An error occurred: " + ex.Message);
  	}
	}
  }
}

Connect with EF Core using Scaffold-DbContext

To connect to the MySQL database using Entity Framework Core (EF Core) and scaffold its context, follow this step-by-step instruction. This process generates the necessary EF Core model classes and a DbContext based on the existing MySQL database schema.

Install the required NuGet packages

In the Solution Explorer, right-click the project and select Manage NuGet Packages. Then, open the Package Manager Console. To do this, go to Tools > NuGet Package Manager > Package Manager Console

Scaffold the DbContext

In the Package Manager Console, run the following command to scaffold the DbContext and entity classes from the MySQL database. Replace the connection string with your relevant connection details.

Scaffold-DbContext "Host=127.0.0.1;Port=3306;User Id=TestUser;Password=TestPassword;Database=sakila;License Key=**********;" Devart.Data.MySql.EFCore -OutputDir Models

After running the scaffold command, EF Core generates the DbContext class and entity classes in the specified output directory (e.g., Models). Review the generated code to ensure it matches your database schema.

Video tutorial: How to connect .NET console application to a MySQL database

Conclusion

This article provides detailed guides on connecting a .NET application to MySQL or MariaDB databases in several ways. It explores various connection methods, including secure SSL/TLS connections and using Entity Framework Core with
Scaffold-DbContext.

When developing applications, you can make the most of Devart dotConnect for MySQL's solutions to achieve a much faster, smoother, and more straightforward performance of all tasks. Download a free trial to experience seamless database connectivity, performance optimizations, and robust security for your .NET projects!