Accessing Data

Up to this point, most of the work that has been done producing any code for examples has been limited to static HTML because we were focused on concerns such as configuring source control and publishing content as opposed to writing code. In dynamic real-world applications, however, static HTML has limited value unless you also have some sort of server-side code interacting with data behind it. In this chapter, we will take a look at some of the options within Azure Web Sites for accessing and manipulating data. Some of these options are available out of the box with the Azure Web Sites service while others may require additional Azure services.

Windows Azure Table Storage

For years, relational database management systems using SQL to manage and retrieve data have been considered to be the go-to way for applications to work with data. This is based on advantages brought with them such as minimizing storage and improving consistency of data by normalizing structures and providing easy navigation of well-defined data structures through Structured Query Language (SQL). SQL databases do, however, run into many challenges when it comes to scaling massively. This has caused an explosion in the popularity of a category of database alternatives that are collectively referred to as NoSQL databases.

Azure Table storage is the default NoSQL database service within Azure and has shipped since the earliest Azure releases. It features the scalability and low storage costs associated with many NoSQL databases, and is easy to set up and configure within Azure.

For developers familiar with traditional SQL databases, some of the characteristics of Azure Table Storage that take a while to get used to are:

• While you will see references to columns in a table, the idea of a fixed schema is not present. Azure would allow you to have records stored in a single table where one record contains the data attributes appropriate to a bottle of pharmaceutical product and another record contains data attributes appropriate to a tricycle. This does not, however, mean that it is often a recommended to store vastly different entities within the same table.
• The concept of having multiple indexes is not present. Azure table storage segments data based on a single partition key, and then identifies rows within a partition by a single row key. Beyond these two keys, retrieving a subset of data based on the value of a data attribute will be an exercise in programmatically filtering a larger set of data. This is sometimes shielded from the developer somewhat by APIs that do this work for you.

Azure Table Storage does not come out of the box with your Azure Web Site service so, before you can utilize Azure Table Storage, you must first create a Storage account. This was described briefly in the earlier chapter “Provisioning Resources”. When you have provisioned a Storage account, you are set to start using Azure Table Storage in your application.

Note: Remember that Azure services are billed based on utilization when setting up any new service.

The first step in getting ready to use Storage from your web application is to configure a reference to the Azure storage library “WindowsAzure.Storage”. The easiest way to do this in Visual Studio is to right-click the web project and select “Manage Nuget Packages…”. The package manager dialog will display and you can search the online repository for “WindowsAzure.Storage”. Find the Microsoft Windows Azure Storage Client Library in the search results and install the package. This will install all of the libraries required to interact with the storage account.

After your project references are appropriately configured, the next step to set up your application to use Azure Table Storage is to create an entry for the Storage account in your connection strings. In this example, we’ll add an application setting to reflect the Azure Storage account that we’ll be using. Add the following to the appSettings in web.config (make sure to use your own account):

Now that the connection string is available, you’re set to reference the storage in code. You’ll want to first make sure that the following are represented in the using statements at the top of your code module:

• Microsoft.WindowsAzure.Storage
• Microsoft.WindowsAzure.Storage.Table
• System.Configuration

The top-level object that gives you access to all of the storage functionality is the CloudStorageAccount object. In an Azure Web Site project, an instance of this object of this type is obtained through calling the class’s static Parse method, passing the connection string that you stored in the web.config such as in the following:

Now that you have access to your account, you can use the account object to produce an appropriately configured CloudTableClient which will be used to interact with the Azure Table Storage service. To access an instance of CloudTableClient, call the CreateCloudTableClient method of the CloudStorageAccount instance as follows:

With the CloudTableClient object, you can access and create tables within the storage account. In the following code, a reference to a table named “tricycle” is set up and the existence of the table is guaranteed prior to using the table by using the CreateIfNotExists method:

In order to work with the data in a table from .NET code, the easiest thing to do is to create .NET classes to represent the records in the table. These classes should inherit from the TableEntity class present in the Microsoft.WindowsAzure.Storage.Table namespace and, in addition to including the fields necessary to describe the entity, should establish the partition and row keys in the constructor. Continuing with our tricycle example, we’ll use the following to describe the entity that is to be stored in the “tricycle” table:

