sql async query problem

Question

So, why doesn't this ever make it to the callback function?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace sqlAsyncTesting {
    public partial class Form1 : Form {

    public Form1() {
        InitializeComponent();
    }

    private void button1_Click(object sender, EventArgs e) {
        using (SqlConnection conn = new SqlConnection(@"Data Source = bigapple; Initial Catalog = master; Integrated Security = SSPI; Asynchronous Processing = true;")) {
            conn.Open();
            SqlCommand cmd = new SqlCommand(@"WAITFOR DELAY '00:03'; Select top 3 * from sysobjects;", conn);
            IAsyncResult result = cmd.BeginExecuteReader(new AsyncCallback(HandleCallback), cmd, CommandBehavior.CloseConnection);
        }
    }

    private void HandleCallback(IAsyncResult result) {
        SqlDataReader dr;
        SqlCommand _this = (SqlCommand)result.AsyncState;

        if (result.IsCompleted) {
            dr = _this.EndExecuteReader(result);
        } else dr = null;

        DataTable dt = new DataTable();
        DataSet ds = new DataSet();

        dt.Load(dr);
        ds.Tables.Add(dt);
        dr.Close();
        Complete(ds);
    }

    private void Complete(DataSet ds) {
        string output = string.Empty;
        foreach (DataColumn c in ds.Tables[0].Columns) {
            output += c.ColumnName + "\t";
        }
        output += "\r\n";
        foreach (DataRow dr in ds.Tables[0].Rows) {
            foreach (object i in dr.ItemArray) {
                output += i.ToString() + "\t";
            }
            output += "\r\n";
        }
    }
}

}

Answer 1

Some points I noticed:

1. The callback method was called only after I removed the WAITFOR DELAY stmt.
2. There is no need to poll for result.IsCompleted, because the Callback method gets fired only after async processing is completed.
3. No need to explicitly set dr = null in the else part because by default it will be null.
4. You should handle InvalidOperationException and ArgumentException in the HandleCallback method.
5. In the handle callback whenever, the EndExecuteReader() was called I kept getting the exception "The asynchronous operation has already completed." So I was never able to get the result in dr.

If you are facing the issue listed in point no. 5, you can use the following alternate solution implemented by using asynchronous delegates rather than the built-in BeginExecuteReader() and EndExecuteReader(). In the solution below too, the control will be immediately returned to the next line after the delegate is invoked, just like it happens in the case of BeginExecuteReader().

Alternate Solution:

public partial class Form2 : Form
{
    public Form2()
    {
        InitializeComponent();
    }

    private delegate DataSet GetDSDelegate(string query);

    private void button1_Click(object sender, EventArgs e)
    {
        GetDSDelegate del = new GetDSDelegate(GetDataSetAsync);
        del.BeginInvoke(@"Select top 3 * from table1;", null, null);
    }

    private DataSet GetDataSetAsync(string query)
    {
        DataSet ds;
        using (SqlConnection conn = new SqlConnection(@"Data Source = mmmmm000011\sqlexpress; Initial Catalog = SOExamples; Integrated Security = SSPI; Asynchronous Processing = true;"))
        using (SqlCommand cmd = new SqlCommand(query, conn))
        {
            try
            {
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader();

                DataTable dt = new DataTable();
                ds = new DataSet();

                dt.Load(dr);
                ds.Tables.Add(dt);
                dr.Close();
                Complete(ds);
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                    conn.Close();
            }
        }
        MessageBox.Show("Done!!!");
        return ds;
    }

    private void Complete(DataSet ds)
    {
        ...
    }
}

Answer 2

I think the connection is being closed before the Reader could work...

using (SqlConnection conn = new SqlConnection(@"Data Source = bigapple; Initial Catalog = master; Integrated Security = SSPI; Asynchronous Processing = true;"))

Try changing it to...

SqlConnection conn = new SqlConnection(@"Data Source = bigapple; Initial Catalog = master; Integrated Security = SSPI; Asynchronous Processing = true;");
        conn.Open();
        SqlCommand cmd = new SqlCommand(@"WAITFOR DELAY '00:03'; Select top 3 * from sysobjects;", conn);
        IAsyncResult result = cmd.BeginExecuteReader(new AsyncCallback(HandleCallback), cmd, CommandBehavior.CloseConnection);

By the way this code waits for 3 minutes? Because to pause for 3 seconds shouldn't it be WAITFOR DELAY '0:0:3'?