Here, I am going to introduce you with asynchronous ado.net method which works asynchronously for any operation on database.
Introduction
ADO.Net 2.0 introduces asynchronous versions of several of its methods.These methds come in pairs: Begin and End method and provides asynchronous DML, DQL, etc, operations on the database.
Objective
This article will give you basic understanding of accessing database asynchronously by using ado.net asynchronous method & asynchronous nature of asp.net pages.
Using the code
Some methods for SqlCommand objects, for example, are
- BeginExecuteNonQuery()
- EndExecuteNonQuery()
- BeginExecuteReader()
- EndExecuteReader()
To use these asynchronous methods, you must use a special attribute in your connection string the
Asynchronous Processing=true
I am using a table named Movies that have following structure
Title Director
Movie_name Director_Name
Below, i have defined a data access component AsyncDataLayer containing BeginGetMovies() and EndGetMovies() method that fetch movie from Movies table asynchronously. These two methods use the ado.net BeginExecuteReader() and EndExecuteReader() to fetch a datareader asynchronously.
The AsyncDataLayer component is as follow
public class AsyncDataLayer
{
private static readonly string _connectionString;
private SqlCommand _cmdMovies;
public IAsyncResult BeginGetMovies(AsyncCallback callback, object state)
{
SqlConnection con = new SqlConnection(_connectionString);
_cmdMovies = new SqlCommand("WAITFOR DELAY '0:0:0';select title,director from movie", con);
con.Open();
return _cmdMovies.BeginExecuteReader(callback, state, CommandBehavior.CloseConnection);
}
public List<AsyncDataLayer.Movie> EndGetMovies(IAsyncResult result)
{
List<AsyncDataLayer.Movie> results = new List<AsyncDataLayer.Movie>();
SqlDataReader reader = _cmdMovies.EndExecuteReader(result);
while (reader.Read())
{
AsyncDataLayer.Movie newMovie = new AsyncDataLayer.Movie();
newMovie.Title = (string)reader["Title"];
newMovie.Director = (string)reader["Director"];
results.Add(newMovie);
}
return results;
}
static AsyncDataLayer()
{
_connectionString = WebConfigurationManager.ConnectionStrings["Movies"].ConnectionString +
";Asynchronous Processing=true";
}
public class Movie
{
private string _title;
public string Title
{
get { return _title; }
set { _title = value; }
}
private string _director;
public string Director
{
get { return _director; }
set { _director = value; }
}
}
}
For taking advantage of asynchronous ado.net method, you must enable asynchronous asp.net page execution. Enable page execution by adding following line in page directive.
<%@ Page Async="true" AsyncTimeout="1" Trace="true" %>
The first attribute enables asynchronous page execution and second attribute specifies a timeout value in seconds. After you enable asynchronous page execution , we set up asynchronous task and register this task with the page.
The complete code for asp.net page is given below
<%@ Page Language="C#" Async="true" AsyncTimeout="1" Trace="true" %>
<%@ Import Namespace="System.Threading" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
private AsyncDataLayer dataLayer = new AsyncDataLayer();
void Page_Load()
{
// Setup asynchronous data execution
PageAsyncTask task = new PageAsyncTask(BeginGetData, EndGetData, TimeoutData, null, true);
Page.RegisterAsyncTask(task);
// Fire off asynchronous tasks
Page.ExecuteRegisteredAsyncTasks();
}
IAsyncResult BeginGetData(object sender, EventArgs e, AsyncCallback callback, object state)
{
// Show Page Thread ID
Trace.Warn("BeginGetData: " + Thread.CurrentThread.GetHashCode());
// Execute asynchronous command
return dataLayer.BeginGetMovies(callback, state);
}
void EndGetData(IAsyncResult ar)
{
// Show Page Thread ID
Trace.Warn("EndGetDate: " + Thread.CurrentThread.GetHashCode());
// Bind results
grdMovies.DataSource = dataLayer.EndGetMovies(ar);
grdMovies.DataBind();
}
void TimeoutData(IAsyncResult ar)
{
// Display error message
lblError.Text = "Could not retrieve data!";
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Show Page AsyncTask</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label
id="lblError"
Runat="server" />
<asp:GridView
id="grdMovies"
Runat="server" />
</div>
</form>
</body>
</html>
The constructor for the PageAsyncTask object accepts the following parameters:
beginHandler - method that executes when asynchronous task begins
endHandler-method that executes when asynchronous task ends.
timeoutHandler - this method executes when the asynchronous task go out of time according to the Page directive's AsyncTimeout attribute.
state - an arbitrary object that represents state information.
executeInParallel - A boolean value that indicates whether multiple asynchronous tasks should execute at the same time or execute in sequence.
the OutPut of the complete operation is shown below
For checking this operation change the AsyncTimeout in page directive and 'WAITFOR DELAY 0:0:1' in the AsyncDataLayer component.
Conclusion
It will give a basic understanding of asynchronous nature of ado.net method and asp.net page.
Best Regards
Vikash Pathak
If you like this article, subscribe to our 
RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.