Simple .NET Database Programming using SQL Azure

Posted by: Mahesh Sabnis , on 6/3/2012, in Category Windows Azure
Views: 55703
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 www.azure.com. 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:

i0-portal-view

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

i0-db-portal-page

Step 2: Click on the Create icon

i1-create-new-database

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

i2-select-region

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.

i3-specify-credentials

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

i4-access-rules

Specify the rule as shown below:

i5-rule

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:

i6-master-db

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

i7-manage

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

i8-db-login

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

i9-admin-provide

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:

 

i11-create-database

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

i12-db-created-refresh

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

i13-summary

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:

i14-tb-design

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

i15-new-table

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

i15-tb-datatypes

So the final Table Structure will be as shown below:

i16-tb-created

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

i17-db-info

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

i18-connection-strings

You can also create Tables using Queries as shown below:

i19-creating-query-programatically

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

i23-create-sp-code

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:

i25-connect-using-ssmo

The object Explorer will show the List of Database objects

i26-object-explorer

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:

i24-fron-vs2010

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

i27-aspnet-page

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)
        {
            LoadData();
        }
    }

    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;

        objContext.AddToDepartments(objDept); 
        objContext.SaveChanges();
        LoadData();
        GetLastRecord();
    }

    /// <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;
        objContext.SaveChanges();
    }

    /// <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);
        objContext.DeleteObject(deptToDelete);
        objContext.SaveChanges();
    }

    /// <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";
        lstdno.DataBind(); 
    }

    /// <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

i28-result

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.

Give me a +1 if you think it was a good article. Thanks!
Recommended Articles


Page copy protected against web site content infringement by Copyscape


User Feedback
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................

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel