Connect to Amazon RDS for MySQL Using C#
Amazon RDS for MySQL is one of the most popular options for those who want to combine the capacities of MySQL with the scalability, efficiency, and performance of cloud environments. This service is an excellent fit for developers seeking a reliable database backend without the hassle of manual maintenance, backups, or server management.
This article provides a comprehensive tutorial on connecting .NET applications to MySQL databases hosted on Amazon RDS. It will cover the full cycle of database interactions, from establishing the connection to reading and modifying data using C# and ADO.NET, as well as dotConnect for MySQL.
Why dotConnect for MySQL?
dotConnect for MySQL is an ideal data provider for all MySQL and MariaDB-related operations. It enables direct connection and data-related operations without any client libraries and additional complex calculations, and 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.
- Amazon RDS for MySQL: A managed cloud database service provided by Amazon Web Services (AWS) for MySQL workloads. You can create a sample database instance for learning and testing.
Download and activate dotConnect for MySQL
Depending on your conditions, you may choose a fully-functional free trial or a full version.
30-day free trial version
You can start using dotConnect for MySQL immediately with a 30-day free trial. Choose one of the 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 — just install it and explore.
Full version
After purchasing 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; you can find the Activation Key there.
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 create a simple demo project: a console application in Visual Studio that will connect to Amazon RDS. Also, we need to install dotConnect for MySQL to ensure connectivity.
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 RdsMySql), 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 Amazon RDS for MySQL with Data Explorer
Let us connect to Amazon RDS for MySQL with the help of the built-in Data Explorer. Click Tools, then select Connect to Database, and choose MySQL as the data source.
By default, Amazon RDS for MySQL requires an SSL connection. To enable it, click Advanced in the Add connection configuration window and specify that the app must use the SSL protocol.
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
In this section, we'll demonstrate how to connect to the data source and then run a simple query to pull records from the Actor table.
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=dbf-mysql0.********.eu-central-1.rds.amazonaws.com;" +
"User Id=********;" +
"Password=*********;" +
"Database=sakila;" +
"License key=*************";
return new MySqlConnection(connectionString);
}
}
Replace the placeholders with your actual credentials. If you own a paid license of dotConnect for MySQL, include the license key in the connection code.
Update the connection string
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("+---------+----------------+----------------+");
Console.WriteLine("| ActorId | First Name | Last Name |");
Console.WriteLine("+---------+----------------+----------------+");
while (reader.Read())
{
int actorId = reader.GetInt32(0);
string firstName = reader.GetString(1);
string lastName = reader.GetString(2);
Console.WriteLine(String.Format("| {0,-7} | {1,-14} | {2,-14} |", actorId, firstName, lastName));
}
Console.WriteLine("+---------+----------------+----------------+");
}
}
}
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 Amazon RDS for MySQL
Let's add a new row to the Actor table. For that, we need to compose and execute an INSERT command and use a parameterized query. The code is below.
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);
}
}
}
See the results in the application:
Update rows in Amazon RDS 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 from within your C# 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 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", 201);
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", 201);
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: 201");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
The result is:
Delete rows from Amazon RDS 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.
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", 201);
int rowsAffected = deleteCmd.ExecuteNonQuery();
if (rowsAffected > 0)
{
Console.WriteLine("Deleted actor with actor_id: 201");
}
else
{
Console.WriteLine("No actor found with actor_id: 201");
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("An error occurred: " + ex.Message);
}
}
}
We can see that the operation is successful.
Conclusion
Now you have a solid foundation for integrating Amazon RDS for MySQL into your .NET applications. Now you can move forward and master more advanced techniques to scale your app using the reliable AWS infrastructure. Meanwhile, dotConnect for MySQL simplifies your workflow by providing direct access to MySQL and MariaDB databases hosted in the AWS cloud.