Amazon RDS for C# Developers

Making the Choice

Are you a C# developer working on a Web site that needs to store data? Are you working on a killer application designed to share data across multiple locations? Does your database server have high availability and high reliability requirements? If you answer yes to any of these questions, Amazon RDS may provide the solution you need.

The Amazon RDS service makes it easy to set up, operate, and scale a relational database in the cloud. The service is based on MySQL, so if you've developed Microsoft.NET–based MySQL solutions, you'll find Amazon RDS familiar. Indeed, you could go so far as to say that Amazon RDS is a MySQL 5.1 database that runs on a virtual machine (VM) hosted in the Amazon cloud. If you have not used a MySQL database before but have used other relational database products, developing applications using Amazon RDS will quickly have you in your comfort zone.

As the owner or administrator of the database, you don't need to worry about hardware or software failure: Amazon RDS patches the database software and backs up your database. Through the Amazon Web Services (AWS) Management Console, you can easily create additional databases or replicate your data. As a developer, you simply point your application at the database instance you create and start developing your application using technologies like the ADO.NET Entity Framework—technologies you're already familiar with.

Provisioning an Account and Creating a Database

The first step in getting started is to create a database instance. Go to http://aws.amazon.com/rds and click Sign Up for Amazon RDS. Follow the on-screen instruction until you come to the Amazon RDS Console Dashboard (see Figure 1), which allows you to launch a new database instance.

Figure 1. Launch a new database instance

Note: To use Amazon RDS, you need an AWS account. If you don't already have one, you'll be prompted to create one when you sign up for Amazon RDS.

From the console, the Launch DB Instance Wizard prompts you to provide information about your database instance (see Figure 2).

1. On the DB Instance Details wizard page, choose the small database option from the DB Instance Class list.

   
   Figure 2. Provide basic information about your database instance

   The DB instance class you choose—the small, large, or extra large database size—relates directly to how much you'll be charged for running the database instance. You pay for each hour your database instance is up and running. See http://aws.amazon.com/rds/#pricing for current pricing information.

2. In DB Instance Identifier, type myDemoInstance.
3. Fill in the other fields as appropriate, and then click Continue.
4. On the Additional Configuration wizard page, name your database and provide zoning and port information, as shown in Figure 3, and then click Continue.

   
   Figure 3. Name your database.

5. On the Management Options wizard page, set up your backup information, as shown in Figure 4, and then click Continue.

   Note: There is a cost component to backing up your database. Obviously, once you go live with your application, you will want to make sure you regularly back up your data.

   
   Figure 4. Backup settings for your database instance

6. On the Review wizard page, confirm your information and launch the database instance, as shown in Figure 5. Then, click Launch DB Instance.

   
   Figure 5. Confirm and launch your database.

Your database instance is up and running! Now, you must set up security: Without it, you won't be able to connect to the database instance (see Figure 6).

Figure 6. The instance is launched.

Link your database to your AWS account, and then allow access to the database from you IP address, as shown in Figure 7.

Figure 7. Set up access to your database.

Note: In addition to the steps you just completed, you may want to read the Amazon RDS Getting Started Guide.

Connecting to the RDS Database Instance

Now that the database instance is up and running and security settings have been applied, you can verify connectivity. For this example, you build a connection using the following technologies:

• MySQL Workbench
• Microsoft Visual Studio 2010
• Microsoft Visual Studio 2008

MySQL Workbench

MySQL Workbench Community Edition is an open source solution available at no cost that provides database administrators (DBAs) and developers an integrated tools environment for database design and modeling, Structured Query Language (SQL) development (replacing MySQL Query Browser), and database administration (replacing MySQL Administrator).

Begin by downloading the MySQL Workbench, then start the workbench and add a new connection. To build the connection, you need to copy the endpoint from your Amazon RDS instance information (see Figure 8).

Figure 8. Get the instance endpoint.

Use the endpoint information to create your connection (see Figure 9).

Figure 9. Create the MySQL Workbench connection.

