Monthly Archives: October 2012

Using .NET 4.5 Task Async and Await with WPF and SQL Server

Most of the examples I’ve seen of  using the new async, await and Task methods in .NET 4.5 have been aimed at Windows 8 (Metro or “UI Style”) apps. This is understandable, given Microsoft’s push towards both a new OS and to populate the Windows 8 Store. But for some (many?) developers there is also a need to support both Windows 7 and Windows 8 Desktop. Notably missing from the WinRT scenario is the ability to directly connect to SQL Server. I (mostly) get the reasons for that, although it seems against Microsoft’s interests to cede that space to SQLite instead of a sandboxed, light version of SQL Server.

Anyway, I put together a sample WPF application (download below) that supports asynchronous connections to SQL Server. You can cancel or restart the demo query, which populates a ListView with data from the AdventureWorks DB Production.WorkOrder table, which had a decent number of records for testing. I really wanted to simulate some real world conditions, such as server-side delays, so that’s included too. One of my beefs with many of the samples I come across is that they never include decent error handling, especially to accomodate network latency or timeouts. This sample also includes the Microsoft.Data.ConnectionUI SQL Server connection dialog box.

One advantage of making a WPF app asynchronous is it no longer locks up while running a process. Try dragging the window around while the ListView is being populated with data. Yay, no UI-thread blocking!

Clicking the Start button triggers the StartTest method. Notice that in the query, I’ve embedded an artificial server-side delay of 3 seconds – WAITFOR DELAY ’00:00:03′; – which will simulate a slow server return. Also, a queryTimeout has been set to 120 seconds. If you set the WAITFOR delay longer than that, you will see an error occurs and is handled. I did my best to include specific SQL Server error handling so we get actual error details, rather than just “some crap happened and that’s all we know.”


private async void StartTest(object sender, RoutedEventArgs e)
{
try
{
 if (_connectionString == String.Empty)
 {
 var scsb = new SqlConnectionStringBuilder(Settings.Default.DBConnString);

_connectionString = scsb.ConnectionString;

if (_connectionString == String.Empty)
 {
 throw new Exception("Please use Change DB to first select a server and database.");
 }
 }

if (DbInfo.DbName == String.Empty)
 {
 throw new Exception("Please use Change DB to first select a database from the server.");
 }

// Note: If the start test button was disabled, this would be unnecessary
 _cts.Cancel();

// Re-initialize this. Otherwise, StartTest won't work.
 _cts = new CancellationTokenSource();

DataItemsList.Clear();

// Note the simulated delay, which will happen on the DB server. You can vary this to see its effect.
 // Try dragging the window around after hitting the start test button.
 const string query =
 @"WAITFOR DELAY '00:00:03'; SELECT * FROM [Production].[WorkOrder];";

// Note that setting the WAITFOR delay to exceed this, eg. 00:02:01, will trigger an exception
 int queryTimeout = 120;

RequestStatus = "started";

await ProcessQueryCancelleable(_connectionString, query, queryTimeout, _cts.Token);
}
catch (Exception ex)
{
 MessageBox.Show(ex.Message + " (from StartTest)");

Debug.WriteLine(ex.Message);
}
}

The ProcessQueryCancelleable method. Note the use of the CancellationToken…’cause users might actually want to cancel a long-running query. Inside this method is another simulated delay (await Task.Delay(TimeSpan.FromMilliseconds(1));):

