C# OpenCbsCommand类代码示例

 /// <summary>
 /// Method to add a TrancheEvent into database. We use the NullableTypes to make the correspondance between
 /// nullable int, decimal and double types in database and our own objects
 /// </summary>
 /// <param name="trancheEvent">TrancheEvent Object</param>
 /// <returns>The id of the Tranche Event which has been added</returns>
 public int Add(TrancheEvent trancheEvent)
 {
     const string sqlText = @"
         INSERT INTO [TrancheEvents]
                    ( [id]
                     ,[interest_rate]
                     ,[amount]
                     ,[maturity]
                     ,[start_date]
                     ,[applied_new_interest])
                     VALUES
                     (@Id,
                      @InterestRate,
                      @Amount,
                      @Maturity,
                      @StartDate,
                      @applied_new_interest)
         SELECT SCOPE_IDENTITY()";
     using (SqlConnection conn = GetConnection())
     using (var cmd = new OpenCbsCommand(sqlText, conn))
     {
         SetTrancheEvent(cmd, trancheEvent);
         return int.Parse(cmd.ExecuteScalar().ToString());
     }
 }

 public void Delete(SqlTransaction pSqlTransaction)
 {
     const string sqltext = "DELETE FROM LoanScale";
     using (OpenCbsCommand delete = new OpenCbsCommand(sqltext, pSqlTransaction.Connection, pSqlTransaction))
     {
         delete.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 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 int Add(Currency pCurrency, SqlTransaction t)
 {
     const string q = @"INSERT INTO [Currencies] ([name], [code], [is_pivot], [is_swapped],use_cents)
                             VALUES(@name, @code, @is_pivot, @is_swapped,@use_cents) SELECT SCOPE_IDENTITY()";
     using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
     {
         c.AddParam("@name", pCurrency.Name);
         c.AddParam("@code", pCurrency.Code);
         c.AddParam("@is_pivot", pCurrency.IsPivot);
         c.AddParam("@is_swapped", pCurrency.IsSwapped);
         c.AddParam("@use_cents", pCurrency.UseCents);
         return int.Parse(c.ExecuteScalar().ToString());
     }
 }

        public double GetMostRecentlyRate(DateTime pDate, Currency pCurrency)
        {
            const string q = @"SELECT TOP 1 exchange_rate
                                    FROM ExchangeRates
                                    WHERE exchange_date <= @date AND currency_id = @currency
                                    ORDER BY exchange_date DESC";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@currency",pCurrency.Id);
                c.AddParam("@date", pDate);
                return Convert.ToDouble(c.ExecuteScalar());
            }
        }

 public int GetContractQuantityByCurrencyId(int currencyId)
 {
     const string q = @"
                     SELECT COUNT(Credit.id) AS contract_quantity
                     FROM [dbo].[Currencies] AS Cur
                     INNER JOIN Packages AS Pack ON Pack.currency_id=Cur.id
                     INNER JOIN Credit ON Credit.package_id=Pack.id
                     WHERE [email protected]_id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@currency_id", currencyId);
         return (int)c.ExecuteScalar();
     }
 }

        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 void AddProvisioningRate(ProvisioningRate pR, SqlTransaction sqlTransac)
        {
            const string sqlText = @"INSERT INTO ProvisioningRules(
                                        id,
                                        number_of_days_min,
                                        number_of_days_max,
                                        provisioning_value)
                                    VALUES(
                                      @number,
                                      @numberOfDaysMin,
                                      @numberOfDaysMax,
                                      @provisioningPercentage)
                                   SELECT SCOPE_IDENTITY()";

            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
            {
                SetProvisioningRate(insert, pR);
                insert.ExecuteScalar();
            }
        }

        public void SelectLoanScales()
        {
            LoanScaleTable loanscaleTable = LoanScaleTable.GetInstance(_user);
            const string sqlText = @"SELECT id, ScaleMin, ScaleMax
                                     FROM LoanScale";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        if (reader.Empty) return;
                        while (reader.Read())
                        {
                            loanscaleTable.AddLoanScaleRate(GetLoanScale(reader));
                        }
                    }
                }
            }
        }