Note: Even though you may not need MySQL Workbench if you're working with Visual Studio, it is still useful to build a connection using MySQL Workbench just to verify connectivity and troubleshoot any issues.

You're now ready to use MySQL Workbench to connect to your database and start building your database schema. Let's create a customer table and add some sample data:

CREATE  TABLE `myDemoDB`.`Customer` 
    

    (

    

  `Id` INT NOT NULL AUTO_INCREMENT ,

    

  `Name` VARCHAR(45) NOT NULL ,

    

  `City` VARCHAR(45) NULL ,

    

  `DateAdded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    

    PRIMARY KEY (`Id`) );

    

    INSERT INTO `myDemoDB`.`Customer` (`Name`, `City`) VALUES ('Amazon 
    Inc.', 'Seattle, WA');
    

    INSERT INTO `myDemoDB`.`Customer` (`Name`, `City`) VALUES 
    ('Develop-One Inc.', 'Lincoln, ME');
    

    INSERT INTO `myDemoDB`.`Customer` (`Name`, `City`) VALUES ('Studio 
    B Productions, Inc.', 'Seattle, WA');
    

Visual Studio 2010

As a developer creating C# solutions with Visual Studio 2010, you will most likely be using either ADO.NET and/or the ADO.NET Entity Framework to access relational data sources. Accessing Amazon RDS is no different than accessing any other relational store. It all starts with two steps:

1. Install the MySQL .NET connector.
2. Create the connection string.

To connect your Microsoft .NET application to a MySQL database, you must install the MySQL .NET Connector, which is a free, open source, fully managed ADO.NET driver for MySQL.

Note: A free version of Visual Studio 2010 called Visual Studio 2010 Express is also available for download.

After installing the ADO.NET driver for MySQL, you're ready to build a data connection. Start Visual Studio, and then go to Server Explorer. Right-click Data Connections, and then click Add Connection to invoke the connection wizard. Change the data source to MySQL Database, as shown in Figure 10.

Figure 10. Change the data source to MySQL Database.

Next, provide the endpoint of your database instance as the name of the server, and specify your user name and password. You can test the settings by clicking Test Connection, as shown in Figure 11.

Figure 11. Set the connection properties.

You can now use Server Explorer to browse your database instance as if it were any other database. The customer table and columns will be displayed, and you can use Server Explorer to add to, modify, or delete from your database schema.

Visual Studio 2008

Visual Studio 2008 offers the same features with regard to ADO.NET Entity Framework and ADO.NET as Visual Studio 2010 but only if you install Visual Studio 2008 Service Pack 1.

Using the Entity Framework with Amazon RDS

Having established a data connection to Amazon RDS from within Visual Studio 2010, you can now access the database from within your application using the ADO.NET Entity Framework:

1. Open a C# project, and choose to add a new item of type ADO.NET Entity Data Model (see Figure 12).

   
   Figure 12. Add a model.

2. On the Choose Model Contents wizard page, select Generate from database, and then click Next (see Figure 13).

   
   Figure 13. Generate the model from a database.

3. On the Choose Your Data Connection wizard page, select the connection to your Amazon RDS database from the list box., as shown in Figure 14.

   
   Figure 14. Choose your data connection.

4. Select the Save entity connection settings in App.Config as check box, and then supply a name for settings. Click Next.

   Selecting this check box allows you to easily configure a different connection once the application has been deployed.

5. On the Choose Your Database Objects wizard page, select the database objects you want to you in your application, as shown in Figure 15.

   The Entity Data Model Wizard uses the connection to retrieve all existing tables, views, and stored procedures from your Amazon RDS database.

   
   Figure 15. Choose your data objects.

The model has been generated, as shown in Figure 16, and your table is now available as an entity in your application. Time to write some code!

Figure 16. Choose your data objects.

The following code sample demonstrates how you can use the Entity Framework to select and update data in the Amazon RDS database. If you are familiar with the Entity Framework, you'll see that this code is no different from what you would use to access any other database.

Each method uses the Entity Framework object context to build a Language Integrated Query (LINQ) statement. The statement is executed, which means that the LINQ expression tree is parsed and transformed into an SQL statement. The result of the SQL statement is projected onto the entities in the object context (in this case, the Customer entity), resulting in a Microsoft .NET Framework object against which you can program your logic. Calling the SaveChanges() method tells the Entity Framework object context to generate and execute SQL statements to propagate any changes in the object context back to the database.

/// <summary>
/// Find a list of customers based on part of their name.
/// </summary>
/// <param name="name">Part of the customer name.</param>
/// <returns>List of customer entities.</returns>
public List<Customer> FindByName(string name)
{
    // CustomerEntities uses the Amazon RDS connection info
    using ( CustomerEntities db = new CustomerEntities() )
    {
        // create an Entity Framework query
        var query = from c in db.Customers
                    where c.Name.Contains( name )
                    select c;
 
        // execute query and return a list of customer objects
        return query.ToList(); 
    }
}
/// <summary>
/// Customers can move to a new city.
/// </summary>
/// <param name="customerId">Customer identifier.</param>
/// <param name="city">City that the customer is moving to.</param>
public void Move(int customerId, string city)
{
    using ( CustomerEntities db = new CustomerEntities() )
    {
        // execute query and tell Entity Framework to expect a single result.
        var customer = ( from c in db.Customers
                            where c.Id == customerId
                            select c ).Single();
 
        // customer is moving to new city
        customer.City = city;
 
        // write changes back to Amazon RDS
        db.SaveChanges();
    }
}

Using ADO.NET to Connect to Amazon RDS

The Entity Framework is an object relational mapper built on top of the ADO.NET base classes. Sometimes, you may not have a need for an object relational mapper but instead want to have direct access to the database to execute SQL statements. You can do this using the ADO.NET implementation provided by the MySQL .NET Connector libraries.

Adding a reference to the MySQL.Data assembly provides you with a range of classes accessible through the MySql.Data.MySqlClient namespace. Classes like MySqlConnection and MySqlCommand, which implement the abstract framework classes DbConnection and DbCommand, allow you to execute SQL statements against your Amazon RDS database.

The following code sample shows how to execute an SQL statement using the MySqlConnection, MySqlCommand, and MySqlParameter classes.

using MySql.Data.MySqlClient;
using RDS.Sample.DAL.Properties;
 
class SqlDB
{
 
/// <summary>
/// Delete a customer using core ADO.NET implementation
/// provided by the MySQL .NET Connector library.
/// </summary>
/// <param name="customerId"></param>
public void Delete(int customerId)
{
    // create a connection to Amazon RDS based on project setting
    using ( var conn = new MySqlConnection( Settings.Default.ConnectionString) )
    {
        // create a parameterized command
        var cmd = new MySqlCommand( "delete customer where id = @id", conn );
 
        // create parameters
        var par = new MySqlParameter("id", MySqlDbType.Int32 );
        par.Value = customerId;
        cmd.Parameters.Add( par );
 
        // open connection to Amazon RDS database
        conn.Open();
 
        // execute query
        cmd.ExecuteNonQuery();
 
        // close the connection
        conn.Close();
    }
}
 
}

Note: It is also possible to use the OleDB classes in the System.Data.OleDB namespace of the Microsoft .NET Framework to connect to the Amazon RDS database.

Summary

This article provided a view of the steps involved for a C# developer to start using an Amazon RDS database instance, starting with provisioning the database instance to building an Entity Framework model to access and update the database. If you need to execute SQL statements directly, you can use the MySQL .NET Connector classes or the managed OleDB database drivers to connect to the Amazon RDS database instance.

Learning More About Amazon RDS

The following resources provide more information about Amazon RDS:

• Amazon RDS Getting Started Guide
• Amazon RDS User Guide
• Introducing Amazon RDS
• Amazon Web Services blog

About the Author

Mark Blomsma is a software architect and owner of his own business, Develop-One. He is a part-time instructor for DevelopMentor and a frequent speaker and writer of articles for a variety of magazines. Mark specializes in Microsoft .NET technology, enterprise application development, application integration, and software renovation. Visit his blog at http://blog.develop-one.com.