0

I need a code to store (eventually large) files into DB using C#.

The solution I'm using is sth like: (I'm using varbinary(MAX) column in the DB)

1) Create SqlCommand

2) Create SqlParameter

3) Set parameter.Value = File.ReadAllBytes(filePath)

4) Execute SqlCommand

Is there any more effective solution? Since the file can be large, I'm affraid of performance problems, when reading all bytes into memory, and then storing them into DB.

Thank you in advance

Miroslav Kaděra
  • 25
  • 1
  • 4
  • This is not an exact duplicate, but very similar to http://stackoverflow.com/questions/8952/storing-a-file-in-a-database-as-opposed-to-the-file-system This question has been asked in many different ways here, and the consensus is always that storing files in a database is a bad idea. Performance will almost always be an issue, eventually. – David Jan 17 '11 at 18:54
  • 2
    @David - **Large** files. See this white paper: http://research.microsoft.com/apps/pubs/?id=64525 – Oded Jan 17 '11 at 18:56
  • Thanks for the link, @Oded. Reading it now. – David Jan 17 '11 at 18:57
  • @Oded - again, let me say.. NICE article. Thanks! I love this site! I'm always learning something new. – David Jan 17 '11 at 19:15
  • 1
    @David: The `FILESTREAM` type in SQL Server is also at least a *partial* cure for the issue that storing large binary objects in the database presents. – Adam Robinson Jan 17 '11 at 19:20
  • @David Thank you for your comment, storing data in DB is a fact i have to respect. The question was aimed on the C# solution (it was a C# coding question, not App-design question) - whether there is a way to pass data from File to SQL DB other then ReadBytes (==> load into memory) --> Parameter --> Command --> DB ... For example whether the file can be streamed to DB or something like that. – Miroslav Kaděra Jan 17 '11 at 19:37

2 Answers2

4

I'm facing the same issue right now. There is a way to sequentially write into varbinary fields. See this article: 'Download and Upload Images from SQL Server via ASP.NET MVC' http://www.codeproject.com/KB/aspnet/streamingblobhttp.aspx

It shows a way to use a command like:

UPDATE <table> SET <field>.WRITE(@data, null, null) WHERE ...

This allows writing chunks of data into your column without having to read an entire file.

Le Fab
  • 83
  • 1
  • 6
1

What you have is the best you can get with a varbinary(MAX) column. While you can stream data out of the database, there's no way to stream it in.

Adam Robinson
  • 182,639
  • 35
  • 285
  • 343