Efficiently adding large amounts of text to a database

Question

I have a C# app that needs to add large amounts of text to a SQL Server database (one row could have up to 1GB of text). So, I have some code that looks like:

SqlParameter param = command.Parameters.Add("@text", SqlDbType.NVarChar);
param.Value = new string(buffer);

The text is added in chunks, so the buffer is say, 20 MB. I've tried to reduce memory pressure by re-using buffers (as opposed to doing a new char[10000000] every time I need to add a chunk). What I'm noticing is that ADO.NET seems to be allocating a new buffer for every time my sproc is called. Every time the sproc is called, I see my process's working set go up by almost exactly 20 MB.

Now, you might think that's because of the "new string(buffer)" (I did), but I get the same behavior when I do

param = command.Parameters.Add("@text", SqlDbType.Text, buffer.Length);
param.Value = buffer;

Is there any way to prevent ADO.NET from doing the extra memory allocation every time I add a chunk of text to the database?

Thanks!

Answer 1

If you use SqlDbType.VarBinary with a byte[] array, then ADO.NET will use the buffer you provide without copying.