Using SqlDataReader’s new async methods in .Net 4.5, Part 2: Examples
In the previous post about the new column-level asynchronous methods added to SqlDataReader (and DbDataReader) we covered two new SqlDataReader methods (IsDBNullAsync and GetFieldValueAsync<T>), how data is read from the network by the data reader and guidance on how to when to use, and not use, asynchronous methods. To follow up from that post, we will be going through a couple of examples to demonstrate what needs to be considered when implementing a solution with asynchronous ADO.NET.
AdventureWorks
For this example, we will be using the “Adventure Works for SQL Server 2012” database running on SQL Server 2012 (if you are running Visual Studio 2012, then you should already have SQL Server 2012 Express LocalDB installed). The full example is attached, including a basic command line “harness” to run the data access layer.
The queries in the example focus around getting the details, reviews and photos for a product from the database which could, for instance, be used by a website for the product’s page:
GetProductByIdCommand = "SELECT [Name], [Color], [ListPrice], [DiscontinuedDate] FROM [Production].[Product] WHERE [ProductID]=@productid";
GetProductPhotoByIdCommand = "SELECT [pp].[LargePhotoFileName], [pp].[LargePhoto] FROM [Production].[ProductPhoto] AS [pp] INNER JOIN [Production].[ProductProductPhoto] AS [ppp] ON [pp].[ProductPhotoID]=[ppp].[ProductPhotoID] WHERE [ppp].[Primary]=1 AND [ppp].[ProductID]=@productid";
GetProductReviewsPagedById = "SELECT [EmailAddress], [ReviewDate], [Rating], [Comments] FROM [Production].[ProductReview] WHERE [ProductId]=@productid ORDER BY [ReviewDate] ASC OFFSET @reviewStart ROWS FETCH FIRST @reviewCount ROWS ONLY";
The “GetProductReviewsPagedById” query also uses the new Offset\Fetch keywords which were introduced in SQL Server 2012 to allow groups of reviews to be retrieved (instead of all of the product’s reviews, which may take a while to send to the client if there are many reviews).
Products and Reviews
Since both the product details and reviews are limited in size (about 150 bytes per product and under 8000 bytes for a review) it will be best to read them using the default, “non-sequential” access mode. As an optimization we can also retrieve the first “batch” of reviews when reading the product details:
public static async Task<Product?> GetProductAndReviews(int productID, int reviewsToGet)
{
// Create a connection, open it and create a command on the connection
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
await connection.OpenAsync();
const string commandString = GetProductByIdCommand + ";" + GetProductReviewsPagedById;
using (SqlCommand command = new SqlCommand(commandString, connection))
{
command.Parameters.AddWithValue("productid", productID);
command.Parameters.AddWithValue("reviewStart", 0); // Always start with the first review
command.Parameters.AddWithValue("reviewCount", reviewsToGet);
// Since none of the rows are likely to be large,
// we will execute this without specifying a CommandBehavior
// This will cause the default (non-sequential) access mode to be used
using (SqlDataReader reader = await command.ExecuteReaderAsync())
{
// Always use ReadAsync
if (await reader.ReadAsync())
{
// Since this is non-sequential mode,
// all columns should already be read in by ReadAsync
// Therefore we can access individual columns synchronously
Product product = GetProductFromReader(reader, productID);
// Always use NextResultAsync
if (await reader.NextResultAsync())
{
List<Review> allReviews = new List<Review>();
while (await reader.ReadAsync())
{
// We're in non-sequential mode, so access data synchronously
Review review = GetReviewFromReader(reader);
allReviews.Add(review);
}
// If we are here, then everything has worked
// So attach the reviews to the product and return the final value
product.Reviews = allReviews.AsReadOnly();
return product;
}
else
{
// No result set for the Reviews, which is unexpected
throw new InvalidOperationException("Query to server failed to return list of reviews");
}
}
else
{
// There were no results when trying to get the Product
return null;
}
}
}
}
}
So far all calls to SqlDataReader in this example have been asynchronous, but getting the actual column values has been left to the “GetProductFromReader” and “GetReviewFromReader” methods. Since the data reader is in non-sequential mode and ReadAsync was used, the column data should be read synchronously:
private static Product GetProductFromReader(SqlDataReader reader, int productID)
{
Product product = new Product();
product.ProductID = productID;
product.Name = reader.GetFieldValue<string>(0);
product.ListPrice = reader.GetFieldValue<decimal>(2);
// Only set the Color property if it is not null
// Otherwise GetFieldValue<string>() will throw a SqlNullValueException
if (!reader.IsDBNull(1))
{
product.Color = reader.GetFieldValue<string>(1);
}
// An alternative to IsDBNull for nullable columns is to get the SqlType for the column,
// then inspect the IsNull property
SqlDateTime discontinuedDate = reader.GetFieldValue<SqlDateTime>(3);
product.DiscontiuedDate = discontinuedDate.IsNull ? null : (DateTime?)discontinuedDate.Value;
return product;
}
private static Review GetReviewFromReader(SqlDataReader reader)
{
Review review = new Review();
review.EmailAddress = reader.GetFieldValue<string>(0);
review.ReviewDate = reader.GetFieldValue<DateTime>(1);
review.Rating = reader.GetFieldValue<int>(2);
// Comments is nullable
review.Comments = reader.IsDBNull(3) ? null : reader.GetFieldValue<string>(3);
return review;
}
Photos
Retrieving the photo data in a separate command also suits the pattern for a website since this would need to be sent to the client in a different response that the HTML data containing the product information. Unlike the product details or reviews, the photo’s row can potentially be massive as the “LargePhoto” column is a VARBINARY(MAX) data type (which can be up to 2Gb in size). Since we don’t want to buffer the entire photo into memory we can use sequential mode with the new GetStream method and CopyToAsync to send the data directly to the client. Since the SqlDataReader will be in sequential access mode, ReadAsync will not be reading any row data so we will need to get the file’s name asynchronously:
public static async Task<string> CopyProductPhotoToStream(int productID, Stream outputStream)
{
// Create a connection, open it and create a command on the connection
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
await connection.OpenAsync();
using (SqlCommand command = new SqlCommand(GetProductPhotoByIdCommand, connection))
{
command.Parameters.AddWithValue("productid", productID);
// The photo data is a VARBINARY(MAX), which could be up to 2Gb in size
// While we don't expect any photos to be that size,
// they can potentially be megabytes in size
// Therefore it is better to execute the reader in sequential access mode
// And stream the data directly to the output stream
// (such as a file or a HTTP response stream) than to buffer the entire row at once
using (SqlDataReader reader = await command.ExecuteReaderAsync(CommandBehavior.SequentialAccess))
{
// Always use ReadAsync
if (await reader.ReadAsync())
{
// This is sequential access mode, so we need to read all columns asynchronously
// since ReadAsync will read upto the start of the row,
// but it will not read any column data
// The photo's name is nullable, but we want to use an empty string to represent no
// name, so first check if the column is null and, if not, read the actual value
string photoName = await reader.IsDBNullAsync(0) ? string.Empty : await reader.GetFieldValueAsync<string>(0);
// The photo data is also nullable,
// so check if there is data available, otherwise we need to return null
if (await reader.IsDBNullAsync(1))
{
// No photo data, return null
return null;
}
else
{
// GetStream doesn't actually read any data, so it will never block
using (Stream photoData = reader.GetStream(1))
{
// Copy all data across then return a non-null name to indicate success
await photoData.CopyToAsync(outputStream);
return photoName;
}
}
}
else
{
// There is no photo for this product, so return null
return null;
}
}
}
}
}
Conclusion
When using a SqlDataReader it is important to consider how large each row can potentially be and, therefore, if you should be using the default (non-sequential) or sequential access mode. Once you have chosen the access mode, then you can consider whether or not to access columns asynchronously.
Happy Coding!
Daniel Paoliello
Software Development Engineer
ADO.NET Managed Providers and DataSet Team