Some ADO.NET classes supports built-in asynchronous query behavior. Some aren’t. In this post, we’ll revise the former classes, like SqlDataReader. However, if you are familiar with multithread computing (particularly, in the .NET Framework) you can easily implement asynchronous execution in classes which don’t support it, such as the TableAdapter.
Before you’d do anything, you should introduce a new name-value pair into your connection string: Async=true or Asynchronous Processing=true. Including it, you enable asynchronous queries to be executed on the database server. This was the easy part.
Writing your asynchronous data access class is a little bit more complicated. If you are familiar with delegates and the classes of System.Threading, then you are ready to go. But if you’d like to rely on built-in logic, consider using the DbCommand’s BeginExecuteReader and EndExecuteReader. First the code, then the explanation:
There may be several situations when your web pages need to handle long-running processes, for example, query information from a slow database, work with the file system, etc. In this case, you’d need to use asynchronous pages.
First you need to understand that asynchronous pages aren’t actually faster, than their synchronous counterparts. Using them won’t affect the speed of your site, just the scalability of it. The second thing to know is that you shouldn’t use the traditional multithreading ways when you are working with ASP.NET. ASP.NET serves incoming requests from its on thread pool; one thread is responsible for the whole lifetime of the request. When you use the Threading namespace, you are getting a thread from the very same thread pool ASP.NET itself uses. So you shouldn’t do it. Instead you should use asynchronous pages, which in fact gets their working threads from another thread pool, thus freeing up the ones in ASP.NET’s, which it can use to serve more requests.