This class will be used to perform all of the Create, Read, Update and Delete (CRUD) operations with the table. These are accomplished using the TableOperation class which exposes static methods to do the following operations:

• Delete: Deletes the entity.
• Insert: Inserts a new entity into the table.
• InsertOrMerge: If the entity with a given key does not yet exist in the table, it will be inserted. If it does exist, the data attributes of the stored item will be merged with those of the entity passed to the method. In cases where the entity already exists, attributes of the entity will be updated to reflect the new values being passed in, but any attributes that exist on the stored record and are not provided in the new entity will remain stored as before.
• InsertOrReplace: If the entity with a given key does not yet exist in the table, it will be inserted. If it does exist, the stored version will be replaced with the data from the entity passed to the method. Unlike with the merge command, when the entity is already stored and the list of attribute differs between the stored and new entity, attributes which are not present in the new entity will not be retained.
• Merge: This operation merges the data in the entity stored in the table with a given key with that contained in the entity passed to the method. The object must already exist in the database or the operation will cause a StorageException to be thrown when executed.
• Replace: This operation replaces the entity stored in the table with a given key with the entity passed to the method. The object must already exist in the database or the operation will cause a StorageException to be thrown when executed.
• Retrieve: This operation is used to retrieve the entity with a given partition and row key.  If the key specified does not correlate to an entity stored in the table, the fetch operation will contain a null result so, when retrieving directly by key, it is advisable to always check to ensure that the operation result contains a non-null value before attempting to process the record.

The TableOperation does not actually change any data itself but is passed as a sort of “command object” to the CloudTable object. The following code builds upon the earlier example to use the constructed table reference to retrieve an instance of a Tricycle entity where the partition key (Manufacturer) is “Schwinn” and the row key (SerialNumber) is “ABC123”:

If you wanted to update the stored data for the entity, you would retrieve the object (or potentially just create a new instance of the object with appropriate key values if you don’t care what is currently stored), update field values, and then create an update operation such as Replace with the entity to execute with the CloudTable instance. Building off of the example where we just retrieved a Tricycle entity, the following code would be used to update the color and save the updated values to the database:

Azure Table Storage is relative easy to interact with, and the steepest part of the learning curve is that many developers today are just very accustomed to using relational databases. We’ve only scratched the surface enough to jumpstart your own exploration in this section so you will likely first want to explore filtering options and then batch operations.

Windows Azure Blob Storage

Azure Blob Storage utilizes the same storage account used by Azure Table Storage but is used to store unstructured data (files) rather than records in a database. These files can be optimized for transfer as read-only chunks called Block blobs. Block blobs can be quickly streamed sequentially to a client which works very well for many media types such as large video files, or they can be Page Blobs, optimized for random read/write access which is useful when the files are documents—or even a complete file system as is used when creating Virtual Hard Disk (VHD) files to mount as disk drives from Azure VM instances.

When building applications, Blob Storage should be considered whenever you have an application need on-premises such as writing files to the file system like in image-processing workflows. Because of the massive scalability achieved with Azure Storage accounts (at the time of this writing, all production storage accounts support scaling up to 200 terabytes and 20,000 requests per second) over what can be achieved by a hard drive on most on-premises servers, you may even find yourself considering Blob Storage for scenarios where you would like to have a file system solution but discounted the option out of concern that it would drag down your server with I/O requests when under heavy load.

In the following sections, interaction with Block and Page blobs will be demonstrated. In order to get started with the examples, you must first ensure that you have set up a Storage account in the Azure Management Portal and install the WindowsAzure.Storage Nuget package, both of which have been described in the previous section discussing Azure Table Storage. You will also need to make sure that any code files are referencing the following namespaces in the using statements:

• Microsoft.WindowsAzure.Storage
• Microsoft.WindowsAzure.Storage.Auth
• Microsoft.WindowsAzure.Storage.Blob
• System.Configuration

