Read/write large blob to SQL Server from C#

Rather than try to make it happen in one big command, here’s breaking it out into many commands. Whether or not you use the transaction is up to you and your use case – you could always have a “Completed” column and only set that to true after success, if you wanted to skip a transaction.

First, insert a record, leaving the blob column as an empty byte array, making sure you have access to the primary key of the record you just inserted:

using var conn = new SqlConnection(_config.GetConnectionString("MyDB"));
await conn.OpenAsync().ConfigureAwait(false);
using var tran = (SqlTransaction)await conn.BeginTransactionAsync();
int fileID;
using (var comm = conn.CreateCommand())
{
    comm.Transaction = tran;
    comm.CommandText = @"
        insert dbo.Files (FileName, FileContents)
        values (@FileName, 0x);
        select scope_identity();
    ";
    comm.Parameters.AddWithValue("@FileName", fileName);
    fileID = (int)(await comm.ExecuteScalarAsync().ConfigureAwait(false));
}

Then, in the same connection, write the bytes, 8000 at a time, appending the blob column, and commit the transaction once they’re all done. The “set FileContents.Write” function is a little clumsy, but it works:

using var fileStream = System.IO.File.OpenRead(file);
byte[] buffer = new byte[8000];
int count;
while ((count = fileStream.Read(buffer, 0, buffer.Length)) > 0)
{
    byte[] tmp = new byte[count];
    Array.Copy(buffer, 0, tmp, 0, count);
    using var comm = conn.CreateCommand();
    comm.Transaction = tran;
    comm.CommandText = "update dbo.Files set FileContents.write(@ContentChunk, null, null) where FileID = @FileID";
    comm.Parameters.AddWithValue("@FileID", fileID);
    comm.Parameters.AddWithValue("@ContentChunk", tmp);
    await comm.ExecuteNonQueryAsync();
}
await tran.CommitAsync();

To read the data, use the substring function:

private async IAsyncEnumerable<byte[]> ReadFileAsync(int fileID)
{
    int startingByte = 1;
    while (true)
    {
        byte[] bytes;
        using var conn = new SqlConnection(_config.GetConnectionString("MyDB"));
        await conn.OpenAsync().ConfigureAwait(false);
        using var comm = conn.CreateCommand();
        comm.CommandText = @"
            select substring(FileContents, @StartingByte, 8000) [FileContents]
            from dbo.Files
            where FileID = @FileID;
        ";
        comm.Parameters.Add(new SqlParameter("@FileID", SqlDbType.Int) { Value = fileID });
        comm.Parameters.Add(new SqlParameter("@StartingByte", SqlDbType.Int) { Value = startingByte });
        using var rdr = await comm.ExecuteReaderAsync().ConfigureAwait(false);
        if (!await rdr.ReadAsync().ConfigureAwait(false))
        {
            break;
        }
        bytes = (byte[])rdr[0];
        if (bytes == null || bytes.Length == 0)
        {
            break;
        }
        startingByte += bytes.Length;
        yield return bytes;
    }
}
 
 
await foreach (var byteArray in ReadImportFileAsync(importBatchID))
{
    fileWriter.Write(byteArray, 0, byteArray.Length);
}