Linq to Entities and SQL Server 2008 FileStream

Question

Backend: SQL Server 2008 database with FileStream enabled

Data Access: Linq to Entities

I have thousands of pdf's that currently reside on a file server. I would like to move these pdf's off of the file server and into a SQL Server 2008 database so that I can manage them easier.

As a proof of concept (ie - to ensure that the new FileStream ability in SQL Server 2008 is what I'm looking for), I wrote a small app that would read and write these pdf's to the FileStream enabled database via the entities framework.

The app is very simple; here's the code:

datReport report = new datReport();
report.ReportName = "ANL-7411-Rev-Supp-1.pdf";
report.RowGuid = Guid.NewGuid();

// The following line blows up on really big pdf's (350+ mb's)
report.ReportData = File.ReadAllBytes(@"C:\TestSavePDF\ANL-7411-Rev-Supp-1.pdf");

using (NewNNAFTAEntities ctx = new NewNNAFTAEntities()) {
   ctx.AddTodatReport(report);
   ctx.SaveChanges();
}

I have the line of code commented above where the error occurs. The exact error is 'System.outofmemoryexception', which leaves me with little doubt that the file size is what is causing the problem. The above code does work on smaller pdf's. I don't know where the exact limit is as far as file size, but my biggest pdf's are over 350 megabytes and they get the error.

Any help would be greatly appreciated. Thanks!

Answer 1

I would suggest checking Task Manager on the server where SQL Server is installed and check the memory usage when you try to load the largest PDF's into the database, which causes the failure.

Also check the tempdb to see if thats being used and causing the problem.

I would be interested to see the outcome of this as i'm doing something very similar.

Answer 2

You're not using the streaming in FILESTREAM very much in your example....

Check out the MSDN docs on FILESTREAM in ADO.NET and this article that both show how to use the SqlFileStream as a stream from C# - that should work a lot better (I would believe) than sucking the whole PDF into memory....

Marc