After your project references are appropriately configured, the next step to set up your application to use Azure Blob Storage is to create an entry for the Storage account in your connection strings. In this example, we’ll add an application setting to reflect the Azure Storage account that we’ll be using. Add the following to the appSettings in the web.config file (make sure to use your own account):

The top-level object that gives you access to all of the storage functionality is the CloudStorageAccount object. In an Azure Web Site project, an instance of this object of this type is obtained through calling the class’s static Parse method, passing the connection string that you stored in web.config such as in the following:

Now that you have access to your account, you can use the account object to produce an appropriately configured CloudBlobClient instance, which is the primary object through which you will interact with storage. A CloudBlobClient instance is retrieved from the CloudStorageAccount object by calling its CreateCloudBlobClient method such as in the following code:

After obtaining the CloudBlobClient instance, you are ready to interact with blobs stored in your Azure Storage account. These blobs are organized into containers that are analogous to folders in the file system of your PC, with the exception that they are flat rather than forming a hierarchy. When the appearance of a hierarchical folder structure is desired, creative naming of these containers can help give that appearance. Whether working with Page or Block blobs, you start by obtaining a reference to the container in which the blob will be stored. This is done by calling the GetContainerReference of the CloudBlobClient object, passing the name of the container. For this walk-through, we call the CreateIfNotExists method of the retrieved CloudBlobContainer instance which will ensure that the container actually exists before we attempt to read from and write to it:

Reading and Writing Block Blobs

Access to blobs for reading or writing is made through the CloudBlockBlob class, which is obtained by calling the GetBlockBlobReference method of a CloudBlobContainer instance, passing it the name of the blob. The simplest way to store content in a Block blob is to use one of the Upload methods of the CloudBlockBlob class, which include:

• UploadFromByteArray
• UploadFromByteArrayAsync
• UploadFromFile
• UploadFromFileAsync
• UploadFromStream
• UploadFromStreamAsync
• UploadText
• UploadTextAsync

These methods are as intuitively named as they appear and allow you to specify various sources for the data which will be saved to the blob. In the following example, a new blob is created and populated with the contents of a file on the local file system:

There are also several options to read from a blob (including just providing a link directly to the blob). But the simplest way is to use one of the Download methods which include:

• DownloadText
• DownloadTextAsync
• DownloadToByteArray
• DownloadToByteArrayAsync
• DownloadToFile
• DownloadToFileAsync
• DownloadToStream
• DownloadToStreamAsync

As with the upload methods, these methods are very intuitively named and can be used to handle the downloaded content based on your needs. Additional Download methods exist but these are not for downloading the contents of a blob in a single call and, instead, are used to manage download of a blob in multiple calls (such as when a very large blob is to be downloaded and you want to create either an interruptible process or one that can communicate progress to the end user). In the following example, the contents of a blob containing text data are used to populate a string variable:

Reading and Writing Page Blobs

While Block blobs are highly recommended for blob storage because they are much easier to deal with in an environment that must massively scale, there are times when random access is required and you can use Page blobs to meet this need. The CloudPageBlob class exposes the functionality and, similar to the Block blob, it is accessed by calling the GetPageBlobReference method of a CloudBlobContainer instance. The CloudPageBlob object has the methods described in the Block blob section for reading and writing data, but additional support exists for working with pages of data within the blob using methods such as WritePages.

Windows Azure SQL Database

In addition to the NoSQL Azure Table Storage service, Azure also makes available the ability to interact with a more familiar relational database through the SQL Database product. This service is very similar to the on-premises Microsoft SQL Server offering, with the list of features present in SQL Server that are not implemented in the Azure SQL Database product. This list seems to shrink with every release of Azure and I expect it will eventually whittle down to just those features that don’t make as much sense in the Azure environment such as linked database servers. Some of the features currently available in the on-premises editions of Microsoft SQL Server that are not present in SQL Azure include:

• SQL Agent
• Master Data Services
• Change Data Capture
• FILESTREAM Data
• Replication
• Mirroring
• Limited XML support
• Backup/restore

