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

image

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

image

image

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

image

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)

image

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

image

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

image

When you successfully connect you should see something like this

image

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

image

image

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

image

SO grabbing the connection address to the Azure SQL server instance, lets connect via Visual Studio, and create a table

image

Once you have a connection in Visual Studio, lets create a new table using the context menu

image

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

image

image

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

image

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

image

And lets also check the Azure portal query app

image

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.

image

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

image

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

image

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.

image

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: