// Application retrieving a large BLOB from SQL Server in .NET 4.5 using the new asynchronous capability
public async Task <BinaryDataModel> GetBinaryDataModel(Guid id)
{
using (var connection = (SqlConnection)_commonDb.GetConnection())
{
await connection.OpenAsync();
using (var command = new SqlCommand("SELECT fileName, [data] FROM [FileSystem] WHERE [id]=@id", connection))
{
command.Parameters.AddWithValue("id", id);
var dataModel = new BinaryDataModel();
// The reader needs to be executed with the SequentialAccess behavior to enable network streaming
// Otherwise ReadAsync will buffer the entire BLOB into memory which can cause scalability issues or even OutOfMemoryExceptions
using (var reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
if (await reader.ReadAsync())
{
dataModel.FileName = (string)reader[0];
dataModel.Stream = Stream.Null;
if (!(await reader.IsDBNullAsync(0)))
{
using (var data = reader.GetStream(1))
{
var ms = new MemoryStream();
data.CopyTo(ms);
dataModel.Stream = ms;
}
}
var tcs = new TaskCompletionSource <BinaryDataModel>();
tcs.SetResult(dataModel);
return(await tcs.Task);
}
throw new FileNotFoundException("Файл не найден");
}
}
}
}