There is no built-in ADO.Net functionality to handle this really gracefully for large data. The problem is two fold:
- there is no API to 'write' into a SQL command(s) or parameters as into a stream. The parameter types that accept a stream (like
FileStream) accept the stream to READ from it, which does not agree with the serialization semantics of write into a stream. No matter which way you turn this, you end up with a in memory copy of the entire serialized object, bad.
- even if the point above would be solved (and it cannot be), the TDS protocol and the way SQL Server accepts parameters do not work well with large parameters as the entire request has to be first received before it is launched into execution and this would create additional copies of the object inside SQL Server.
So you really have to approach this from a different angle. Fortunately, there is a fairly easy solution. The trick is to use the highly efficient UPDATE .WRITE syntax and pass in the chunks of data one by one, in a series of T-SQL statements. This is the MSDN recommended way, see Modifying Large-Value (max) Data in ADO.NET. This looks complicated, but is actually trivial to do and plug into a Stream class.
The BlobStream class
This is the bread and butter of the solution. A Stream derived class that implements the Write method as a call to the T-SQL BLOB WRITE syntax. Straight forward, the only thing interesting about it is that it has to keep track of the first update because the UPDATE ... SET blob.WRITE(...) syntax would fail on a NULL field:
class BlobStream: Stream
{
private SqlCommand cmdAppendChunk;
private SqlCommand cmdFirstChunk;
private SqlConnection connection;
private SqlTransaction transaction;
private SqlParameter paramChunk;
private SqlParameter paramLength;
private long offset;
public BlobStream(
SqlConnection connection,
SqlTransaction transaction,
string schemaName,
string tableName,
string blobColumn,
string keyColumn,
object keyValue)
{
this.transaction = transaction;
this.connection = connection;
cmdFirstChunk = new SqlCommand(String.Format(@"
UPDATE [{0}].[{1}]
SET [{2}] = @firstChunk
WHERE [{3}] = @key"
,schemaName, tableName, blobColumn, keyColumn)
, connection, transaction);
cmdFirstChunk.Parameters.AddWithValue("@key", keyValue);
cmdAppendChunk = new SqlCommand(String.Format(@"
UPDATE [{0}].[{1}]
SET [{2}].WRITE(@chunk, NULL, NULL)
WHERE [{3}] = @key"
, schemaName, tableName, blobColumn, keyColumn)
, connection, transaction);
cmdAppendChunk.Parameters.AddWithValue("@key", keyValue);
paramChunk = new SqlParameter("@chunk", SqlDbType.VarBinary, -1);
cmdAppendChunk.Parameters.Add(paramChunk);
}
public override void Write(byte[] buffer, int index, int count)
{
byte[] bytesToWrite = buffer;
if (index != 0 || count != buffer.Length)
{
bytesToWrite = new MemoryStream(buffer, index, count).ToArray();
}
if (offset == 0)
{
cmdFirstChunk.Parameters.AddWithValue("@firstChunk", bytesToWrite);
cmdFirstChunk.ExecuteNonQuery();
offset = count;
}
else
{
paramChunk.Value = bytesToWrite;
cmdAppendChunk.ExecuteNonQuery();
offset += count;
}
}
// Rest of the abstract Stream implementation
}
Using the BlobStream
To use this newly created blob stream class you plug into a BufferedStream. The class has a trivial design that handles only writing the stream into a column of a table. I'll reuse a table from another example:
CREATE TABLE [dbo].[Uploads](
[Id] [int] IDENTITY(1,1) NOT NULL,
[FileName] [varchar](256) NULL,
[ContentType] [varchar](256) NULL,
[FileData] [varbinary](max) NULL)
I'll add a dummy object to be serialized:
[Serializable]
class HugeSerialized
{
public byte[] theBigArray { get; set; }
}
Finally, the actual serialization. We'll first insert a new record into the Uploads table, then create a BlobStream on the newly inserted Id and call the serialization straight into this stream:
using (SqlConnection conn = new SqlConnection(Settings.Default.connString))
{
conn.Open();
using (SqlTransaction trn = conn.BeginTransaction())
{
SqlCommand cmdInsert = new SqlCommand(
@"INSERT INTO dbo.Uploads (FileName, ContentType)
VALUES (@fileName, @contentType);
SET @id = SCOPE_IDENTITY();", conn, trn);
cmdInsert.Parameters.AddWithValue("@fileName", "Demo");
cmdInsert.Parameters.AddWithValue("@contentType", "application/octet-stream");
SqlParameter paramId = new SqlParameter("@id", SqlDbType.Int);
paramId.Direction = ParameterDirection.Output;
cmdInsert.Parameters.Add(paramId);
cmdInsert.ExecuteNonQuery();
BlobStream blob = new BlobStream(
conn, trn, "dbo", "Uploads", "FileData", "Id", paramId.Value);
BufferedStream bufferedBlob = new BufferedStream(blob, 8040);
HugeSerialized big = new HugeSerialized { theBigArray = new byte[1024 * 1024] };
BinaryFormatter bf = new BinaryFormatter();
bf.Serialize(bufferedBlob, big);
trn.Commit();
}
}
If you monitor the execution of this simple sample you'll see that nowhere is a large serialization stream created. The sample will allocate the array of [1024*1024] but that is for demo purposes to have something to serialize. This code serializes in a buffered manner, chunk by chunk, using the SQL Server BLOB recommended update size of 8040 bytes at a time.
