- Posted by brunofig on October 17, 2007
Since Ado.Net 2.0 you can make asynchronous operations with the SqlCommand class. This allows your application to start a long database proccess, continue to perform all the taks required and later invoke the end method completing then the db task.
In the following example I will create a seat reservation form.
The user selects the row and the seat we prefers and registers for that seat. The table has a insert trigger that checks if the choosen seat is a reserved one, and if so, rolls back the transaction, raising an error.
The Page Source:
<%@ Page Language="C#" MasterPageFile="~/App_MasterPages/MasterPage.master" AutoEventWireup="true" CodeFile="demo.aspx.cs" Inherits="sync" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
Please choose where you want to seat*:<br />
<asp:Label runat="server" AssociatedControlID="ddlRow" Text="Row" />
<asp:DropDownList ID="ddlRow" runat="server" />
<asp:Label runat="server" AssociatedControlID="ddlSeat" Text="Seat" />
<asp:DropDownList ID="ddlSeat" runat="server" />
<br />
<asp:Button ID="btnSubmit" runat="server" Text="Register (sync)" OnClick="btnSubmit_Click" />
<asp:Button ID="btnSubmitAsync" runat="server" Text="Register (async)" OnClick="btnSubmitAsync_Click" /><br />
<small>* - The seat D10 is reserved.</small>
</asp:Content>
The Code Behind:
using System;
using System.Web.UI;
using AsyncDB;
public partial class sync : Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
ddlSeat.DataSource = Utils.GetConferenceRoomRowSeats();
ddlSeat.DataBind();
ddlRow.DataSource = Utils.GetConferenceRoomRows();
ddlRow.DataBind();
}
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataManager.RegisterParticipant(1,ddlRow.SelectedValue, ddlSeat.SelectedValue,false);
Response.Redirect("~/done.aspx");
}
protected void btnSubmitAsync_Click(object sender, EventArgs e)
{
DataManager.RegisterParticipant(1,ddlRow.SelectedValue, ddlSeat.SelectedValue,true);
Response.Redirect("~/done.aspx");
}
}
The DataManager Class:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
namespace AsyncDB
{
public static class DataManager
{
private static readonly string CONNECTION_STRING = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|sampleAsync.mdf;Integrated Security=True;User Instance=True; Asynchronous Processing=true";
private static readonly string SQL_INSERT_CONFERENCE_PARTICIPANT = "WAITFOR DELAY '0:00:15';INSERT INTO [Conference.Seats] (UserID, Row,Seat) VALUES (@UserID,@Row,@Seat)";
/// <summary>
/// Syncs the insert.
/// </summary>
/// <param name="participantID">The participant ID.</param>
/// <param name="row">The row.</param>
/// <param name="seat">The seat.</param>
/// <param name="runAsync">if set to <c>true</c> to run the async call.</param>
/// <returns></returns>
public static int RegisterParticipant(int participantID, string row, string seat, bool runAsync)
{
SqlConnection connection = new SqlConnection(CONNECTION_STRING);
SqlCommand command = new SqlCommand(SQL_INSERT_CONFERENCE_PARTICIPANT, connection);
command.Parameters.AddWithValue("@UserID", participantID);
command.Parameters.AddWithValue("@Row", row);
command.Parameters.AddWithValue("@Seat",seat);
int rowsAffected = 0;
try
{
connection.Open();
if (!runAsync)
{
rowsAffected = command.ExecuteNonQuery();
}
else
{
AsyncCallback callback = new AsyncCallback(ASyncInsertHandler);
command.BeginExecuteNonQuery(callback, command);
}
}
catch (Exception)
{
connection.Close();
throw;
}
finally
{
if (connection.State!=ConnectionState.Closed && !runAsync)
{
connection.Close();
}
}
return rowsAffected;
}
/// <summary>
/// Handles the async call.
/// </summary>
/// <param name="result"></param>
private static void ASyncInsertHandler(IAsyncResult result)
{
SqlCommand cmd = (SqlCommand)result.AsyncState;
try
{
int numRowsAffected = cmd.EndExecuteNonQuery(result);
Debug.WriteLine("numRowsAffected: " + numRowsAffected + " @ " + DateTime.Now);
}
catch (Exception ex)
{
cmd.Connection.Close();
//THE ERROR HANDLING OF THE TRIGGER SHOULD BE DONE HERE!
}
finally
{
cmd.Connection.Close();
}
}
}
}
The Utils Class:
using System.Collections;
namespace AsyncDB
{
/// <summary>
/// Utility class
/// </summary>
public static class Utils
{
private static readonly string[] CONFERENCE_ROOM_ROWS = { "A", "B", "C", "D" };
private static readonly int CONFERENCE_ROOM_SEATS_PER_ROW = 10;
/// <summary>
/// Gets the conference room rows.
/// </summary>
/// <returns></returns>
public static string[] GetConferenceRoomRows()
{
return CONFERENCE_ROOM_ROWS;
}
/// <summary>
/// Gets the conference room row seats.
/// </summary>
/// <returns></returns>
public static IEnumerable GetConferenceRoomRowSeats()
{
for (int i = 1; i <= CONFERENCE_ROOM_SEATS_PER_ROW; i++)
{
yield return i;
}
}
}
}
In the DataManager class notice that our sql insert command, stored in the DataManager.SQL_INSERT_CONFERENCE_PARTICIPANT field was a delay allowing us to simulate a long database proccess.
Also, for using the async operations, you will have to add the "Asynchronous Processing=true" to the connection string.
While observing the DataManager.RegisterParticipant method, notice that we are calling the BeginExecuteNonQuery method with a AsyncCallBack handler. This is what allows the application to continue to run its tasks without having to wait for the database proccess to end.
Finally, when the database tasks ends, the AsyncCallBack handler DataManager.ASyncInsertHandler is called. Here we cast the IAsyncResult back to the SqlCommand that started the async request and then we end the call executing the EndExecuteNonQuery.
Testing this page you will notice that:
- Clicking the "Register (sync)" button blocks any page tasks until the sql command is completed.
- Clicking the "Register (async)" redirects the user to the "Done" page.
- All async calls runs on the background, even if the user navigates to another page.
