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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-------------------------------------------------------
-- 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 + '%'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

//-------------------------------------------------------
//-- 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

-------------------------------------------------------
-- 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 + '%'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

//-------------------------------------------------------
//-- 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

-------------------------------------------------------
-- 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 + '%'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

//-------------------------------------------------------
//-- 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116

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:

  1. 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.
  2. The connection string must contain the async=true attribute to support asynchronous commands.
  3. 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 not found]
CoNatural.Extensions Source Code (190)
[Download not found]
[Download not found]
[Download not found]
[Download not found]