Managing Files with SQL Server 2008's FILESTREAM Feature : Page 4

Using FILESTREAM to Read Data

To the C# project's default form, add a Button named btnReadFile, and paste the code in Listing 2 into its Click event:

For brevity, I'll only discuss the code that differs from the previous section. When you create the SqlFileStream, you need to indicate that you are opening the file stream for read-only access with SequentialScan enabled.

       SqlFileStream stream = new SqlFileStream(path,
        (byte[])reader.GetValue(1),FileAccess.Read,
        FileOptions.SequentialScan, 0);                        

After that, read the contents of the file stream into a byte array, convert that into a string, and display it in the ListBox.

       int length = (int) stream.Length;
       byte[] contents = new byte[length];
       stream.Read(contents,0,length);                     
       string results = System.Text.Encoding.ASCII.GetString
        (contents);
       lstResults.Items.Add(results);

Figure 2. Sample Project: At this point, you can read and write user-entered data by setting and reading the contents of the FILESTREAM column using the SqlFileStream Class.

When you run the application, you'll see a screen similar to that shown in Figure 2 (you'll see more about the Append File button in the next section). When you click the "Write File" button, the application writes the contents of the TextBox to the file stream. When you click the "Read File" button, the application reads the contents from the file stream and displays that in the ListBox.

So far, you have seen how to read and write to a file stream. The next example shows the code required to append to an existing file stream in the database.

Using FILESTREAM to Append Data

Add a command button named btnAppendFile and modify its Click event handler as shown in Listing 3:

This time, when you instantiate the SqlFileStream object, you set the file access enumeration to ReadWrite, so you can both read from and write to the file stream.

    SqlFileStream stream = new SqlFileStream(path,
      (byte[])reader.GetValue(1), FileAccess.ReadWrite,
      FileOptions.SequentialScan, 0);
   

You then move the file stream pointer to the end of the file so that you can append to it.

   stream.Seek(0, SeekOrigin.End);
   

Then, write the user input to the file stream using the SqlFileStream.Write() method.

   stream.Write((System.Text.Encoding.ASCII.GetBytes
      (contents)), 0, contents.Length);       
            

Finally, commit the transaction by calling the SqlTransaction.Commit() method.

Advantages of FILESTREAM

That's the entire process. You can now read, write, and append data to a database-managed file. Although the process may be slightly more complex than using files or storing data directly in BLOBs, you'll find that letting the database manage file storage has several advantages:

• You can access both relational and non-relational data using a single data store.
• SQL Server automatically includes the non-relational data (BLOB) during database backups and restores.
• There is no file size limitation. The 2GB maximum size limitation for a varbinary(max) column doesn't apply to FILESTREAM; you are limited only by the available space on the NTFS file system.
• You can insert, update, and delete both relational and non-relational data within a single transaction.
• Using FILESTREAM is very efficient, because SQL Server doesn't use buffer pool memory to operate on non-relational (BLOBs) data (as is the case with traditional BLOB columns).
• You can access non-relational data directly from within middle tier .NET code using ADO.NET without having to resort to a separate API.
• Depending on the size of the file, the NTFS file system can save and retrieve large BLOBs faster than SQL Server.

The examples in this article showed how to implement the new FILESTREAM feature. As you can see, FILESTREAM provides an easy-to-use transaction programming model when you want to store both relational and non-relational data in a single transaction.

Previous Page