Streaming

Many SQL Server databases store binary data in varbinary(n) or varbinary(max) columns. These Binary Large Objects (BLOBs) can take non-trivial amount of time to to retrieve from the database (sheer network IO). Ideally, such BLOB retrievals should be streamed from the database, so that the retrieving client can start and/or forward the download as soon as some BLOB data is received. BLOB streaming also reduces memory pressure caused by buffering, and allows retrieval of BLOBs much larger than the memory available on the SQL client.

TinyORM makes BLOB streaming easy:

ValueTask<bool> SequentialReaderAsync(
	string sql, /* sql parameters, if any */,
	Func<SqlDataReader, ValueTask<bool>> actionAsync);

SequentialReaderAsync takes a query and a user-provided callback:

var ms = new MemoryStream(); // assume this is your streaming target (ex. Response buffer in ASP.NET)
using (var ts = DbContext.CreateTransactionScope())
{
	await db.SequentialReaderAsync(@"
	SELECT d.name, d.ContentLength, d.ContentType, d.Content
	FROM dbo.[Document] d
	WHERE d.ContentLength > 0 AND d.ContentType = 'image/jpeg';",
	async (reader, ct /* cancellationToken */) =>
	{
		do
		{
			while (await reader.ReadAsync(ct))
			{
				var fileName = reader.GetString(0); // 1st column as string
				var fileSize = reader.GetInt64(1);  // 2nd column as int
				var fileType = reader.GetString(2); // 3rd column as string

				if (!await reader.IsDBNullAsync(3, ct)) // 4th column is VARBINARY
				{
					await reader.GetStream(3).CopyToAsync(ms, 16 /* tiny buffer size for streaming illustration only */, ct);
					Console.WriteLine("File: {0} Size: {1} Type: {2}", fileName, fileSize, fileType);
				}
				else Console.WriteLine("[NULL]");
				ms.SetLength(0);
			}
		} while (await reader.NextResultAsync(ct));
		return true;
	});
	ts.Complete();
}

SequentialReaderAsync opens the exposed SqlDataReader in SequentialAccess mode, which is necessary for streaming. This also implies that all columns must be accessed in forward-only manner. Ex. if you access a column #4 you can no longer access all preceding columns 1,2,3. Since you usually want to process all non-BLOB columns before streaming out the BLOB column, put the BLOB column last in your query. In the example above files are streamed with the correct filename, content-length, and content-type by reading these meta-data columns before accessing and streaming the last BLOB column.