This is a quick example showing how to set up Asynchronous Actions in ASP.NET MVC and SQLDependency using a standard example – Chat application.
1. IIS Threading
IIS 7 creates a new thread from the thread pool whenever it receives a request from the client. When the thread processes the request synchronously it cannot handle other requests since it’s waiting for operation completion. This can be a problem for applications with a big number of requests and long running IO operations. (this is a very simplified version, lots more is happening in IIS but let’s not worry about this…).
When the thread pool no longer has any available threads on the server the requests will start being queued in the global IIS queue. Once the queue reaches its limit IIS will start returning “Server Busy” AKA 503.
This error is often generated when we are performing long running synchronous tasks or spamming the server with requests when it’s not really needed. A good / classic example of an application that can do both is the chat application.
When created incorrectly you can use JavaScript setInterval/setTimeout to get data from the server every X ms/s regardless of the changes (pooling).This will cause a big load on the server for every client / browser tab open.
There are several solutions for this problem. One of them is to use a long pooling technique:
2. Asynchronous request to the rescue
When an asynchronous request is executed on the CLR Thread Pool instead of waiting for the process to finish, the thread can go back to the thread pool and serve other request. Once the processing is finished a new thread (or the same…) is dispatched to process the remainder of the request and render the results. This increases performance for long running operations since threads can be used in a more efficient way.
I’ll use SqlDependency to get notifications whenever someone submits a chat message and asynchronous MVC actions to keep requests open and update the Chat window only when needed.
3. SqlDependency configuration
SqlDependency allows registering for query notifications in SQL server. This allows us to be notified on the changes in the thunderlying database instead of querying it for changes.
- Enable CLR integration
EXEC sp_configure 'clr enabled', 1 go RECONFIGURE
- Enable SQL Service Broker
ALTER DATABASE <DataBaseNameHere> SET ENABLE_BROKER
- Setup user permissions
GRANT CREATE PROCEDURE TO <UserNameHere>; GRANT CREATE SERVICE TO <UserNameHere>; GRANT CREATE QUEUE TO <UserNameHere>; GRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO <UserNameHere>; GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <UserNameHere>; GRANT CONTROL ON SCHEMA::[dbo] TO <UserNameHere>; GRANT IMPERSONATE ON USER::DBO TO <UserNameHere>;
4. “Coding” SqlDependency
Implementing SqlDependency is fairly simple but has a few quirks. To get the SqlDependency working you just need to:
- Call SqlDependency.Start method which will start listening for notifications.
- Create and Open new SqlConnection
- Create SQL command that you want to “listen to”. In my example I’m listening for changes in all chat messages in a room specified by constant “ROOM_NUMBER”.
- Create new SqlDependency object that takes SqlCommand as a parameter and handle OnChange event. This event will fire whenever there is a change that affects the results of our query.
- Execute Query
Unfortunately LINQ to SQL or Entity Framework does not work with SqlDepenency. That means there is no way to create a new EF ObjectContext, create a LINQ query and use it as SQL notification subscription. There is no way…or I just didn’t find a way, that is. If anyone knows how to do it please let me know! (I’ve tried
System.Runtime.Remoting.Messaging.CallContext.SetData("MS.SqlDependency", dependency.Id);
but couldn’t make it work…)
Keep in mind that when working with SqlDependency:
- You cannot use SELECT * FROM in your query
- You cannot use aggregate functions
- For full list of not supported queries please refer to : http://msdn.microsoft.com/en-us/library/aewzkxxh(v=vs.80).aspx
5. Simple chat application
To create Asynchronous actions in MVC we need to:
- Make the controller inherit from AsyncController
- Action method needs to be divided into 2 methods:
<MethodName>Async ( this method needs to return void ),
<MethodName>Completed (this method returns actual ActionResult )
- AsyncManager contains several properties/methods that are required to create asynchronous action:
- Increment / Decrement – these 2 methods exposed by OutstandingOperations property are used to indicate the start and end of long running tasks to AsyncManager.
- Parameters dictionary is used to store results of running tasks. Every key from this dictionary will be passed as parameter to <MethodName>Completed action.
There are also 2 interesting attributes that will help us in creating Async actions:
AsyncTimeout
and
NoAsyncTimeout
Names of these Action Filter attributes should be self-explanatory. By default async method will time-out after 45 seconds. In that case we can either write the code to re-request an asynchronous method or set it disable timeout. In this example I’ll go with setting timeout to 90 seconds so we can see what’s actually happens.
Sample code controller code (I hope that the code comments and the explanation above should make this clear):
public class ChatController : AsyncController {
[AsyncTimeout(90000)]
public void GetMessagesAsync(){
AsyncManager.OutstandingOperations.Increment();
SqlConnection conn = null;
try
{
string connString = ConfigurationManager.ConnectionStrings["ChatConnectionString"].ConnectionString;
conn = new SqlConnection(connString);
conn.Open(); // open new connection and create command to notify about all new Chat-Messages in -ROOM_NUMBER- const
using (var sqlCommand = new SqlCommand(@"SELECT dbo.Chat.Id FROM dbo.Chat WHERE dbo.Chat.RoomNumber = @room", conn))
{
sqlCommand.Parameters.AddWithValue("room", Configuration.ROOM_NUMBER);
var sqlDependency = new SqlDependency(sqlCommand);
//handle onChange event - this will be fired whenever there is a change in database that affacts our query
sqlDependency.OnChange += (sender, e) =>
{
//change detected - get all messages...
Models.MyValetBayEntities entities = null;
try
{
entities = new Models.MyValetBayEntities();
var query = from c in entities.ChatRoom
where c.RoomNumber == Configuration.ROOM_NUMBER
select c;
AsyncManager.Parameters["chatModel"] = query.ToArray();
}
finally
{
entities.Dispose();
//...and complete async operation
AsyncManager.OutstandingOperations.Decrement();
}
};
//execute query with associated SqlDependency to subscribe for notifications
sqlCommand.ExecuteNonQuery();
}
}
catch {
//make sure that if something goes wrong we are still have proper AsyncManager operations count
AsyncManager.OutstandingOperations.Decrement();
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
public ActionResult GetMessagesCompleted(Models.Chat[] chatModel){
return PartialView("Chat", chatModel);
}
}
Sample Javascript:
$.ajaxSetup({
// Disable caching of AJAX responses
cache: false
});
function getNews(){
$.ajax({
url: '@Url.Action("GetMessages", "Chat")',
success: function (data) {
//update messages ...
$('div#content').html(data);
//... and subscribe for notification again
getNews();
},
error: function () {
getNews(); //timeout error create new request
}
});
}
getNews();
Part of Global.asax
protected void Application_Start(){
//... MVC registration removed for simplicity
SqlDependency.Start(connString);
}
private void Application_End(){
SqlDependency.Stop(connString);
}
DEMO (off-line)
An asynchronous request waiting for information from the server in action:
6. The End…
I hope you enjoyed my post. This demo could be probably improved by using HTML5 WebSockets but that’s something for another blog post since WebSockets are not supported that well yet…
Please also note that creating asynchronous methods will be simplified with C# 5 with async and await keyword. Have a look at http://msdn.microsoft.com/en-us/vstudio/gg316360 for more details but please be careful with installing it since it’s only CTP (at the time of writing this blog post).
