Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Educating and Consulting in IT
Browse by Tags · View All 16
SQL Server 2012 11
FileTable 7
speaking 1
service pack 1 1
giveaway 1
Troubleshooting 1

Archive · View All
January 2012 4
April 2012 3
May 2012 2
March 2013 1
January 2013 1
December 2012 1
November 2012 1
August 2012 1
July 2012 1
June 2012 1

Sven Aelterman's Blog

Using SQL Server 2008 FILESTREAM in an N-Tier Solution

Sep 7 2009 12:00AM by Sven Aelterman   

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

Republished from svenaelterman [17 clicks].  Read the original version here [3 clicks].

Sven Aelterman
673 · 0% · 50


Your Comment

Sign Up or Login to post a comment.

"Using SQL Server 2008 FILESTREAM in an N-Tier Solution" rated 5 out of 5 by 1 readers
Using SQL Server 2008 FILESTREAM in an N-Tier Solution , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]