Connect to Azure Database for MySQL Using C#
Azure Database for MySQL is a fully managed service that brings the reliability and scalability of the Azure cloud to one of the most popular open-source databases in the world. Whether you're building web apps, APIs, or data-driven services, MySQL in Azure offers a robust backend that integrates easily with the .NET technology.
This tutorial describes how to connect to an Azure MySQL database from a .NET application and perform basic CRUD operations using C# code. To simplify the task, we will use dotConnect for MySQL, a powerful data provider that enables direct connection to MySQL and MariaDB in the cloud.
Why dotConnect for MySQL?
dotConnect for MySQL is a high-performance data provider that allows .NET developers to connect directly to MySQL databases on-premises and in the cloud from various applications, with no need for any additional client libraries and configurations.
dotConnect for MySQL is ideal for .NET specialists who work with MySQL and MariaDB databases hosted in the Microsoft Azure cloud, as it enhances functionality, performance, security, and ease of development.
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.
- Azure Database for MySQL: A sample Azure cloud 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 necessary for the trial version — install it 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 illustrate this tutorial, we will need a simple demo project. It will be a console application that connects to a MySQL database in Visual Studio. Let us initialize this new project and install dotConnect for MySQL.
- 1. Launch Visual Studio and click Create a new project.
- 2. Choose Console App (.NET Core) and click Next.
- 3. Give this project a name (in our tutorial, we name it AzureMySqlTest), specify the path to its folder, and click Create.
- 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 to Azure Database for MySQL with Data Explorer
Let us connect to MySQL in Azure with the help of the built-in Data Explorer. Click Tools, then select Connect to Database, and choose MySQL as the data source.
Enter your server details and credentials. Click Connect.
Once connected, you can browse tables, execute queries, and manage data directly within the Data Explorer.
Connect and retrieve data from the database
Let us establish a connection with the Azure MySQL database and then run a simple query to pull records from the Actor table. It confirms the connection is working and the data is accessible.
Create the DatabaseConnection.cs class (connection strings)
The DatabaseConnection.cs class keeps connection details separate for better maintainability.
using Devart.Data.MySql;
public static class DatabaseConnection
{
public static MySqlConnection CreateConnection()
{
string connectionString = "" +
"Server=test.mysql.database.azure.com;" +
"User Id=TestUser;" +
"Password=TestPassword;" +
"Database=TestDatabase;" +
"License Key=**********";
return new MySqlConnection(connectionString);
}
}
Update the connection string
Replace the placeholders in the connection string with your actual MySQL database credentials. If you own a paid license of dotConnect for MySQL, include the license key in the connection strings.
Add the below code to your Program.cs file.
using Devart.Data.MySql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (MySqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
string sql = "SELECT actor_id, first_name, last_name FROM actor ORDER BY actor_id LIMIT 10";
using (MySqlCommand command = new MySqlCommand(sql, connection))
using (MySqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine("ActorId\tFirstName\tLastName");
while (reader.Read())
{
int actorId = reader.GetInt32(0);
string firstName = reader.GetString(1);
string lastName = reader.GetString(2);
Console.WriteLine(actorId + "\t" + firstName + "\t" + lastName);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
Run the application
Build and run your application: select Start from the menu or click F5. It displays the data retrieved from the database.
Insert new records into Azure Database for MySQL
Let's add a new row to the Actor table. For that, we need to compose and execute an INSERT command. Use the below code.
using Devart.Data.MySql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (MySqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
string insertSql = "INSERT INTO actor(first_name, last_name) VALUES(@first, @last)";
using (MySqlCommand insertCmd = new MySqlCommand(insertSql, connection))
{
insertCmd.Parameters.AddWithValue("@first", "TestFirst");
insertCmd.Parameters.AddWithValue("@last", "TestLast");
insertCmd.ExecuteNonQuery();
}
long newActorId = 0;
using (MySqlCommand idCmd = new MySqlCommand("SELECT LAST_INSERT_ID()", connection))
{
object result = idCmd.ExecuteScalar();
if (result != null && result != DBNull.Value)
{
newActorId = Convert.ToInt64(result);
}
}
Console.WriteLine("Inserted test actor (actor_id: " + newActorId + "): TestFirst TestLast");
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
We can see the results in our application:
Update rows in Azure Database for MySQL
Next, we want to update an existing record by changing the actor's first and last name. The following code shows how to build and run an UPDATE command to modify data already stored in the database.
using Devart.Data.MySql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (MySqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
string updateSql = "UPDATE actor SET first_name = @first, last_name = @last WHERE actor_id = @id";
using (MySqlCommand updateCmd = new MySqlCommand(updateSql, connection))
{
updateCmd.Parameters.AddWithValue("@first", "Michael");
updateCmd.Parameters.AddWithValue("@last", "Anderson");
updateCmd.Parameters.AddWithValue("@id", 205);
int rowsAffected = updateCmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("Updated actor: Michael Anderson");
// Retrieve and display the updated row
string selectSql = "SELECT actor_id, first_name, last_name FROM actor WHERE actor_id = @id";
using (MySqlCommand selectCmd = new MySqlCommand(selectSql, connection))
{
selectCmd.Parameters.AddWithValue("@id", 205);
using (MySqlDataReader reader = selectCmd.ExecuteReader())
{
if (reader.Read())
{
Console.WriteLine($"actor_id: {reader["actor_id"]}, first_name: {reader["first_name"]}, last_name: {reader["last_name"]}");
}
}
}
}
else
{
Console.WriteLine("No actor found with actor_id: 205");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
We can see the results in our application:
Delete rows from Azure Database for MySQL
Finally, let us see how to delete the record. We are going to remove the actor we have just added to the database using the DELETE command with a condition.
using Devart.Data.MySql;
public sealed class Program
{
public static void Main(string[] args)
{
try
{
using (MySqlConnection connection = DatabaseConnection.CreateConnection())
{
connection.Open();
Console.WriteLine("Connection successful!");
string deleteSql = "DELETE FROM actor WHERE actor_id = @id";
using (MySqlCommand deleteCmd = new MySqlCommand(deleteSql, connection))
{
deleteCmd.Parameters.AddWithValue("@id", 205);
int rowsAffected = deleteCmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("Deleted actor with actor_id: 205");
}
else
{
Console.WriteLine("No actor found with actor_id: 205");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
The output in the application proves that the operation is successful.
Conclusion
This tutorial shows how to perform all the basic database operations against the cloud MySQL database in Azure from a .NET application. We have successfully connected, queried, inserted, updated, and deleted records, which are essential tasks for almost any application. From that, you can move forward to more advanced techniques, utilizing parameterized queries, transactions, stored procedures, and other features. In any case, you may not worry about connecting to MySQL and MariaDB in the Azure cloud, as dotConnect for MySQL will grant a smooth and secure connection simply and efficiently.