C# OpenCbsCommand.ExecuteNonQuery方法代码示例

 public void DeleteAllProvisioningRules(SqlTransaction sqlTransac)
 {
     const string sqlText = "DELETE FROM ProvisioningRules";
     using (OpenCbsCommand delete = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
     {
         delete.ExecuteNonQuery();
     }
 }

 public void Delete(SqlTransaction pSqlTransaction)
 {
     const string sqltext = "DELETE FROM LoanScale";
     using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction))
     {
         delete.ExecuteNonQuery();
     }
 }

        public void InsertLoanScale(LoanScaleRate pLoanScaleRate, SqlTransaction pSqlTransaction)
        {
            const string sqlText = @"INSERT INTO LoanScale(id,ScaleMin, ScaleMax) VALUES(@number,@Min, @Max)";

            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlTransaction.Connection, pSqlTransaction))
            {
                SetLoanScale(insert, pLoanScaleRate);
                insert.ExecuteNonQuery();
            }
        }

 public void Add(DateTime pDate, double pRate, Currency pCurrency)
 {
     const string q = "INSERT INTO [ExchangeRates] ([exchange_date], [exchange_rate],[currency_id]) " +
                      "VALUES(@date,@rate,@currency)";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         SetExchangeRate(c, pDate.Date, pRate,pCurrency);
         c.ExecuteNonQuery();
     }
 }

 public void DeleteStandardBooking(int id)
 {
     const string sqlText = @"DELETE FROM StandardBookings WHERE Id = @Id";
     using (SqlConnection conn = GetConnection())
     {
         using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
         {
             insertIntoTable.AddParam("@Id", id);
             insertIntoTable.ExecuteNonQuery();
         }
     }
 }

 public void CreateStandardBooking(Booking booking)
 {
     const string sqlText = @"INSERT INTO StandardBookings([Name], debit_account_id, credit_account_id)
                              VALUES (@name, @debit_account_id, @credit_account_id)";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand insertIntoTable = new OpenCbsCommand(sqlText, conn))
         {
             insertIntoTable.AddParam("@name", booking.Name);
             insertIntoTable.AddParam("@debit_account_id", booking.DebitAccount.Id);
             insertIntoTable.AddParam("@credit_account_id", booking.CreditAccount.Id);
             insertIntoTable.ExecuteNonQuery();
         }
 }

        public static void AddDatabaseToAccounts(string pAccountName, string pDatabaseName, string pLogin, string pPassword, SqlConnection pSqlConnection)
        {
            const string sqlText = @"INSERT INTO [Accounts].[dbo].[SqlAccounts]
                                   ([account_name],[database_name],[user_name],[password],[active])
                                    VALUES (@accountName, @databaseName, @login, @password,1)";

            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, pSqlConnection))
            {
                insert.AddParam("@accountName",  pAccountName);
                insert.AddParam("@databaseName",  pDatabaseName);
                insert.AddParam("@login",  pLogin);
                insert.AddParam("@password", pPassword);

                insert.ExecuteNonQuery();
            }
        }

 public void AddCreditInsuranceEvent(CreditInsuranceEvent pEvent, SqlTransaction pSqlTransac)
 {
     const string q = @"
                 INSERT INTO [dbo].[CreditInsuranceEvents]
                ([id]
                ,[commission]
                 ,[principal])
                  VALUES
                (@id
                ,@commission
                ,@principal)";
     using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
     {
         SetCreditInsuranceEvent(pEvent, c);
         c.ExecuteNonQuery();
     }
 }

        public void AddParameter(DictionaryEntry entry)
        {
            ApplicationSettings.GetInstance(_user.Md5).AddParameter(entry.Key.ToString(), entry.Value);

            string sqlText = "INSERT INTO [GeneralParameters]([key], [value])" +
                " VALUES(@name,@value)";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(sqlText, conn))
            {
                c.AddParam("@name", entry.Key.ToString());

                if (entry.Value != null)
                    c.AddParam("@value", entry.Value.ToString());
                else
                    c.AddParam("@value", null);

                c.ExecuteNonQuery();
            }
        }

        public static void ShrinkDatabase(string pDatabaseName, SqlConnection pSqlConnection)
        {
            string database = TechnicalSettings.DatabaseName;
            string sql1 = String.Format("ALTER DATABASE {0} SET RECOVERY SIMPLE", database);
            string sql2 = String.Format("ALTER DATABASE {0} SET AUTO_SHRINK ON", database);

            try
            {
                OpenCbsCommand cmd = new OpenCbsCommand(sql1, pSqlConnection);
                cmd.ExecuteNonQuery();
                cmd = new OpenCbsCommand(sql2, pSqlConnection);
                cmd.ExecuteNonQuery();

                string sql = String.Format("DBCC SHRINKDATABASE ({0})", database);
                cmd = new OpenCbsCommand(sql, pSqlConnection);
                cmd.ExecuteNonQuery();
            }
            catch (Exception) { }
        }

        public static void DeleteDatabase(string pDatabaseName, SqlConnection pSqlConnection)
        {
            string sqlText = string.Format("ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE", pDatabaseName);
            OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlConnection);
            cmd.ExecuteNonQuery();

            sqlText = "DROP DATABASE " + pDatabaseName;
            cmd = new OpenCbsCommand(sqlText, pSqlConnection);
            cmd.ExecuteNonQuery();
        }

 public void UpdateSavingContractCode(int savingsId, string code, SqlTransaction pSqlTransac)
 {
     const string sqlText = @"UPDATE SavingContracts SET code = @code WHERE id = @id";
     using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
     {
         cmd.AddParam("@code", code);
         cmd.AddParam("@id", savingsId);
         cmd.ExecuteNonQuery();
     }
 }

        public void UpdateNextMaturityForSavingBook(int savingId, DateTime? nextMaturity)
        {
            const string sqlText =
                            @"UPDATE SavingBookContracts
                             SET next_maturity = @nextMaturity
                             WHERE id = @id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, conn))
            {
                cmd.AddParam("@nextMaturity", nextMaturity);
                cmd.AddParam("@id", savingId);
                cmd.ExecuteNonQuery();
            }
        }

        public void AddCollateralPropertyValue(ContractCollateral contractCollateral, CollateralPropertyValue propertyValue, SqlTransaction pSqlTransac)
        {
            string q = @"INSERT INTO [CollateralPropertyValues] ([contract_collateral_id], [property_id], [value])
                                       VALUES (@contract_collateral_id, @property_id, @value)";

            using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
            {
                c.AddParam("@contract_collateral_id", contractCollateral.Id);
                c.AddParam("@property_id", propertyValue.Property.Id);
                c.AddParam("@value", propertyValue.Value);
                c.ExecuteNonQuery();
            }
        }

        private void _SetLoanShareAmount(Loan pLoan, SqlTransaction pSqlTransac)
        {
            // Get group id
            int group_id;
            const string q = @"SELECT p.tiers_id
                FROM dbo.Projects AS p
                LEFT JOIN dbo.Contracts AS c ON c.project_id = p.id
                WHERE c.id = @contract_id";
            using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
            {
                c.AddParam("@contract_id", pLoan.Id);
                group_id = Convert.ToInt32(c.ExecuteScalar());
            }

            const string sqlText = @"INSERT INTO LoanShareAmounts (person_id, group_id, contract_id, amount)
                                     VALUES (@person_id, @group_id, @contract_id, @amount)";

            using(OpenCbsCommand c = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
            {
                foreach (LoanShare ls in pLoan.LoanShares)
                {
                    c.ResetParams();
                    c.AddParam("@person_id", ls.PersonId);
                    c.AddParam("@group_id", group_id);
                    c.AddParam("@contract_id", pLoan.Id);
                    c.AddParam("@amount", ls.Amount.Value);

                    c.ExecuteNonQuery();
                }
            }
        }