Creating sample Application that uses a RBS Provider

Summary

This is a simple application sample that leverages the RBS Provider. In this case, it’s using a Filestream provider but none of the code itself is aware what type of provider is in use.

This iteration does not attempt to use the more advanced, optimal writing and reading methods. These will come soon enough, for now a reader of this blog should be able to accomplish insights into creating a sample application to demonstrate RBS capability.

Objective/Context

I want to share the experiences of creating a sample application. My intent was simply to understand how a value can be written to the database, and then read from the database when the data must be accessed through RBS. Historically, even when I implemented a Filestream solution, I was able to meet all my programmatic needs through T-SQL. This allowed me to focus on the database design, and infrastructural requirements.

RBS is forcing me to recall very rusty memories about C# coding, go beyond simply reading code and deriving the intent. As I’ve said, my first iteration is to get a record into the database and read it again. I will follow this up with the recommended pattern for optimal performance, which I believe will require a deeper understanding of how buffering plays an important role in the streaming of blob data. All this is to ultimately get to a point where we can scale the solution to have a 200MB/sec insert throughput, and a 100MB/sec read throughput. I’m expecting numerous concurrent connections, so it must do that too. The solution must be able to maintain these throughputs even when there are 100’s of millions of records.

Ok, there is more to be said about what I want the solution to achieve but for now, let’s just remain focus on the sample application.

Planning

I highly recommend that you read the whitepaper “Remote BLOB Storage”. It is 56 pages so you may be interested in first reading this 9-page review on it “Review & musings of whitepaper “Remote BLOB Storage””. The whitepaper is very informative, and reasonably recent enough to be trustworthy.

I referred to the following sample code “How To: Write a BLOB (Remote BLOB Store)” and “How To: Read a BLOB (Remote BLOB Store)”.

I chose a Windows Form project. This helps me visually understand concepts, and I can easily provide input parameters.

Nothing too intellectually challenging

On the first iteration, the writing and reading of the blobs uses basic logic and constructs. As mentioned under the objectives/context section, I will enhance the solution and therefore you can expect additional fields will appear on the form as the application gets more advanced, such as:

    • Ability to read and/or write multiple files
    • Ability to have a multiple connections simultaneously work through the multiple files
    • Ability to evaluate various file sizes, for instance should we be writing 4MB files
    • Ability to evaluate various buffer sizes when writing files, for instance should we be writing 4MB files in 1MB buffer sizes
      • I must remember to investigate the value of implementing Elastic Buffering

NOTE: RBS is managed code; therefore if you are writing in C++ unmanaged code, then you will have to access the RBS API using C++ Managed code.

Writing the code

At the beginning of this article, I referred you to another blog on this topic. There you will have learnt that you have to create the application’s database and BLOB store database prior to running the RBS.msi. Now before you can run this code, you have to:

  1. Enable the database to use RBS (mssqlrbs.rbs_sp_enable_rbs)
  2. Create the table that holds the BlobId column
    1. The column must be varbinary(64)
    2. You must index this column
    3. I suggest that you add other indexes that align with the type of queries you would run to get the BlobID. For instance, if you collect Blobs based on date then index that column.
  3. Register the column of this table (mssqlrbs.rbs_sp_register_column)

These are the database objects I created. This will do for now, but I caution that more attention should be taken.

CREATE TABLE [dbo].[BlobUserTable]
(
    [id]        uniqueidentifier NOT NULL,
    [FileName]  varchar(50)      NOT NULL,
    [BlobId]    varbinary(64)    NOT NULL
)

CREATE UNIQUE NONCLUSTERED INDEX [IX_BlobUserTable_BlobId]
    ON [dbo].[BlobUserTable] ([BlobId] ASC)

In order to use the sample you will require the following references in the C# project:

using Microsoft.Data.SqlRemoteBlobs;
using System.Transactions;
using System.Globalization;
using System.Data.SqlClient;
using System.IO;

There will be several cases where I need to connect to the database:

private string GetSQLConnString()
 {
     string sSqlServer = txtSQLInst.Text;
     string sDatabase = txtDBName.Text;

     string sConnection = string.Format(CultureInfo.InvariantCulture,
      "Data Source={0};Initial Catalog={1};Integrated Security=SSPI",
      sSqlServer, sDatabase);

     return sConnection;
 }

For the list box I made a call to the database to return all the values from the “filename” column. The design could be better, specifically in that the list should include a Primary Key value (hidden) which can be used when selecting which Blob to read.

private void PopulateListBox()
{
    //Get Connection String
    string sConnection = GetSQLConnString().ToString();

    //Prepare the query string
    string sQuery = "SELECT [FileName] AS [BlobName] FROM dbo.BlobUserTable";

    //Call SP to return list of Filenames and fill data set
    using (SqlConnection connection = new SqlConnection(sConnection))
    {
     SqlCommand SQLCmd = new SqlCommand(sQuery, connection);

     connection.Open();

     SqlDataReader reader = SQLCmd.ExecuteReader();

     //Empty the list box
     lbxBlobInDB.Items.Clear();

     //Add them to the list
     while (reader.Read())
     {
         lbxBlobInDB.Items.Add(String.Format("{0}", reader[0]));
     }

     //Close when done reading
     reader.Close();

     connection.Close();
    }
}

