How to Connect to Google Cloud for MySQL in .NET With C#

Google Cloud offers a reliable and scalable MySQL service through Cloud SQL, making it easy to run managed relational databases in the cloud. For .NET developers, integrating a MySQL instance on Google Cloud into your application can streamline data access while offloading infrastructure management.

In this tutorial, you'll learn how to connect your C# application to a MySQL database hosted on Google Cloud, and perform basic data operations such as querying, inserting, updating, and deleting records. This guide is ideal for developers new to cloud integrations with .NET.

Why dotConnect for MySQL?

dotConnect for MySQL is the data provider that is a perfect solution for all MySQL and MariaDB-related operations. Its numerous features enhance the functionality, improve the performance, and facilitate 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
• Google Cloud for MySQL: A sample Google 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 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 Google Cloud 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) and click Next.

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

4. In the Solution Explorer, right-click the project you created 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

Check MySQL database objects

To connect to MySQL using the built-in Data Explorer, click Tools and select Connect to Database. Then choose MySQL Server as the data source.

By default, Google Cloud for MySQL requires an SSL connection.

To enable it, go to the Google Cloud Console → SQL → your MySQL instance → Connections → Security → Certificates, and download the Server CA certificate.

Then, open the Advanced connection settings in your application and specify the path to the downloaded certificate file under the SSL configuration before connecting.

Enter your server details and credentials and click Connect.

Once connected, you can browse tables, execute queries, and manage data directly within Data Explorer.

Connect to the database and retrieve data

First, let's establish a connection to your Cloud SQL for MySQL instance and run a simple query to pull records from the Actor table. This step ensures that your application is communicating properly with the database.

Create DatabaseConnection.cs (connection strings)

This class keeps connection details separate for better maintainability.

using Devart.Data.MySql;

public static class DatabaseConnection
{
    public static MySqlConnection CreateConnection()
    {
        string connectionString = "" +
          "Server=127.0.0.1;" +
          "User Id=TestUser;" +
          "Password=TestPassword;" +
          "Database=TestDatabase;" +
          "Protocol=Ssl;" +
          "SSL CA Cert=file://C:\\server-ca.pem" +
          "License key=**********";

        return new MySqlConnection(connectionString);
    }
}

Update the connection string

Replace the placeholders in the connection string with your actual MySQL database credentials.

Add the code below 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 by pressing F5 or selecting Start from the menu.

Insert new records in MySQL

Now that the connection is active, let's add new data. In this example, you will learn how to insert a row into the Actor table using parameterized SQL to write data into your MySQL instance safely.

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);
        }
    }
}

You can see the results in your application:

Update rows in MySQL

Need to modify existing data? In this example, let's update the first and last names of an actor in the table, applying changes to a specific row using a targeted update statement.

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);
        }
    }
}

You can see the results in your application:

Delete rows in MySQL

To clean up the test data, let's delete the actor we just added. This demonstrates how to remove records from a MySQL database on Google Cloud using standard SQL commands in C#.

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 application output shows that the operation is successful.

Conclusion

In this tutorial, you've successfully connected a .NET application to a Google Cloud MySQL instance and completed basic CRUD operations. With this knowledge, you're equipped to build more robust cloud-native apps that leverage MySQL's flexibility within the Google Cloud ecosystem.