DotNetCurry Logo

Simple .NET Database Programming using SQL Azure

Posted by: Mahesh Sabnis , on 6/3/2012, in Category Windows Azure
Views: 63593
Abstract: SQL Azure is a highly available and scalable cloud database service built on SQL Server technologies. In this article, we will see how simple it is to create a database using SQL Azure and create a .NET application that connects to it

Windows Azure provides a high scalable and fault-tolerant environment that lets developers create powerful distributed applications without the overhead to maintain and configure hardware and operating systems. The Azure platform allows organization to deploy their applications which needs to be available 24X7X365 and accessible world-wide without any downtime. This platform facilitates storage like Blob, Table, and Queue etc. However when organizations want to store their application transactional data using a transactional persistent storage mechanism, then a database server is required.

To manage transactional data, Azure provides the SQL Azure service which allows developers to create Databases, Tables, Views and Stored procedures on the cloud. SQL Azure is a highly available and scalable cloud database service built on SQL Server technologies. For those who are comfortable using SQL Server Management studio, an integration with the SQL Azure portal is provided using which Database, Tables, Views, Stored Procedures can be created.

Apart from Database and its objects creation, developers can use their regular ADO.NET, ADO.NET EF coding styles to interact with these databases. So no additional learning curve is required.



Working with SQL Azure and Creating Database

Step 1: Visit the Azure Portal You need to log-In using your ‘Windows Live Id’. For this purpose you should have a subscription. Once you log-in, you will see a screen similar to the one below:


Click on Database and you will get a Page as shown below:


Step 2: Click on the Create icon


A database will be created in the Data Center, so the wizard will prompt you to enter the region where you want to create the database


Select the region as per your requirements and click on ‘Next’. You will be asked about the Database administration information. This is the Admin user used to create Database and its objects.


On clicking ‘Next’, you will asked the access rules. Here you need to specify the ‘IP Adddress’ ranges from which you can make call to the server, where the database is available. You need to make sure that the port ‘1433’ must be open to make call to SQL Database over the cloud from your SQL Server Management Studio or Visual Studio 2010 projects. Generally this IP address is from the line of your corporate network or it is of your machine. You also need to check the checkbox specifying that ‘Allow other Windows Azure Services to access this server’. This is for all other applications from your organization hosted on the Azure. The screen looks similar to the following


Specify the rule as shown below:


Once you enter the details and click on OK, the rule will be added.

Step 3: Go back to the SQL Azure portal. Now if you click on the ‘Refresh’ icon on the ribbon (not browser refresh), you will get the default master database as shown below:


To test the connectivity, click on the ‘Test Connectivity’ icon on the Ribbon:


This provide the Login Page where you need to enter your admin credentials


Once the login is successful, a SQL Administration page will be displayed as shown below:


Step 4: To create a database, click on the ‘New Query’ icon. You will see two editor boxes where you can write commands to create Database. On clicking the ‘Run’ button, the command will be executed as below:



Go back to the SQL Azure portal page and click on the ‘Refresh’ icon on the Ribbon to see the database we just created


Once again go to the SQL Administration page and click on ‘Administration’. A Database summary is shown


Step 5: To design Table, click on the ‘Design’ on the Administration page, you will get Database Object Creation view for Tables, Views and Stored Procedures as below:


Click on ‘New Table’, as in the above figure. The Table Creation UI will appear


This is so user-friendly, ain’t it? Here you can specify the Column Name, its Data Type and constrains as shown below:


So the final Table Structure will be as shown below:


Now if you go back to the SQL Azure Page, you will find some database properties as shown below:


Now to write the client code from your .NET, VB, Java or a PHP based application, you require connection strings. So click on the (…) button next to ‘View’ as seen in the above figure. A list of connection strings will be displayed


You can also create Tables using Queries as shown below:


To create Stored Procedure, click on the Stored procedure and you get a nice Stored Procedure UI


Those were some basics about creating some tables on the cloud.


Connecting to Azure From the SQL Server 2008 R2 Management Studio

Step 1: Open SQL Server Management Studio and click on ‘Connect’ in the object Explorer. You need to enter the Database server and credentials information as shown below:


The object Explorer will show the List of Database objects


