Layered Application: Store the file in filestream in the database

Question

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

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

I am concerned about the performance problem, so I want to avoid everything I can to have an array of bytes anywhere in the program, the goal is to work with the stream every time.

I have a multi-level application, which basically means that I have several "DataStore" that are responsible for connecting and receiving / inserting / updating data from the database.

Since EF does not support Filestream, I process the "part of the file" through simple Sql queries. I read a good article about using streams: http://blog.tallan.com/2011/08/22/using-sqlfilestream-with-c-to-access-sql-server-filestream-data/

And I have additional questions that I hope you can help me / point out a good direction:

• Since I have a layered application, as soon as I instantiate an SQLFileStream object, can I place the SqlCommand / Sql Connection / Transaction scope?
• If not, how should I close them?
• , , ASP. ASP.Net MVC, , ? , - Stream.ToArray(), . , FileStreamResult, a Stream. ?

( , )

( , 50 . , "read" - ( ):

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

rdr.GetSqlBinary(1).Value, GET_FILESTREAM_TRANSACTION_CONTEXT null. , .

"TransactionScope" + .Complete();. .

BEGIN TRANSACTION, :

        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();

"ExecuteNonQuery" "A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back." FIRST-!

Answer 1

. , , :

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

.

:

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);
    }
}

, :

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");
    }
}

, <img>, Photo:

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

, , , :

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();
        }
    }    
}

, , - DI PhotosRepositorySql.

, .

Answer 2

4 : fooobar.com/questions/222342/...

, Filestream:

• ( SQLEXPRESS, 10 SQL 2008 R2. , 10 , , .
• (MSDN), SQL. , !

Answer 3

The SQL database works very poorly with huge files, and there is also a limit on the size of the registry. I recommend using a NoSQL database (e.g. MongoDB) to store these huge files. you can use two databases (SQL for simple data, NoSQL for files), no problem.

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