C# OpenCbsCommand.ExecuteScalar方法代码示例

 /// <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 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 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 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 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 decimal GetGlobalOLBForProvisionning()
 {
     const string q = @"SELECT ISNULL(SUM(Installments.capital_repayment - Installments.paid_capital),0)
             FROM Credit
            INNER JOIN Installments ON Credit.id = Installments.contract_id
             WHERE (Credit.disbursed = 1)
               AND (Credit.written_off = 0)
               AND (Credit.bad_loan = 0)";
     using (SqlConnection conn = GetConnection())
     using(OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         return Convert.ToDecimal(c.ExecuteScalar());
     }
 }

 public int GetNbOfLoansForClosure()
 {
     const string q = @"SELECT COUNT(Credit.id) FROM Credit WHERE Credit.disbursed = 1 AND Credit.written_off = 0 AND
                         (NOT ((SELECT SUM(interest_repayment) + SUM(capital_repayment) - SUM(paid_interest) - SUM(paid_capital)
                         FROM Installments WHERE contract_id = Credit.id) < 0.02))";
     using (SqlConnection conn = GetConnection())
     using(OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         return Convert.ToInt32(c.ExecuteScalar());
     }
 }

        public void AddCollateral(ContractCollateral contractCollateral, int contractId, SqlTransaction pSqlTransac)
        {
            string q = @"INSERT INTO [CollateralsLinkContracts] ([contract_id])
                                      VALUES (@contract_id) SELECT CONVERT(int, SCOPE_IDENTITY())";

            using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
            {
                c.AddParam("@contract_id", contractId);
                contractCollateral.Id = Convert.ToInt32(c.ExecuteScalar());
            }

            foreach (CollateralPropertyValue propertyValue in contractCollateral.PropertyValues)
            {
                AddCollateralPropertyValue(contractCollateral, propertyValue, pSqlTransac);
            }
        }

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

 public bool IdsExist(int[] ids)
 {
     string[] idStrings = ids.Select(i => i.ToString(CultureInfo.InvariantCulture)).ToArray();
     string query = string.Format(
         "IF EXISTS(SELECT 1 FROM ChartOfAccounts WHERE id in ({0})) SELECT 1 ELSE SELECT 0",
         string.Join(",", idStrings)
         );
     using (SqlConnection connection = GetConnection())
     using (OpenCbsCommand command = new OpenCbsCommand(query, connection))
         return Convert.ToInt32(command.ExecuteScalar()) == 1;
 }

 public string GetRepaymentModuleLastStartupDate()
 {
     const string sqlText = @"SELECT value
                             FROM dbo.TechnicalParameters
                             WHERE name='repayment_module_last_startup_date'";
     using (var conn = GetConnection())
     using (var select = new OpenCbsCommand(sqlText, conn))
     {
         var re = select.ExecuteScalar();
         return re == null ? String.Empty : re.ToString();
     }
 }

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

        private static void BackupToFileImpl(string pTempSavingPath, string pDatabaseName, SqlConnection pSqlConnection)
        {
            string sqlRecoveryMode1 = String.Format("ALTER DATABASE {0} SET RECOVERY SIMPLE", pDatabaseName);

            string sqlAutoShrink2 = String.Format("ALTER DATABASE {0} SET AUTO_SHRINK ON", pDatabaseName);

            string sqlBackupFile3 = String.Format(@"
                    DECLARE
                    @DataBaseName varchar(255),
                    @BackupFile varchar(255)

                    SET @DataBaseName = '{0}'
                    SET @BackupFile = N'{1}'

                    BACKUP DATABASE @DataBaseName TO DISK= @BackupFile
                    WITH FORMAT", pDatabaseName, pTempSavingPath);

            string sqlTruncLogFile4 = String.Format(@"
                    DECLARE
                    @DestDataBaseName varchar(255)
                    SET @DestDataBaseName = '{0}'
                    BACKUP LOG @DestDataBaseName WITH TRUNCATE_ONLY", pDatabaseName);

            string sqlTruncLogFile5 = String.Format(@"
                    DECLARE
                    @DestDataBaseName varchar(255),
                    @LogName varchar(255)
                    SET @DestDataBaseName = '{0}'
                    SET @LogName = (SELECT name from sysfiles where groupid = 0)
                    DBCC SHRINKFILE(@Logname)", pDatabaseName);

            const string sqlSqlServerVersion
                = @"SELECT CONVERT(INTEGER, CONVERT(FLOAT, CONVERT(VARCHAR(3), SERVERPROPERTY('productversion'))))";

            // Ensure recovery mode is FULL
            var cmd = new OpenCbsCommand(sqlRecoveryMode1, pSqlConnection);
            cmd.CommandTimeout = 300;
            cmd.ExecuteNonQuery();

             //Ensure auto shrink is on
            cmd = new OpenCbsCommand(sqlAutoShrink2, pSqlConnection);
            cmd.CommandTimeout = 300;
            cmd.ExecuteNonQuery();

            // Backup data int file
            cmd = new OpenCbsCommand(sqlBackupFile3, pSqlConnection);
            cmd.CommandTimeout = 300;
            cmd.ExecuteNonQuery();

            // Trunc transaction log
            cmd = new OpenCbsCommand(sqlSqlServerVersion, pSqlConnection);
            cmd.CommandTimeout = 300;
            var sqlVersion = (int)cmd.ExecuteScalar();

            if (sqlVersion < 10) // If SQL Server is 2000 or 2005
            {
                cmd = new OpenCbsCommand(sqlTruncLogFile4, pSqlConnection);
                cmd.CommandTimeout = 300;
                cmd.ExecuteNonQuery();
            }
            else // If SQL Server is 2008 or higher
            {
                cmd = new OpenCbsCommand(sqlTruncLogFile5, pSqlConnection);
                cmd.CommandTimeout = 300;
                cmd.ExecuteNonQuery();
            }
        }

        public int Add(SavingEvent pSavingEvent, int pSavingContractId, SqlTransaction sqlTransac)
        {
            const string q = @"INSERT INTO [SavingEvents](
                                       [user_id],
                                       [contract_id],
                                       [code],
                                       [amount],
                                       [description],
                                       [deleted],
                                       [creation_date],
                                       [cancelable],
                                       [is_fired],
                                       [related_contract_code],
                                       [fees],
                                       [savings_method],
                                       [pending],
                                       [pending_event_id],
                                       [teller_id],
                                       [loan_event_id])
                                     VALUES(
                                       @user_id,
                                       @contract_id,
                                       @code,
                                       @amount,
                                       @description,
                                       @deleted,
                                       @creation_date,
                                       @cancelable,
                                       @is_fired,
                                       @related_contract_code,
                                       @fees,
                                       @savings_method,
                                       @pending,
                                       @pending_event_id,
                                       @teller_id,
                                       @loan_event_id)
                                     SELECT CONVERT(int, SCOPE_IDENTITY())";

            using (OpenCbsCommand c = new OpenCbsCommand(q, sqlTransac.Connection, sqlTransac))
            {
                SetInsertCommandForSavingEvent(c, pSavingEvent, pSavingContractId);
                pSavingEvent.Id = Convert.ToInt32(c.ExecuteScalar());
                return pSavingEvent.Id;
            }
        }

        public int AddAccountingRule(IAccountingRule pRule)
        {
            const string sqlText = @"INSERT INTO [AccountingRules] (
                                       [debit_account_number_id],
                                       [credit_account_number_id],
                                       [rule_type],
                                       [booking_direction],
                                       [event_type],
                                       [event_attribute_id],
                                       [order],
                                       [description])
                                    VALUES (@debit_account_number_id,
                                            @credit_account_number_id,
                                            @rule_type,
                                            @booking_direction,
                                            @event_type,
                                            @event_attribute_id,
                                            @order,
                                            @description)
                                    SELECT SCOPE_IDENTITY()";
            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, conn))
                {
                    insert.AddParam("@debit_account_number_id",  pRule.DebitAccount.Id);
                    insert.AddParam("@credit_account_number_id", pRule.CreditAccount.Id);
                    insert.AddParam("@rule_type", pRule is ContractAccountingRule ? 'C' : 'F');
                    insert.AddParam("@booking_direction", (int)pRule.BookingDirection);

                    insert.AddParam("@event_type", pRule.EventType.EventCode);
                    insert.AddParam("@event_attribute_id", pRule.EventAttribute.Id);
                    insert.AddParam("@order", pRule.Order);
                    insert.AddParam("@description", pRule.Description);
                    pRule.Id = Convert.ToInt32(insert.ExecuteScalar());
                }
            }

            if (pRule is ContractAccountingRule)
                AddContractAccountingRule(pRule as ContractAccountingRule);

            return pRule.Id;
        }