使用异步回调方式查询数据

异步方式

在.NET 1.1中,SqlCommand类提供的ExeuteReader()方法用于将CommandText发送到Connection并生成一个SqlDataReader,该方法是一个同步方法,也就是说,在该方法未完成之前,工作线程一直处于阻塞状态,程序不能做其他工作;

在.NET 2.0中,微软在SqlCommand类中提供了BeginExecuteReader()和EndExecuteReader()公共方法实现异步CommandText应答,因此它能够实现在执行命令的过程中允许程序完成其他工作,在网络数据库查询中,这种异步调用方式将使程序在查询大数据量记录时变得更加灵活.

当使用BeginExecuteReader方法后,必须调用EndExecuteReader 方法完成操作并检索命令返回的SqlDataReader.如果命令尚未执行完就调用EndExecuteReader,会导致SqlCommand对象在命令执行完之前一直被阻止.通常,开发人员必须使用BeginExecuteReader方法返回的IasyncResult接口的IsCompleted属性进行轮询,以确认命令是否完成,或者使用返回的IasyncResult借口的AsyncWaitHandle属性,等待一个或多个命令完成.

例如: 注意: 在连接字符串里一定要指定Asynchronous Processing=true;

SqlConnection sconn = new SqlConnection("Server=.;Database=NorthWind;Integrated Security=SSPI;Asynchronous Processing=true");             SqlCommand cmd = new SqlCommand("select * from Orders",sconn);             try             {                 sconn.Open();                 IAsyncResult result = cmd.BeginExecuteReader();                 while (!result.IsCompleted)                 { }                 SqlDataReader dateReader = cmd.EndExecuteReader(result);                 DataTable dt = new DataTable();                 dt.Load(dateReader);                 this.dataGridView1.DataSource = dt;             }             finally             {                 sconn.Close();                 sconn.Dispose();             }

 

回调方式

在SqlCommand类的BeginExecuteReader公共方法的重载形式中,以下重载形式能够实现在提供回调过程和状态信息的情况下，启动此SqlCommand描述的T-SQL语句或存储过程的异步执行,并从服务器中检索一个或多个结果集.该重载的形式的声明如下

public IAsyncResult BeginExcuteReader         {                         AsyncCallback callback, //表示命令执行完成时调用的AsyncCallback委托，传递空引用以指示不需要回调             Object StateChangeEventArgs//表示传递到回调过程的用户定义的状态对象。使用AsyncState属性从回调过程内检索此对象         }

在该重载形式中,.可以从委托过程内或者从应用程序内的其他任何位置调用EndExecuteReader方法;此外,还可以在stateObject参数中传递任何对象,并前回调过程可以使用AsyncState属性检索该信息.

例如:

using System.Diagnostics;

using System.Threading;

private SqlConnection CallBackConn;         private Stopwatch CallBackWatch = new Stopwatch();         private delegate void DisplayTimeInfoDelegate(string text);         private delegate void DisplayDataGridViewDelegate(DataTable table);         private delegate void DisplayInfoDelegate(string text);         private void DisplayTimeResults(string text)         {             MessageBox.Show(text);         }           private void DisplayDateResults(DataTable table)         {             this.dataGridView1.DataSource = table;         }           private void HandleCallBack(IAsyncResult result)         {             try             {                 SqlCommand cmd = (SqlCommand)result.AsyncState;                 SqlDataReader dataReader = cmd.EndExecuteReader(result);                 CallBackWatch.Stop();                 string CallBackTime = "所用时间：" + CallBackWatch.ElapsedMilliseconds.ToString() + "毫秒";                 DataTable table = new DataTable();                 table.Load(dataReader);                 DisplayTimeInfoDelegate timeDelegate = new DisplayTimeInfoDelegate(DisplayTimeResults);                 this.Invoke(timeDelegate, CallBackTime);                 DisplayDataGridViewDelegate DataDelegate = new DisplayDataGridViewDelegate(DisplayDateResults);                 this.Invoke(DataDelegate, table);             }             catch (Exception MyEx)             {                 this.Invoke(new DisplayInfoDelegate(DisplayTimeResults), String.Format(MyEx.Message));             }             finally             {                 CallBackConn.Close();             }         }           private void button1_Click(object sender, EventArgs e)         {             Application.DoEvents();             CallBackConn = new SqlConnection("Server=.;Database=NorthWind;Integrated Security=SSPI;Asynchronous Processing=true");             SqlCommand cmd = new SqlCommand(" Select * From Orders Order By OrderID DESC", CallBackConn);               try             {                 CallBackConn.Open();                 CallBackWatch.Start();                 AsyncCallback callBack = new AsyncCallback(HandleCallBack);                 cmd.BeginExecuteReader(callBack, cmd);               }            catch             {                 CallBackConn.Close();             }         }