Azure: SQL Azure

This is a new post in a series of beginners' articles on how to do things in Azure. This series will be for absolute beginners, and if you are not one of those, this will not be for you.

You can find the complete set of posts that make up this series here:

• http://sachabarbs.wordpress.com/azure/

This time, we will look at how to create a new SQL server database within Azure (in a later article, we will look using Microsoft's NoSQL database “DocumentDB”).

Anyway so step 1 is to open up the portal:

• https://manage.windowsazure.com

From there, you can click on “SQL Databases” and choose the “Create a SQL Database” hyperlink:

From there, you need to fill in your preferences within the wizard:

Once this wizard has completed, you will see a new database has been created.

IMPORTANT: When the database is created, you will need to ensure that the standard port 1433 is opened. One of the easiest ways to do that is to use the Azure portal to query the database (even though there are no tables in the database yet)

This little cheat will prompt you to open up the Firewall ports, which is great, let's just let the Azure portal do this work for us.

So once the port is open, you will be redirected to an app in the browser (Silverlight app at present), that allows you to use the connection details you chose.

When you successfully connect, you should see something like this:

Now there is no data in the SQL database yet. We could use this Silverlight app to add some tables, and data. However, I would prefer to do that in Visual Studio, so let's go back to the portal, and open the connection strings, as shown below:

We are interested in the ADO.NET one, where the part I have highlighted is the important part you need

SO grabbing the connection address to the Azure SQL server instance, let's connect via Visual Studio, and create a table:

Once you have a connection in Visual Studio, let's create a new table using the context menu:

When you are happy with the table, click the “Update” button which will push the changes to Azure. This is only a demo, for a real app, you would likely have some sort of scripts, or would use the Entity Framework migrations facility to manage changes.

So now let's check everything worked by connecting to the Azure SQL database from SQL server management studio.

As we can see, we see the table we just created above:

And let's also check the Azure portal query app.

Yep, the table looks good, there is no data there yet, as expected for a new table. So let's now turn our attention to getting some data into the new table.

Let's use a new Entity Framework model to talk to the new SQL Azure database/table we just created.

I went with the defaults but you can choose what you like:

This will result in a few files being created in the demo app, such as these, as well as an entry in the App.Config file to point to the SQL Azure database instance.

And here is some code that will do some basic CRUD operation using the Entity Framework context that was created for us.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace SQLAzureTest
{
    class Program
    {
        static void Main(string[] args)
        { 
            //insert
            using (var sachaAzureSQLEntities = new SachaAzureSQLEntities())
            {
                sachaAzureSQLEntities.Orders.Add(new Order()
                {
                    //note we are spelling this wrong so we can update it later
                    Description = "Buzz Lighyear toy",
                    Quanitity = 1
                });
                sachaAzureSQLEntities.SaveChanges(); 
 
                //select
                var order = sachaAzureSQLEntities.Orders.Single(
                    x => x.Description == "Buzz Lighyear toy");
                Console.WriteLine("Order : Id: {0}, Description: {1}, Quanity {2}",
                    order.Id, order.Description, order.Quanitity);
 
                //update
                order.Description = "Buzz Lightyear toy";
                sachaAzureSQLEntities.SaveChanges();
 
                var exists = sachaAzureSQLEntities.Orders.Any(
                    x => x.Description == "Buzz Lighyear toy");
                Console.WriteLine("Buzz Lighyear toy exists :  {0}", exists);
 
                order = sachaAzureSQLEntities.Orders.Single(
                    x => x.Description == "Buzz Lightyear toy");
                Console.WriteLine("Order : Id: {0}, Description: {1}, Quanity {2}",
                    order.Id, order.Description, order.Quanitity); 
 
                //delete
                sachaAzureSQLEntities.Orders.Remove(order);
                sachaAzureSQLEntities.SaveChanges();
 
                Console.WriteLine("Orders count :  {0}", 
                    sachaAzureSQLEntities.Orders.Count()); 
            }
 
            Console.ReadLine();
        }
    }
}

And here are the results of this against the SQL Azure instance we just created.