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 us this series here :

https://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 loot using Microsofts 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 is no tables in the database yet)

This little cheat will prompt you to open up the Firewall ports, which is great, lets 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 your 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 lets 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, lets connect via Visual Studio, and create a table

Once you have a connection in Visual Studio, lets 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 lets 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 lets 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 lets now turn our attention to getting some data into the new table.

Lets 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 is the results of this against the SQL Azure instance we just created.