The Weekly Source Code 51 - Asynchronous Database Access and LINQ to SQL Fun
You can learn a lot by reading other people's source code. That's the idea behind this series, "The Weekly Source Code." You can certainly become a better programmer by writing code but I think good writers become better by reading as much as they can.
I was poking around in the WebFormsMVP project's code and noticed an interesting pattern.
You've seen code to get data from a database and retrieve it as an object, like this:
public Widget Find(int id)
{
Widget widget = null;
widget = (from w in _db.Widgets
where w.Id == id
select w).SingleOrDefault();
return widget;
}
This code is synchronous, meaning basically that it'll happen on the same thread and we'll wait around until it's finished. Now, here's an asynchronous version of the same code. It's a nice combination of the the new (LINQ, in this case, LINQ to SQL) and the older (DataReaders, etc). The LINQ (to SQL) query is in query, then they call GetCommand to get the underlying SqlCommand for that query. Then, they call BeginExecuteReader on the SqlCommand which starts asynchronous execution of that command.
SqlCommand _beginFindCmd = null;
public IAsyncResult BeginFind(int id, AsyncCallback callback, Object asyncState)
{
var query = from w in _db.Widgets
where w.Id == id
select w;
_beginFindCmd = _db.GetCommand(query) as SqlCommand;
_db.Connection.Open();
return _beginFindCmd.BeginExecuteReader(callback, asyncState, System.Data.CommandBehavior.CloseConnection);
}
public Widget EndFind(IAsyncResult result)
{
var rdr = _beginFindCmd.EndExecuteReader(result);
var widget = (from w in _db.Translate<Widget>(rdr)
select w).SingleOrDefault();
rdr.Close();
return widget;
}
When it's done, in this example, EndFind gets called and they call DataContext.Translate<T> passing in the type they want (Widget) and the source, the DataReader retrieved from EndExecuteReader. It's an asynchronous LINQ to SQL call.
I found it clever so I emailed my parallelism friend and expert Stephen Toub and asked him if this was any or all of the following:
a. clever
b. necessary
c. better done with PFX/TPL (Parallel Extensions to the .NET Framework/Task Parallel Library)
Stephen said, in his own get-down-to-business fashion:
a) It's a standard approach to converting a LINQ query to a command to be executed with more control over how it's executed. That said, I don't see it done all that much, so in that capacity it's clever.
b) It's necessary to run the query asynchronously; otherwise, the call to MoveNext on the enumerator will block. And if ADO.NET's MARS support is used (multiple asynchronous result sets), you could have multiple outstanding operations in play.
c) TPL can't improve upon the interactions with SQL Server, i.e. BeginExecuteReader will still need to be called. However, TPL can be used to wrap the call such that you get a Task<Widget> back, which might be a nicer API to consume. Once you have it as a Task, you can do useful things like wait for it, schedule work for when its done, wait for multiple operations or schedule work when multiple operations are done, etc.
One other thing that's interesting, is the WebFormsMVP project's PageAsyncTaskManagerWrapper:
namespace WebFormsMvp.Web
{
/// <summary>
/// Represents a class that wraps the page's async task methods
/// </summary>
public class PageAsyncTaskManagerWrapper : IAsyncTaskManager
{
readonly Page page;
/// <summary />
public PageAsyncTaskManagerWrapper(Page page)
{
this.page = page;
}
/// <summary>
/// Starts the execution of an asynchronous task.
/// </summary>
public void ExecuteRegisteredAsyncTasks()
{
page.ExecuteRegisteredAsyncTasks();
}
/// <summary>
/// Registers a new asynchronous task with the page.
/// </summary>
/// <param name="beginHandler">The handler to call when beginning an asynchronous task.</param>
/// <param name="endHandler">The handler to call when the task is completed successfully within the time-out period.</param>
/// <param name="timeout">The handler to call when the task is not completed successfully within the time-out period.</param>
/// <param name="state">The object that represents the state of the task.</param>
/// <param name="executeInParallel">The vlaue that indicates whether the task can be executed in parallel with other tasks.</param>
public void RegisterAsyncTask(BeginEventHandler beginHandler, EndEventHandler endHandler, EndEventHandler timeout, object state, bool executeInParallel)
{
page.RegisterAsyncTask(new PageAsyncTask(beginHandler, endHandler, timeout, state, executeInParallel));
}
}
}
They made a nice wrapper for these existing System.Web.UI.Page methods and they use it like this, combined with the asynchronous LINQ to SQL from earlier:
AsyncManager.RegisterAsyncTask(
(asyncSender, ea, callback, state) => // Begin
{
return widgetRepository.BeginFindByName(e.Name, callback, state);
},
result => // End
{
var widget = widgetRepository.EndFindByName(result);
if (widget != null)
{
View.Model.Widgets.Add(widget);
}
},
result => { } // Timeout
, null, false);
AsyncManager.ExecuteRegisteredAsyncTasks();
They fire off their task, which then does its database work asynchronously, and then it all comes together.
I'll leave (for now) the wrapping of the APIs to return a Task<TResult> as an exercise for the reader, but it'd be nice to see if this pattern can benefit from the Task Parallel Library or not.
About Scott
Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.
About Newsletter
