Using SQL Server 2008 FILESTREAM in an N-Tier Solution

For my recent talk at SQL Saturday #14, I had to find a solution that would allow a client to read SQL Server 2008 FILESTREAM BLOBs using SqlFileStream.

The Problem

While the addition of the FILESTREAM attribute is a great solution to a common problem, most performance benefits are only realized when accessing the BLOBs using file streams, and not using T-SQL. When your client or middle tier wants to use SqlFileStream, they need an open SqlTransaction. Opening a SqlTransaction in a tier other than the Data Access layer creates an architecture problem.

Solution Alternatives

As I see it, there are four solution alternatives:

  1. Pass the BLOB as a byte array through the tiers
    That’s not an attractive solution at all, because it has no performance benefits for the client or middle tier (note that there are still performance benefits for SQL Server, such as the BLOB not taking up valuable buffer space).
    This is, however, the only solution when using disconnected clients or clients not on your LAN. After all, you are probably not going to get your network admin to open up the required ports on the firewall to access a file share. Also, it’s important to understand that WCF and Silverlight type scenarios are not what FILESTREAM was designed to handle.
  2. Start a SqlTransaction in the client tier or middle tier
    To me, that’s a very poor design. SqlTransactions are supposed to live in your data tier, not in the client tier. One could argue about starting a SqlTransaction in the middle tier, but it’s still not my favorite design.
    However, this solution is easy to implement.
  3. Start the SqlTransaction in the data tier, open the SqlFileStream and pass the stream back to the client
    This is better from an architectural viewpoint, but leaves one problem: how will the SqlTransaction and SqlConnection be disposed properly?
  4. Create a custom class containing the SqlConnection, SqlTransaction and SqlFileStream and pass that back to the client
    This solution encapsulates the data tier objects, while allowing them to be disposed by the client or middle tier as part of disposing the SqlFileStream.

The Code

The code below implements the custom class. This is for a scenario where we are retrieving the album image of a CD. This example can actually be generalized much better. One could easily design an abstract base class containing all this functionality and re-use it for many different FILESTREAM fields and even across projects.

public class AlbumArt : MarshalByRefObject, IDisposable
  private System.Data.SqlClient.SqlTransaction Transaction { get; set; }
  public System.Data.SqlTypes.SqlFileStream Stream { get; set; }
  private System.Data.SqlClient.SqlConnection Connection { get; set; }

  public AlbumArt(System.Data.SqlClient.SqlTransaction transaction,
                  System.Data.SqlTypes.SqlFileStream stream,
                  System.Data.SqlClient.SqlConnection connection)
    Transaction = transaction;
    Stream = stream;
    Connection = connection;

#region IDisposable Members

  void IDisposable.Dispose()

  private void Dispose(bool disposing)
    if (disposing)
      // Call release on contained classes (none here)

    // Release unmanaged resources




  • This custom class lives with the other domain objects.
  • It inherits from MarshallByRefObject to allow it to be remoted across tiers. (Can’t serialize a SqlConnection or SqlTransaction).

2 thoughts on “Using SQL Server 2008 FILESTREAM in an N-Tier Solution

  1. How do you coordinate different sequential (but transactional) data access operations on different objects (say you have to check security on an object, and/or a variety of other validation conditions, before updating it) if you don’t have a transaction in your business/domain layer?

    1. @Simon: You wouldn’t, I would think the best solution is to have a Transaction in the business/domain layer (but not a SqlTransaction, use a System.Transactions.TransactionScope instead).

Let me know what you think, or ask a question...

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.