/// <summary>
/// Runs a cancelleable query
/// </summary>
/// <param name="connectionString">SQL Server connection string</param>
/// <param name="query">SQL query</param>
/// <param name="timeout">SqlDataReader timeout (maximum time in seconds allowed to run query). Note: Try varying this in conjunction
/// with the WAITFOR DELAY in the SQL query, e.g. make it shorter than the WAITFOR or maybe a second longer</param>
/// <param name="cancellationToken">Allows cancellation of this operation</param>
/// <returns></returns>
private async Task ProcessQueryCancelleable(string connectionString, string query, int timeout,
 CancellationToken cancellationToken)
{
 // await Task.Delay(TimeSpan.FromSeconds(5));

try
 {
 // Keep sqlConnection wrapped in using statement so disposal is handled automatically
 using (var sqlConnection = new SqlConnection(connectionString))
 {
 await sqlConnection.OpenAsync(cancellationToken);

using (SqlCommand cmd = sqlConnection.CreateCommand())
 {
 cmd.CommandTimeout = timeout;

cmd.CommandText = query;

using (SqlDataReader dataReader = await cmd.ExecuteReaderAsync(cancellationToken))
 {
 while (!dataReader.IsClosed)
 {
 // While the dataReader has not reached its end keep adding rows to the DataItemsList
 while (await dataReader.ReadAsync(cancellationToken))
 {
 // Process dataReader row
 WorkOrder workOrder = GetWorkOrderFromReader(dataReader);

DataItemsList.Add(workOrder);

// Another simulated delay...this one internal
 await Task.Delay(TimeSpan.FromMilliseconds(1));
 }

if (!dataReader.NextResult())
 {
 dataReader.Close();
 }
 }

Debug.WriteLine("done with reader");

RequestStatus = "finished";
 }
 }
 }
 }
 catch (SqlException sqlEx)
 {
 Debug.WriteLine(sqlEx.Message);
 }
}

Download C# Project (an MDF with just the WorkOrders table is included)

Google Map Maker

I doubt it adds a ton of new data. Or that all but the simplest roads drawn by hand by an amateur wouldn’t be reviewed and redrawn by a professional, but Google is smart to include this:

There is a class of user that would be disappointed not to be able to submit GPS-collected data. But they’re all doing that with OpenStreetMap.org anyway. One thing for sure: Apple never thought of this…and, to be fair, Bing/Nokia/Navteq hasn’t either (although their data collection via UPS and Fedex is huge…3 billion miles driven annually).

Fun with ClickOnce and XML files…and Prerequisites

I have a recent WPF project that is published using ClickOnce. However, the application has some XML templates that weren’t being copied over to the install directory. It was strange because they were being published to the deployment package. What I found was that in Visual Studio, you need to open Properties > Publish and click on the Application Files button.

Find the files that aren’t being copied over and change the Publish Status to “Include”. Mine were originally set to “Data (Auto)” and that didn’t cut it:

Incidentally, within the Solution Explorer, each file has its Build Action set to Content and Copy to Output Directory is set to Copy always.

An update: Don’t forget to deal with prerequisites – in my case, .NET 4.5 and the Visual C++ 2010 Runtime – x64. We had three developer machines where installation worked fine and client machines where it didn’t. I installed a fresh copy of Win7 in a VM and was able to fairly quickly determine this. To Microsoft’s credit, you can create a setup program to install prerequisites. However, IIRC, you get to download and install them even if they’re already installed. Still, better than beating your head against it all day. Cable broadband is a lot cheaper than my mental bandwidth.

What’s in a map projection?

If you take a look at the map below of the contiguous U.S. you might ask, “I see that Maine has the easternmost point in the lower 48, but shouldn’t it also have the northernmost point?”

Projections can sometimes be misleading. Well, they’re all misleading in some aspect or another. Take, for example, the Mercator projection, which is used in mapping services like those provided by Bing or Google. (There are sound technical reasons for this BTW). It greatly distorts areas the farther north or south you go. However, in this case, it does make it easy to see that the northernmost point of Maine is south of quite a bit of the U.S.-Canadian border:

So, how far north does Maine go? Approximately 47.46°, which puts it south of that large stretch of the border that runs along the 49th parallel. Incidentally, Sumas, WA – because of a surveying error – extends just slightly past the 49th parallel (49.002389,-122.261111).

Nice use of gesture semantics in #WP7 http…

Nice use of gesture semantics (in #WP7): http://www.wpcentral.com/windows-phone-app-review-gestulator

I installed it and after figuring out that the keypad is the gesture target area, quickly got the hang of it. The only unnatural thing about such an interface is having been conditioned to think of a keypad as just being for tapping. I’d actually institute some fast-moving, directional animations that obscure the screen like this: