ADO.NET 2.0 Asynchronous Command Execution (ASYNC) FAQ

ADO.NET 2.0 Asynchronous Command Execution (ASYNC) FAQ (DISCLAIMER: Based on Whidbey beta1 and subject to change)

Before going into the Q&A below I would highly recommend reading Pablo’s excellent Async article located here:
https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp

Q: What is the new ADO.NET 2.0 Asynchronous Command Execution feature.
A: ASYNC allows you to execute a command in a non-blocking manner. We are exposing in the SqlCommand the following asynchronous methods: BeginExecuteNonQuery, BeginExecuteReader and BeginExecuteXmlReader with polling, synchronization and (*shudder*) callbacks.

Q: Wait, I have seen this before, you just create a new thread and block it right?
A: No! This is a very important concept since this feature would be useless if implemented like that. Ado.net is not thread safe and blocking threads is eviel.

Q: So if you don’t create new threads how do you execute without blocking?
A: The secret is in the network layer, when you specify ASYNC=TRUE in the connection string we will open the TCP socket in overlapped mode and bind it to the I.O completion port.

Q: So does this mean that every command I execute (sync or async) will happen in overlapped mode when I add ASYNC=TRUE to the connection string?
A: Yes it does, _everything_ that we execute on this connection will be done in overlapped mode. For synchronous operations we internally wait for the completion before returning, we are basically faking the synchronous behavior on this connection. This is the reason why we require a connection string keyword.

Q: Does this have a perf impact?
A: Definitely, only use ASYNC=TRUE when you know that you are going to be using the async functionality.

Q: You mention that this feature is dependent on the network layer, does this mean that the version of MDAC in the machine matters?
A: In most scenarios it does not, in ado.net we have built the network layer for SqlClient into System.Data and you have everything you need out of the box.

Q: In most scenarios? Are there any exceptions?
A: Yes ASYNC does not work when using shared memory against Sql Server 2000 or lower. If you want to use ASYNC against a local Sql Server 2000 server you need to force the use of a different network layer. Take a look at this blog for more information: https://weblogs.asp.net/despos/archive/2004/07/22/190984.aspx

Q: Any other instance where ASYNC does not work?
A: Yes ASYNC does not work with Win9x.

Q: No win9x? doesn’t that make it really hard to create a winforms app to distribute?
A: I would highly recommend not using ASYNC with winforms. It is really not what it was designed for. Take a look at Pablo’s document for more information.

Q: (IMPORTANT) So the main scenario for this feature is to create WinForms apps that are responsive while doing data access right?
A: NO! I would not use callbacks on winforms for no money. We are fortunate in that Ken Getz is writing the documentation for this feature and has done a great job of going over the pitfalls that occur when trying this scenario, I still believe that people trying to use ASYNC with WinForms is going to be one of the worst problem areas in the newsgroups for the 2.0 release. There are a lot of pitfalls; you end up with really ugly code and you will only find the problems after you deploy. Take a look at the Whidbey BackgroundWorker class for a better option.

Q: So what are the pitfalls of using ASYNC Callbacks with WinForms?
A: ado.net objects are not thread safe, when using polling or synchronization there are no problems since everything happens on the same thread, callbacks however are by definition on a separate thread. You need to _guarantee_ that all of your ado.net objects are only used on one thread at a time. This includes having a global connection and command, guaranteeing that the command is only used by one thread, guaranteeing that the connection does not close before the callback occurs and since WinForm controls have pretty much the same limitations you need to guarantee that you only databind in the thread where the UI control was created.

Q: This does not sound so bad…
A: The biggest problem IMO is the fact that when you fail to do any of the above your application may look like it is working fine most of the time. It is only when you deploy it and customers start using it that you will start seeing random exceptions that are very hard to duplicate (machine dependent in some cases) and debug. The exception message can be absolutely anything and it will not be obvious that it is a threading issue.

Rambling out: Disclaimer this post is provided AS IS and confers no rights. Everything in this post is in my opinion only.
I will be happy to incorporate any questions you may have into this FAQ, just leave me feedback.

Comments

• Anonymous
  September 15, 2004
  Humm, I would have thought that WinForms would be the ideal target for Async. When I run a long query, I want the thread back to "entertain" the user (showing progress bars etc.). Sure, I can poll, but I would rather use callbacks. I'll get with Ken to see what he has to say about this.
  I'm unclear how you could use async in an ASP application that does not have context once the code executes and sends the HTML to the browser.
• Anonymous
  September 15, 2004
  The comment has been removed
• Anonymous
  November 28, 2004
  .NET 2.0 - get ready to understand Thread Safety
• Anonymous
  March 23, 2008
  Construyendo comandos para consultas a base de datos, es un tema que siempre despierta interés y siempre
• Anonymous
  May 29, 2009
  The comment has been removed