Creating a Windows Azure SQL Database

Introduction

Windows Azure SQL Database does not have support for REST API. The easiest way to connect to a database is through ADO.NET and System.Data.SQLClient namespace. To connect to a database, you need to have a database in a Server in Windows Azure.

You can use the Windows Azure SQL Database by connecting to it through a connection string. The functionality is the same as other local databases but the connectivity depends on network.

Creating a Windows Azure SQL Database

Login to your Windows Azure management portal.

Now to create a database, click on the NEW button in the bottom left corner.

Then, it will show you a wizard.

Now choose DATA SERVICES from the first pane, then select SQL DATABASE. Then select QUICK CREATE. After you click QUICK CREATE, it will show you a dialog, to enter name, select server and data center’s region for your database.

Enter the name for your database, and choose New SQL database server in the SERVER if you don’t have any. Choose a login name which will be your username to access the database. Enter a strong password to meet the following requirements.

The password must meet the following requirements:

• Is more than eight characters in length
• Does not contain all of the login name
• Contains characters from at least three of the following categories:
  • English uppercase characters (A through Z)
  • English lowercase characters (a through z)
  • Base 10 digits (0 through 9)
  • Non-alphanumeric characters (for example: !, $, #, %)

Then click CREATE SQL DATABASE. Then you will see the following process.

When the process is completed, you will see your database name in ALL ITEMS list in management portal.

Now your database is created successfully! The next step is to allow your IP address for your client app to connect to the server.

Managing Firewall

To allow your client app to connect to your database, you need to add client pc’s IP address to allowed list. To do so, click on SQL DATABASES in management portal.

You will see a list of current databases in your Management Portal. Click on Cars database.

Click on DASHBOARD in menu. Then click on “Manage allowed IP addresses” link in QUICK GLANCE.

Then click on ADD TO THE ALLOWED IP ADDRESS against your CURRENT CLIENT IP ADDRESS.

Then click on SAVE button in the bottom bar.

Now you are done managing your database connectivity settings. You can now connect your database to your client app.

Make Some Changes to Database

Now to check the connectivity to the database, we need to add some table to the database. To do so, click on DATABASES. Then click on Cars database.

Now click on “Design your SQL database” link in Cars database management window.

Now a new Window will open. Put your username and password you entered for your database in this window. And click Log On button.

Now you will see the SQL DATABASE Management Portal. Click on DESIGN in the bottom left corner.

Now click NEW TABLE, to create a new table in database.

Now click Is Identity? Against column ID. And make columns as shown in the image below. And then click SAVE.

Now click DATA. Add some rows of data and then click SAVE.

Checking Database Connectivity

Now to check connectivity to database from your app, copy Connection String from your Cars database DASHBOARD.To do so, go to DASHBOARD and click Show Connection Strings in QUICK GLANCE.

Copy the ADO.NET Connection String

And replace {your_password_here} with the password your entered in credentials of database.

Now make a simple console application in Visual Studio and enter this code in the Main() function.

try
{
   
Console.WriteLine("Creating Connection");
    var conn = new
SqlConnection(/* CONNECTION STRING HERE */ );
    var cmd = new
SqlCommand("Select * From Manufacturers", conn);
 
   
Console.WriteLine("Opening Connection!");
    conn.Open();
    Console.WriteLine("Connection
Opened");
 
   
Console.WriteLine("Executing Query");
    var result =
cmd.ExecuteReader();
   
Console.WriteLine("Query Executed!");
 
               
Console.WriteLine("Manufacturers found in database:");
    while
(result.Read())
    {
        object[]
rowArr = new object[result.FieldCount];
       
result.GetValues(rowArr);
       
Console.WriteLine("\t"+rowArr[1]);
    }
 
   
Console.WriteLine("Closing Connection!");
    conn.Close();
} 
catch (Exception ex)
{
    Console.WriteLine("Error
Occured\n"+ ex.Message);
}
Console.Read();  

Now run the application. It give the following output, which means that everything is running OK and the SQL query is run against the server successfully, returning the rows of data.