A very good book by the authors that I admire and appreciate mentioned the use of a class called SQLAsyncResult to access data asynchronously using ADO.NET 2.0 via the callback approach. My attempt to try out an example failed as I couldn’t find the SQLAsyncResult class, not in the namespace, not in the MSDN library. However, I did find a post on ASP.NET forum about a dev’s unsuccessful hunt for this class and an MVP answering that it might be a type in the example.This post is in continuation to my previous post about Asynchronous data access using ADO.NET however, that post need not be referred if you are interesting in the callback only.
The reason I’m mentioning it at the start of this post is that, if any one developer finds this class, I would really appetite you leaving a comment.
Now, back to the Callback approach …
In this approach we leverage .NET infrastructure relating to threading, AsyncCallback delegate, the AsyncResult class and the IAsyncResult interface.
The approach is accomplished by:
- Creating a call back method which will process the data (essentially call the end invoke command) which accepts the IAsyncResult
- Creating an AsyncCallback delegate for the callback methods created &
- Passing the delegate along with the command object in the begin invoke command.
The model class to load data:
public class Customer {
public Customer() {
}
public Customer(SqlDataReader reader) {
CustomerID = reader["CustomerID"].ToString();
CompanyName = reader["CompanyName"] == null ? string.Empty : reader["CompanyName"].ToString();
ContactName = reader["ContactName"] == null ? string.Empty : reader["ContactName"].ToString();
Phone = reader["Phone"] == null ? string.Empty : reader["Phone"].ToString();
}
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string Phone { get; set; }
}
Here’s the data access class that fetches customer asynchronously using the callback approach:
public class CustomerDB {
/// <summary>
/// This variable keeps the track of number of asynchronous calls
/// </summary>
private static int counter = 0;
/// <summary>
/// Class member to store the result.
/// </summary>
private List<Customer> _customers = new List<Customer>();
/// <summary>
/// This is the main method that needs to be called from other application layer to get Customers.
/// </summary>
/// <returns></returns>
public List<Customer> GetCustomers() {
//Initiate Connection
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
//Initiate Command
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT top 10 * FROM [Customers]";
cmd.CommandType = CommandType.Text;
try {
con.Open();
// create AsyncCallback delegate
AsyncCallback callback = new AsyncCallback(RetrieveDataCallback);
cmd.BeginExecuteReader(callback, cmd, CommandBehavior.CloseConnection);
//Wait until the call is complete
while (counter < 1) {
}
}
catch {
//Log error
}
return _customers;
}
/// <summary>
/// The callback method to retrieve data.
/// </summary>
/// <param name="result"></param>
private void RetrieveDataCallback(IAsyncResult result) {
SqlDataReader dr = null;
try {
SqlCommand command = (SqlCommand)result.AsyncState;
Customer customer = null;
dr = command.EndExecuteReader(result);
while (dr.Read()) {
customer = new Customer(dr);
_customers.Add(customer);
}
}
catch {
//Log error
}
finally {
try {
if (!dr.IsClosed) {
dr.Close();
}
}
catch { } //Suppress any exceptions here
//Increment value to notify that the call is complete.
Interlocked.Increment(ref counter);
}
}
}
Let’s rewrite this code to make multiple calls to different databases using the same customer model class. To make sure that the data access is thread safe we need to add a static object. This static object needs to be locked every time a thread completes and attempts to update the Customers list. Here’s the new CustomerDB class:
public class CustomerDB {
/// <summary>
/// static object for thread safety.
/// </summary>
static object lockObject = new object();
/// <summary>
/// This variable keeps the track of number of asynchronous calls
/// </summary>
private static int counter = 0;
/// <summary>
/// Class member to store the result.
/// </summary>
private List<Customer> _customers = new List<Customer>();
/// <summary>
/// This is the main method that needs to be called from other application layer to get Customers.
/// </summary>
/// <returns></returns>
public List<Customer> GetCustomers() {
//Initiate Connection
SqlConnection con1 = new SqlConnection();
con1.ConnectionString = ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString;
SqlConnection con2 = new SqlConnection();
con1.ConnectionString = ConfigurationManager.ConnectionStrings["SouthwindConnectionString"].ConnectionString;
SqlConnection con3 = new SqlConnection();
con1.ConnectionString = ConfigurationManager.ConnectionStrings["EastwindConnectionString"].ConnectionString;
//Initiate Command
SqlCommand cmd1 = con1.CreateCommand();
SqlCommand cmd2 = con2.CreateCommand();
SqlCommand cmd3 = con3.CreateCommand();
//The table structures are same in my case.
//In case of different table structures include logic in the call back method
//or Customer constructor to DB specific load.
cmd1.CommandText = "SELECT top 10 * FROM [Customers]";
cmd1.CommandType = CommandType.Text;
cmd2.CommandText = "SELECT top 10 * FROM [Customers]";
cmd2.CommandType = CommandType.Text;
cmd3.CommandText = "SELECT top 10 * FROM [Customers]";
cmd3.CommandType = CommandType.Text;
try {
con1.Open();
con2.Open();
con3.Open();
// create AsyncCallback delegate
AsyncCallback callback1 = new AsyncCallback(RetrieveDataCallback);
AsyncCallback callback2 = new AsyncCallback(RetrieveDataCallback);
AsyncCallback callback3 = new AsyncCallback(RetrieveDataCallback);
cmd1.BeginExecuteReader(callback1, cmd1, CommandBehavior.CloseConnection);
cmd2.BeginExecuteReader(callback2, cmd2, CommandBehavior.CloseConnection);
cmd3.BeginExecuteReader(callback3, cmd3, CommandBehavior.CloseConnection);
//Wait until all the call are complete
while (counter < 3) {
}
}
catch {
//Log error
}
return _customers;
}
/// <summary>
/// The callback method to retrieve data.
/// </summary>
/// <param name="result"></param>
private void RetrieveDataCallback(IAsyncResult result) {
SqlDataReader dr = null;
try {
SqlCommand command = (SqlCommand)result.AsyncState;
Customer customer = null;
dr = command.EndExecuteReader(result);
while (dr.Read()) {
customer = new Customer(dr);
// lock the static object when data is loaded
lock (lockObject) {
_customers.Add(customer);
}
}
}
catch {
//Log error
}
finally {
try {
if (!dr.IsClosed) {
dr.Close();
}
}
catch { } //Suppress any exceptions here
//Increment value to notify that the call is complete.
Interlocked.Increment(ref counter);
}
}
}
Ok, my question has to do with the callback part of the code. If you never close the SqlDataReader, wouldn't you have a connection leak?
ReplyDelete@ThreadAbort - Thanks for identifying the issue, it's now fixed.
ReplyDelete