WinForm Application Streaming from SQL Azure

[This article was contributed by the SQL Azure team.]

In this blog post, we are proving code for a sample WinForm application that streams images from SQL Azure to a PictureBox control. Our goal is to show how you can stream large BLOB data from a varbinary(max) column in SQL Azure to the WinForm application, in this case, an image. One of the nice things about streaming data is that you can update a process bar control to give the user some indication of how long it will take to download all the data. This blog post is an extension of this blog post that introduces the SqlStream class.

Our goals for this code sample:

• Show example code of calling SQL Azure from a WinForm application.
• Stream the response into the PictureBox control.
• Update a progress bar control as the download takes place.
• Prompt for login and password, a best practice of connecting to SQL Azure, as discussed in this blog post.

Adventure Works Viewer

The sample application views the thumbnail images in the SalesLT.Products table in the Adventure Works for SQL Azure database. You can download this database from here. When the application is started, it prompts the user for their login and password to the SQL Azure database. Then it fills in the drop down list with all the product names from the database that contain images. If the user clicks on the download button, the application queries the database to determine the size of the image; the size is needed to accurately configure the progress bar. The image is then streamed from SQL Azure and displayed. During the download, the status bar is updated to give the user an indication of the download speed and size of the image.

Filling the Drop Down List

The code below fills the drop down list with product names

private Dictionary ProductId = new Dictionary<int, int>();

private void FillDropDownList()
{
    productComboBox.Items.Clear();

    using (SqlConnection sqlConnection =
        new SqlConnection(ConnectionString))
    {
        sqlConnection.Open();
        String sql = "SELECT [ProductId], [Name] FROM" +
            "[SalesLT].[Product] WHERE NOT [ThumbNailPhoto] IS NULL";
        using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
        {
            using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
            {
                while (sqlDataReader.Read())
                {
                    Int32 index = productComboBox.Items.Add(
                        (String)sqlDataReader["Name"]);
                    ProductId[index] = (Int32)sqlDataReader["ProductId"];
                }
            }
        }
    }

    if (productComboBox.Items.Count > 0)
        productComboBox.SelectedIndex = 0;
}

private void Form1_Load(object sender, EventArgs e)
{
    FillDropDownList();
}

Prompting the User for Credentials

The Connection String property prompts the user for their login and password, storing them in private properties and constructing the connection string used in the SqlConnection constructor.

String Server { get; set; }
String Login { get; set; }
String Password { get; set; }

String ConnectionString
{
    get
    {
        if (String.IsNullOrEmpty(Login))
        {
            LoginDialog loginDialog = new LoginDialog();
            switch (loginDialog.ShowDialog())
            {
                case DialogResult.OK:
                    Login = loginDialog.Login;
                    Password = loginDialog.Password;
                    break;
                default:
                    throw (new OperationCanceledException());
            }
        }

        return (
            String.Format(
            "Server=tcp:{0}.database.windows.net;" +
            "Database=AdventureWorksLTAZ2008R2;" +
            "User ID={1}@{0};Password={2};" +
            "Trusted_Connection=False;Encrypt=True;",
            Server, Login, Password));
    }
}

Streaming the Image

When the button click event is fired, the code uses the SqlStream class to stream the image from SQL Azure into a MemoryStream. This memory stream is used to create an instance of the Bitmap class which is assigned to the Image property of the picture box control.

private void button1_Click(object sender, EventArgs e)
{
    button1.Enabled = false;
    Cursor.Current = Cursors.WaitCursor;
    progressBar1.Value = 0;

    using (SqlConnection sqlConnection =
        new SqlConnection(ConnectionString))
    {
        sqlConnection.Open();

        // WWB: Convert the Selected Index To a Product Id
        Int32 productId = ProductId[productComboBox.SelectedIndex];

        // WWB: The Length Can Be Great Than Int32.MaxValue
        Int64 length = FetchLength(sqlConnection, productId);
        Int32 scale = 1;

        // WWB: If the Length Is Greater Than Int32.MaxValue
        // Scale it down to fit within Int32.MaxValue, Since
        // the Progress Bar supports Int32
        while (length > Int32.MaxValue)
        {
            // WWB: Track the Scale, We Will Need
            // It To Increment the Progress Bar
            scale = scale * 2;
            length = length / 2;
        }

        // Set the Maximum Length of the Progres Bar
        progressBar1.Maximum = (Int32)length;
        
        using (SqlStream sqlStream = new SqlStream(
            sqlConnection, "SalesLT", "Product",
            "ThumbNailPhoto", "ProductID", 
            SqlDbType.Int, productId))
        {
            Byte[] buffer = new Byte[8192];
            Int32 read = 0;

            using (BinaryReader binaryReader =
                            new BinaryReader(sqlStream))
            {
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    using (BinaryWriter binaryWriter
                        = new BinaryWriter(memoryStream))
                    {
                        do
                        {
                            // WWB: Read From SQL Azure
                            read = binaryReader.Read(buffer, 0, 8192);
                            // WWB: Write To the Memory Stream
                            binaryWriter.Write(buffer, 0, read);
                            // WWB: Increment the Progress Bar
                            progressBar1.Increment(read / scale);
                        } while (read > 0);

                        // WWB: Reset the Memory Stream
                        memoryStream.Position = 0;

                        // WWB: Load the Memory Stream Into The Image
                        Bitmap bitmap = new Bitmap(memoryStream);

                        // WWB: Assign the BitMap to the Picture Box
                        pictureBox1.Image = bitmap;
                    }

                }
            }
        }
    }

    Cursor.Current = Cursors.Default;
    button1.Enabled = true;
}

You can download the full code sample below, included the project file.

Summary

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