How to connect to MySQL and MariaDB in .NET with C#
Database connectivity is crucial because no application is complete without some means of data storage and management so we can insert and retrieve the information as necessary. MySQL is a highly popular choice for building applications due to its open-source nature, reliability, high performance, and ease of use and management. Supported by a large community of developers and extensive documentation, MySQL ensures that you can always get assistance.
If you're working on a .NET application, MySQL as part of your backend provides a robust, scalable, and cost-effective solution that can grow with your needs. In this article, we explore several of the most efficient methods for connecting your .NET application to MySQL.
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.
Download and activate dotConnect for MySQL
Visit the dotConnect for MySQL download page. You can either get it for a free trial or purchase a full version at once. The free trial gives you 30 days to explore all features, but it cannot be used for deploying commercial projects and does not require a license key for activation.
If you're ready to purchase the full version, select Buy Now to add it to your cart.
Download and install the trial version
For the trial version, simply download and install dotConnect for MySQL on your machine. No license key is required for the trial, and you can start exploring the product immediately.
Purchase and access the full version
After purchasing the full version, go to your profile's Licenses page. You'll find a list of your licenses. Find dotConnect for MySQL and click Details. Here, you'll find your license details, including the activation key.
Download the full version
Click Download Product Versions to access the list of available versions. Select the most recent version and download the installer.
Run the downloaded file and follow the on-screen instructions to install dotConnect for MySQL on your machine. This process will install the software and activate all features associated with your license key.
Create .NET project
First, we need to set up a new .NET Application project in Visual Studio. Open Visual Studio and click Create a new project. Then search for ASP.NET Core Web Application to select a template.
Name the project (in this tutorial, it is called MySQL_ASP.NET), then select .NET 8 as the target framework and click Create.
Next, we are going to install dotConnect for MySQL via the NuGet Package Manager in Visual Studio. In the taskbar, click Tools > NuGet Package Manager > Manage NuGet packages for Solution.
On the NuGet Package Manager page, click Browse and search for dotConnect. Select Devart.Data.MySQL and install it for your project.
When running the application, click F5 for the scaffolded web page to appear.
Now, let us set up our Database connection class. Right-click on your project, go to Add, and select Class.
Let's call it DatabaseConnectionClass (we'll use it to simulate connecting the MySQL/MariaDB database to our application).
After that, right-click and add again, now it will be for the class named Actor.cs in our work scenario.
Copy the below script and insert it into that class:
public class Actor
{
public int ActorId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime LastUpdate { get; set; }
}
Connect to MySQL or MariaDB
Now, let us add a method to our DatabaseConnectionClass. Copy the following piece of code into your class:
public static DataTable GetActorTable() {
try {
// Open a connection to the MySQL database
string connectionString = "" +
"Server=127.0.0.1;" +
"Port=3306;" +
"UserId=TestUser;" +
"Password=TestPassword;" +
"Database=sakila;"
"LicenseKey=**********";
DataTable table;
using(var connection = new Devart.Data.MySql.MySqlConnection(connectionString)) {
connection.Open();
string query = "SELECT * FROM actor LIMIT 10;";
MySQLDataAdapter adapter = new Devart.Data.MySql.MySqlDataAdapter(query, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "actor");
table = dataSet.Tables["actor"];
connection.Close();
}
return table;
} catch (Exception ex) {
Console.WriteLine(ex.Message);
return null;
}
}
Make sure to specify using Devart.Data.MySql at the top of your file, and provide your actual User and Password.
Video tutorial: How to Connect a .NET Console Application to a MySQL Database
Connect using an SSL/TLS connection
Another option is connecting to a MySQL or MariaDB database via SSL/TLS connection. Have a look at the below code:
public static DataTable GetActorTableUsingSSL() {
try {
// Open a SSL connection to the MySQL database
DataTable table = new DataTable();
MySqlConnection connection = new Devart.Data.MySql.MySqlConnection("" +
"Server=127.0.0.1;" +
"Protocol=SSL;" +
"UserId=TestUser;" +
"Password=TestPassword;" +
"Database=sakila;"
"LicenseKey=**********");
connection.SslOptions.CACert = "file://C:\\Project\\CA-cert.pem";
connection.SslOptions.Cert = "file://C:\\Project\\SSL-client-cert.pem";
connection.SslOptions.Key = "file://C:\\Project\\SSL-client-key.pem";
MySqlCommand command = new Devart.Data.MySql.MySqlCommand("SELECT * FROM actor LIMIT 10;", myConn);
connection.Open(); using(MySqlDataReader reader = command.ExecuteReader()) {
table.Load(reader);
}
connection.Close();
return table;
} catch (Exception ex) {
Console.WriteLine(ex.Message);
return null;
}
}
Here, we have defined the following parameters:
- SslOptions.CACert - the location of your authority certificate
- SslOptions.Cert - the location of your client certificate
- SslOptions.Key - the location of your client's private key
This way, your communication between the MySQL client and server becomes secure as you encrypt it with SSL.
We need the ability to visualize the data. In the Visual Studio Solution Explorer, click Controllers and then Add a new class to add a class named ActorsController.
Now, inside the MySQL_ASP.NET.Controllers namespace, modify the class to use the below code:
[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
[HttpGet]
public IEnumerable GetActors()
{
List actorList = new();
DataTable dataTable = DatabaseConnectionClass.GetActorTable();
foreach (DataRow row in dataTable.Rows)
{
Actor obj = new Actor
{
ActorId = Convert.ToInt32(row["actor_id"]),
FirstName = Convert.ToString(row["first_name"]),
LastName = Convert.ToString(row["last_name"]),
LastUpdate = Convert.ToDateTime(row["last_update"])
};
actorList.Add(obj);
}
return actorList;
}
}
When you run your project, click on the dropdown for GetActors, choose Try it now, and then Execute. After executing, we can see the list we want:
Great! We have successfully established a connection to our MySQL database.
Connect with EF Core
Another way of connecting to a MySQL or MariaDB database is through Entity Developer. For our purposes, download a free trial; it is provided for 30 days and it offers all the functionality for proper testing under the actual workload.
After downloading and installing Entity Developer, launch it and select New Model from the File menu.
In the New Model window, click Entity Model under Devart Entity Framework.
In the wizard, select Database First and click Next.
The Set up data connection properties window pops up. Provide the details of the MySQL database connection. Specify the correct user ID and password configured while setting up MySQL. Click Next.
A new window will come prompting you to choose the modal contents. Select Generate From Database and click Next.
A new modal window asks you to choose the database objects you want to scaffold. Since we have been using the Actors table, let's select just that table. However, depending on your work scenarios, you can choose a group of objects or all objects. Click Next.
The next step is the Naming rules setup. This will define the naming convention for the property names in the database object to follow. Here we leave them as default and proceed by clicking Next.
In the following stage, we set up model properties like namespace and entity container. The only thing we need to change on this page is the Target Framework property. In our case, we are using .NET 8 and specify it. You should choose the framework your project uses. Then click Next.
After that, we need to choose the Model Diagram Contents. You can use all entities, split the entities by database, or do a custom selection. For this tutorial, we will use All Entities.
The next window asks us to choose Code Generation Templates for our objects. Here, you can define different parameters for the object to follow. We will stick to the default settings. Click Next.
Now, the model creation is complete.
It is done. We have connected to our database using Entity Developer. From here, we can perform a variety of tasks, like creating new models, adding properties to existing models, and even creating mappings. Head over to our webpage to get familiar with all features of Entity Developer.
Advantages of Entity Developer
Increased Productivity
- Visually design your data access layer with drag-and-drop functionality
- Generate code automatically from databases or existing models in minutes
- Utilize pre-built and tested code generation templates
Reduced Errors
- Automate data access layer generation to minimize coding mistakes
- Benefit from years of experience and testing in the code templates
Enhanced Development Experience
- Seamless integration with Visual Studio for a familiar workflow
- Support for both Model-First and Database-First approaches to model creation
Visual Design and Code Generation
- Visually define various mapping types for your data model
- Synchronize changes between your model and the database
- Refactor your model with built-in tools
- Handle large and complex models efficiently
Flexibility and Customization
- Execute LINQ/ESQL/HQL queries directly within the designer
- View and edit data stored in the source tables
- Leverage background model validation for proactive error detection
- Customize code generation with pre-defined and custom templates
- Automate code generation tasks using the console application
Connect with EF Core using Scaffold-DbContext
We can also connect our .NET application to the MySQL database through EF (Entity Framework) Core. Let us see how we can do that.
First, we need to add a separate project to our solution to illustrate the case. Right-click the current MySQL_ASP.NET solution in the Visual Studio Solution Explorer and go to Add > New project.
This additional project in our case is called MySQL_EF_Core_ASP.NET, and we have added it in the same way as did at the beginning of this tutorial. Right-click it and set it as a startup project.
First, ensure you have EF Core Tools installed in your development environment. You can install them globally using the .NET CLI from the command line:
dotnet tool install --global dotnet-ef
Next, open NuGet Package Manager for the project in Visual Studio, choose Devart.Data.MySQL.EFCore, and click Install.
We want to install the Scaffold-DbContext via the Package Manager Console. To do this, open Visual Studio, select Tools > Package Manager Console and select MySQL_EF_Core_ASP.NET as the project.
When the console opens up, execute the following command:
Install-Package Microsoft.EntityFrameworkCore.Tools
Once installed, we can use Scaffold-DbContext to generate DbContext and entity classes for our MySQL database.
Execute the following command from the Package Manager Console:
Scaffold-DbContext "Server=127.0.0.1;Port=3306;UserId=TestUser;Password=TestPassword;Database=sakila" Devart.Data.MySQL.Entity.EFCore
Remember to provide your actual credentials for your User ID and password.
After that, the dbContext called SakilaContext and the table entity classes should be generated. You should see them in your Solution Explorer.
We have our Actors class also generated, so let us use it again. We will query this entity table using Linq.
Right-click the MySQL_EF_Core_ASP.NET project, select the option to add a class, and add a DatabaseConnectionClass as we did earlier. Then copy the below code into the class:
public class DatabaseConnectionClass
{
private readonly SakilaContext _context;
public DatabaseConnectionClass()
{
_context = new SakilaContext();
}
public IEnumerable GetActors()
{
return _context.Actors.Take(10).ToList();
}
}
We have used our sakilacontext to get the entity of actors and returned it to a list.
Just like before, we need a way to visualize our data, so add a new class called ActorsController into the Controllers folder.
Now, inside the namespace MySQL_EF_Core_ASP.NET.Controllers, modify the class to use the code below:
[ApiController]
[Route("api/[controller]/[action]")]
public class ActorsController : ControllerBase
{
private readonly DatabaseConnectionClass _context;
public ActorsController()
{
_context = new DatabaseConnectionClass();
}
[HttpGet]
public IEnumerable GetActors()
{
return _context.GetActors();
}
}
When you run your application clicking F5 and navigate to the GetActors endpoint, click Try it out and Execute. You will see your query results in the response body as was shown earlier.
For more information on working with EF Core refer to our guide, which includes instructions on performing INSERT, UPDATE, and DELETE operations.