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()
  {
    Dispose(true);
    GC.SuppressFinalize(this);
  }

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

    // Release unmanaged resources
    Stream.Dispose();
    Transaction.Dispose();
    Connection.Dispose();
  }

  ~AlbumArt()
  {
    Dispose(false);
  }

#endregion
}

Notes

• 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).