c# - sql异步查询问题

Question

那么，为什么这从来没有进入回调函数呢？

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

我注意到的几点:

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.

如果您遇到第 1 点中列出的问题。 5，您可以使用以下通过使用异步委托(delegate)而不是内置的 BeginExecuteReader() 和 EndExecuteReader() 实现的替代解决方案。在下面的解决方案中，在调用委托(delegate)后控件将立即返回到下一行，就像在 BeginExecuteReader() 的情况下发生的那样。

替代解决方案:

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)
    {
        ...
    }
}