Windows Azure: Effective use of the Storage and SQL Azure

Posted by: Mahesh Sabnis , on 7/20/2012, in Category Windows Azure
Views: 31737
Abstract: This article explains with an example how to use Azure Storage and SQL Azure effectively to save costs while dealing with BLOB and transactional data

Developers  working on large scale enterprise applications often have doubts about the selection of Storage services and SQL Azure under certain cases. The doubts are usually around requirements where you want to store binary contents like Images, PDF etc. and other docs in a persistent and transactional data store, along with its information. For e.g. if a customer applied for a loan online, then all documents and customer information should be saved in the transactional database. But the problem is that because of the size of the document, the database ends up consuming a lot of space. Another requirement is to fetch data using a single fetch operation and at the same time, also follow some guidelines on the use of Azure for such scenarios.

 

For requirements as described above, I usually suggest the use of SQL Azure for storing customer information and Blob storage for binary file storage. In this article, I will highlight some guidelines to follow in similar scenarios. I have created a scenario where on SQL Azure, I have the following Table for storing Image data. This table stores image information e.g. Create By, Created Data and Image Name, but the actual Image is stored using Blob Storage. Make sure you read my article on Simple .NET Database Programming using SQL Azure to understand some basics

azure-image-table

The Architecture of the application is as shown below:

azure-app-design

The client application stores the Image information in the Database server and the actual Image is uploaded to the BLOB.

Designing the Application

Step 1: Open VS2010 and create new Cloud project of the type ASP.NET Web Role. Name it as ‘Cloud_Effective_Storage’.

Step 2: In the ASP.NET project, add a new WebForm, name it as ‘Page_SaveData.aspx’. Design the WebForm as shown below:

azure-webform

The ImageName is a File Upload Control. Since the Blob requires blob container, the button ‘Create Container’ event creates container for the blobs.

Step 3: Open the code-behind file and declare the following objects at Page class level:

SqlConnection Conn;
List<ImageInfo> Images;

Write the following code in the Page_load event. This code reads the cloud configuration and defines an instance of the sql connection which connects to sql-azure as shown below:

protected void Page_Load(object sender, EventArgs e)
{
     Conn = new SqlConnection("Server=tcp:[MyServer].database.windows.net,1433;
                Database=Company;User ID=[myuser@MyServer];Password=[MyPassword];
                Trusted_Connection=False;Encrypt=True;Connection Timeout=30");
     Images = new List<ImageInfo>();
    if (this.IsPostBack == false)
    {
        //Informs that Read the Configuration form .csdef
        CloudStorageAccount.SetConfigurationSettingPublisher((configName, configSetter) =>
        {
            configSetter(RoleEnvironment.GetConfigurationSettingValue(configName));
        });
    }
}

Note: Values in ‘[]’ are placeholders.

Step 4: Now a major task here is to upload the image in the blob. To perform this task, we need to create a Blob using CloudStorageAccount as shown below:

/// <summary>
/// Helper method to create Blob Storage if it is not present
/// </summary>
void CreateBlobIfNotExist()
{
    //S1: Read the  Development Storage
    CloudStorageAccount account =
    CloudStorageAccount.FromConfigurationSetting("DataConnectionString");
    //S2: Create a Blob Client
    CloudBlobClient blobClient = account.CreateCloudBlobClient();
    //S3: Get the reference to the Container
    CloudBlobContainer container =
    blobClient.GetContainerReference("imagesstore");
    //S4:Create a Container
    container.Create();

}

The above code uses the DataConnectionString from the configuration file. This represents the Storage to be used to store the blob. Call the above helper method on the Click event of the ‘Create Container’ button:

protected void btncreatecontainer_Click(object sender, EventArgs e)
{
    //Call to Create Blob
    CreateBlobIfNotExist();
}

 

/// <summary>
/// Helper Method to Upload Image into Blob
/// </summary>
bool UploadImageToBlob()
{
    bool Uploaded = false;
    try
    {
        //S1: Read the Local Development Storage
        CloudStorageAccount account =
            CloudStorageAccount.FromConfigurationSetting("DataConnectionString");
        //S2: Create a Blob Client
        CloudBlobClient blobClient = account.CreateCloudBlobClient();
        //S3: Get the reference to the Container
        CloudBlobContainer container = blobClient.GetContainerReference("imagesstore");
       
         //S4:Get the Blob Reference by Reading the FileName from the File Upload Control
        var blob = container.GetBlobReference(fileimg.PostedFile.FileName);
        ViewState["BlobName"] = fileimg.PostedFile.FileName;
        //To Avoide deplicate entry in Blob Get All Blobs
        var blobs = container.ListBlobs();
        if (blobs.Count() > 0)
        {
            foreach (var b in blobs)
            {
                if (b.Uri == blob.Uri)
                {
                    lblInfo.Text = "The File is Already Present So cannot Add more";
                     Uploaded = false;
                }
            }
        }
        else
        {
            //   blob.Properties.ContentType = fileimg.PostedFile.ContentType;
            //S5: Upload the File as ByteArray
            blob.UploadFromStream(fileimg.FileContent);
            Uploaded = true;
           
        }
      
    }
    catch (Exception ex)
    {
        lblInfo.Text = ex.Message;
    }
    return Uploaded;
}

The above method first checks whether the file to be posted is already present in the container, if yes then it will not be uploaded. If the file is not present in the container, then it is uploaded. If the upload is successful, then the method returns true. The significance of this method is that if the upload is successful, only then the related information of the file will be saved in SQL Azure.

Step 6: Add the class in the code-behind. This class is used to store information of the image when it needs to be displayed in the GridView:

public class ImageInfo
{
    public int ID { get; set; }
    public string ImageCreatedBy { get; set; }
    public DateTime ImageCreatedDate { get; set; }
    public Uri ImageUri { get; set; }
}

Step 7: Write the following helper method to read information of the uploaded blob from Blob and from the SQL Azure Database

/// <summary>
/// Helper Method to Get Blob Data from Cloud
/// </summary>
void GetInfoFromSqlAzureAndBlob()
{
    try
    {
        //S1: Read the Local Development Storage
        CloudStorageAccount account =
        CloudStorageAccount.FromConfigurationSetting("DataConnectionString");
        //S2: Create a Blob Client
        CloudBlobClient blobClient = account.CreateCloudBlobClient();
        //S3: Get the reference to the Container
        CloudBlobContainer container = blobClient.GetContainerReference("imagesstore");

        //Set the Permission at Container Level for Public Access
        //So that the data storeg inside it can be accessed publically
        var permission = container.GetPermissions();
        permission.PublicAccess = BlobContainerPublicAccessType.Container;
        container.SetPermissions(permission);


        //First store Data in Sql Server
        SqlCommand Cmd = new SqlCommand();
        Conn.Open();
        Cmd.Connection = Conn;
        Cmd.CommandText = "Select * from ImageInfo";

        SqlDataReader Reader = Cmd.ExecuteReader();
       
        int i = 0;
        var BlobData = container.ListBlobs().ToArray();
     
        while ((Reader.Read()))
        {
            ImageInfo imgInfo = new ImageInfo();

            imgInfo.ID = Convert.ToInt32(Reader["ID"]);
            imgInfo.ImageCreatedBy = Reader["ImageCreatedBy"].ToString();
            imgInfo.ImageCreatedDate = Convert.ToDateTime(Reader["ImageCreatedDate"]);
            imgInfo.ImageUri = BlobData[i].Uri;

            Images.Add(imgInfo);
            i = i + 1;
           if (i == BlobData.Count())
            {
                break;
            }

        }
       Reader.Close();

        Conn.Close();

        //S4: List All Blobs Available in the Container
        gdvImageData.DataSource = Images;
        gdvImageData.DataBind();
    }
    catch (Exception ex)
    {
        lblInfo.Text = ex.Message;               
    }
}

Step 8: Write the following code in the click event of the ‘Upload File’ button:

protected void btnUploadFile_Click(object sender, EventArgs e)
{
    try
    {
        //First Check if the Upload is successfull
        if (UploadImageToBlob())
        {
            try
            {
                SqlCommand Cmd = new SqlCommand();
                Conn.Open();
                Cmd.Connection = Conn;
                Cmd.CommandText =
                "Insert into ImageInfo values(@ImageCreatedBy,@ImageCreatedDate,@ImageName)";
                Cmd.Parameters.AddWithValue("@ImageCreatedBy", txtimgcreatedby.Text);
                Cmd.Parameters.AddWithValue("@ImageCreatedDate", DateTime.Now.Date);
                Cmd.Parameters.AddWithValue("@ImageName", fileimg.FileName);
                Cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                lblInfo.Text = ex.Message;
                //Delete the Entry from the Blob Immediately
                DeleteBlob(ViewState["BlobName"].ToString());
            }
            finally
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
            }
        }

        //Get the Uploaded Data
        GetInfoFromSqlAzureAndBlob();
    }
    catch (Exception ex)
    {
        lblInfo.Text = ex.Message;
    }
}

The above code checks whether the Image is successfully uploaded in the blob if yes then the information about image will be stored in Sql Azure database table. Once the Image information is saved in data table then the information is retrieved from Database and Blob in displayed in the GridView.

Step 9: Run the application and upload the image. You will find the data in the table in Sql Azure and the Image. Its information will be displayed in the GridView as shown below:

I4_Res

Conclusion: To use Storage and SQL Azure effectively to save costs, it helps to store binary data like files on BLOB and other transactional information on SQL Azure

The entire source code of this article can be downloaded over here

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by a on Saturday, July 21, 2012 4:38 AM
a
Comment posted by a on Saturday, July 21, 2012 4:38 AM
a
Comment posted by a on Saturday, July 21, 2012 4:38 AM
a
Comment posted by Matthew on Sunday, July 22, 2012 10:57 PM
For those interested, there is a pricing calculator http://www.windowsazure.com/en-us/pricing/calculator/
Comment posted by Nirali on Sunday, March 10, 2013 8:25 AM
i want to make a cloud storage service that does provide encryption and decryption of files also..can u help me for that?
Comment posted by Hina on Saturday, December 7, 2013 1:52 PM
Do we need to change anything in webconfig file? I'm getting error while running the project.

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