You can create databases, tables etc. using Management studio just like your usual Sql Server programming.


Using Sql Azure in the .NET client application

I mentioned in the beginning of this article that to consume the SQL Azure service, you need not to learn anything new, you can simply use traditional ADO.NET or ADO.NET EF programming principles. In this part of this article, we will see how to make use of ADO.NET EF to connect to SQL Azure.

Step 1: Open VS2010 and create a new ASP.NET application. In this application, add a new ADO.NET EF model. The wizard will prompt a screen where you need to create connection with the SQL Azure server as below:


Step 2: Add a new Web Form in your ASP.NET Application and design it as shown below:


Step 3: Write the following code in button click event and in the seletedindexchanged event of the dropdownlist

public partial class Page_Sql_Azure_EF : System.Web.UI.Page
    CompanyEntities objContext;
    protected void Page_Load(object sender, EventArgs e)
        objContext = new CompanyEntities();
        if (IsPostBack == false)

    protected void btnNew_Click(object sender, EventArgs e)
        txtdno.Text = "";
        txtdname.Text = "";
        txtloc.Text = "";

    /// <summary>
    /// Method to Insert Record
    /// </summary>
    protected void btnAdd_Click(object sender, EventArgs e)
        Department objDept = new Department();
        objDept.DeptName = txtdname.Text;
        objDept.Location = txtloc.Text;


    /// <summary>
    /// method to Update Record
    /// </summary>
    protected void btnUpdate_Click(object sender, EventArgs e)
        var dept = Session["Department"] as Department;
        var deptToupdate = objContext.Departments.First(d => d.DeptNo == dept.DeptNo);
        deptToupdate.DeptName = txtdname.Text;
        deptToupdate.Location = txtloc.Text;

    /// <summary>
    /// Method to delete Record
    /// </summary>
    protected void btndelete_Click(object sender, EventArgs e)
        var dept = Session["Department"] as Department;
        var deptToDelete = objContext.Departments.First(d => d.DeptNo == dept.DeptNo);

    /// <summary>
    ///Method to Search a specific Record 
    /// </summary>
    protected void lstdno_SelectedIndexChanged(object sender, EventArgs e)
        int dno = Convert.ToInt32(lstdno.SelectedValue);
        var dept = objContext.Departments.First(d => d.DeptNo == dno);
        txtdno.Text = dept.DeptNo.ToString() ;
        txtdname.Text = dept.DeptName;
        txtloc.Text = dept.Location;

        Session["Department"] = dept;

    /// <summary>
    /// Helper method to Load data in the DropdownList
    /// </summary>
    void LoadData()
        var Departments = objContext.Departments.ToList();
        lstdno.DataSource = Departments;
        lstdno.DataValueField = "DeptNo";
        lstdno.DataTextField = "DeptName";

    /// <summary>
    /// Helper method to read the last record (recently) added in the Department Table
    /// </summary>
    void GetLastRecord()
        var Departments = objContext.Departments.ToList();
        var dept = (from d in Departments
                    where(d.DeptNo == (Departments.Max(d1 => d1.DeptNo)))
                    select d).Single();
        txtdno.Text = dept.DeptNo.ToString();

If you carefully observe the above code, you will not find any change from your regular .NET database programing. This is the regular code which we write for performing DB operations using ADO.NET EF. So one important thing I want to specify here is that just by changing the connection string in your config file to the SQL Azure database, you can connect to the cloud database without recompiling your application.

On the page, configure the GridView DataSource to EntityDataSource and browse the Page


Conclusion: SQL Azure provides a more reliable and manageable mechanism to maintain application data over the cloud which can be accessible from anywhere without any downtime.

Recommended Articles

Page copy protected against web site content infringement by Copyscape

Leave us some feedback
comments powered by Disqus
Comment posted by PAVAN KUAMR BAKKU on Monday, August 12, 2013 5:12 AM
Why did you connect to SQL SERVER 2008.i think it is need not.

Do we not use database from windows azure directly?
if, we can ,please suggest me, how to write stored procedure and how to use that in visual studio c# dot net??????????????????????

Waiting for your answer................
Comment posted by Preethi on Thursday, February 12, 2015 12:56 PM
how to access Azure sql in
Free DNC .NET Magazine