How to insert or update large data into varchar(max) using EF 6
ado.net c# entity-framework-6 sql-server
Question
I am using EF6 and SQL Server 2012. I have a table which contains text files in a varchar(max) column. Average file size is a few MBs but I am looking for a solution that will work for larger files too. I add or update this column using string data type but I don't think this is efficient way to update a few thousand records where each record is 4 to 5 Mb. Is there any efficient way to insert or update large data types in EF (or even in ADO.net)?
Accepted Answer
I ended up creating a stored procedure which uses write function of varchar(max). I was able to make my code 10x faster by calling SP instead of updating EF object. This SP then executes query similar to
update MyTable set col1.Write(@data, @oldLength, @data_length)
Popular Answer
For large files the field can be changed to varbinary(max) and the files can be converted into binary format and inserted into the database. For files that are greater than 1MB,Filestreams provide a better option. Try to visit Convert file to binary in C# for binary conversion.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
