C# SqlCommand.EndExecuteNonQuery方法代码示例

        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;
                }
            }
        }

        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 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 = '[email protected]'", 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;
 }