public AsyncConnect()
{
SqlConnection connection = new SqlConnection();
SqlConnectionStringBuilder connectionBuilder = new SqlConnectionStringBuilder();
connectionBuilder.DataSource = @"(local)\SQLEXPRESS";
connectionBuilder.IntegratedSecurity = true;
connectionBuilder.AsynchronousProcessing = true;
connectionBuilder.InitialCatalog = "AutoLot";
connection.ConnectionString = connectionBuilder.ConnectionString;
connection.Open();
string query = "WaitFor Delay '00:00:05';Select * From Inventory";
SqlCommand command = new SqlCommand(query, connection);
IAsyncResult connectionResult = command.BeginExecuteReader(CommandBehavior.CloseConnection);
while (!connectionResult.IsCompleted)
{
Console.WriteLine("Working on Main thread...");
Thread.Sleep(1000);
}
Console.WriteLine();
SqlDataReader reader = command.EndExecuteReader(connectionResult);
Console.WriteLine(DbUtils.ReaderToString(reader));
}
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("Data Source=localhost\\sqlexpress;Initial Catalog=csharp;Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand("select Id, Nome from Categoria", conn);
SqlDataReader r = cmd.EndExecuteReader();
while (r.Read()) {
Console.WriteLine("Categoria: {0}", r["Nome"].ToString());
}
conn.Close();
}
private void btnExecute_Click(object sender, EventArgs e)
{
string connString = "Async=true;SERVER=(local);DATABASE=northwind;UID=sa;";
using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand("SELECT lastname,firstname FROM employees", conn);
conn.Open();
IAsyncResult iar = cmd.BeginExecuteReader();
// Do something here
SqlDataReader reader = (SqlDataReader)cmd.EndExecuteReader(iar);
ProcessData(reader);
}
}
/// <summary>
/// 执行查询语句(SqlDataReader)
/// 调用前connection需要处于Open状态
/// 调用得到SqlDataReader，使用后需要将connection关闭
/// </summary>
/// <param name="cmdText"></param>
/// <param name="connection"></param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader GetSqlDataReader(string cmdText, SqlConnection connection)
{
//判断调用本方法前，连接对象connection是否已经打开
if (connection.State == ConnectionState.Closed)
{
connection.Open();//自动为其打开
}
try
{
SqlCommand cmd = new SqlCommand(cmdText, connection);
IAsyncResult result = cmd.BeginExecuteReader(); //异步访问数据库
//使用异步访问，便后期拓展
//等待BeginExecuteReader操作完
while (true)
{
if (result.IsCompleted)
{
SqlDataReader reader = cmd.EndExecuteReader(result); //将异步结果转换为SqlDataReader
return reader;
}
else
{
Thread.Sleep(500);//减少资源占用率
}
}
}
/////////异常处理/////////
catch (SqlException ex)
{
Debug.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
return null;
}
catch (InvalidOperationException ex)
{
Debug.WriteLine("Error: {0}", ex.Message);
return null;
}
catch (Exception ex)
{
// You might want to pass these errors
// back out to the caller.
Debug.WriteLine("Error: {0}", ex.Message);
return null;
}
}
/// <summary>
/// Save and get import file header ids
/// </summary>
public static List<ImportFileHeaderIdInstance> SaveImportFileHeaders(int ImportFileId, DataTable objTable, SqlConnection objConnection, SqlTransaction objTransaction)
{
SqlCommand objCommand = null;
SqlDataReader objReader = null;
IAsyncResult objResult = null;
List<ImportFileHeaderIdInstance> objList = new List<ImportFileHeaderIdInstance>();
ImportFileHeaderIdInstance objFileHeader = null;
foreach (DataRow Item in objTable.Rows)
{
objCommand = null;
objCommand = new SqlCommand("bvSaveImportFileHeaders_sp", objConnection, objTransaction);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@p_import_file_id", SqlDbType.Int).Value = Item["imported_file_id"];
objCommand.Parameters.Add("@p_column_order", SqlDbType.Int).Value = Item["column_order"];
objCommand.Parameters.Add("@p_column_name", SqlDbType.NVarChar).Value = Item["column_name"];
objReader = null;
objResult = null;
objResult = objCommand.BeginExecuteReader(CommandBehavior.SingleRow);
objReader = objCommand.EndExecuteReader(objResult);
objReader.Read();
objFileHeader = null;
objFileHeader = new ImportFileHeaderIdInstance();
objFileHeader.id = (int) objReader[0];
objFileHeader.column_name = Item["column_name"].ToString();
objReader.Close();
objList.Add(objFileHeader);
}
return objList;
}
/// <summary>
/// Save import file record
/// </summary>
public static int SaveImportFile(string SourceFileName,string ListName, int CustomerId, int CampaignId, int CountryId, SqlConnection objConnection, SqlTransaction objTransaction)
{
SqlCommand objCommand = new SqlCommand("bvSaveImportFile_sp", objConnection, objTransaction);
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.Parameters.Add("@p_source_file_name", SqlDbType.NVarChar).Value = Path.GetFileName(SourceFileName);
objCommand.Parameters.Add("@p_list_name", SqlDbType.NVarChar).Value = ListName;
objCommand.Parameters.Add("@p_customer_id", SqlDbType.Int).Value = CustomerId;
objCommand.Parameters.Add("@p_campaign_id", SqlDbType.Int).Value = CampaignId;
objCommand.Parameters.Add("@p_country_id", SqlDbType.Int).Value = CountryId;
objCommand.Parameters.Add("@p_created_by_id", SqlDbType.Int).Value = UserSession.CurrentUser.UserId;
IAsyncResult objResult = objCommand.BeginExecuteReader(CommandBehavior.SingleRow);
SqlDataReader objReader = objCommand.EndExecuteReader(objResult);
objReader.Read();
int ImportFileId = Convert.ToInt32(objReader[0]);
objReader.Close();
return ImportFileId;
}
private static void SampleAsyncMethods()
{
IAsyncResult asyncResult;
/***** SQL Connection *****/
// NOTE: "Async=true" setting required for asynchronous operations.
using (SqlConnection connection = new SqlConnection(@"Async=true;Server=SERVER;Database=DATABASE;Integrated Security=true"))
{
connection.Open();
using (SqlCommand cmd = new SqlCommand("SELECT UserId, Name, LastLogIn FROM Users WHERE Email = '*****@*****.**'", connection))
{
asyncResult = cmd.BeginExecuteReader();
// ... query executes asynchronously in background ...
using (IDataReader reader = cmd.EndExecuteReader(asyncResult))
{
// WARNING: The DbAsyncResult object returned by BeginExecuteReader always creates a ManualResetEvent, but
// never closes it; after calling EndExecuteReader, the AsyncWaitHandle property is still valid, so we close it explicitly.
asyncResult.AsyncWaitHandle.Close();
while (reader.Read())
{
// do stuff
}
}
}
using (SqlCommand cmd = new SqlCommand("UPDATE Users SET LastLogIn = GETUTCDATE() WHERE UserId = 1", connection))
{
asyncResult = cmd.BeginExecuteNonQuery();
// ... query executes asynchronously in background ...
int rowsAffected = cmd.EndExecuteNonQuery(asyncResult);
// WARNING: The DbAsyncResult object returned by BeginExecuteNonQuery always creates a ManualResetEvent, but
// never closes it; after calling EndExecuteReader, the AsyncWaitHandle property is still valid, so we close it explicitly.
asyncResult.AsyncWaitHandle.Close();
}
}
/***** File Operations *****/
// NOTE: FileOptions.Asynchronous flag required for asynchronous operations.
using (Stream stream = new FileStream(@"C:\Temp\test.dat", FileMode.Open, FileAccess.Read, FileShare.ReadWrite, 4096,
FileOptions.Asynchronous))
{
byte[] buffer = new byte[65536];
asyncResult = stream.BeginRead(buffer, 0, buffer.Length, null, null);
// ... disk read executes asynchronously in background ...
int bytesRead = stream.EndRead(asyncResult);
}
/***** HTTP Operation *****/
// WARNING: DNS operations are synchronous, and will block!
WebRequest request = WebRequest.Create(new Uri(@"http://www.example.com/sample/page"));
request.Method = "POST";
request.ContentType = "application/x-www-form-urlencoded";
asyncResult = request.BeginGetRequestStream(null, null);
// ... connection to server opened in background ...
using (Stream stream = request.EndGetRequestStream(asyncResult))
{
byte[] bytes = Encoding.UTF8.GetBytes("Sample request");
asyncResult = stream.BeginWrite(bytes, 0, bytes.Length, null, null);
stream.EndWrite(asyncResult);
}
// WARNING: WebRequest will swallow any exceptions thrown from the AsyncCallback passed to BeginGetResponse.
asyncResult = request.BeginGetResponse(null, null);
// ... web request executes in background ...
using (WebResponse response = request.EndGetResponse(asyncResult))
using (Stream stream = response.GetResponseStream())
{
// read response from server
// WARNING: This code should also use asynchronous operations (BeginRead, EndRead); "Using synchronous calls
// in asynchronous callback methods can result in severe performance penalties." (MSDN)
}
/***** DNS hostname resolution *****/
// WARNING: Doesn't truly use async I/O, but simply queues the request to a ThreadPool thread.
asyncResult = Dns.BeginGetHostEntry("www.example.com", null, null);
// ... DNS lookup executes in background
IPHostEntry entry = Dns.EndGetHostEntry(asyncResult);
/***** Other: Sockets, Serial Ports, SslStream *****/
}
protected void QueryButton_Click(object sender, EventArgs e)
{
// You might want to move this code out to a separate class
// Using two connection strings to simulate distinct databases
string custID = CustomerList.SelectedValue.ToString();
using (SqlConnection conn1 = new SqlConnection(ConnString))
using (SqlConnection conn2 = new SqlConnection(ConnString))
{
// Fire the first command: get customer info
SqlCommand cmd1 = new SqlCommand(CustomerInfoCmd, conn1);
cmd1.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
conn1.Open();
IAsyncResult arCustomerInfo = cmd1.BeginExecuteReader();
// Fire the second command: get order info
SqlCommand cmd2 = new SqlCommand(CustomerOrderHistory, conn2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
conn2.Open();
IAsyncResult arOrdersInfo = cmd2.BeginExecuteReader();
// Prepare wait objects to sync up
WaitHandle[] handles = new WaitHandle[2];
handles[0] = arCustomerInfo.AsyncWaitHandle;
handles[1] = arOrdersInfo.AsyncWaitHandle;
SqlDataReader reader;
// Wait (no longer than 5 secs)
for (int i = 0; i < 2; i++)
{
StringBuilder builder = new StringBuilder();
int index = WaitHandle.WaitAny(handles, 5000, false);
if (index == WaitHandle.WaitTimeout)
throw new Exception("Timeout expired");
if (index == 0)
{
reader = cmd1.EndExecuteReader(arCustomerInfo);
// Only one record caught here
if (!reader.Read())
continue;
builder.AppendFormat("Company Name: {0}\n", reader["companyname"]);
builder.AppendFormat("Address: {0}\n", reader["address"]);
builder.AppendFormat("Country: {0}\n", reader["country"]);
Info.Text = builder.ToString();
reader.Close();
}
if (index == 1)
{
reader = cmd2.EndExecuteReader(arOrdersInfo);
// Only one record caught here
if (!reader.Read())
continue;
builder.AppendFormat("Product Name: {0}\n", reader["ProductName"]);
builder.AppendFormat("Total: {0}\n", reader["Total"]);
MInfo.Text = builder.ToString();
reader.Close();
}
}
}
}
public DataTable ExecuteDataTableTest(String procName, SqlParameter[] parms)
{
DataTable dataTable = null;
SqlDataReader reader1=null;
try
{
SqlCommand command = new SqlCommand(procName);
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
if (_sqlTransAction != null)
command.Transaction = _sqlTransAction;
if (parms != null)
{
for (int i = 0; i <= parms.Length - 1; i++)
{
command.Parameters.Add(parms[i]);
}
}
IAsyncResult result = command.BeginExecuteReader();
WaitHandle waitHandle = result.AsyncWaitHandle;
WaitHandle[] waitHandles = {waitHandle};
int index;
for (int countWaits = 0; countWaits <= 1; countWaits++)
{
// WaitAny waits for any of the processes to
// complete. The return value is either the index
// of the array element whose process just
// completed, or the WaitTimeout value.
index = WaitHandle.WaitAny(waitHandles,60000000, false);
// This example doesn't actually do anything with
// the data returned by the processes, but the
// code opens readers for each just to demonstrate
// the concept.
// Instead of using the returned data to fill the
// controls on the page, the example adds the time
// the process was completed to the corresponding
// text box.
switch (index)
{
case 0:
reader1 =
command.EndExecuteReader(result);
reader1.Close();
break;
}
}
//SqlDataAdapter sqlDatatAdapter = new SqlDataAdapter(command);
dataTable = new DataTable();
dataTable.Load(reader1);
//sqlDatatAdapter.Fill(dataTable);
}
catch
{
throw;
}
finally
{
}
return dataTable;
}
public SqlDataReader EndExecuteReader(IAsyncResult result, string procName, SqlParameter[] parms)
{
try
{
SqlCommand command = new SqlCommand(procName);
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
for (int i = 0; i <= parms.Length - 1; i++)
{
command.Parameters.Add(parms[i]);
}
}
return command.EndExecuteReader(result);
}
catch
{
throw;
}
}
private void QueryButton_Click(object sender, EventArgs e)
{
string custID = CustomerList.SelectedValue.ToString();
// You might want to move this code out to a separate class
// Using two connection strings to simulate distinct databases
// To make connection pooling work effectively, it is extremely
// important that connnection objects are returned to the pool
// as soon as possible. A connection object that goes out of scope
// is not closed and, therfore, not immediately returned.
// SqlConnection conn = new SqlConnection(ConnString);
// Try{
// Conn.Open();
// // Do something here
// }
// Catch{
// // Trap errors here
// }
// Finally{
// Conn.Close();
// }
// Or
// Using (SqlConnection conn1 = new SqlConnection(ConnString))
// {
// // Do something here
// // Trap errors here
// }
using (SqlConnection conn1 = new SqlConnection(ConnString))
using (SqlConnection conn2 = new SqlConnection(ConnString))
{
// Fire the first command: get customer info
SqlCommand cmd1 = new SqlCommand(CustomerInfoCmd, conn1);
cmd1.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
conn1.Open();
IAsyncResult arCustomerInfo = cmd1.BeginExecuteReader();
// Fire the second command: get order info
SqlCommand cmd2 = new SqlCommand(CustomerOrderHistory, conn2);
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.Parameters.Add("@customerid", SqlDbType.Char, 5).Value = custID;
conn2.Open();
IAsyncResult arOrdersInfo = cmd2.BeginExecuteReader();
// Prepare wait objects to sync up
WaitHandle[] handles = new WaitHandle[2];
handles[0] = arCustomerInfo.AsyncWaitHandle;
handles[1] = arOrdersInfo.AsyncWaitHandle;
SqlDataReader reader;
// Wait (no longer than 5 secs)
for (int i = 0; i < 2; i++)
{
StringBuilder builder = new StringBuilder();
int index = WaitHandle.WaitAny(handles, 5000, false);
if (index == WaitHandle.WaitTimeout)
throw new Exception("Timeout expired");
if (index == 0)
{
reader = cmd1.EndExecuteReader(arCustomerInfo);
// Only one record caught here
if (!reader.Read())
continue;
builder.AppendFormat("{0}\n", reader["companyname"]);
builder.AppendFormat("{0}\n", reader["address"]);
builder.AppendFormat("{0}\n", reader["country"]);
Info.Text = builder.ToString();
reader.Close();
}
if (index == 1)
{
reader = cmd2.EndExecuteReader(arOrdersInfo);
gridOrders.DataSource = reader; // Does not work here!
reader.Close();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd2);
da.Fill(ds, "Customers");
gridOrders.DataSource = ds.Tables["Customers"].DefaultView;
}
}
}
}
/// <summary>
/// 创建并准备数据执行命令对象，同时以适当的 CommandBehavior 属性调用 ExecuteReader 方法。
/// </summary>
/// <remarks>
/// 如果创建并打开数据库连接对象，当 DataReader 被关闭时必须关闭数据库连接。
/// 如果是由调用方提供数据库连接对象，不必进行任何关闭操作，由调用方进行管理。
/// </remarks>
/// <param name="connection">有效的数据库连接对象</param>
/// <param name="transaction">有效的事务对象，或者为 null</param>
/// <param name="commandType">获取或设置一个值，该值指示如何解释 CommandText 属性</param>
/// <param name="commandText">获取或设置要对数据源执行的 Transact-SQL 语句或存储过程</param>
/// <param name="commandParameters">用来执行命令的参数数组，如果不必提供参数数组可设置为 null</param>
/// <param name="connectionOwnership">指示调用方是否提供数据库连接，或者由数据访问帮助类创建</param>
/// <param name="callback">异步期间需要调用的Callback函数</param>
/// <param name="commandTimeout">设定Command执行时间，秒，大于0有效</param>
/// <returns>执行命令后返回包含结果的数据读取对象</returns>
private static SqlDataReader ExecuteReaderAsync(SqlConnection connection,SqlTransaction transaction,CommandType commandType,string commandText,AsyncCallback callback,int commandTimeout,IEnumerable<SqlParameter> commandParameters,SqlConnectionOwnership connectionOwnership)
{
if(connection == null){
throw new ArgumentNullException("connection");
}
bool mustCloseConnection = false;
SqlCommand cmd = new SqlCommand();
SqlDataReader result;
try{
PrepareCommand(cmd,connection,transaction,commandType,commandText,commandParameters,out mustCloseConnection);
if(commandTimeout > 0){
cmd.CommandTimeout = commandTimeout;
}
SqlDataReader dataReader;
if(connectionOwnership == SqlConnectionOwnership.External){
IAsyncResult asyncResult = cmd.BeginExecuteReader(null,null);
if(callback != null){
callback(asyncResult);
}
dataReader = cmd.EndExecuteReader(asyncResult);
}
else{
IAsyncResult asyncResult2 = cmd.BeginExecuteReader(null,null,CommandBehavior.CloseConnection);
if(callback != null){
callback(asyncResult2);
}
dataReader = cmd.EndExecuteReader(asyncResult2);
}
bool canClear = true;
foreach(SqlParameter commandParameter in cmd.Parameters){
if(commandParameter.Direction != ParameterDirection.Input){
canClear = false;
}
}
if(canClear){
cmd.Parameters.Clear();
}
result = dataReader;
}
catch{
if(mustCloseConnection){
connection.Close();
}
throw;
}
return result;
}