General Issues | CoNatural Blogging

Async Search Operations with ASP.NET MVC + jQuery + CoNatural Components.

Roger Torres - April 25th, 2009
1 Comment

After a few extremely busy weeks (and weekends) dedicated to my clients, I finally found some time to polish a little bit my components, including support for asynchronous data operations. This is something that I needed for a small project I’m refactoring, but wasn’t at the top of my priorities. Anyway, now that it’s done, I will use the opportunity to please some of the readers who asked me to include more examples in this Blog, … so I’m going to try with another series of posts focusing on practical ways in which we can use these technologies.

I prepared a simple real world scenario where asynchronous data operations are usually desired. The goal is to start a search against the AdventureWorks database and continue working normally until the operation notifies the initiator with complete or partial results (in case it’s canceled or errors are found). To make things more interesting, one operation will spawn multiple individual searches (as CoNatural Commands) in parallel, and the application will be implemented using the newly released ASP.NET MVC framework.

I don’t want to miss the opportunity to congratulate the ASP.NET MVC team for this great piece of work. I truly believe that this architecture is not only superior to ASP.NET, but will eventually become the standard .NET web platform. I also want to commend the managers who decided to involve the community from the beginning, accepting many of their recommendations, and for not reinventing the wheel when they adopted jQuery.

The Commands

As usual, we will need a module to encapsulate our “search” data commands and launch the asynchronous operations. So, let’s create the AsyncOp class library and add the following CoNatural commands to the “Commands” folder:

SearchCustomers Command

-------------------------------------------------------
-- SearchCustomers
--
-- Created by Roger
-- Created on 4/16/2009 7:18:35 PM
-------------------------------------------------------
 
WAITFOR DELAY @Delay 
 
SELECT     
	I.CustomerID,
	'Customer', 
	P.FirstName + ' ' + P.LastName AS Name
FROM         
	Sales.Individual I
	JOIN Person.Contact P ON I.ContactID = P.ContactID
WHERE
	UPPER(P.FirstName) LIKE @Criteria + '%'
	OR UPPER(P.LastName) LIKE @Criteria + '%'

//-------------------------------------------------------
//-- SearchCustomers
//--
//-- Created by Roger
//-- Created on 4/16/2009 7:18:35 PM
//-------------------------------------------------------
using System;
 
namespace AsyncOp.Commands {
   public class SearchCustomers : CoNatural.Data.Command {
      [CoNatural.Data.Parameter(30)]
      public string Delay { get; private set; }
 
      [CoNatural.Data.Parameter(30)]
      public string Criteria { get; private set; }
 
      public SearchCustomers(string delay, string criteria) {
         Delay = delay;
         Criteria = criteria;
      }   
   }
}

SearchEmployees Command

-------------------------------------------------------
-- SearchEmployees
--
-- Created by Roger
-- Created on 4/16/2009 7:18:35 PM
-------------------------------------------------------
 
WAITFOR DELAY @Delay 
 
SELECT     
	P.ContactID, 
	'Employee',
	P.FirstName + ' ' + P.LastName AS Name
FROM         
	HumanResources.Employee E
	JOIN Person.Contact P ON E.ContactID = P.ContactID
WHERE
	UPPER(P.FirstName) LIKE @Criteria + '%'
	OR UPPER(P.LastName) LIKE @Criteria + '%'

//-------------------------------------------------------
//-- SearchEmployees
//--
//-- Created by Roger
//-- Created on 4/16/2009 7:18:35 PM
//-------------------------------------------------------
using System;
 
namespace AsyncOp.Commands {
   public class SearchEmployees : CoNatural.Data.Command {
      [CoNatural.Data.Parameter(30)]
      public string Delay { get; private set; }
 
      [CoNatural.Data.Parameter(30)]
      public string Criteria { get; private set; }
 
      public SearchEmployees(string delay, string criteria) {
         Delay = delay;
         Criteria = criteria;
      }   
   }
}

SearchVendors Command

-------------------------------------------------------
-- SearchVendors
--
-- Created by Roger
-- Created on 4/16/2009 7:18:35 PM
-------------------------------------------------------
 
WAITFOR DELAY @Delay 
 
SELECT     
	V.VendorID, 
	'Vendor',
	V.Name
FROM         
	Purchasing.Vendor V
WHERE
	UPPER(V.Name) LIKE @Criteria + '%'

//-------------------------------------------------------
//-- SearchVendors
//--
//-- Created by Roger
//-- Created on 4/16/2009 7:18:35 PM
//-------------------------------------------------------
using System;
 
namespace AsyncOp.Commands {
   public class SearchVendors : CoNatural.Data.Command {
      [CoNatural.Data.Parameter(30)]
      public string Delay { get; private set; }
 
      [CoNatural.Data.Parameter(30)]
      public string Criteria { get; private set; }
 
      public SearchVendors(string delay, string criteria) {
         Delay = delay;
         Criteria = criteria;
      }   
   }
}

Note that I’m introducing an artificial delay to the SQL statements in order to simulate long running search operations.

The Parallel Async Search

We just need to add one more class to take care of our async search operation. In case you are new to this Blog, here you can find a detailed explanation of CoNatural Commands and CoNatural Async Operations.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Runtime.Serialization;
 
using CoNatural.Threading.Async;
using CoNatural.Data;
using CoNatural.Data.SqlClient;
using CoNatural.Extensions.Threading.Async.Data;
 
namespace AsyncOp {
   [DataContract]
   public class AsyncOp {
      public class AsyncOpResult {
         public int Id { get; private set; }
         public string Type { get; private set; }
         public string Name { get; private set; }
 
         internal AsyncOpResult(int id, string type, string name) {
            Id = id;
            Type = type;
            Name = name;
         }
      }
 
      [DataMember]
      public string Status { get; set; }
      [DataMember]
      public Guid AsyncId { get; private set; }
      [DataMember]
      public bool Cancelled { get; private set; }
      [DataMember]
      public bool Completed { get; private set; }
      [DataMember]
      public DateTime StartTime { get; private set; }
      [DataMember]
      public string Criteria { get; private set; }
 
      public List<AsyncOpResult> Results { get; private set; }
      public Exception Error { get; private set; }
 
      private AsyncContext ctx;
 
      public AsyncOp(string criteria) {
         AsyncId = Guid.NewGuid();
         Cancelled = false;
         Completed = false;
         Status = "Started";
         StartTime = DateTime.Now;
         Criteria = criteria;
         Results = new List<AsyncOpResult>();
         Error = null;
      }
 
      public void Start() {
         ctx = new AsyncContext(false);
         ctx.AsyncActionCompleted += new EventHandler<AsyncActionCompletedEventArgs>(ctx_AsyncActionCompleted);
         ctx.Completed += new EventHandler<System.ComponentModel.AsyncCompletedEventArgs>(ctx_Completed);
 
         // our operation will search three tables in parallel
         string connectionString = @"data source=INSPIRON\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security=True;async=true;";
         AsyncConnection conn = new AsyncConnection(connectionString, new SqlDbProvider());
         Commands.SearchEmployees se = new Commands.SearchEmployees("000:00:10", Criteria);
         Commands.SearchCustomers sc = new Commands.SearchCustomers("000:00:20", Criteria);
         Commands.SearchVendors sv = new Commands.SearchVendors("000:00:30", Criteria);
 
         AsyncAction<IEnumerable<AsyncOpResult>> s1 = conn.ExecuteReaderAsync<AsyncOpResult>(se, ReadResult);
         AsyncAction<IEnumerable<AsyncOpResult>> s2 = conn.ExecuteReaderAsync<AsyncOpResult>(sc, ReadResult);
         AsyncAction<IEnumerable<AsyncOpResult>> s3 = conn.ExecuteReaderAsync<AsyncOpResult>(sv, ReadResult);
 
         Parallel p = new Parallel("main", true, s1, s2, s3); 
         p.Start(ctx);
      }
 
      public void Cancel() {
         if (ctx != null) {
            ctx.Cancel();
            // cancel commands
            Parallel root = (Parallel)ctx.RootAction;
            if (root != null) {
               foreach (AsyncAction<IEnumerable<AsyncOpResult>> action in root.Branches)
                  ((CoNatural.Data.SqlClient.AsyncConnection.StateObject)action.AsyncState).DbCommand.Cancel();
            }
         }
      }
 
      void ctx_Completed(object sender, System.ComponentModel.AsyncCompletedEventArgs e) {
         if (e.Cancelled)
            Cancelled = true;
         else {
            Completed = true;
            Error = e.Error;
         }
      }
 
      void ctx_AsyncActionCompleted(object sender, AsyncActionCompletedEventArgs e) {
         if (e.Action.Name != "main") {
            lock (this) {
               AsyncAction<IEnumerable<AsyncOpResult>> action = (AsyncAction<IEnumerable<AsyncOpResult>>)e.Action;
               if(action.Result != null)
                  Results.AddRange(action.Result);
            }
         }
      }
 
