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.

TinyORM makes BLOB streaming easy:

Task SequentialReaderAsync(string sql, /* sql parameters, if any */, Func<SqlDataReader, Task> 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));
	});
}