This is called as soon as the form launches

public Form1()
{
    InitializeComponent();

    PopulateListBox();
}

You can type the path to the location of the file, and I added the ability to browse using the “openFiledialog” object to select a file. I’ve seen it fail with permission issues when I try to select from certain directories, therefore use files from an accessible location. I’m not running down that rat-hole for this sample.

private void lblBrowse_Click(object sender, EventArgs e)
{
    OpenFileDialog openFileDialog1 = new OpenFileDialog();

    openFileDialog1.InitialDirectory = "c:\\"";
    openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
    openFileDialog1.FilterIndex = 2;
    openFileDialog1.RestoreDirectory = true;

    if (openFileDialog1.ShowDialog() == DialogResult.OK)
    {
     try
     {
         txtFilePath.Text = openFileDialog1.FileName.ToString();
         txtFileNameInDB.Text = openFileDialog1.SafeFileName.ToString();
     }
     catch (Exception ex)
     {
         MessageBox.Show("Error: Could not read file from disk. Original error: "
                         + ex.Message);
     }
    }
}

There is an opportunity for the user to create a more identifiable filename for when the Blob is stored in the database. The default is derived from the file name.

When you click on the WriteBlob it will fetch the file that was selected, and write it to the SQL Server database, and of course send it to the RBS Filestream data store. For now I simply confirmed that there were no errors AND the Application’s database had a new record. Later on, I want to confirm that the small files are stored in the tables and large files in the filesystem. There should be no reason that this is not happening.

private void btnWriteBLOB_Click(object sender, EventArgs e)
{
    string sFilePath = txtFilePath.Text;
    string sFileNameInDB = txtFileNameInDB.Text;
    string sConnection = GetSQLConnString().ToString();
    byte[] blobId = WriteBlobPush(sFilePath, sFileNameInDB, sConnection);
}

rivate byte[] WriteBlobPush(string sFilePath, string sFileNameInDB, string sConn)
{
    byte[] blobId = null;

    //Open the connection to the database, Create and write the BLOB.
   using(TransactionScope ts = new TransactionScope(TransactionScopeOption.Required
        , TimeSpan.FromMinutes(10)))
   {
    using (SqlConnection conn = new SqlConnection(sConn))
    {
        conn.Open();

        SqlRemoteBlobContext blobContext = new SqlRemoteBlobContext(conn);
        using (SqlRemoteBlob blob = blobContext.CreateNewBlob())
        {
            using (FileStream fileToRead = new FileStream(sFilePath, FileMode.Open))
            {
                blob.WriteFromStream(fileToRead);
            }
            ts.Complete();

            //Update the application user table with the
            //returned BLOB id and the filename.
         using (SqlCommand cmd = new SqlCommand(string.Format(CultureInfo.InvariantCulture,
           "INSERT INTO BlobUserTable(id, FileName, BlobId) Values('{0}', '{1}', @blobId)",
                Guid.NewGuid(), sFileNameInDB)))
            {
                cmd.Parameters.Add(new SqlParameter("@blobId", blob.GetBlobId()));
                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
        }

        conn.Close();
    }

    lbxBlobInDB.Items.Add(sFileNameInDB);
  }
    return blobId;
}

You should see the listbox get one more record.

You can select an item from the listbox and then select “Read Blob”, you should see the content of your file appear in the box.  Be cautious because the code fixes the length at 256 bytes, which will be handled in my future enhancements. Additionally, pay careful attention to the following scenario (I’m still determining if this actually happens), the scenario is where you store the filename in the database such that it contains the path to the file. When the function refers to it, and the path doesn’t exist then an error will be generated.

private void ReadBlobPull(string sFileNameInDB, string sConn)
 {
     using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required
            , TimeSpan.FromMinutes(10)))
     {
         using (SqlConnection connection = new SqlConnection(sConn))
         {
           connection.Open();

           byte[] blobId;

           using (SqlCommand cmd = new SqlCommand(string.Format(CultureInfo.InvariantCulture,
               "SELECT BlobId FROM BlobUserTable WHERE FileName = '{0}'", sFileNameInDB)))
           {
               cmd.Connection = connection;
               blobId = (byte[])cmd.ExecuteScalar();
           }

           //Read the BLOB directly from the store to the FileStream.
           SqlRemoteBlobContext blobContext = new SqlRemoteBlobContext(connection);
           using (FileStream readStream = new FileStream(sFileNameInDB, FileMode.Create))
           {
               using (SqlRemoteBlob readBlob = blobContext.OpenBlob(blobId))
               {
                   byte[] content = new byte[256];
                   readBlob.Read(content, 0, content.Length);
                   txtCurrentBlob.Text = Encoding.Default.GetString(content);
               }
               lblCurrentBlob.Text = sFileNameInDB;
           }
       }
   }
 }
In summary, the experience has ensured that I could focus on the fundamentals, learn a few nuances and prepare an environment where the next step allows for more attention to be spent on optimizing RBS specific access patterns.  Most importantly this was fun, and I can’t wait to start working on the buffer logic.
Advertisements
This entry was posted in Work and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s