      AsyncOpResult ReadResult(IDataRecord record, int resultIndex) {
         int id = record.GetInt32(0);
         string type = record.GetString(1);
         string name = record.GetString(2);
 
         return new AsyncOpResult(id, type, name);
      }
   }
}

There are a couple of subtleties in this code:

The class is decorated with the DataContract attribute and some of the properties with the DataMember attribute. I will explain why this is necessary later when presenting all the active searches in ASP.NET MVC views, passing information via JSON action results.
The connection string must contain the async=true attribute to support asynchronous commands.
In order to cancel the search, we must loop through all the actions (branches) in the main parallel operation, and cast the AsyncState property to a StateObject holding the SqlCommand.

And that’s all we need. The CoNatural components will take care of the rest!

I’m attaching the new source code with the modifications to support asynchronous data commands, and the compiled CoNatural assemblies for your convenience. I encourage you to try building this project and play a little with it while I prepare the next post.

In the second part I will show how to present the operations in an ASP.NET MVC application, using a combination of jQuery, AJAX, and JSON serialization to efficiently start, cancel, and remove multiple searches.

Attachments

[download id="7"]
[download id="8"]
[download id="9"]
[download id="10"]
[download id="11"]
[download id="12"]

Streamlining .NET Asynchronous Operations - Part III

Roger Torres - February 21st, 2009
Comments

This is the last part of my series about .NET asynchronous operations. Here you can download:

