Azure Database for MySQL

Integrating Azure Database for MySQL with C#

This guide details how to connect to and interact with your Azure Database for MySQL instance using C# applications.

Prerequisites

• An Azure subscription.
• An Azure Database for MySQL server instance created.
• The connection string details for your MySQL server (hostname, username, password, database name).
• A C# development environment (e.g., Visual Studio, .NET CLI).

Installation

You'll need to install the appropriate MySQL connector for .NET. Use the NuGet Package Manager for this:

dotnet add package MySql.Data --version 8.0.33

Connecting to the Database

Establishing a connection involves using the MySqlConnection class and providing your connection string.

        using MySql.Data.MySqlClient;
        using System;

        public class DatabaseConnector
        {
            private string connectionString;

            public DatabaseConnector(string host, string user, string password, string database)
            {
                connectionString = $"Server={host};Database={database};Uid={user};Pwd={password};";
            }

            public void TestConnection()
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    try
                    {
                        connection.Open();
                        Console.WriteLine("Connection to Azure Database for MySQL successful!");
                    }
                    catch (MySqlException ex)
                    {
                        Console.WriteLine($"Error connecting to database: {ex.Message}");
                    }
                }
            }
        }
        

Performing CRUD Operations

Once connected, you can execute SQL commands to create, read, update, and delete data.

Creating Data (INSERT)

        public void InsertData(string name, int age)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string query = "INSERT INTO Users (Name, Age) VALUES (@Name, @Age)";
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Age", age);
                    command.ExecuteNonQuery();
                    Console.WriteLine($"Inserted: {name}");
                }
            }
        }
        

Reading Data (SELECT)

        public void ReadData()
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string query = "SELECT Id, Name, Age FROM Users";
                using (MySqlCommand command = new MySqlCommand(query, connection))
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader["Id"]} - {reader["Name"]} ({reader["Age"]})");
                    }
                }
            }
        }
        

Updating Data (UPDATE)

        public void UpdateData(int id, string newName)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string query = "UPDATE Users SET Name = @NewName WHERE Id = @Id";
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@NewName", newName);
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                    Console.WriteLine($"Updated user with ID {id} to name {newName}");
                }
            }
        }
        

Deleting Data (DELETE)

        public void DeleteData(int id)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();
                string query = "DELETE FROM Users WHERE Id = @Id";
                using (MySqlCommand command = new MySqlCommand(query, connection))
                {
                    command.Parameters.AddWithValue("@Id", id);
                    command.ExecuteNonQuery();
                    Console.WriteLine($"Deleted user with ID {id}");
                }
            }
        }
        

Best Practices

• Use Parameterized Queries: Always use parameters to prevent SQL injection vulnerabilities.
• Connection Pooling: The MySqlConnection class supports connection pooling by default, which improves performance.
• Error Handling: Implement robust error handling for database operations.
• Close Connections: Ensure connections are properly closed and disposed of using using statements.
• Azure Security: Configure firewall rules on your Azure Database for MySQL server to restrict access to trusted IP addresses.

Continue to Setup