Thursday, June 17, 2010

Streaming Blobs To and From SQL Azure

by Wayne Walter Berry via SQL Azure Team Blog on 6/17/2010 4:00:00 PM

Because of the remoteness of SQL Azure it is beneficial to have some tricks in your coding toolbox for dealing with large binary objects, the varbinary(max) data type in SQL Azure. One of these is to be able to stream large binary objects (BLOB) -- reading or writing a piece of the data at a time.

This article provides a SqlStream class written in C# code. The class implements the abstract Stream class for the varbinary(max) data type on SQL Azure; Stream is an abstract class defined in the .NET CLR that is well supported and very versatile. The SqlStream class provided when used with SQL Azure allows you to manipulate a single blob a chunk at a time.

Using the SqlStream class provided you can:

• Create a console application to copy a file of BLOB data to SQL Azure from the command line without having to load the whole BLOB into memory.
• Create a Windows Azure Role that would read a BLOB from SQL Azure and store it in Windows Azure Storage.
• Stream the BLOB from SQL Azure to a Winform application one chunk at a time - with the added benefit of being able to provide a good status dialog with progress bar.
• Use the BinaryWriter, BinaryReader, FileStream, and MemoryStream classes in CLR to read and write the varbinary(max) data type without having to load the all the data into memory.

The blog post is a start of a series, in the coming days we will build some of the applications listed above. For now, here are some samples for using the SqlStream class. Download the SqlStream class its own .cs file at the bottom of the post.

Samples

The first sample uses the SqlStream class to read an image from the Adventure Works database deployed on SQL Azure and saving it to a file. You can download the Adventure Works database from SQL Server Database Samples.

using (SqlConnection sqlConnection = new SqlConnection(
    "Server=tcp:yourServer.database.windows.net;" + 
    "Database=AdventureWorksLTAZ2008R2;" +
    "User ID=yourLogin@yourServer;Password=yourPassword;" +
    "Trusted_Connection=False;Encrypt=True;"))
{
    sqlConnection.Open();

    using (SqlStream sqlStream = new SqlStream(
        sqlConnection, "SalesLT", "Product",
        "ThumbNailPhoto", "ProductID", SqlDbType.Int, 884))
    {
        Byte[] buffer = new Byte[1024];
        Int32 read = 0;

        using (BinaryReader binaryReader = 
                        new BinaryReader(sqlStream))
        {
            using (FileStream fileStream = new FileStream(
                "c:\\temp\\image.jpg", FileMode.Create))
            {
                using (BinaryWriter binaryWriter =
                        new BinaryWriter(fileStream))
                {
                    do
                    {
                        read = binaryReader.Read(buffer, 0, 1024);
                        binaryWriter.Write(buffer, 0, read);
                    } while (read > 0);
                }
            }
        }
    }
}

This sample uses the SqlStream class to write the data in a file to a varbinary(max) column defined in out Adventure Works database deployed on SQL Azure.

using (SqlConnection sqlConnection = new SqlConnection(
    "Server=tcp:yourServer.database.windows.net;" +
    "Database=AdventureWorksLTAZ2008R2;" +
    "User ID=yourLogin@yourServer;Password=yourPassword;" +
    "Trusted_Connection=False;Encrypt=True;"))
{
    sqlConnection.Open();

    using (SqlStream sqlStream = new SqlStream(sqlConnection, 
        "SalesLT",
        "Product",
        "ThumbNailPhoto", 
        "ProductID", 
        SqlDbType.Int, 884))
    {
        Byte[] buffer = new Byte[1024];
        Int32 read = 0;

        using (BinaryWriter binaryWriter = 
                        new BinaryWriter(sqlStream))
        {
            using (FileStream fileStream = new FileStream(
                "c:\\temp\\image.jpg",
                FileMode.Open))
            {
                using (BinaryReader binaryReader = 
                        new BinaryReader(fileStream))
                {
                    do
                    {
                        read = binaryReader.Read(buffer, 0, 1024);
                        binaryWriter.Write(buffer, 0, read);
                    } while (read > 0);
                }
            }
        }
    }
}

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.