Layered application: Store file in filestream in the database

Question

For an asp.Net MVC project, I will need to handle large files( mostly 200-300Mo, sometime 1Go).

I will store them in the database(for backup reasons/consistency reason).

I'm concerned by performance issue, so I want to avoid all I can to have a array of byte anywhere in the program, the goal is then to work with stream every where.

I've a layered application, which mostly means that I've several "DataStore", which are responsible to connect and retrieve/insert/update data from the database.

Since EF doesn't support Filestream for now, I'm handling the "File part" through simple Sql requests. I've read a good article on filestream usage here: http://blog.tallan.com/2011/08/22/using-sqlfilestream-with-c-to-access-sql-server-filestream-data/

And I've some additional questions, which I hope you can help me/point me to the good direction:

• Since I've a layered application, once I've my SQLFileStream object instantiated, could I dispose SqlCommand/Sql Connection/Transaction scope?
• If not, how I'm supposed to close them?
• In the previous link, there is an example which show how to use it with ASP. But since I'm using ASP.Net MVC, isn't there an helper which is directly able to stream a file to the browser? Because I found many example of return binary data to browser, but for now, all example I found make basically something like Stream.ToArray() to fill an array of byte and return it to the browser. I found that I can return a FileStreamResult which can take in parameter a Stream. Is that the right direction?

(I'm not currently concerned by uploading big files, since they are inserted by an heavy client in the database)

EDIT

(Sorry for the dirty code, it's only to not have 50 different methods here. I've made a few more try, and I'm currently stuck with the "read" part, because of separated part(where we generate the layer and where we consume it):

        SqlConnection conn = GetConnection();
        conn.Open();
        SqlCommand cmd = new SqlCommand(_selectMetaDataRequest, conn);
        cmd.Parameters.Add(_idFile, SqlDbType.Int).Value = idFile;
        SqlDataReader rdr = cmd.ExecuteReader();
        rdr.Read();
        string serverPath = rdr.GetSqlString(0).Value;
        byte[] serverTxn = rdr.GetSqlBinary(1).Value;
        rdr.Close();
        return new SqlFileStream(serverPath, serverTxn, FileAccess.Read);

But I get an exception at rdr.GetSqlBinary(1).Value because GET_FILESTREAM_TRANSACTION_CONTEXT return null. I found here that this is due the missing transaction.

I tried with a "TransactionScope"+its .Complete(); call. Doesn't change anything.

I tried to do a BEGIN TRANSACTION like showed in the previous link:

        SqlConnection connection = GetConnection();
        connection.Open();
        SqlCommand cmd = new SqlCommand();

        cmd.CommandText = "BEGIN TRANSACTION";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        cmd.ExecuteNonQuery();

        cmd = new SqlCommand(_selectMetaDataRequest, connection);
        cmd.Parameters.Add(_idFile, SqlDbType.Int).Value = idFile;
        SqlDataReader rdr = cmd.ExecuteReader();
        rdr.Read();
        string serverPath = rdr.GetSqlString(0).Value;
        byte[] serverTxn = rdr.GetSqlBinary(1).Value;
        rdr.Close();
        SqlFileStream sqlFileStream = new SqlFileStream(serverPath, serverTxn, FileAccess.Read);

      cmd = new SqlCommand();
        cmd.CommandText = "COMMIT TRANSACTION";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = connection;
        cmd.ExecuteNonQuery();

But it crashes on the first "ExecuteNonQuery" with the exception "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back." But it's the FIRST query executed!

Answer 1

Let's have an example. We could start by defining a contract that will describe the operation we are willing to perform:

public interface IPhotosRepository
{
    void GetPhoto(int photoId, Stream output);
}

We will see the implementation later.

Now we could define a custom action result:

public class PhotoResult : FileResult
{
    private readonly Action<int, Stream> _fetchPhoto;
    private readonly int _photoId;
    public PhotoResult(int photoId, Action<int, Stream> fetchPhoto, string contentType): base(contentType)
    {
        _photoId = photoId;
        _fetchPhoto = fetchPhoto;
    }

    protected override void WriteFile(HttpResponseBase response)
    {
        _fetchPhoto(_photoId, response.OutputStream);
    }
}

then a controller which will allow us to show the photo:

public class HomeController : Controller
{
    private readonly IPhotosRepository _repository;

    public HomeController(IPhotosRepository repository)
    {
        _repository = repository;
    }

    public ActionResult Index()
    {
        return View();
    }

    public ActionResult Photo(int photoId)
    {
        return new PhotoResult(photoId, _repository.GetPhoto, "image/jpg");
    }
}

and a corresponding view in which we are going to show the photo in an <img> tag using the Photo action:

<img src="@Url.Action("photo", new { photoid = 123 })" alt="" />

Now the last part is of course the implementation of the repository which might look something along the lines of:

public class PhotosRepositorySql : IPhotosRepository
{
    private readonly string _connectionString;
    public PhotosRepositorySql(string connectionString)
    {
        _connectionString = connectionString;
    }

    public void GetPhoto(int photoId, Stream output)
    {
        using (var ts = new TransactionScope())
        using (var conn = new SqlConnection(_connectionString))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText =
            @"
                SELECT 
                    Photo.PathName() as path, 
                    GET_FILESTREAM_TRANSACTION_CONTEXT() as txnToken 
                FROM 
                    PhotoAlbum 
                WHERE 
                    PhotoId = @PhotoId
            ";
            cmd.Parameters.AddWithValue("@PhotoId", photoId);
            using (var reader = cmd.ExecuteReader())
            {
                if (reader.Read())
                {
                    var path = reader.GetString(reader.GetOrdinal("path"));
                    var txnToken = reader.GetSqlBinary(reader.GetOrdinal("txnToken")).Value;
                    using (var stream = new SqlFileStream(path, txnToken, FileAccess.Read))
                    {
                        stream.CopyTo(output);
                    }
                }
            }
            ts.Complete();
        }
    }    
}

All that's left now is to instruct your favorite DI framework to use PhotosRepositorySql.

This technique allows you to efficiently work with arbitrary big files as it never loads the entire stream into memory.

Answer 2

Check this answer for an example of successfully downloading files of up to 4GB: https://stackoverflow.com/a/3363015/234415

A couple of interesting points to remember about using Filestream:

• The files do not count as part of the size limit (so it works nicely with SQLEXPRESS which has a 10GB limit for the SQL 2008 R2 version. So you can have 10GB of data and terabytes of files, all for free, which is cool.
• You HAVE to use integrated security (MSDN) , it doesn't support SQL login. This is annoying if you want to use shared hosting!

Answer 3

A SQL Database works very bad with huge files, and there are registry size limit too. I recommend to use a NoSQL databse (like MongoDB) to store that huge files. you can use two databases, (SQL for plain data, NoSQL for files) there no problem.

I know this is not the answer you want, but is the best way to have good performance.