OutOfMemoryException SqlCommand VarChar(Max)

I am trying to insert a large amount of string data into a VARCHAR(MAX) field for SQLExpress R2 using ADO.NET (4.0 SP1). The table has a single column which is VARCHAR(MAX). Currently, the size of the string is 77012287. Here is the query:

insert into dbo.t_test (c1) values (@c1);

I am currently running a test app that constructs a new string as follows:

private static void ExecuteSqlQuery(int size) {
        Console.WriteLine("Inserting data. Parameter size: {0}", size);

        using (SqlConnection cn = new SqlConnection(SQL_CONNECTION_STRING)) {
            Console.WriteLine("Opening database connection.");
            cn.Open();

            using (SqlCommand cm = cn.CreateCommand()) {
                cm.CommandType = CommandType.Text;
                cm.CommandText = SQL_INSERT;
                cm.CommandTimeout = SQL_COMMAND_TIMEOUT;
                cm.Parameters.Add(SQL_PARM_DATA, SqlDbType.VarChar, -1).Value = new string('a', size);

                cm.ExecuteNonQuery();
            }

            Console.WriteLine("Closing database connection.");
        }
    }

I am receving a SqlException when trying to run this method with larger values. It never actually hits the database (I have profiled SQL Express). The SqlException is a TimeoutException on the ExecuteNonQuery call. If I run this method a few times before running it with the larger number, I will get a System.OutOfMemoryException on the ExecuteNonQuery call. It looks like the internal parser is trying to a char array and is throwing an error. How does one insert a large string value from .NET into SQL Express? Thanks.

See Download and Upload images from SQL Server via ASP.Net MVC for an example of how to stream a large file into a BLOB column, look at the Streaming Upload of BLOB data section. You can download the code from codeproject.

The gist of it is that you need to upload in chunks and use UPDATE set column.WRITE syntax because the ADO.Net client has no proper streaming semantics.

An alternative is to use FILESTREAM because the SqlFileStream type does support streaming, see FILESTREAM MVC: Download and Upload images from SQL Server for a full sample. But FILESTREAM storage comes with its quirks and is not a trivial decision (specially if your hosting doesn't offer it...).

It sounds like your strings are ending up on the Large Object Heap and not being compacted. Unfortunately, I don't know a good way to force a compact or cleanup of the Large Object Heap (LOH), and so the best advice I can give is to use shorter strings.

The LOH takes anything larger than a mere 80Kb. Remember that because strings are immutable, if you build the string up over time you may be inserting many different strings into to LOH in the processes of creating your single large string. These other strings will be collected by the GC and the the physical memory returned to the operating system, but because the LOH is not compacted (think defragmented) they will continue to use up address space available to your process, which is typically capped at a mere 2GB. At this point you will start to see OutOfMemory exceptions.