2015-10-16



alvinashcraft
shared this story
from Canadian Developer Connection.

This post shows you how to create a ClearDB MySQL Database on Azure and how to create a table in the database using the My SQL Workbench client tool. Students get free Azure benefits through DreamSpark, one of those benefits is a MySQL Database.

This post will cover:

Pre-requisites

Creating your MySQL Azure database

Connecting to an Azure MySQL Database from MySQL Workbench

Creating a table using MySQL Workbench

Connecting to your MySQL Database from code

Pre-requisites

You will need an Azure account

students can get a free Azure account through DreamSpark that includes the ability to create a 20 MB MySQL database at no charge. Instructions on how to activate that account are located here

Startups may qualify for free Azure through BizSpark (www.bizspark.com)

Anyone can get a one month free trial of Azure here

You will need to install My SQL Workbench (or another client tool that can connect to a My SQL database.) You can find a link to download and install MySQL Workbench here

Creating your My SQL Azure database

Go to the Azure portal (www.azure.com) and select PORTAL

When you select PORTAL you will be brought to a login screen, use your Azure account and password to log in
(TIP: As mentioned in the pre-requisites, you need an Azure account to complete this workshop. students can get a free Azure account through DreamSpark that includes the ability to create a 20 MB MySQL database at no charge. Anyone can get a one month free trial of Azure here )

On the portal page select + NEW | Data + Storage | MySQL Database

Specify the properties for your database

Database name – this is the name of the database you are creating, when you connect to the database from code or MySQL Workbench this is the name of the database you will connect to and where you will create and populate tables

Pricing Tier – if you are just trying this out, you can use the Free pricing tier (Mercury) with a cost of 0.00$ /month. At the time this document was created that provided 20 MB of storage, and allowed up to 4 database connections at a time. Great for exploring and trying out a database. If you are going to use this for a production application, you can always upgrade to a higher priced tier later.

Resource Group – a resource group is just a way of grouping different services and products you have in Azure together. If you have already created other things in Azure you can add this database to your existing group. If this is your first time using Azure just create a new resource group and give it a name

Subscription – This will be the name of your Azure subscription

Location – This is the physical data center where your database will be created, pick a location geographically close to you

Legal Terms – This is the page where you agree to the terms and conditions of the service

Pin to Startboard – this option allows you to create a tile on your Azure portal start page. It gives you an easy way to go see the database properties in Azure when needed.

When you have specified values for each of these properties select Create to create your database

Once your database created you can select BROWSE ALL | MYSQL databases and you will see your database listed as status Healthy.

Congratulations! You have created a MySQL Database on Azure!

Our next step is to connect to it from the MySQL Workbench tool so we can create a table to store records.

Connecting to an Azure MySQL Database from MySQL Workbench

The Azure portal is designed to help you create and manage resources and services on Azure, it is not a full database management tool. If you create a database on Azure, you will want to use a client tool such as SQL Server Management Studio (for SQL Server on Azure), or MySQL Workbench (for MySQL databases on Azure). You install these tools on your PC or laptop but you can connect them to the database you created on Azure.

I have installed MySQL Workbench, a popular client tool for managing MySQL databases. If you do not have it installed, you can download it here.

When you first open MySQL Workbench you see a screen listing the database connections you have set up. If you don’t have any database connections the screen looks like this:

You need to click on the + sign to create a connection to your MySQL database on Azure

You will get a popup window that asks for the following fields, some fields we will need to look up in the Azure portal, we’ll look at how to do that shortly!

Connection name: This is a name you assign to the connection so you can remember the database it supports

Connection method: You can leave this at the default Standard (TCP/IP)

Hostname: specifies the server where your database is hosted, we can look this up in the Azure portal.

Port: specifies the port number for your database, we can look this up in the Azure portal.

Username: specifies the username to use to connect to the database, we can look this up in the Azure portal

Password: specifies the password for the connection, we can look this up in the Azure portal

Default Schema: You can leave this blank

You can leave the SSL and Advanced properties at their default values

Retrieving the Hostname and port from the Azure portal

Select the database from the list of MySQL databases and then on the database page select All settings

Now select Properties from the Settings panel

The Properties panel will appear and you will see fields that display the Hostname, Port, Username and password for your connection!

Return to MySQL Workbench.

Enter the Hostname, Port and Username you copied from the Azure portal in the Connection fields in MySQL Workbench.

