C# OpenCbsCommand.AddParam方法代码示例

 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 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 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 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 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 string GetObjectDropScript(string db, string name, SqlConnection conn)
        {
            string q = @"SELECT xtype
            FROM {0}..sysobjects
            WHERE name = @name";
            q = string.Format(q, db);

            OpenCbsCommand c = new OpenCbsCommand(q, conn);
            c.AddParam("@name", name);
            string xtype = c.ExecuteScalar().ToString().Trim();

            string xtypeObject = string.Empty;
            switch (xtype)
            {
                case "FN":
                case "IF":
                case "TF":
                    xtypeObject = "FUNCTION";
                    break;

                case "P":
                    xtypeObject = "PROCEDURE";
                    break;

                case "V":
                    xtypeObject = "VIEW";
                    break;

                default:
                    Debug.Fail("Cannot be here.");
                    break;
            }

            const string retval = @"IF  EXISTS (
                SELECT *
                FROM sys.objects
                WHERE object_id = OBJECT_ID(N'dbo.{0}') AND type = N'{1}'
            )
            DROP {2} [dbo].[{0}]";

            return string.Format(retval, name, xtype, xtypeObject);
        }

        public static string GetDatabaseNameForAccount(string pAccountName, SqlConnection pSqlConnection)
        {
            const string sqlText = @"SELECT database_name
                                     FROM Accounts.dbo.SqlAccounts
                                     WHERE account_name = @account";

            if (pSqlConnection.State == ConnectionState.Closed)
                pSqlConnection.Open();

            using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
            {
                select.AddParam("@account", pAccountName);
                return (string)select.ExecuteScalar();
            }
        }

 public void UpdateStatus(int savingId, OSavingsStatus status, DateTime? closedDate)
 {
     const string sqlText = @"UPDATE SavingContracts SET status = @status, closed_date = @closedDate WHERE id = @id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
     {
         update.AddParam("@status",  (int)status);
         update.AddParam("@closedDate", closedDate);
         update.AddParam("@id", savingId);
         update.ExecuteNonQuery();
     }
 }

        public void UpdateOverdraftStatus(int savingId, bool inOverdraft)
        {
            const string sqlText = @"UPDATE SavingBookContracts SET in_overdraft = @inOverdraft WHERE id = @id";
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
            {
                update.AddParam("@id", savingId);
                update.AddParam("@inOverdraft", inOverdraft);

                update.ExecuteNonQuery();
            }
        }

        public void UpdateInitialData(int pSavingId, OCurrency initialAmount, OCurrency entryFees)
        {
            const string sqlText = @"UPDATE SavingContracts SET initial_amount = @initial_amount, entry_fees = @entry_fees WHERE id = @id";
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand update = new OpenCbsCommand(sqlText, conn))
            {
                update.AddParam("@id", pSavingId);
                update.AddParam("@initial_amount", initialAmount);
               update.AddParam("@entry_fees", entryFees);

                update.ExecuteNonQuery();
            }
        }

 public int GetNumberOfSavings(int clientId)
 {
     const string sqlText = @"SELECT COUNT(id) FROM SavingContracts WHERE SavingContracts.tiers_id = @tiers_id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
     {
         select.AddParam("@tiers_id", clientId);
         return (int)select.ExecuteScalar();
     }
 }

        public void AddSavingsBookContract(SavingBookContract pSaving, SqlTransaction pSqlTransac)
        {
            const string sqlText = @"INSERT INTO [SavingBookContracts]
                                    (
                                        [id],
                                        [flat_withdraw_fees],
                                        [rate_withdraw_fees],
                                        [flat_transfer_fees],
                                        [rate_transfer_fees],
                                        [flat_deposit_fees],
                                        [flat_close_fees],
                                        [flat_management_fees],
                                        [flat_overdraft_fees],
                                        [in_overdraft],
                                        [rate_agio_fees],
                                        [cheque_deposit_fees],
                                        [flat_reopen_fees],
                                        [flat_ibt_fee],
                                        [rate_ibt_fee],
                                        [use_term_deposit],
                                        [term_deposit_period],
                                        [term_deposit_period_min],
                                        [term_deposit_period_max],
                                        [transfer_account],
                                        [rollover],
                                        [next_maturity]
                                    )
                                        VALUES
                                    (
                                        @id,
                                        @flatWithdrawFees,
                                        @rateWithdrawFees,
                                        @flatTransferFees,
                                        @rateTransferFees,
                                        @flatDepositFees,
                                        @flatCloseFees,
                                        @flatManagementFees,
                                        @flatOverdraftFees,
                                        @inOverdraft,
                                        @rateAgioFees,
                                        @chequeDepositFees,
                                        @flatReopenFees,
                                        @flat_ibt_fee,
                                        @rate_ibt_fee,
                                        @use_term_deposit,
                                        @term_deposit_period,
                                        @term_deposit_period_min,
                                        @term_deposit_period_max,
                                        @transfer_account,
                                        @rollover,
                                        @next_maturity
                                    )";

            using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, pSqlTransac.Connection, pSqlTransac))
            {
                cmd.AddParam("@id",  pSaving.Id);
                cmd.AddParam("@flatWithdrawFees", pSaving.Product.WithdrawFeesType == OSavingsFeesType.Flat ? pSaving.FlatWithdrawFees : null);
                cmd.AddParam("@rateWithdrawFees", pSaving.Product.WithdrawFeesType == OSavingsFeesType.Rate ? pSaving.RateWithdrawFees : null);
                cmd.AddParam("@flatTransferFees", pSaving.Product.TransferFeesType == OSavingsFeesType.Flat ? pSaving.FlatTransferFees : null);
                cmd.AddParam("@rateTransferFees", pSaving.Product.TransferFeesType == OSavingsFeesType.Rate ? pSaving.RateTransferFees : null);
                OCurrency flat = null;
                double? rate = null;
                if (pSaving.Product.InterBranchTransferFee.IsFlat)
                {
                    flat = pSaving.FlatInterBranchTransferFee;
                }
                else
                {
                    rate = pSaving.RateInterBranchTransferFee;
                }
                cmd.AddParam("@flat_ibt_fee",  flat);
                cmd.AddParam("@rate_ibt_fee", rate);
                cmd.AddParam("@flatDepositFees", pSaving.DepositFees);
                cmd.AddParam("@flatCloseFees", pSaving.CloseFees);
                cmd.AddParam("@flatManagementFees", pSaving.ManagementFees);
                cmd.AddParam("@flatOverdraftFees", pSaving.OverdraftFees);
                cmd.AddParam("@inOverdraft", pSaving.InOverdraft);
                cmd.AddParam("@rateAgioFees", pSaving.AgioFees);
                cmd.AddParam("@chequeDepositFees", pSaving.ChequeDepositFees);
                cmd.AddParam("@flatReopenFees", pSaving.ReopenFees);
                cmd.AddParam("@use_term_deposit",  pSaving.UseTermDeposit);
                cmd.AddParam("@term_deposit_period", pSaving.NumberOfPeriods);

            //                if (pSaving.UseTermDeposit)
            //                {
                    cmd.AddParam("@term_deposit_period_min", pSaving.TermDepositPeriodMin);
                    cmd.AddParam("@term_deposit_period_max", pSaving.TermDepositPeriodMax);
                    cmd.AddParam("@transfer_account",
                        pSaving.TransferAccount != null ? pSaving.TransferAccount.Code : null);
                    cmd.AddParam("@rollover", (int)pSaving.Rollover);
            //                }
            //                else
            //                {
            //                    cmd.AddParam("@term_deposit_period_min", null);
            //                    cmd.AddParam("@term_deposit_period_max", null);
            //                    cmd.AddParam("@transfer_account", null);
            //                    cmd.AddParam("@rollover", null);
            //                }

                cmd.AddParam("@next_maturity", pSaving.NextMaturity);
//.........这里部分代码省略.........

        public int Add(ISavingsContract savings, Client client, SqlTransaction sqlTransac)
        {
            const string sqlText = @"INSERT INTO [SavingContracts]
                (
                    [product_id],
                    [user_id],
                    [code],
                    [status],
                    [tiers_id],
                    [creation_date],
                    [interest_rate],
                    [closed_date],
                    [savings_officer_id],
                    [initial_amount],
                    [entry_fees],
                    [nsg_id]
                )
                VALUES
                (
                    @product_id,
                    @user_id,
                    @code,
                    @status,
                    @tiers_id,
                    @creation_date,
                    @interest_rate,
                    @closedDate,
                    @savings_officer_id,
                    @initial_amount,
                    @entry_fees,
                    @nsg_id
                )
                SELECT CONVERT(int, SCOPE_IDENTITY())";

            using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, sqlTransac.Connection, sqlTransac))
            {
                cmd.AddParam("@product_id",  savings.Product.Id);
                cmd.AddParam("@user_id", savings.User.Id);
                cmd.AddParam("@code", savings.Code);
                cmd.AddParam("@status",  (int)savings.Status);
                cmd.AddParam("@tiers_id", client.Id);
                cmd.AddParam("@creation_date", savings.CreationDate);
                cmd.AddParam("@interest_rate", savings.InterestRate);
                cmd.AddParam("@closedDate",  savings.ClosedDate);
                cmd.AddParam("@savings_officer_id", savings.SavingsOfficer.Id);
                cmd.AddParam("@initial_amount",  savings.InitialAmount);
                cmd.AddParam("@entry_fees", savings.EntryFees);
                cmd.AddParam("@nsg_id",  savings.NsgID);

                savings.Id = int.Parse(cmd.ExecuteScalar().ToString());
            }

            AddSavingsBookContract((SavingBookContract)savings, sqlTransac);

            return savings.Id;
        }