In addition to providing a familiar paradigm to developers who are used to working with relational data, Azure SQL Database uses many of the same libraries such as the ADO.NET data access API, LINQ to SQL, and Entity Framework. This means that moving many existing applications into the Azure environment from a data access perspective could be just a matter of changing connection strings.

Note: Entity Framework is Microsoft’s currently recommended data access methodology of choice. The most recent versions include functionality optimized for Azure such as integrated support for retrying commands executed against unreliable connections.

While Azure SQL Database is a product offering of its own, it is also considered to be a part of the Azure Web Site offering and out of the box the Azure Web Sites product includes a free Azure SQL Database instance with 20MB of storage. This is not a lot of storage but, for many smaller websites that just need to manage a membership database and a little bit of information to drive what is displayed on webpages, it will be more than enough.

The easiest way to ensure that you can connect to an Azure SQL Database with your Azure Web Site is to select that database among the database options when creating the Azure Web Site. To this point, during all the walk-throughs and demonstrations that involved creating an Azure Web Site, either “quick create” was used or you were instructed to select “no database”. But now it is time to explore those options.

To create an Azure SQL Database as part of a newly created Azure Web Site that is configured with connectivity enabled between the Web Site and the database, start by going to the Azure Management Portal and initiating the process of creating a new Azure Web Site with the Custom Create method. The Custom Create dialog is displayed, prompting for information such as the URL, billing subscription, and hosting region for the Web Site as well as a drop-down menu with database options. Selecting “Create a free 20 MB SQL database” in this drop-down menu adds a “DB connection string name” text box as well as a new page to the wizard as shown in Figure 51:

51. Custom Create database selection

Advancing to the next step in the Wizard allows you to override the default name selected for your database with something that may be more meaningful to your application and prompts you to select a server to host your database. If you have not yet created a SQL Database in your Azure plan, the only option in the server drop-down menu will be “New SQL database server”. Selecting the “New SQL database server” option prompts you to specify the login credentials which will be used to connect to the database as well as the region in which the database server should be provisioned—which should be the same as the region in which the Azure Web Site is hosted to prevent having to jump across data centers when your application is retrieving and updating data. The completed form should look similar to what is shown in Figure 52:

52. Custom Create database settings

After you complete the wizard, Azure provisions the Web Site and SQL Database. Once complete, the SQL Database can be seen in the SQL Databases section of the Azure Management Portal as well as in the Linked Resources tab when viewing the Azure Web Site. Navigating to the SQL Database via either will display the SQL Database “Quick Start” screen that is shown in Figure 53, which includes the option to view database connection strings for various client connectivity methods:

53. SQL Database Quick Start

Selecting the “View SQL Database connection strings” command will display the dialog shown in Figure 54 which provides the connection strings properly constructed for ADO.NET, Open Database Connectivity (ODBC), PHP, and JDBC. These connection strings are displayed within HTML textarea controls so that you can easily select the contents and then copy the value to your clipboard:

54. SQL database connection strings

Note: Heed the warning displayed at the bottom of the connection string dialog. By default, Azure SQL Databases do not allow connections from any client unless it has been specifically granted permission. This is handled automatically when setting up the Azure Web Site and selecting the SQL Database during the creation process. But, if you later try to connect to the database from some other source and do not configure the firewall rules, you could find yourself trying to figure out the connectivity problem for hours.

Now that you have set up your database, enabled connectivity between your Azure Web Site and the database (the setup wizard did this for you), and know the connection string, you’re all set to use it within your application just as you would access an on-premises instance of Microsoft SQL Server.

Summary

In this chapter, we have covered some of the most common data access options used in Azure Web Sites to include the NoSQL Azure Table Storage option, the file-based approach with Azure Blob Storage, and the Azure SQL Database for a relational database option that is included with your Azure Web Site account. We have certainly not covered every possible database access option because there are a growing list of other database options hosted within Azure such as MySQL and Oracle, and you can also use advanced configuration options to connect to data hosted in your on-premises data center.