Example #1
0
        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;
                }
            }
        }
Example #3
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);
        }
Example #4
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);
                }
            }
        }
Example #5
0
        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);
                }
            }
        }
Example #6
0
        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);
                }
            }
        }
Example #7
0
        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);
                }
            }
        }
Example #8
0
        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);
            }
        }
Example #9
0
        /// <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);

            }
        }
Example #10
0
        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 *****/
        }
Example #11
0
        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);
        }
Example #12
0
 /// <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;
 }
Example #13
0
 /// <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;
 }
Example #14
0
 /// <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;
 }