public bool insertContract(Contract contract) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.Connection = con; con.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spInsertContract"; cmd.Parameters.AddWithValue("@id", contract.ContractID); cmd.Parameters.AddWithValue("@Title", contract.Title); cmd.Parameters.AddWithValue("@ContractDate", contract.ContractDate); cmd.Parameters.AddWithValue("@Value", contract.TotalValue); cmd.Parameters.AddWithValue("@StatusID", contract.StatusID); cmd.Parameters.AddWithValue("@ContractTypeId", contract.ContracTypeID); cmd.Parameters.AddWithValue("@ActiveFlag", 'A'); cmd.Parameters.AddWithValue("@SignCount", contract.SignCount); IAsyncResult result = cmd.BeginExecuteNonQuery(); cmd.EndExecuteNonQuery(result); con.Close(); return(result.IsCompleted); } } }
public int RunCommandCount(string commandText) { using (SqlConnection connection = new SqlConnection(connectionString)) { try { SqlCommand command = new SqlCommand(commandText, connection); connection.Open(); IAsyncResult result = command.BeginExecuteNonQuery(); return command.EndExecuteNonQuery(result); } catch (InvalidOperationException ex) { Console.WriteLine("Error: {0}", ex.Message); return 0; } catch (Exception ex) { Console.WriteLine("Error: {0}", ex.Message); return 0; } } }
/// <summary> /// 依SQL指令執行 /// </summary> /// <param name="sSQL"></param> /// <returns></returns> public int ExecCmdAsync(string sSQL) { int RetV = 0; try { CheckDBConnection(); if (_bHasTrans) { System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(sSQL, DbConn, _trans); IAsyncResult cres = Cmd.BeginExecuteNonQuery(null, null); RetV = Cmd.EndExecuteNonQuery(cres); } else { DbConn.Open(); System.Data.SqlClient.SqlCommand Cmd = new System.Data.SqlClient.SqlCommand(sSQL, DbConn); //Cmd.CommandTimeout = 1500; //Cmd.EndExecuteNonQuery(); IAsyncResult cres = Cmd.BeginExecuteNonQuery(null, null); RetV = Cmd.EndExecuteNonQuery(cres); DbConn.Close(); } } catch (Exception ex) { string strMsg = "SQL指令執行失敗:" + sSQL + "\r\n" + ex.Message; throw new Exception(strMsg); } return(RetV); }
private static void RunCommandAsynchronously( string commandText, string connectionString) { using (SqlConnection connection = new SqlConnection(connectionString)) { try { int count = 0; SqlCommand command = new SqlCommand(commandText, connection); connection.Open(); IAsyncResult result = command.BeginExecuteNonQuery(); while (!result.IsCompleted) { Console.WriteLine( "Waiting ({0})", count++); // Wait for 1/10 second, so the counter // doesn't consume all available // resources on the main thread. System.Threading.Thread.Sleep(100); } Console.WriteLine( "Command complete. Affected {0} rows.", command.EndExecuteNonQuery(result)); } catch (SqlException ex) { Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message); } catch (InvalidOperationException ex) { Console.WriteLine("Error: {0}", ex.Message); } catch (Exception ex) { Console.WriteLine("Error: {0}", ex.Message); } } }
public bool updateContractStatus(int id) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.Connection = con; con.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spUpdateContractStatus"; cmd.Parameters.AddWithValue("@id", id); IAsyncResult result = cmd.BeginExecuteNonQuery(); cmd.EndExecuteNonQuery(result); con.Close(); return(result.IsCompleted); } } }
public bool insertContractMember(ContractMember contractMember) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.Connection = con; con.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spInsertContractMember"; cmd.Parameters.AddWithValue("@CONTRACTID", contractMember.ContractID); cmd.Parameters.AddWithValue("@MEMBERID", contractMember.MemberID); cmd.Parameters.AddWithValue("@VALUE", contractMember.Value); IAsyncResult result = cmd.BeginExecuteNonQuery(); cmd.EndExecuteNonQuery(result); con.Close(); return(result.IsCompleted); } } }
public bool insertContractDate(ContractDate contractDate) { using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(text)) { using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand()) { cmd.Connection = con; con.Open(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "spInsertContractDate"; cmd.Parameters.AddWithValue("@ContractId", contractDate.ContractID); cmd.Parameters.AddWithValue("@No", contractDate.No); if (contractDate.RedeemDate == null) { cmd.Parameters.AddWithValue("@RedeemDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@RedeemDate", contractDate.RedeemDate); } if (contractDate.SignDate == null) { cmd.Parameters.AddWithValue("@SignDate", DBNull.Value); } else { cmd.Parameters.AddWithValue("@SignDate", contractDate.SignDate); } IAsyncResult result = cmd.BeginExecuteNonQuery(); cmd.EndExecuteNonQuery(result); con.Close(); return(result.IsCompleted); } } }
public static void ExcuteNonQueryPreparedStatement(SqlCommand command) { if (command == null) { return; } //Check for "'" in singular form causing a problem with the SQL Statment foreach (object sqlParameter in from object sqlParameter in ((command.Parameters)) from item in ((IEnumerable) ((SqlParameter) (sqlParameter)).Value).Cast<object>() .Where ( item => item.ToString() .Contains("'")) select sqlParameter) { } command.StatementCompleted += (SMECustContactCon_StatementCompleted); try { IAsyncResult result = command.BeginExecuteNonQuery(); while (!result.IsCompleted) { Thread.Sleep(100); } if (command.Connection != null && command.Connection.State == ConnectionState.Closed) { return; } command.EndExecuteNonQuery(result); } catch (Exception ex) { MessageDialog.Show ( "Error retrieved updating \r\n" + ex.InnerException, "Error", MessageDialog.MessageBoxButtons.Ok, MessageDialog.MessageBoxIcon.Error, "Please Check You Credentials Are Valid" + "\r\n" + ex.StackTrace); } }
/// <summary> /// 数据库数据修改操作(插入、更新、删除等操作) /// 直接调用,自动处理connection相关状态 /// </summary> /// <param name="cmdText"></param> /// <param name="connection"></param> public static void ReviseDataToDataBase(string cmdText,SqlConnection connection) { SqlCommand cmd=new SqlCommand(cmdText,connection); if(connection.State==ConnectionState.Closed) connection.Open();//将与数据库的连接打开 try { IAsyncResult result= cmd.BeginExecuteNonQuery();//异步执行sql语句 //等待操作异步操作完成 while (true) { if (result.IsCompleted) { cmd.EndExecuteNonQuery(result); //结束异步BeginExecuteNonQuery } //待拓展 else { } } } /////////异常处理///////// catch (SqlException ex) { Debug.WriteLine("Error ({0}): {1}", ex.Number, ex.Message); } catch (InvalidOperationException ex) { Debug.WriteLine("Error: {0}", ex.Message); } catch (Exception ex) { // You might want to pass these errors // back out to the caller. Debug.WriteLine("Error: {0}", ex.Message); } }
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 *****/ }
public IAsyncResult ExecuteNonQuery(string query) { SqlCommand sqlCommand = new SqlCommand(query); SqlConnection connection = this.Connection; sqlCommand.Connection = connection; AsyncCallback callback = ((IAsyncResult result) => { try { sqlCommand.EndExecuteNonQuery(result); connection.Close(); } catch (SqlException e) { throw e; } catch (Exception e) { throw e; } finally { if (connection != null) { if(connection.State == ConnectionState.Open) connection.Close(); connection.Dispose(); } if (sqlCommand != null) { sqlCommand.Dispose(); } } }); connection.Open(); return sqlCommand.BeginExecuteNonQuery(callback, sqlCommand); }
/// <summary> /// 对连接异步执行 Transact-SQL 语句并返回受影响的行数。 /// </summary> /// <remarks> /// 示例: /// int result = ExecuteNonQueryAsync(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">有效的事务对象</param> /// <param name="commandType">获取或设置一个值,该值指示如何解释 CommandText 属性</param> /// <param name="commandText">获取或设置要对数据源执行的 Transact-SQL 语句或存储过程</param> /// <param name="callback">异步期间需要调用的Callback函数</param> /// <param name="commandParameters">用来执行命令的参数数组</param> /// <param name="commandTimeout">设定Command执行时间,秒,大于0有效</param> /// <returns>执行命令后受影响的行数</returns> public static int ExecuteNonQueryAsync(SqlTransaction transaction,CommandType commandType,string commandText,AsyncCallback callback,int commandTimeout,params SqlParameter[] commandParameters) { if(transaction == null){ throw new ArgumentNullException("transaction"); } if(transaction != null && transaction.Connection == null){ throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.","transaction"); } SqlCommand cmd = new SqlCommand(); bool mustCloseConnection; PrepareCommand(cmd,transaction.Connection,transaction,commandType,commandText,commandParameters,out mustCloseConnection); if(commandTimeout > 0){ cmd.CommandTimeout = commandTimeout; } IAsyncResult asyncResult = cmd.BeginExecuteNonQuery(null,null); if(callback != null){ callback(asyncResult); } int result = cmd.EndExecuteNonQuery(asyncResult); cmd.Parameters.Clear(); return result; }
/// <summary> /// 对连接异步执行 Transact-SQL 语句并返回受影响的行数。 /// </summary> /// <remarks> /// 示例: /// int result = ExecuteNonQueryAsync(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">有效的数据库连接对象</param> /// <param name="commandType">获取或设置一个值,该值指示如何解释 CommandText 属性</param> /// <param name="commandText">获取或设置要对数据源执行的 Transact-SQL 语句或存储过程</param> /// <param name="commandTimeout">设定Command执行时间,秒,大于0有效</param> /// <param name="callback">异步期间需要调用的Callback函数</param> /// <param name="commandParameters">用来执行命令的参数数组</param> /// <returns>执行命令后受影响的行数</returns> public static int ExecuteNonQueryAsync(SqlConnection connection,CommandType commandType,string commandText,AsyncCallback callback,int commandTimeout,params SqlParameter[] commandParameters) { if(connection == null){ throw new ArgumentNullException("connection"); } SqlCommand cmd = new SqlCommand(); bool mustCloseConnection; PrepareCommand(cmd,connection,null,commandType,commandText,commandParameters,out mustCloseConnection); if(commandTimeout > 0){ cmd.CommandTimeout = commandTimeout; } IAsyncResult asyncResult = cmd.BeginExecuteNonQuery(null,null); if(callback != null){ callback(asyncResult); } int result = cmd.EndExecuteNonQuery(asyncResult); cmd.Parameters.Clear(); if(mustCloseConnection){ connection.Close(); } return result; }
/// <summary> /// 对连接异步执行 Transact-SQL 语句并返回受影响的行数。 /// </summary> /// <param name="connectionString">有效的数据库连接字符串</param> /// <param name="spName">储存过程名称</param> /// <param name="callback">异步期间需要调用的Callback函数</param> /// <param name="commandTimeout">命令执行的超时时间(单位:秒,不允许传入0)</param> /// <param name="commandParameters">用来执行命令的参数数组</param> /// <returns>执行命令后受影响的行数</returns> /// <remarks> /// 当对上下文连接(要用连接字符串中的“context connection=true”打开的 SqlConnection)执行命令时,CommandTimeout 将不起作用。 /// 此属性是在执行命令或处理结果期间所有网络读取的累积超时。 在返回第一行之后,超时仍然可能发生,但只包括网络读取时间,而不包括用户处理时间。 /// 示例: /// int result = ExecuteNonQueryAsync(conn, "PublishOrders", 100, new SqlParameter("@prodid", 24)); /// </remarks> public static int ExecuteNonQueryAsync(string connectionString,string spName,AsyncCallback callback,int commandTimeout,params SqlParameter[] commandParameters) { if(string.IsNullOrEmpty(connectionString)){ throw new ArgumentNullException("connectionString"); } int result2; using (SqlConnection connection = new SqlConnection(connectionString)){ connection.Open(); SqlCommand cmd = new SqlCommand(); bool mustCloseConnection; PrepareCommand(cmd,connection,null,CommandType.StoredProcedure,spName,commandParameters,out mustCloseConnection); if(commandTimeout > 0){ cmd.CommandTimeout = commandTimeout; } IAsyncResult asyncResult = cmd.BeginExecuteNonQuery(null,null); if(callback != null){ callback(asyncResult); } int result = cmd.EndExecuteNonQuery(asyncResult); cmd.Parameters.Clear(); if(mustCloseConnection){ connection.Close(); } result2 = result; } return result2; }