Streaming Blobs To and From SQL Azure - SQL Azure Team Blog - Site Home - MSDN Blogs

Streaming Blobs To and From SQL Azure

Streaming Blobs To and From SQL Azure

  • Comments 2

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.

Attachment: SQLStream.zip
  • Dear Wayne,

    I think there's a little bug in the SqlStream class. In the Seek method, there are tests to check wether the offset is in range. But you use and && operator instead of ||. The if statement "if ((offset < 0) && (offset > this.Length))" will always fail if length > 0.

    Greetings,

    Michiel Vos

  • In SQL Azure, how can I insert varbinary(max) data from a script?  I want to be able to populate some data from a visual studio database build.  I have a script that will work in SSMS but it fails in VSDB due to the very long text.  The script has a very long value.  I would like to read the value from a file instead of having it in the script.  But, OPENROWSET with SINGLE_BLOB is not supported.  Any ideas?

Page 1 of 1 (2 items)
Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post