执行Command对象命令时,需要等待命令完成才能执行其他操作。
比如,执行ExcuteNonQuery()方法,应用程序将会保持阻塞,直到数据操作成功完成或者异常终止以及连接超时。
异步执行的思想是,在执行命令操作时,无需等待命令操作完成,可以并发的处理其他操作。
BeginExecuteNonQuery和EndExcuteNonQuery就是一对典型的为异步操作服务的方法。
BeginExecuteNonQuery方法返回System.IAsyncResult接口对象。我们可以根据IAsyncResult的IsCompleted属性来轮询(检测)命令是否执行完成。
案例代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace sqlCommand
{
class Program
{
static void Main(string[] args)
{
sqlCommandExe();
}
private static SqlConnectionStringBuilder getConnDbStr()
{
//1.构造连接字符串
SqlConnectionStringBuilder connStr = new SqlConnectionStringBuilder();
connStr.DataSource = "192.168.1.20";
connStr.InitialCatalog = "mshDB_Debug";
connStr.UserID = "developer";
connStr.Password = "developer";
connStr.Pooling = true;
connStr.MaxPoolSize = 40000;
connStr.MinPoolSize = 1;
connStr.AsynchronousProcessing = true; //显示说明异步操作
return connStr;
}
private static StringBuilder getExeSqlStr()
{
//2.构造查询语句字符串
StringBuilder m_strSQL = new StringBuilder();
//比如,插入50万个测试用户
for (int i = 1; i <= 500000; ++i)
{
m_strSQL.Append("insert into tb_user(u_name,u_pwd,u_sex,u_age,u_class,u_address,u_phone) ");
m_strSQL.Append("values('");
string name = "测试客户" + i.ToString();
m_strSQL.Append(name);
m_strSQL.Append("','123456','女',20,'406','浙江省平湖市实验小学','13967310'); ");
}
return m_strSQL;
}
private static void sqlCommandExe()
{
//3.执行一个SQL语句,按以下步骤进行
//3.1生成一个数据库连接对象
SqlConnectionStringBuilder strConn = getConnDbStr();//获取连接字符串
SqlConnection conn = new SqlConnection(strConn.ConnectionString);
//3.2生成SqlCommand对象
StringBuilder strSQL = getExeSqlStr();
SqlCommand cmd = new SqlCommand(strSQL.ToString(), conn);
cmd.CommandType = CommandType.Text;
//3.3 开始异步执行
try
{
double time = 0;
conn.Open(); //打开数据库连接
Console.WriteLine("Data is inserted beigin...\nTotal coast {0}s", time * 0.001);
IAsyncResult pending = cmd.BeginExecuteNonQuery(); //开始执行异步操作
//检查异步处理状态
while (pending.IsCompleted == false)
{
System.Threading.Thread.Sleep(1);
time++;
Console.WriteLine("{0}s", time * 0.001);
}
if (pending.IsCompleted == true)
{
Console.WriteLine("Data is inserted completely...\nTotal coast {0}s", time * 0.001);
}
cmd.EndExecuteNonQuery(pending);//结束异步操作
}
catch (Exception e)
{
//
Console.WriteLine(e.Message);
}
finally
{
conn.Close();
conn.Dispose();
}
Console.Read();
}
}
}
开启SQLServer 资源调控器