I created a small MVC4 website for file sharing using the first code approach. Files are stored in my database as
public byte[] Content { get; set; }
Ive set
<security> <requestFiltering> <requestLimits maxAllowedContentLength="536870912" /> </requestFiltering> </security>
and
<httpRuntime targetFramework="4.5" executionTimeout="6000" maxRequestLength="524288"/>
To limit the file size to 500 MB, the file actually turns it into my code (it got stuck at first).
The real problem arises when I received the file and try to save it to the database, I call:
DbContext.SaveChanges()
And get this error:
An exception of type "System.OutOfMemoryException" occurred in System.Data.Entity.dll, but was not processed in the user code.
I guess this is because I push some kind of limit, as the amount of data can be stored in byte [] in the database (or how much memory I am allowed to use). Everything works when downloading smaller files.
The database server is a SQL 2008 R2 Standard server.
I would prefer not to store files on disk for the sake of simplicity. What are my options?
EDIT: Using the direct SQL query suggestion to insert a file, I missed the first problem of getting the file in the database:
DbContext.Database.ExecuteSqlCommand( @"UPDATE FileContents SET Content = @data WHERE ID = @id", new SqlParameter("id", content.ID), new SqlParameter("data", bytearray));
But now I get the same error when trying to get a file from the database. This causes an error:
byte[] respdata = DbContext.Database.SqlQuery<Byte[]> ("SELECT TOP 1 Content FROM FileContents WHERE ID = @id", new SqlParameter("id", filepost.File.ID)).Single();
Again, it works with smaller files and 100 MB, but the failure on files is 200 MB.
Adding stacktrace to the question as per the comment in the answers below:
System.OutOfMemoryException failed to execute user code
HResult = -2147024882 Message = type exception Fixed "System.OutOfMemoryException". Source = mscorlib
Stack traces: in System.Object.MemberwiseClone () in System.Array.Clone () in System.Data.Common.CommandTrees.DbConstantExpression..ctor (TypeUsage resultType, Object value) in System.Data.Mapping.Update.Internal. UpdateCompiler.GenerateValueExpression (EdmProperty property, value PropagatorResult) in System.Data.Mapping.Update.Internal.UpdateCompiler.BuildSetClauses (DbExpressionBinding target, PropagatorResult, PropagatorResult originalRow, Processor TableChangeProcessorModelBementlementary 2& outputIdentifiers, DbExpression& returning, Boolean& rowMustBeTouched) at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor) at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler) at System.Data.Mapping.Update.Internal.UpdateTranslator.<ProduceDynamicCommands>d__0.MoveNext() at System.Linq.Enumerable.<ConcatIterator>d__71 2& outputIdentifiers, DbExpression& returning, Boolean& rowMustBeTouched) at System.Data.Mapping.Update.Internal.UpdateCompiler.BuildInsertCommand(PropagatorResult newRow, TableChangeProcessor processor) at System.Data.Mapping.Update.Internal.TableChangeProcessor.CompileCommands(ChangeNode changeNode, UpdateCompiler compiler) at System.Data.Mapping.Update.Internal.UpdateTranslator.<ProduceDynamicCommands>d__0.MoveNext() at System.Linq.Enumerable.<ConcatIterator>d__71 1.MoveNext () in System.Data.Mapping.Update.Internal.UpdateCommandOrderer..ctor (IEnumerable 1 commands, UpdateTranslator translator) at System.Data.Mapping.Update.Internal.UpdateTranslator.ProduceCommands() at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at FilePublicator2.Controllers.FileController.Upload(String qqfile) in d:\Projects\FilePublicator2\trunk\FilePublicator2\Controllers\FileController.cs:line 115 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary 1 commands, UpdateTranslator translator) at System.Data.Mapping.Update.Internal.UpdateTranslator.ProduceCommands() at System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter) at System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache) at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options) at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at FilePublicator2.Controllers.FileController.Upload(String qqfile) in d:\Projects\FilePublicator2\trunk\FilePublicator2\Controllers\FileController.cs:line 115 at lambda_method(Closure , ControllerBase , Object[] ) at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary 2 parameters) in System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod (ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionaryW 2 parameters) at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41() at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8 vultravents.result.vres.ultsvres.ultsvres.ult.scripts 1.End () in System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod (IAsyncResult AsyncResult) in System.Web.Mvc.Async.AsyncControllerActionInvoker. <> c_DisplayClass37. <> c_DisplayClass39.b_33 () in System.Web.Mvc.Async.AsyncControllerActionInvoker. <> c_DisplayClass4f.b_49 () InnerException:
Decision:
Here is a complete example of how this was resolved:
http://www.syntaxwarriors.com/2013/stream-varbinary-data-to-and-from-mssql-using-csharp/