c# - Layered application: Store file in filestream in the database -

c# - Layered application: Store file in filestream in the database -

for asp.net mvc project, need handle big files( 200-300mo, sometime 1go).

i store them in database(for backup reasons/consistency reason).

i'm concerned performance issue, want avoid can have array of byte anywhere in program, goal work stream every where.

i've layered application, means i've several "datastore", responsible connect , retrieve/insert/update info database.

since ef doesn't back upwards filestream now, i'm handling "file part" through simple sql requests. i've read 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 additional questions, hope can help me/point me direction:

since i've layered application, 1 time i've sqlfilestream object instantiated, dispose sqlcommand/sql connection/transaction scope? if not, how i'm supposed close them? in previous link, there illustration show how utilize asp. since i'm using asp.net mvc, isn't there helper straight able stream file browser? because found many illustration of homecoming binary info browser, now, illustration found create stream.toarray() fill array of byte , homecoming browser. found can homecoming filestreamresult can take in parameter stream. right direction?

(i'm not concerned uploading big files, since inserted heavy client in database)

edit

(sorry dirty code, it's not have 50 different methods here. i've made few more try, , i'm stuck "read" part, because of separated part(where generate layer , 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(); homecoming new sqlfilestream(serverpath, servertxn, fileaccess.read);

but exception @ rdr.getsqlbinary(1).value because get_filestream_transaction_context homecoming null. found here due missing transaction.

i tried "transactionscope"+its .complete(); call. doesn't alter anything.

i tried begin transaction showed in 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 crashes on first "executenonquery" exception "a transaction started in mars batch still active @ end of batch. transaction rolled back." it's first query executed!

let's have example. start defining contract describe operation willing perform:

public interface iphotosrepository { void getphoto(int photoid, stream output); }

we see implementation later.

now define 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 controller allow show photo:

public class homecontroller : controller { private readonly iphotosrepository _repository; public homecontroller(iphotosrepository repository) { _repository = repository; } public actionresult index() { homecoming view(); } public actionresult photo(int photoid) { homecoming new photoresult(photoid, _repository.getphoto, "image/jpg"); } }

and corresponding view in going show photo in <img> tag using photo action:

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

now lastly part of course of study implementation of repository might along 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() path, get_filestream_transaction_context() txntoken photoalbum 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 instruct favorite di framework utilize photosrepositorysql.

this technique allows efficiently work arbitrary big files never loads entire stream memory.

c# asp.net asp.net-mvc stream filestream