You will be prompted to enter the password when you try to connect.

Select Test Connection, to see if MySQL Workbench can successfully connect to your database on Azure

A window will pop up asking you for the password. Copy the password from the Azure portal to the Password field.

You may choose to save the password in the vault for this connection, by selecting the checkbox Save password in vault. If you do this your password is saved and you don’t have to type it in every time you connect.

Select OK to test your database connection

You should see a popup window telling you your Connection parameters are correct. If not double check the values you copied from the Azure portal to the MySQL Connection properties.

Once you see the popup message telling you Connection parameters are correct, select OK to return to the Setup New Connection popup window

Select Ok to create the new connection

You should see your connection listed on the home page in MySQL Workbench

Congratulations! You have created a connection to your MySQL Database on Azure from MySQL Workbench!

Now let’s try creating a table and executing a SQL command to insert a row in the table just to prove to ourselves we now have a database and we can start creating our tables and records!

Creating a table using MySQL Workbench

Select the connection on the MySQL Workbench homepage you created during the last step. This will bring up the SQL Editor for your database.

In the Navigator pane on the left, under Schemas, double click on the name of your database and then expand the database so you can see the object in the database.

Double clicking on the database name makes your database the default database for SQL commands you issue in the SQL editor. You will know you have successfully set it as the default when the database name appears in bold font.

Right click Tables under your database name and select Create Table to create a new database table in your database

Specify the name of your table, as well as all your database columns and properties. Although you can enter a mix of upper and lower case in the fields, the table names and column names will always be created in lowercase. I have created a sample table to hold questions for a quiz application in the example below.

Select Apply to create your table

A page showing the SQL statement that will create your table is displayed.

Select Apply to run the SQL statement and create your table.

You will see a progress window as the SQL statement executes.

When the SQL statement completes execution you will see a message saying your SQL script was successfully applied to the database.

Select Finish to return to the SQL Editor.

If you expand the tables for your database in the navigator pane you can now see your table listed

Congratulations! You have just created a table in your MySQL Database on Azure.

You can now start adding, updating, and deleting rows using the SQL editor or using application code on a website, mobile app, or any other application!

Connecting to your MySQL Database from code

At some point you will probably want to connect to your database so you can add, update, and delete records using code!

Here is an example of how to connect to your MySQL database using C# code in Visual Studio

You will need to complete the following steps

Add a reference to the MySQL Data library

Go the Solution Explorer in Visual Studio and add a reference to your project

Search for MySql.Data and select the most recent package from the search results and select OK to add the reference.

Import the library to your code by adding a Using statement

Create a database connection to the MySQL database on Azure

In order to create a connection you will need to specify the

Server

Database

Uid

Pwd

We can retrieve all this information from the Azure portal

Go the Azure portal, log in with your Azure account. Select BROWSE ALL | MYSQL databases and select the MYSQL database you wish to connect to from the list of MYSQL databases

From the MySQL Database page select All settings | Properties to bring up the properties for your Azure MYSQL database

On the properties pane

DATABASE NAME corresponds to the Database of your connection

HOSTNAME corresponds to the Server of your connection

USERNAME corresponds to the Uid of your connection

PASSWORD corresponds to the Pwd of your connection

You can click on the clipboard icon next to the fields to copy the values from the Azure portal to your clipboard.

Now we can create and open a connection to our database in our C# Code

Execute a command on your database connection

Just to make sure our code is working, let’s execute an INSERT statement and see if the record successfully inserts into a database table on our MYSQL database.

My database has a questions table with the following columns:

questionId

question

answer

points

To add a record to this table with SQL we would issue the following command

In C# we can just create a MySQLCommand and pass it a SQL statement to execute and an open connection

When you are finished your complete code looks something like this:

PLEASE NOTE! The code above is simply a test to prove that we have successfully connected to our database, this is not how I would write production code! I would store the username and password information in a configuration file, and I would use parameters to specify the values to insert in the database table to avoid SQL injection attacks. There are lots of great ways to execute commands against a database safely, the code snippet above is just a simple code example to illustrate the proof of concept.

If I connect to my database from MYSQL Workbench I can execute a SELECT statement and see the inserted row

Congratulations! You have written code that connects to your MYSQL Database on Azure!

This particular example used C#, but you can connect to your MYSQL database on Azure from just about any programming language!

Happy coding!

Show more