The [download#5] for the main components.
The [download#6] for the extension methods we are using in our examples.

Since we didn’t quite finish our example in part II, in this post I’m going to show you a simple Windows Forms application that will:

Provide a GUI to our last example.
Allow the user to cancel the asynchronous operation.
Show the managed thread id where internal actions execute, so you can understand how ThreadPool threads are created.
Show the code that handles operation events and errors.

The Form

Let’s start by creating a new Windows Application Project named WinFormDemo. The default Form1 will do, we just need to add a toolbar with two buttons (to start and cancel the asynchronous operation), and a list view to show the execution log - see the screenshots below.

Now we can associate the following code-behind to our form:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Net;
using System.IO;
using CoNatural.Threading.Async;
using CoNatural.Extensions.Threading.Async.IO;
using CoNatural.Extensions.Threading.Async.Net;
 
namespace WinFormDemo {
   public partial class Form1 : Form {
      AsyncContext context;
 
      public Form1() {
         InitializeComponent();
 
         // create context
         context = new AsyncContext();
         context.Completed += new EventHandler<AsyncCompletedEventArgs>(Completed);
         context.AsyncActionCompleted += new EventHandler<AsyncActionCompletedEventArgs>(ActionCompleted);
         context.AsyncActionProgressChanged += new EventHandler<AsyncActionProgressChangedEventArgs>(ReportProgress);
         context.AsyncActionTraced += new EventHandler<AsyncActionTracedEventArgs>(TraceAction);
      }
 
      private void WriteLine(string message, params object[] args) {
         logListView.Items.Add(string.Format(message, args));
      }
 
      private IEnumerable<AsyncAction> DownloadAsync(string url) {
         WebRequest req = HttpWebRequest.Create(url);
 
         // get contents of site
         AsyncAction<WebResponse> response = req.GetResponseAsync();
         yield return response;
 
         // copy results to memory stream
         Stream responseStream = response.Result.GetResponseStream();
         MemoryStream copyTo = new MemoryStream();
         yield return new Serial(url, true, responseStream.CopyAsync(copyTo));
 
         // return results
         copyTo.Seek(0, SeekOrigin.Begin);
         string html = new StreamReader(copyTo).ReadToEnd();
         yield return new Return<string>(html);
      }
 
      void DownloadAll() {
         Serial<string> microsoft = new Serial<string>("microsoft", true, DownloadAsync("http://www.microsoft.com"));
         Serial<string> conatural = new Serial<string>("conatural", true, DownloadAsync("http://www.conatural.com"));
 
         Serial<string> google = new Serial<string>("google", true, DownloadAsync("http://www.google.com"));
         Serial<string> yahoo = new Serial<string>("yahoo", true, DownloadAsync("http://www.yahoo.com"));
 
         Parallel favorite = new Parallel("Favorite Sites", true, google, yahoo);
 
         Parallel all = new Parallel("All Sites", true, microsoft, conatural, favorite);
 
         all.Start(context);
      }
 
      private void Completed(object sender, AsyncCompletedEventArgs args) {
         if (args.Cancelled)
            WriteLine("CONTEXT WAS CANCELLED.");
 
         if (args.Error != null)
            foreach (Exception ex in ((AsyncContextException)args.Error).Errors)
               DisplayError(ex);
 
         WriteLine("CONTEXT COMPLETED.");
      }
 
      private void ActionCompleted(object sender, AsyncActionCompletedEventArgs args) {
         if (args.Error != null)
            DisplayError(args.Error);
 
         if (args.Cancelled)
            WriteLine(args.Action.Name + " Cancelled");
 
         if (args.Error == null && !args.Cancelled) {
            if (args.Action is Serial<string>) {
               // Form2 displays the contents of a web site in a Browser control
               //Form2 f2 = new Form2();
               //f2.SetContent(((Serial<string>)args.Action).Result);
               //f2.Text = args.Action.Name;
               //f2.Show();
            }
            else {
               WriteLine("[{0}][{1}] completed", args.ManagedThreadId, args.Action.Name);
            }
         }
      }
 
      private void ReportProgress(object sender, AsyncActionProgressChangedEventArgs args) {
         WriteLine("[{0}][{1}] {2}", args.ManagedThreadId, args.Action.Name, args.ProgressPercentage);
      }
 
      private void TraceAction(object sender, AsyncActionTracedEventArgs args) {
         WriteLine("[{0}][{1}] {2}", args.ManagedThreadId, args.Action.Name, args.Message);
      }
 
      private void DisplayError(Exception ex) {
         if (ex != null) {
            if (ex is AsyncActionException)
               WriteLine("{0} -> {1}", ((AsyncActionException)ex).Action.Name, ex.Message);
            else
               WriteLine(ex.Message);
            DisplayError(ex.InnerException);
         }
      }
 
      private void startToolStripButton_Click(object sender, EventArgs e) {
         try {
            DownloadAll();
            logListView.Items.Clear();
            WriteLine("[{0}] GUI thread is alive...", System.Threading.Thread.CurrentThread.ManagedThreadId);
         }
         catch (Exception ex) {
            WriteLine(ex.Message);
         }
      }
 
      private void cancelToolStripButton_Click(object sender, EventArgs e) {
         context.Cancel();
      }
   }
}

We are using exactly the same code from our example in part II, but we are now handling operation events. Note that we don’t need to check if the event handling methods are executing in the GUI thread since the AsyncContext class is already taking care of the details by posting the events to the current synchronization context.

The first of the following two screenshots depicts the application after successfully downloading the contents of several web sites. The log is created by handling Trace events that return the managed thread id where the action is executing, the action’s name, and total bytes downloaded so far.

The second screenshot shows what happens when the Start button is hit twice after the operation has been started - An exception is raised by a “busy” context.

Also note that when the Cancel button is hit, the entire operation stops (the completion event is raised with the Cancelled flag set to true).

Async Test Results

The best way to understand what’s going on here is by playing with this code, so I encourage you to build your own prototypes… and let me know if you find any issues please

What’s next?

Now that I have a system to invoke asynchronous operations in any predetermined sequence, I think it would be a good idea to go back to the drawing board and add an asynchronous component to the CoNatural.Data DAL. The ADO.NET SQL Server provider (under System.Data.SqlClient) already supports asynchronous execution, so with a couple of extension methods I think it will be very easy to invoke asynchronous data commands that will:

Execute without blocking the calling thread, making our GUI more responsive.
Allow the user to cancel commands.
Invoke multiple commands in an ordered sequence or in parallel to maximize performance.
The commands can be executed on one or multiple databases, and under the same transaction scope.

Stay tuned.

Streamlining .NET Asynchronous Operations - Part II

Roger Torres - February 15th, 2009
1 Comment

In Part I of this series I questioned the use of traditional .NET asynchronous patterns when executing a large number of asynchronous operations in a predetermined sequence, showing how the advantages in performance are overshadowed by the complexity in the implementation.

Thanks to the functional language capabilities of C#, we can easily streamline our code by following the guidelines I’m going to discuss here today. The whole idea is based on coroutines, where unlike regular methods (subroutines), we have multiple entry points to resume execution. C# doesn’t support coroutines natively (I hope this is something we will have in future versions), but we can simulate some of that behavior using generators (best known as iterators in C# terms).

The key is to return an enumeration of asynchronous actions that must be executed in sequence by yielding each individual action to the iterator… and as usual, there are many ways to accomplish this goal. I’m going to present a solution that I found well balanced in terms of complexity and functionality.

The Solution

The following class diagram depicts the components of my solution:

Note that all actions have a common base abstract class AsyncAction, so we can nest actions to create compositions of parallel and serial sequences as long as we want. Today we are going to work with an enhanced version of the example discussed in part I, this time downloading the contents of several web pages as shown in the following diagram:

We will see how this mechanism uses ThreadPool threads very efficiently, only starting new threads when necessary.

AsyncAction

using System;
using System.Threading;
 
namespace CoNatural.Threading.Async {
   public abstract class AsyncAction {
      public string Name { get; private set; }
      public bool RaiseEvents { get; private set; }
 
      protected AsyncAction(string name, bool raiseEvents) {
         Name = name;
         RaiseEvents = raiseEvents;
      }
 
      internal void Complete(bool cancelled, AsyncActionException error) {
         Context.OnAsyncActionCompleted(this, cancelled, error);
      }
 
      internal void ChangeProgress(int progressPercentage) {
         Context.OnAsyncActionProgressChanged(this, progressPercentage);
      }
 
      internal void Trace(string message) {
         Context.OnAsyncActionTraced(this, message);
      }
 
      internal AsyncContext Context { get; set; }
 
      public virtual void Start(AsyncContext context) {
         Context = context.OnStart();
         ThreadPool.QueueUserWorkItem((state) => { BeginInvoke(this, () => context.OnCompleted()); });
      }
 
      internal abstract void BeginInvoke(AsyncAction source, Action nextAction);
   }
}

This is the base (abstract) class representing any type of asynchronous action returned by the generator. All our actions are instantiated with a name (to facilitate tracing and debugging), and a flag used by the operation context to raise events generated by the action (Completed, ProgressChanged, Traced). Raising events is something that must be reserved only for actions that you want to debug or display their progress report and termination feedback to the GUI.

We can define an asynchronous operation as the execution of one or many nested asynchronous actions. To start an operation, the user invokes the Start method of the root action, passing an execution context that is reserved by the engine until the operation is completed. Note that the root’s BeginInvoke method is queued in the ThreadPool, so the main calling thread is not blocked by the operation.

BeginInvoke is the method that binds our nested actions together. Each concrete action implements BeginInvoke according to their specific compositional properties. When the asynchronous operation is started, the engine binds each executing action to the execution context (AsyncContext) and starts calling each action’s BeginInvoke in order until the operation is completed. We will see below how the AsyncContext takes care of posting individual AsyncAction events (and its own Completed event) to the current synchronization context.

AsyncAction<T>

using System;
using System.Threading;
 
namespace CoNatural.Threading.Async {
   public class AsyncAction<T> : AsyncAction {
      private Func<AsyncCallback, object, IAsyncResult> _beginXxx;
      private Func<IAsyncResult, T> _endXxx;
      public T Result { get; private set; }
 
      public AsyncAction(string name, Func<AsyncCallback, object, IAsyncResult> beginXxx, Func<IAsyncResult, T> endXxx)
         : base(name, false) {
         _beginXxx = beginXxx;
         _endXxx = endXxx;
      }
 
      internal override void BeginInvoke(AsyncAction source, Action nextAction) {
         Context = source.Context;
 
         try {
            _beginXxx(
               delegate(IAsyncResult asyncResult) {
                  try {
                     Result = _endXxx(asyncResult);
                     Complete(false, null);
                  }
                  catch (Exception ex) {
                     Complete(false, new AsyncActionException(this, string.Empty, ex));
                  }
                  nextAction();
               },
               null
            );
         }
         catch (Exception ex) {
            Complete(false, new AsyncActionException(this, string.Empty, ex));
            nextAction();       
         }
      }
   }
}

This is our first and most important concrete AsyncAction. Its main function is to wrap all .NET APIs using the BeginXxx-EndXxx asynchronous pattern, returning a result of type T (in most cases). The constructor accepts the parameters of the base class plus delegates to the BeginXxx-EndXxx methods.

Note how BeginInvoke wraps the .NET asynchronous pattern, effectively blocking the sequence until the action is completed by EndXxx. In case of success, the result of the action is saved and the Completed event is raised. In case of errors, the Completed event is also raised, but this time with the exception returned in the Error property of the event’s arguments. Finally, the sequence is resumed by invoking the delegate of the next action.

We are achieving our first goal of executing multiple asynchronous actions in a predetermined serial sequence, but we need a way to encapsulate the specific details of every .NET API in a nicer form. Now is when the C# 3.0 extension methods come to the rescue. The code below shows one way to extend System.Net.WebRequest and System.IO.Stream by wrapping their BeginXxx-EndXxx .NET asynchronous operations inside AsyncAction<T> actions.

using System;
using System.Net;
 
namespace CoNatural.Threading.Async.Extensions {
   public static class WebRequestExtensions {
      public static AsyncAction<WebResponse> GetResponseAsync(this WebRequest request) {
         return new AsyncAction<WebResponse>(request.RequestUri.AbsoluteUri, 
            request.BeginGetResponse, request.EndGetResponse);
      }
   }
}

using System;
using System.Collections.Generic;
using System.IO;
 
namespace CoNatural.Threading.Async.Extensions {
   public static class StreamExtensions {
      public static AsyncAction<int> ReadAsync(this Stream stream, byte[] buffer, int offset, int count) {
         return new AsyncAction<int>("ReadAsync",
            (callback, state) =>
               stream.BeginRead(buffer, offset, count, callback, state), stream.EndRead);
      }
 
      public static AsyncAction<int> WriteAsync(this Stream stream, byte[] buffer, int offset, int count) {
         return new AsyncAction<int>("WriteAsync",
            (callback, state) =>
               stream.BeginWrite(buffer, offset, count, callback, state),
               (asyncResult) => { stream.EndWrite(asyncResult); return 0; });
      }
 
      public static IEnumerable<AsyncAction> CopyAsync(this Stream stream, Stream copyTo) {
         long total = stream.CanSeek ? stream.Length : -1;
         int read = -1;
         byte[] buffer = new byte[1024];
         while (read != 0) {
            AsyncAction<int> count = stream.ReadAsync(buffer, 0, 1024);
            yield return count;
 
            yield return copyTo.WriteAsync(buffer, 0, count.Result);
 
            if (total > 0)
               yield return new Progress((int)(((double)count.Result / (double)total) * 100.0));
            else
               yield return new Trace(count.Result.ToString());
 
            read = count.Result;
         }
      }
   }
}

We will see later how we can use these extension methods to implement our asynchronous operations.

Serial

using System;
using System.Collections.Generic;
 
namespace CoNatural.Threading.Async {
   public class Serial : AsyncAction {
      protected IEnumerable<AsyncAction> _actions;
 
      public Serial(string name, bool raiseEvents, IEnumerable<AsyncAction> actions)
         : base(name, raiseEvents) {
         _actions = actions;
      }
 
      internal override void BeginInvoke(AsyncAction source, Action nextAction) {
         Context = source.Context;
         MoveNext(_actions.GetEnumerator(), nextAction);
      }
 
      private void MoveNext(IEnumerator<AsyncAction> actionEnumerator, Action nextAction) {
         try {
            // stop when cancellation requested in context
            if (Context.CancelRequested) {
               Complete(true, null);
               nextAction();
            }
            // advance iteration
            else if (actionEnumerator.MoveNext()) {
               actionEnumerator.Current.BeginInvoke(this, () => MoveNext(actionEnumerator, nextAction));
            }
            // we are done
            else {
               Complete(false, null);
               nextAction();
            }
         }
         catch (AsyncActionException aex) {
            Complete(false, aex);
            nextAction();
         }
         catch (Exception ex) {
            Complete(false, new AsyncActionException(this, string.Empty, ex));
            nextAction();
         }
      }
   }
}

Going back to our first goal, we needed serial actions to synchronize our sequences. Our Serial class takes an IEnumerable list of AsyncAction actions that must be invoked in order.

BeginInvoke is where the fun begins, in this case we save the execution context as usual and start the enumeration of actions by calling MoveNext. Here we check if the context has received a cancellation request to stop the execution, otherwise we just need to advance the iteration and call BeginInvoke again, passing a delegate to MoveNext (this is the recursive part) to guarantee the order of the sequence. Once we reach the end of the enumeration, the serial operation is completed and we can stop the recursion. Of course, in case of exceptions, we also stop and report the error in the completion event.

Serial<T>

using System;
using System.Collections.Generic;
 
namespace CoNatural.Threading.Async {
   public class Serial<T> : Serial {
      public T Result { get; internal set; }
 
      public Serial(string name, bool raiseEvents, IEnumerable<AsyncAction> actions)
         : base(name, raiseEvents, actions) { }
   }
}

In some cases our sequence will return a result of type T, so we need a special Serial<T> class with a property to store the result.

Parallel

using System;
using System.Collections.Generic;
using System.Threading;
 
namespace CoNatural.Threading.Async {
   /// <summary>
   /// Executes multiple asynchronous actions in parallel.
   /// </summary>
   public class Parallel : AsyncAction {
      public AsyncAction[] Branches { get; private set; }
 
      public Parallel(string name, bool raiseEvents, params AsyncAction[] branches)
         : base(name, raiseEvents) {
         Branches = branches;
      }
 
      public override void Start(AsyncContext context) {
         Context = context.OnStart();
         BeginInvoke(this, () => context.OnCompleted());
      }
 
      internal override void BeginInvoke(AsyncAction source, Action nextAction) {
         Context = source.Context;
 
         // parallel actions start in their own managed threads because they must wait
         // for all branches to complete before resuming
         ThreadPool.QueueUserWorkItem(
            (state) => {
               long activeBranches = Branches.Length;
               ManualResetEvent yield = new ManualResetEvent(false);
 
               foreach (AsyncAction branch in Branches)
                  branch.BeginInvoke(
                     this,
                     () => {
                        // this branch is done, decrement counter
                        long pending = Interlocked.Decrement(ref activeBranches);
 
                        // release parallel action when all branches are completed
                        if (pending == 0)
                           yield.Set();
                     }
                  );
 
               // wait for all branches to complete
               yield.WaitOne();
 
               // we are done
               Complete(false, null);
               nextAction();
            }
         );
      }
   }
}

Parallel actions complete our compositional requirements. Here we take a list of actions that must be executed concurrently (called branches), and schedule a thread to invoke each one of them individually. After all branches are invoked, the thread controlling the parallel operation is blocked, waiting for all branches to complete. Note the lambda expression that’s assigned as the “next action” to complete a branch: The counter of pending branches is decremented, and the ManuelResetEvent is signaled to release the parallel thread when the counter = 0.

Return<T>

using System;
 
namespace CoNatural.Threading.Async {
   /// <summary>
   /// Returns Serial of T action results.
   /// </summary>
   /// <typeparam name="T">Result type.</typeparam>
   public class Return<T> : AsyncAction {
      public T Value { get; private set; }
 
      public Return(T value)
         : base(string.Empty, false) {
         Value = value;
      }
 
      internal override void BeginInvoke(AsyncAction source, Action nextAction) {
         if (source is Serial<T>) {
            ((Serial<T>)source).Result = Value;
            nextAction();
         }
         else
            throw new InvalidAsyncActionException(source, "Source of Return<T> must be Serial<T>.");
      }
   }
}

Since we are emulating coroutines with C# iterators, we must “yield” all results to the engine as AsyncAction actions, including action results, progress and trace events. The Return<T> action is a helper class to assign the result of a Serial<T> action. Progress and Trace actions are helpers to report progress and events to the execution context. We will see all these classes “in action” later when we implement our example.

Progress

using System;
 
namespace CoNatural.Threading.Async {
   public class Progress : AsyncAction {
      public int ProgressPercentage { get; private set; }
 
      public Progress(int progressPercentage)
         : base(string.Empty, false) {
         ProgressPercentage = progressPercentage;
      }
 
      internal override void BeginInvoke(AsyncAction source, Action nextAction) {
         source.ChangeProgress(ProgressPercentage);
         nextAction();
      }
   }
}

Trace

using System;
 
namespace CoNatural.Threading.Async {
   public class Trace : AsyncAction {
      public string Message { get; private set; }
 
      public Trace(string message)
         : base(string.Empty, false) {
         Message = message;
      }
 
      internal override void BeginInvoke(AsyncAction source, Action nextAction) {
         source.Trace(Message);
         nextAction();
      }
   }
}

AsyncContext

using System;
using System.Threading;
using System.Collections.Generic;
using System.ComponentModel;
 
namespace CoNatural.Threading.Async {
   public class AsyncContext {
      public event EventHandler<AsyncCompletedEventArgs> Completed;
      public event EventHandler<AsyncActionCompletedEventArgs> AsyncActionCompleted;
      public event EventHandler<AsyncActionProgressChangedEventArgs> AsyncActionProgressChanged;
      public event EventHandler<AsyncActionTracedEventArgs> AsyncActionTraced;
 
      private SynchronizationContext _syncContext;
      public AsyncContext() {
         _syncContext = SynchronizationContext.Current ?? new SynchronizationContext();
      }
 
      private volatile bool _busy;
      public bool Busy { get { return _busy; } }
 
      private volatile bool _cancelRequested;
      private volatile bool _cancelled;
      internal bool CancelRequested { get { return _cancelRequested; } }
 
      public void Cancel() {
         if (_busy)
            _cancelRequested = true;
      }
 
      private List<AsyncActionException> _errors = new List<AsyncActionException>();
 
      internal AsyncContext OnStart() {
         lock (this) {
            if (_busy)
               throw new Exception("Context is busy running other actions.");
 
            _busy = true;
            _cancelRequested = false;
            _cancelled = false;
            _errors.Clear();
 
            return this;
         }
      }
 
      internal void OnCompleted() {
         _busy = false;
 
         if (Completed != null) {
            AsyncContextException error = (_errors.Count > 0 ? new AsyncContextException(_errors.ToArray()) : null);
            _syncContext.Post((state) =>
               Completed(this, new AsyncCompletedEventArgs(error, _cancelled, state)), null);
         }
      }
 
      internal void OnAsyncActionCompleted(AsyncAction action, bool cancelled, AsyncActionException error) {
         lock (this) {
            if (error != null) {
               _errors.Add(error);
               // request cancellation after errors found
               _cancelRequested = true;
            }
 
            // at least one action was cancelled
            if (cancelled) {
               _cancelRequested = true;
               _cancelled = true;
            }
         }
 
         if (action.RaiseEvents && AsyncActionCompleted != null)
            _syncContext.Post((state) =>
               AsyncActionCompleted(action,
               new AsyncActionCompletedEventArgs(action, Thread.CurrentThread.ManagedThreadId, cancelled, error)), null);
      }
 
      internal void OnAsyncActionProgressChanged(AsyncAction action, int progressPercentage) {
         if (action.RaiseEvents && AsyncActionProgressChanged != null) {
            progressPercentage = Math.Max(0, progressPercentage);
            progressPercentage = Math.Max(100, progressPercentage);
            _syncContext.Post((state) =>
               AsyncActionProgressChanged(action,
               new AsyncActionProgressChangedEventArgs(action, Thread.CurrentThread.ManagedThreadId, progressPercentage)), null);
         }
      }
 
      internal void OnAsyncActionTraced(AsyncAction action, string message) {
         if (action.RaiseEvents && AsyncActionTraced != null)
            _syncContext.Post((state) =>
               AsyncActionTraced(action,
               new AsyncActionTracedEventArgs(action, Thread.CurrentThread.ManagedThreadId, message)), null);
      }
   }
}

The execution context is controlled by one AsyncContext instance. We can subscribe for the entire operation’s completion event, or for individual action events (only the actions that are flagged to raise events). The context can receive cancellation requests that will stop all the executing actions. Note that in case of exceptions, the cancellation flag is also set to stop the operation, but I’ll probably reconsider this point later to allow other independent actions to complete.

Putting it all together

Let’s now review our initial example. We need to implement a method to download the contents of a few web sites as follows:

void DownloadAll() {
   Serial<string> microsoft = new Serial<string>("microsoft", true, DownloadAsync("http://www.microsoft.com"));
   Serial<string> conatural = new Serial<string>("conatural", true, DownloadAsync("http://www.conatural.com"));
 
   Serial<string> google = new Serial<string>("google", true, DownloadAsync("http://www.google.com"));
   Serial<string> yahoo = new Serial<string>("yahoo", true, DownloadAsync("http://www.yahoo.com"));
 
   Parallel favorite = new Parallel("Favorite Sites", true, google, yahoo);
   Parallel all = new Parallel("All Sites", true, microsoft, conatural, favorite);
   all.Start(context);
}

While the structure of our operation is controlled by the method above, the details are implemented here:

private IEnumerable<AsyncAction> DownloadAsync(string url) {
   WebRequest req = HttpWebRequest.Create(url);
 
   // get contents of site
   AsyncAction<WebResponse> response = req.GetResponseAsync();
   yield return response;
 
   // copy results to memory stream
   Stream responseStream = response.Result.GetResponseStream();
   MemoryStream copyTo = new MemoryStream();
   yield return new Serial(url, true, responseStream.CopyAsync(copyTo));
 
   // return results
   copyTo.Seek(0, SeekOrigin.Begin);
   string html = new StreamReader(copyTo).ReadToEnd();
   yield return new Return<string>(html);
}

Note how we are using the new extension methods to simplify our code.

In the last part of this series I’ll show how to implement asynchronous operations in a Windows Forms application that will:

Allow to cancel the operation.
Report the progress of internal actions in the GUI.
Show how the ThreadPool threads are using efficiently.
Show how exceptions are handled by the engine.

Stay tuned.

Streamlining .NET Asynchronous Operations - Part I

Roger Torres - February 8th, 2009
Comments

I would like to get some feedback from the community about how they feel when writing asynchronous applications in C#. In the past, I have scheduled work items to the thread pool and implemented basic operations around the BackgroundWorker class like everyone else… but this month, when dealing with a “massive” number of asynchronous operations in my code, I questioned for a moment the benefits of this path over the more inefficient by cleaner synchronous ways. It’s not just that the existing .NET asynchronous patterns are not trivial, but the complexity of the resulting code when multiple asynchronous actions must be executed in a predetermined sequence is unacceptable.

I understand that imperative languages are not specifically designed to deal with these scenarios, but I learned from a group of bloggers (references can be found in my previous post) that C# has been capable of some cool functional stuff for a while, and with the introduction of lambda expressions and extension methods in version 3.0, the syntax is even cleaner.

This weekend I was finally able to streamline my code, encapsulating many of my original issues in a tool that I’m going to present in details here.

Asynchronous Operation

In short, an asynchronous operation is some code that you want to execute without blocking the calling thread. The operation will generally complete within an expected time frame, notifying the calling thread with the results (including exceptions found in the process). In order to use your computing resources at a maximum, it’s a good practice to break a process into multiple independent asynchronous tasks that can be executed in parallel, but it’s also common to require a number of asynchronous operations to execute in a predetermined sequence in order to complete a subprocess. These compositional properties are the key driver of my API, leaving all the thread scheduling and synchronization details to an internal runtime engine fueled by recursive C# generators.

The traditional patterns

We usually want to go “async” when dealing with large files, interfacing with remote computers, or querying a database… and the .NET Framework does a great job providing the basic APIs in the following two flavors:

Asynchronous operations that use IAsyncResult objects.
Asynchronous operations that use events.

We can find asynchronous methods to operate with files, streams, sockets, or web services among others. What the .NET framework doesn’t provide is a simple mechanism to glue operations together (although Microsoft has been working for a while on a special API dedicated to parallel programming and concurrent operations).

Let’s illustrate my problem with a simple but common scenario. I’m going to perform a web request to a given URL and write the results to a file. These are two sequential asynchronous actions that must be synchronized in order to achieve the desired results:

static void Download(string url, string path) {
  ManualResetEvent bufferInUse = new ManualResetEvent(false);
  WebRequest r = HttpWebRequest.Create(url);
  r.Method = "GET";
  r.BeginGetResponse(delegate(IAsyncResult result1) {
     HttpWebResponse response = (HttpWebResponse)r.EndGetResponse(result1);
     using(FileStream fs = File.Create(path)) {
         byte[] buffer = new byte[1024];
         int offset = 0;
         bool done = false;
         while(!done) {
             response.GetResponseStream().BeginRead(buffer, 0, buffer.Length, delegate(IAsyncResult result2) {
                 int count = response.GetResponseStream().EndRead(result2);
                 if(count > 0) {
                    fs.BeginWrite(buffer, 0, count, delegate(IAsyncResult result3) {
                    fs.EndWrite(result3);
                    bufferInUse.Set();
                }, null);
                offset += count;
             }
             else
                done = true;
             }, null);
             bufferInUse.WaitOne();
             bufferInUse.Reset();
          };
          fs.Close();
       }
    }, null);
 }
}

Note how detached these operations look in our code, and this is just a trivial example. We are even using anonymous delegates to make the code more compact, but it’s clear that we cannot sustain this pattern for too long if more operations are required in the sequence. There are also synchronization issues we have to consider, like locking the buffer to avoid concurrent reading and writing operations… and we are not taking care of exception handling, cancellations, and progress reporting yet.

It would be really nice if we could write synchronous-like code but the actions still execute asynchronously as below:

 static void Download(string url, string path) {
   WebRequest req = HttpWebRequest.Create(url);
   WebResponse response = AsyncAction(url, req.BeginGetResponse, req.EndGetResponse);
   Stream rs = response.Result.GetResponseStream();
   FileStream fs = File.Create(path);
   int count = 1;
   byte[] buffer = new byte[1024];
   while(count > 0) {
       count = AsyncAction(rs.BeginRead(buffer, 0, buffer.Length), rs.EndRead);
       AsyncAction(fs.BeginWrite(buffer, 0, count, fs.EndWrite);
   }
}

Well, it’s possible if we follow the syntax required by C# generators, yielding partial results wrapped inside a set of classes provided by our API. This is a small price to pay considering that our code will now look cleaner:

 static IEnumerable<AsyncAction> Download(string url, string path) {
   WebRequest req = HttpWebRequest.Create(url);
 
   AsyncAction<WebResponse> response = new AsyncAction<WebResponse>(url, req.BeginGetResponse, req.EndGetResponse);
   yield return response;
   Stream rs = response.Result.GetResponseStream();
 
   FileStream fs = File.Create(path);
   int count = 1;
   byte[] buffer = new byte[1024];
   while(count > 0) {
       AsyncAction<int> a_count = new AsyncAction<int>("ReadAsync", (callback, state) => 
           rs.BeginRead(buffer, 0, buffer.Length, callback, state), rs.EndRead);
       yield return a_count;
 
       AsyncAction<object> state = new AsyncAction<object>("WriteAsync", (callback, state) => 
           fs.BeginWrite(buffer, 0, a_count.Result, callback, state), 
           (asyncResult) => { fs.EndWrite(asyncResult); return asyncResult.AsyncState; });
       yield return state;
 
       count = a_count.Result;
   }
}

In the next post we will see how the actions in this example can be simplified with extensions methods. The important thing to note is that now it’s a lot easier to add more operations to the sequence.

To invoke our new generator method, we need to introduce another class (Serial) to wrap sequential operations, and a context to provide the following familiar functionality:

Completed and ProgressChanged events.
Cancellation with Cancelled flag returned by Completed event.
Exception handling with Error returned by Completed event.
Tracing events for debugging.
Events posted to the current synchronization context (so we can modify controls in the UI thread).

void DownloadUrl(string url, string path) {
   AsyncContext context = new AsyncContext();
   context.Completed += new EventHandler<AsyncCompletedEventArgs>(Completed);
 
   Serial op = new Serial("test", true, Download(url, path));
   op.Start(context);
}
 
private void Completed(object sender, AsyncCompletedEventArgs args) {
   if (args.Cancelled)
      Console.WriteLine("CONTEXT WAS CANCELLED.");
 
   if (args.Error != null)
      foreach (Exception ex in ((AsyncContextException)args.Error).Errors)
         DisplayError(ex);
 
   Console.WriteLine("CONTEXT COMPLETED.");
}

That’s all for now. It’s past midnight and I have work tomorrow

In my next post I’ll describe in detail the entire API, introducing the remaining pieces - parallel operations, progress reporting, tracing and cancellations.

Stay tuned.

CoNatural.Threading.dll?

Roger Torres - February 5th, 2009
Comments

Three weeks ago I started working on an email archiving system based on Microsoft Exchange Server 2003. After researching a bit about all the possible ways to interface with Exchange, I decided to try the .NET System.DirectoryServices component to query Active Directory (via LDAP) for users/mailboxes, and complement with System.Net web requests (via WebDAV) to manage exchange items (folders, mail, contacts, etc).

After a few hours struggling with the .NET asynchronous programming model, I started “googling” again for options. The problem was that I was trying to invoke multiple asynchronous operations in parallel to achieve maximum performance, but the resulting code was getting too disorganized and hard to follow. All I needed was to find a way to continue experiencing the natural sequential feeling of my imperative code without sacrificing parallelism. The ideal solution would start background threads from the thread pool automatically when required, dealing with GUI synchronization context issues as well.

I found a group of very interesting articles, all showing various ways to solve my problem by leveraging the new functional language features of C# 3.0. With a combination of lambda expressions, closures, and a hack to simulate (more or less) coroutines via C# generators (or iterators), I was able to see the light at the end of the tunnel.

Here are some links to the best articles I found:

http://msdn.microsoft.com/en-us/magazine/cc163323.aspx

http://blogs.msdn.com/michen/archive/2006/03/30/564671.aspx

http://tomasp.net/blog/csharp-async.aspx

http://msmvps.com/blogs/mihailik/archive/2005/12/26/79813.aspx

http://tirania.org/blog/archive/2003/Apr-22.html

After reading all of them (and sorting many of these brilliant ideas out), I felt compelled to write a tool that was more accessible to .NET developers, a tool that will eventually become handy to complete my mail archiving application.

Last night I finally settled for a pattern that seemed well balanced in terms of complexity and functionality. I still need to implement progress reporting and completion/cancellation events though, but I will start this series describing my line of thinking in parallel with the final coding touches

I will name and encapsulate this component inside CoNatural.Threading.dll for a change.

Stay tuned.

CoNatural.Data Source Code

Roger Torres - February 4th, 2009
Comments

You can download the [download#3] here.

This code will compile with Visual Studio 2008. You will have to make a few modifications to compile it with VS2005 (all minor).

I’m also releasing the Visual Studio Wizard project that I described in previous posts. You can modify the template according to your needs. [download#4]

I hope these components and/or the ideas behind them will make your coding life easier!

In the near future I’m planning to start a new couple of series about parallel programming and ASP.NET MVC.

Stay tuned.

Handling DAL Exceptions - Solution

Roger Torres - January 22nd, 2009
3 Comments

It’s generally OK to bubble application exceptions to the GUI layer in order to present to the end users a detailed description of what went wrong; but exceptions generated inside the DAL should be handled with care since displaying too much information about the underlying database could compromise the entire application. The system should also notify administrators when something goes wrong, logging exception details for later review, and sometimes swallowing the original exception and continuing with the execution as if nothing had happened.

In order to fulfill all of the above, I made a small modification to the CoNatural.Data framework to intercept all DAL exceptions by invoking a special method inside the Connection class. This method accepts the Original Exception and the CoNatural Command that generated it, and returns a customized exception that the framework will re-throw to the upper layers. By default, the same original exception is returned, but this method can be overridden by the designers to meet their specific requirements. The most common scenarios we will need to cover are:

Perform some custom logging/notifications before returning the same original exception if there is nothing harmful in the details.
Same as 1, but replacing the original exception with a new one that makes more sense to the end users while hiding sensitive information.
Same as 1, but returning a null value. The DAL will shallow the original exception and continue working normally as if nothing wrong happened.

Let’s illustrate this concept with the specialized connection class I use to test with AdventureWorks. Here I override HandleException to implement the following rules:

a) Log all exceptions to the Console (this is just an example
b) Notify the admin when the exception was generated by a command in the dbo schema.
c) Return a user friendly exception to the upper layer with the name of the command.

public class AdventureWorksConnection : CoNatural.Data.Connection {
  public AdventureWorksConnection(string connectionString) : 
    base(connectionString, new AdventureWorksDbProvider()) {
  }
 
  protected override Exception HandleException(CoNatural.Data.Command command, Exception dalException) {
    // Log exception to console
    Console.WriteLine(dalException.Message);
 
    // Email Exception to system admin if command comes from dbo
    if (command.CommandName.StartsWith("dbo.")) {
      // TODO email exception details
      Console.WriteLine("Exception emailed to admin.");
    }
 
    // Return user friendly exception
    return new Exception("Exception ocurred when executing command " + command.CommandName);
  }
}

Although this solution is very simple, it’s all I need … for now.

Handling DAL Exceptions

Roger Torres - January 16th, 2009
Comments

A couple of readers have asked me about how we handle exceptions inside the CoNatural.Data framework. In general, DAL exceptions should not be bubbled to the upper layers for security reasons, but during the development phase, we need to show exception details to the developers or users testing the application.

The way I have been dealing with DAL exceptions is by catching them inside the controller layer, and deciding right there if I want to bubble it or replace it with some other “more meaningful and secured message”. But I admit there is room for rationalization here, and I will spend some time in the next post to explain an alternative that will centralize this issue, taking care of exception handling policies by specific data commands, exception logging, and notifications.

Stay tuned.

How to write a VS-2008 Item Template and Wizard for CoNatural Data Commands

Roger Torres - January 10th, 2009
3 Comments

Although writing software is a very creative and enjoyable venture, it’s not free from boring and repetitive activities. Software designers should always try to find mechanisms to streamline the development process in order to get rid of these activities. This will not only save us time and money, but will ultimately help us to build a more robust, reusable, and extensible product.

Visual Studio offers multiple extensibility mechanisms aimed to accelerate the development process, including project and item templates as one of the simpler and most effective ways to jump-start a project without having to create all the items from scratch.

A template is basically a compressed .zip file containing:

All the source code files, embedded resources, project files, etc required to recreate and item or project.
A .vstemplate file containing the metadata that provides Visual Studio with the information it needs to display the template in the New Project and Add New Item dialog boxes and create a project or item from the template.

Visual Studio automatically displays all templates (placed in the proper folder) in the My Templates section of the New Project and Add New Item dialog boxes.

Templates are great for applying coding conventions to our classes, or to enforce a consistent project or solution structure, but we must be careful not to overdo it. Templates must be static elements in general, since changing a template in a very advanced project could potentially trigger an immense amount of manual changes in all the derived items that have been modified.

In this post I will show how to define a very simple item template for CoNatural Data Commands, following the naming conventions and rules specified by the CoNatural Data Framework. CoNatural Commands are defined by a pair of files, one implementing a SQL script, and the other (nested file) implementing the “code-behind’ in charge of mapping parameters and other data transformation operations. The template should enforce the following rules:

Both files must have the same name (the name of the command). File extensions are .sql and .cs for CSharp projects.
The code-behind file must be nested inside the SQL script file.
The SQL script file must be configured as an embedded resource inside the project.

Visual Studio Wizard

Sometimes you need to run custom code when creating a project or item from a template. Some of the operations we might need are:

Display a custom UI to collect user input to parameterize the template.
Add additional files to the template.
Perform any actions allowed by the Visual Studio automation object model on a project.

Visual Studio provides the IWizard interface as another extensibility mechanism to customize your projects. The IWizard interface methods are called at various times while the project is being created, starting as soon as a user clicks OK on the New Project dialog box.

In our example we will use this interface to nest the code-behind file and configure the SQL scripts as an embedded resources.

You can find more information about Visual Studio templates and wizards at http://msdn.microsoft.com/en-us/library/6db0hwky(VS.80).aspx

The Solution

Let’s start by creating a new C# Visual Studio 2008 class library project like the one below:

CoNatural Visual Studio Wizard

Here we are implementing our VS Wizard component inside the “TemplateWizard” namespace, but we will also hold the item template and metadata files in the internal “Files” folder for convenience. In case you want to modify the template, this is the place to go. Note that we have four files in this folder:

Command.cs: The template for the code-behind.
Command.sql: The template for the sql script.
Command.ico: The icon VS will use to show our template.
Command.vstemplate: The template metadata.

All these files must be configured with the “Build Action” set to “None” since they are not part of the Wizard component and they will not compile.

Our template is very simple, I will just define a common header in all my SQL script files with the name of the command, who created it and the time of creation. Visual Studio templates support parameter substitution to enable replacement of key parameters, such as class names and namespaces, when the template is instantiated. You can find a list of parameters at http://msdn.microsoft.com/en-us/library/eehb4faa(VS.80).aspx.

The template for the SQL script is defined as follows:

-------------------------------------------------------
-- $itemname$
--
-- Created by $username$
-- Created on $time$
-------------------------------------------------------
 
-- TODO Write your command's sql statement here
-- e.g.  SELECT a, b, c FROM tableName WHERE id = @id

There is nothing very sophisticated here, just the header and a comment with a simple example showing how to write a script. You can modify this template with the information you would normally want your developers to have.

The template for the code-behind file also includes a header section, but there are a couple of important details:

We are declaring our class inside the namespace where the command has been defined.
Our class is derived from CoNatural.Data.Command.

//-------------------------------------------------------
//-- $itemname$
//--
//-- Created by $username$
//-- Created on $time$
//-------------------------------------------------------
using System;
 
namespace $rootnamespace$ {
	public class $safeitemname$ : CoNatural.Data.Command {
		// TODO Write your parameters and other helper methods here
	}
}

The last file defines the template metadata:

<VSTemplate Version="2.0.0" xmlns="http://schemas.microsoft.com/developer/vstemplate/2005" Type="Item">
  <TemplateData>
    <DefaultName>Command.cs</DefaultName>
    <Name>CoNatural.Data.Command Item</Name>
    <Description>An empty CoNatural data command definition.</Description>
    <ProjectType>CSharp</ProjectType>
    <SortOrder>10</SortOrder>
    <Icon>Command.ico</Icon>
  </TemplateData>
  <TemplateContent>
    <ProjectItem TargetFileName="$fileinputname$.sql" ReplaceParameters="true">Command.sql</ProjectItem>
    <ProjectItem TargetFileName="$fileinputname$.cs" ReplaceParameters="true">Command.cs</ProjectItem>
  </TemplateContent>
  <WizardExtension>
    <Assembly>CoNatural.Data.VisualStudio, Version=1.0.0.0, Culture=neutral, PublicKeyToken=1c0fcfe5189a65ef</Assembly>
    <FullClassName>CoNatural.Data.VisualStudio.TemplateWizard.CommandWizard</FullClassName>
  </WizardExtension>
</VSTemplate>

You can find more information about Visual Studio template metadata files at http://msdn.microsoft.com/en-us/library/xsxc3ete(VS.80).aspx.

Enforcing the rules

Now we just need to enforce the rules imposed by our framework, but a simple item template cannot do it. The third section of the metadata describes the WizardExtension component that will take care of our custom code when the template is instantiated. Going back to our project, you will find the CommandWizard class implementing the IWizard interface as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.VisualStudio.TemplateWizard;
 
namespace CoNatural.Data.VisualStudio.TemplateWizard {
  public class CommandWizard : IWizard {
    private EnvDTE.ProjectItem sqlFile;
    private EnvDTE.ProjectItem csFile;
 
#region IWizard Members
 
    public void BeforeOpeningFile(EnvDTE.ProjectItem projectItem) {}
    public void ProjectFinishedGenerating(EnvDTE.Project project) {}
 
    public void ProjectItemFinishedGenerating(EnvDTE.ProjectItem projectItem) {
      if (projectItem.Name.EndsWith(".sql"))
        sqlFile = projectItem;
      else
        csFile = projectItem;
    }
 
    public void RunFinished() {
      if (csFile != null && sqlFile != null) {
        // nest code-behind file
        string filename = csFile.get_FileNames(0);
        sqlFile.ProjectItems.AddFromFile(filename);
 
        // enforce build action as embedded resource   
        sqlFile.Properties.Item("BuildAction").Value = "3";
      }
    }
 
    public void RunStarted(object automationObject, Dictionary<string, string> replacementsDictionary, Microsoft.VisualStudio.TemplateWizard.WizardRunKind runKind, object[] customParams) {}
 
    public bool ShouldAddProjectItem(string filePath) {
      return true;
    }
 
#endregion
  }
}

Here we identify our project items when Visual Studio is generating the template. Before Visual Studio finishes, we make sure the SQL script file (with extension .sql) is configured as an embedded resource, and the code-behind file is “nested” inside the script. If you look at the project file after adding a few commands, you will understand the magic behind all this. Visual Studio takes care of preparing the project metadata and displaying our files as expected. Here is a section of the project I’m using as the DAL to test with AdventureWorks. Note how the code-behind files are linked to their corresponding scripts using the DependentUpon elements:

<ItemGroup>
    <Compile Include="AdventureWorksConnectionProvider.cs" />
    <Compile Include="dbo\uspGetEmployeeManagers.cs">
      <DependentUpon>uspGetEmployeeManagers.sql</DependentUpon>
    </Compile>
    <Compile Include="dbo\uspGetManagerEmployees.cs">
      <DependentUpon>uspGetManagerEmployees.sql</DependentUpon>
    </Compile>
    <Compile Include="HumanResources\uspUpdateEmployeeHireInfo.cs">
      <DependentUpon>uspUpdateEmployeeHireInfo.sql</DependentUpon>
    </Compile>
    <Compile Include="HumanResources\uspUpdateEmployeeLogin.cs">
      <DependentUpon>uspUpdateEmployeeLogin.sql</DependentUpon>
    </Compile>
    <Compile Include="Properties\AssemblyInfo.cs" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="HumanResources\uspUpdateEmployeeHireInfo.sql" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="dbo\uspGetEmployeeManagers.sql" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="dbo\uspGetManagerEmployees.sql" />
  </ItemGroup>
  <ItemGroup>
    <EmbeddedResource Include="HumanResources\uspUpdateEmployeeLogin.sql" />
  </ItemGroup>

Deploying the template

The assembly implementing the Wizard must be installed in the GAC, so we will configure the project to take care of this in the pre-build and post-build actions as follows:

Pre-Build Steps

Post Build Steps

The gacutil.exe can also be found at C:\Program Files\Microsoft SDKs\Windows\v6.0A\Bin\gacutil.exe

Now we can compile the project and make sure the new assembly has been registered in the GAC.

CoNatural Wizard Assembly

Once the wizard is compiled and installed in the GAC, we can verify that the public key token assigned to our assembly matches the one we described in the template metadata.

Finally, let’s Zip our template files into CoNatural.Data.Command.Template.zip and copy it to the Visual Studio templates folder at:

\My Documents\Visual Studio 2008\Templates\ItemTemplates\Visual C#

Restart Visual Studio and the new CoNatural Data Command template will be there for you to enjoy it!

But this is not enough!

This example will help you create new CoNatural Data Commands in a snap, taking care of the configuration details and conventions you might want to use. But what would happen to my command if I rename one of the files?

I will describe one solution to this problem in a future post. Stay tuned.

Better the devil you know - Part VI.

Roger Torres - December 26th, 2008
2 Comments

This is the last part of a series about building a simple .NET data access layer based on ADO.NET. You can find all the previous posts here:

Part I: Motivation.
Part II: Design.
Part III: Execution Methods.
Part IV: Reading Data with Type Binders.
Part V: Writing Data with Command Binders.

BATCH EXECUTION

Sometimes developers need to invoke multiple commands in a single database call, mainly for performance reasons, but ADO.NET doesn’t provide a native mechanism to solve this type of pattern. I decided to include a couple of methods to execute multiple commands in a batch, again taking care of parameter mappings before and after the execution, because this is another common requirement I’ve found in many projects.

Let’s see how we can insert a list of new employees in a single batch call:

List<Command> commands = new List<Command>();
for (int i = 0; i < 10; i++) {
	InsertEmployee cmd = new InsertEmployee();
	cmd.FirstName = "FirstName" + i;
	cmd.LastName = "LastName" + i;
	cmd.HireDate = new DateTime(2006, 1, 1);
	commands.Add(cmd);
}
 
using (TransactionScope scope = new TransactionScope()) {
	cn.Execute(commands);
	scope.Complete();
}
 
// show the new ids
commands.ForEach(e => Console.WriteLine(((InsertEmployee)e).Id));

It’s that simple, and you can verify that after the transaction is completed, each individual command holds the corresponding newly created employee id.

There is one small limitation though, the batch execution methods always use the stored procedure execution mode (for performance reasons), thus a deployment of the commands is required beforehand. In case you want to inspect the generated batch execution script, try the following line of code:

string script = cn.GetBatchExecuteScript(commands);

This is a great way to understand what’s happening inside the framework when one or multiple commands are executed.

BATCH EXECUTION WITH COMMAND BINDERS

A second batch execution method is available to “apply” the same command to multiple entity instances via the command binder interface. This is a common scenario found in web applications for example, when the user wants to apply multiple updates to a list of entities in a single transaction.

Going back to our list of employees, let’s try the same batch insert operation but now using a list that can be created outside the data access layer:

List<Employee> employees = new List<Employee>();
for (int i = 0; i < 10; i++) {
	employees.Add(
		new Employee() { 
			FirstName = "FN" + i, 
			LastName = "LN" + i, 
			HireDate = new DateTime(2005, 1, 1) 
		}
	);
}

The application controller just needs to accept the list of employee instances and invoke the DAL to apply the InsertEmployee command:

using (TransactionScope scope = new TransactionScope()) {
	cn.ExecuteBinder<Employee>(new InsertEmployee(), employees);
	scope.Complete();
}
 
// show the new employees
employees.ForEach(e => Console.WriteLine(e));

The instances are automatically updated with the newly created employee ids.

LINKED PARAMETERS INSIDE A BATCH

There is one final aspect of batch execution I want to cover. Sometimes your application will require to execute a list of data operations in order to complete a use case, and the results of the first group of operations will be used as input parameters to the second group of operations. What developers usually do is make two or more database calls to resolve this issue, but if the list of operations is too big and there are too many dependencies, the amount of calls can kill your performance.

With our DAL you can execute all these operations in a single batch call by linking command parameters. Let’s add a ManagerID column to our Employee table and insert a hierarchy of employees in a batch, where each employee is going to be the direct manager of the next employee in the list.

First, let’s make some modifications to the table and command class to incorporate the ManagerID field:

-- create table with new ManagerID column
CREATE TABLE [dbo].[Employee](
   [EmployeeID] [INT] IDENTITY(1,1) NOT NULL,
   [ManagerID] [INT],
   [FirstName] [NVARCHAR](50) NOT NULL,
   [LastName] [NVARCHAR](50) NOT NULL,
   [HireDate] [DATETIME] NOT NULL,
   CONSTRAINT [PK_Employee_EmployeeID] PRIMARY KEY CLUSTERED
   ([EmployeeID] ASC)
) ON [PRIMARY]
 
-- set the FK constraint
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Employee] FOREIGN KEY([ManagerID])
REFERENCES [dbo].[Employee] ([EmployeeID])

Update the command’s script to insert the ManagerID

INSERT INTO dbo.Employee(ManagerID, FirstName, LastName, HireDate)
VALUES(@ManagerId, @FirstName, @LastName, @HireDate)
SET @Id = SCOPE_IDENTITY()

And update the code-behind. Note that the new ManagerID property is nullable and annotated with Input/Ouput direction since we don’t now the new Ids until the batch execution is completed, and we want the DAL to update these properties for us.

public class InsertEmployee : CoNatural.Data.Command {
	[CoNatural.Data.Parameter(System.Data.ParameterDirection.Output)]
	public int Id { get; set; }
 
	[CoNatural.Data.Parameter(System.Data.ParameterDirection.InputOutput)]
	public int? ManagerId { get; set; }
 
	[CoNatural.Data.Parameter(50)]
	public string FirstName { get; set; }
 
	[CoNatural.Data.Parameter(50)]
	public string LastName { get; set; }
 
	public DateTime HireDate { get; set; }
 
	public override string ToString() {
		return string.Format("Id={0}, ManagerId={1}, Name={2} {3}, HireDate={4}",
			Id, ManagerId, FirstName, LastName, HireDate);
	}
}

Finally, let’s deploy the command and execute the batch inside a transaction:

conn.Deploy(new InsertEmployee());
 
// prepare the commands for batch execution
List<Command> commands = new List<Command>();
for (int i = 0; i < 10; i++) {
	InsertEmployee cmd = new InsertEmployee();
	cmd.FirstName = "FirstName" + i;
	cmd.LastName = "LastName" + i;
	cmd.HireDate = new DateTime(2006, 1, 1);
       // Set Manager.
       // NOTE THE USE OF LINKED PARAMETERS
	if (i > 0)
		cmd["ManagerId"].Value = commands[i - 1]["Id"];
	commands.Add(cmd);
}
 
using (TransactionScope scope = new TransactionScope()) {
	conn.Execute(commands);
	scope.Complete();
}
 
// show the script
System.Console.WriteLine(conn.GetBatchExecuteScript(commands));
 
// show the commands after batch execution
commands.ForEach(e => System.Console.WriteLine(e));

Note how the ManagerId parameter is linked to the previous employee Id command by using the indexer property defined in the command class. You can inspect the console output to understand how the DAL prepares the batch script and verify that the newly created employee ids and linked manager ids properties are properly updated.

Batch Script

-- declare table to record output parameter values
DECLARE @OUTPUT TABLE(commandIndex INT, parameterName VARCHAR(100), parameterVal
ue SQL_VARIANT)
 
DECLARE @Id0 INT
DECLARE @ManagerId0 INT
SET @ManagerId0 = null
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id0 OUTPUT, @ManagerId0 outp
ut, 'FirstName0', 'LastName0', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(0, 'Id', @Id0)
INSERT INTO @OUTPUT VALUES(0, 'ManagerId', @ManagerId0)
 
DECLARE @Id1 INT
DECLARE @ManagerId1 INT
SET @ManagerId1 = @Id0
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id1 OUTPUT, @ManagerId1 outp
ut, 'FirstName1', 'LastName1', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(1, 'Id', @Id1)
INSERT INTO @OUTPUT VALUES(1, 'ManagerId', @ManagerId1)
 
DECLARE @Id2 INT
DECLARE @ManagerId2 INT
SET @ManagerId2 = @Id1
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id2 OUTPUT, @ManagerId2 outp
ut, 'FirstName2', 'LastName2', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(2, 'Id', @Id2)
INSERT INTO @OUTPUT VALUES(2, 'ManagerId', @ManagerId2)
 
DECLARE @Id3 INT
DECLARE @ManagerId3 INT
SET @ManagerId3 = @Id2
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id3 OUTPUT, @ManagerId3 outp
ut, 'FirstName3', 'LastName3', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(3, 'Id', @Id3)
INSERT INTO @OUTPUT VALUES(3, 'ManagerId', @ManagerId3)
 
DECLARE @Id4 INT
DECLARE @ManagerId4 INT
SET @ManagerId4 = @Id3
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id4 OUTPUT, @ManagerId4 outp
ut, 'FirstName4', 'LastName4', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(4, 'Id', @Id4)
INSERT INTO @OUTPUT VALUES(4, 'ManagerId', @ManagerId4)
 
DECLARE @Id5 INT
DECLARE @ManagerId5 INT
SET @ManagerId5 = @Id4
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id5 OUTPUT, @ManagerId5 outp
ut, 'FirstName5', 'LastName5', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(5, 'Id', @Id5)
INSERT INTO @OUTPUT VALUES(5, 'ManagerId', @ManagerId5)
 
DECLARE @Id6 INT
DECLARE @ManagerId6 INT
SET @ManagerId6 = @Id5
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id6 OUTPUT, @ManagerId6 outp
ut, 'FirstName6', 'LastName6', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(6, 'Id', @Id6)
INSERT INTO @OUTPUT VALUES(6, 'ManagerId', @ManagerId6)
 
DECLARE @Id7 INT
DECLARE @ManagerId7 INT
SET @ManagerId7 = @Id6
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id7 OUTPUT, @ManagerId7 outp
ut, 'FirstName7', 'LastName7', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(7, 'Id', @Id7)
INSERT INTO @OUTPUT VALUES(7, 'ManagerId', @ManagerId7)
 
DECLARE @Id8 INT
DECLARE @ManagerId8 INT
SET @ManagerId8 = @Id7
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id8 OUTPUT, @ManagerId8 outp
ut, 'FirstName8', 'LastName8', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(8, 'Id', @Id8)
INSERT INTO @OUTPUT VALUES(8, 'ManagerId', @ManagerId8)
 
DECLARE @Id9 INT
DECLARE @ManagerId9 INT
SET @ManagerId9 = @Id8
EXEC [dbo].[AdventureWorks_DAL_dbo_InsertEmployee] @Id9 OUTPUT, @ManagerId9 outp
ut, 'FirstName9', 'LastName9', '20060101 00:00'
INSERT INTO @OUTPUT VALUES(9, 'Id', @Id9)
INSERT INTO @OUTPUT VALUES(9, 'ManagerId', @ManagerId9)
 
-- return output parameters
SELECT commandIndex, parameterName, parameterValue FROM @OUTPUT

Console Output

Id=51, ManagerId=, Name=FirstName0 LastName0, HireDate=1/1/2006 12:00:00 AM
Id=52, ManagerId=51, Name=FirstName1 LastName1, HireDate=1/1/2006 12:00:00 AM
Id=53, ManagerId=52, Name=FirstName2 LastName2, HireDate=1/1/2006 12:00:00 AM
Id=54, ManagerId=53, Name=FirstName3 LastName3, HireDate=1/1/2006 12:00:00 AM
Id=55, ManagerId=54, Name=FirstName4 LastName4, HireDate=1/1/2006 12:00:00 AM
Id=56, ManagerId=55, Name=FirstName5 LastName5, HireDate=1/1/2006 12:00:00 AM
Id=57, ManagerId=56, Name=FirstName6 LastName6, HireDate=1/1/2006 12:00:00 AM
Id=58, ManagerId=57, Name=FirstName7 LastName7, HireDate=1/1/2006 12:00:00 AM
Id=59, ManagerId=58, Name=FirstName8 LastName8, HireDate=1/1/2006 12:00:00 AM
Id=60, ManagerId=59, Name=FirstName9 LastName9, HireDate=1/1/2006 12:00:00 AM

CONCLUSION

ADO.NET has been around for many years, and we all know it’s stable, fast, simple, widely adopted, and one of the most important pillars of the .NET framework. Today Microsoft is pushing for newer data access technologies like LINQ for SQL and LINQ for Entities, but it will take a while for the community to learn and adopt these technologies (I don’t even know what’s going to happen now that they are more focused on cloud computing and distributed data services).

In the mean time, we need to continue building robust and maintainable software … and to me, ADO.NET still looks like the best option for the DAL (when used properly). This article presented a simple DAL based on ADO.NET that was designed with two goals in mind: simplicity and efficiency. Now I want to invest my limited spare time learning other technologies that will make a bigger impact on my projects, and there are plenty of them (WCF, WWF, Silverlight, ASP.NET MVC).

I’m planning to continue covering this framework in the future, presenting more complex examples using the AdventureWorks database.

Any comments or suggestions are appreciated.

I hope you enjoyed it.

The Commands

The Parallel Async Search

Attachments

The Form

What’s next?

The Solution

AsyncAction

AsyncAction<T>

Serial

Serial<T>

Parallel

Return<T>

Progress

Trace

AsyncContext

Putting it all together

Asynchronous Operation

The traditional patterns

Visual Studio Wizard

The Solution

Enforcing the rules

Deploying the template

But this is not enough!

BATCH EXECUTION

BATCH EXECUTION WITH COMMAND BINDERS

LINKED PARAMETERS INSIDE A BATCH

CONCLUSION

My Links

Recent Posts

Archives

Categories

Meta/RSS

Recent Comments