C# OpenCbsCommand.ExecuteReader方法代码示例

        public List<CreditSearchResult> SearchCreditContractByCriteres(int pageNumber, string pQuery, out int count)
        {
            int startRow = 20*(pageNumber - 1) + 1;
            int endRow = 20*pageNumber;
            const string query = @"
                SELECT * FROM (
                        SELECT ROW_NUMBER() OVER (ORDER BY [user_id]) row, COUNT(1) OVER(PARTITION BY [user_id]) row_count, * FROM (
                            SELECT
                                      Contracts.id,
                                      Contracts.contract_code,
                                      Contracts.status,
                                      Contracts.start_date,
                                      Contracts.align_disbursed_date,
                                      Contracts.close_date,
                                      Persons.identification_data as identification_data,
                                      Credit.amount,
                                      Credit.loanofficer_id,
                                      Tiers.client_type_code,
                                      ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS user_name,
                                      ISNULL(ISNULL(ISNULL(Groups.name, Persons.first_name + SPACE(1) + Persons.last_name), Corporates.name),'Error!') AS client_name,
                                      ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name) AS loanofficer_name,
                                      Credit.[amount_min],
                                      Credit.[amount_max],
                                      Credit.[ir_min],
                                      Credit.[ir_max],
                                      Credit.[nmb_of_inst_min],
                                      Credit.[nmb_of_inst_max],
                                      Credit.[loan_cycle],
                                      @user_id [user_id]
                            FROM Contracts
                            INNER JOIN Credit ON Contracts.id = Credit.id
                            INNER JOIN Projects ON Contracts.project_id = Projects.id
                            INNER JOIN Tiers ON Projects.tiers_id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id)
                            LEFT JOIN Users ON Users.id = Credit.loanofficer_id
                            LEFT OUTER JOIN Persons ON Tiers.id = Persons.id
                            LEFT OUTER JOIN Groups ON Tiers.id = Groups.id
                            LEFT OUTER JOIN Corporates ON Tiers.id = Corporates.id

                            UNION ALL

                            SELECT ISNULL(Contracts.id,0) AS id, ISNULL(Contracts.contract_code,'No contract') AS contract_code,
                              ISNULL(Contracts.status,0) AS status, ISNULL(Contracts.start_date,'01-01-1900') AS start_date,
                              ISNULL(Contracts.align_disbursed_date,'01-01-1900') AS align_disbursed_date,
                              ISNULL(Contracts.close_date,'01-01-1900') AS close_date,
                              Persons.identification_data AS identification_data, ISNULL(Credit.amount,0) AS amount,
                              ISNULL(Credit.loanofficer_id,0) AS loanofficer_id,
                              CASE (Tiers.client_type_code) WHEN 'I' THEN 'V' ELSE '-' END AS client_type_code,
                              ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS user_name,
                              Villages.name AS client_name,
                              ISNULL(ISNULL(Users.first_name + SPACE(1) + Users.last_name, Users.user_name),'No contract') AS loanofficer_name,
                              Credit.[amount_min],
                              Credit.[amount_max],
                              Credit.[ir_min],
                              Credit.[ir_max],
                              Credit.[nmb_of_inst_min],
                              Credit.[nmb_of_inst_max],
                              Credit.[loan_cycle],
                              @user_id [user_id]
                            FROM Villages
                            INNER JOIN VillagesPersons ON VillagesPersons.village_id = Villages.id
                            INNER JOIN Persons ON Persons.id = VillagesPersons.person_id
                            INNER JOIN Tiers ON Persons.id = Tiers.id AND Tiers.branch_id IN (select branch_id from dbo.UsersBranches where user_id = @user_id)
                            INNER JOIN Projects ON Tiers.id = Projects.tiers_id
                            INNER JOIN Contracts ON Contracts.project_id = Projects.id
                            INNER JOIN Credit ON Credit.id = Contracts.id
                            LEFT JOIN Users ON Users.id = Credit.loanofficer_id
                    ) R WHERE (ISNULL(contract_code, '') + ' ' + ISNULL(client_name, '') + ' ' + ISNULL([user_name], '') + ' ' + ISNULL(identification_data, '') + ' ' + ISNULL(loanofficer_name, '')) LIKE @criteria
                ) T WHERE row BETWEEN @startRow AND @endRow
            ";

            count = 0;
            var list = new List<CreditSearchResult>();
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand cmd = new OpenCbsCommand(query, conn))
            {
                cmd.AddParam("user_id", User.CurrentUser.Id);
                cmd.AddParam("startRow", startRow);
                cmd.AddParam("endRow", endRow);
                cmd.AddParam("criteria", string.Format("%{0}%", pQuery));
                using (OpenCbsReader reader = cmd.ExecuteReader())
                    while (reader.Read())
                    {
                        if (count == 0) count = reader.GetInt("row_count");
                        var result = new CreditSearchResult
                        {
                            Id = reader.GetInt("id"),
                            ContractCode = reader.GetString("contract_code"),
                            ClientType = reader.GetString("client_type_code"),
                            ClientName = reader.GetString("client_name"),
                            ContractStartDate = reader.GetDateTime("start_date").ToShortDateString(),
                            ContractEndDate = reader.GetDateTime("close_date").ToShortDateString(),
                            ContractStatus = ((OContractStatus)reader.GetSmallInt("status")).ToString(),
                            LoanOfficer = new User { Id = reader.GetInt("loanofficer_id") }
                        };

                        list.Add(result);
                    }
            }
            return list;
        }

        public static string GetDatabaseVersion(string database, SqlConnection connection)
        {
            if (connection == null)
            {
                throw new ArgumentException("Both connection and transaction cannot be null.");
            }

            if (!IsOctopusDatabase(database, connection)) return string.Empty;

            string sqlText = string.Format(
                "USE [{0}] SELECT [value] FROM [TechnicalParameters] WHERE [name]='version'", database);
            using (OpenCbsCommand select = new OpenCbsCommand(sqlText, connection))
            using (OpenCbsReader reader = select.ExecuteReader())
            {
                if (reader.Empty) return string.Empty;
                reader.Read();
                return reader.GetString("value");
            }
        }

 public static string GetObjectCreateScript(string db, string name, SqlConnection conn)
 {
     string q = string.Format("{0}..sp_helptext", db);
     OpenCbsCommand cmd = new OpenCbsCommand(q, conn).AsStoredProcedure();
     cmd.AddParam("@objname", name);
     var buffer = new StringBuilder(2048);
     using (OpenCbsReader reader = cmd.ExecuteReader())
     {
         if (null == reader) return string.Empty;
         while (reader.Read())
         {
             buffer.Append(reader.GetString("Text"));
         }
     }
     return buffer.ToString();
 }

        ///// <summary>
        ///// This method Fill the instance of the ProvisioningTable object accessed by singleton
        ///// </summary>
        public List<ProvisioningRate> SelectAllProvisioningRates()
        {
            List<ProvisioningRate> list = new List<ProvisioningRate>();

            const string sqlText = @"SELECT
                                       id,
                                       number_of_days_min,
                                       number_of_days_max,
                                       provisioning_value,
                                       provisioning_interest,
                                       provisioning_penalty
                                     FROM ProvisioningRules";
            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
                {
                    using (OpenCbsReader reader = select.ExecuteReader())
                    {
                        if (reader.Empty) return list;
                        while (reader.Read())
                        {
                            list.Add(GetProvisionningRate(reader));
                        }
                        return list;
                    }
                }
            }
        }

 public static string GetDatabaseBranchCodeFromBranches(string pDatabaseName, SqlConnection pSqlConnection)
 {
     string sqlText = string.Format("USE [{0}] SELECT TOP 1 [code] FROM [Branches] WHERE [deleted] = 0", pDatabaseName);
     using (OpenCbsCommand select = new OpenCbsCommand(sqlText, pSqlConnection))
     {
         using (OpenCbsReader reader = select.ExecuteReader())
         {
             if (reader.Empty) return string.Empty;
             reader.Read();
             string code = reader.GetString("code");
             if (string.IsNullOrEmpty(code))
                 throw new ApplicationException(
                     string.Format("Emty or no branch code found for database: {0}.",pDatabaseName));
             return code;
         }
     }
 }

        public List<Loan> SelectLoansBySavingsId(int savingsId)
        {
            List<Loan> listLoans = new List<Loan>();

            const string sqlText = @"SELECT loan_id
                                     FROM LoansLinkSavingsBook
                                     WHERE savings_id = @savingsId";
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
            {
                select.AddParam("@savingsId",  savingsId);

                using (OpenCbsReader reader = select.ExecuteReader())
                {
                    if (!reader.Empty)
                    {
                        while (reader.Read())
                        {
                            listLoans.Add(_loanManager.SelectLoan(reader.GetInt("loan_id"), true, false, false));
                        }
                    }
                }
            }

            return listLoans;
        }

 public void SelectSavingsBookContract(SavingBookContract saving)
 {
     const string sqlText = @"SELECT
                                     sbc.flat_withdraw_fees,
                                     sbc.rate_withdraw_fees,
                                     sbc.flat_transfer_fees,
                                     sbc.rate_transfer_fees,
                                     sbc.flat_deposit_fees,
                                     sbc.flat_close_fees,
                                     sbc.flat_management_fees,
                                     sbc.flat_overdraft_fees,
                                     sbc.in_overdraft,
                                     sbc.rate_agio_fees,
                                     sbc.cheque_deposit_fees,
                                     sbc.flat_reopen_fees,
                                     sbc.flat_ibt_fee,
                                     sbc.rate_ibt_fee,
                                     sbc.use_term_deposit,
                                     sbc.term_deposit_period,
                                     sbc.term_deposit_period_min,
                                     sbc.term_deposit_period_max,
                                     sbc.transfer_account,
                                     sbc.rollover,
                                     t.branch_id,
                                     sbc.next_maturity
                             FROM dbo.SavingBookContracts sbc
                             INNER JOIN dbo.SavingContracts sc on sc.id = sbc.id
                             INNER JOIN dbo.Tiers t on t.id = sc.tiers_id
                             WHERE sbc.id = @id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
     {
         select.AddParam("@id", saving.Id);
         using (OpenCbsReader reader = select.ExecuteReader())
         {
             if (!reader.Read()) return;
             GetSavingBookFromReader(saving, reader);
         }
         //It needs to launch a new reader, that's why next code is here
         saving.TransferAccount = Select(saving.TransferAccount.Code);
     }
     OnSavingSelected(saving);
 }

        /// <summary>
        /// Returns Loan
        /// </summary>
        /// <param name="pLoanId">Loan id</param>
        /// <param name="pAddGeneralInformation">add product, events, chartOfAccount, and funding line</param>
        /// <param name="pAddOptionalInformation"></param>
        /// <param name="pAddOptionalEventInformation"></param>
        /// <returns></returns>
        public Loan SelectLoan(int pLoanId, bool pAddGeneralInformation, bool pAddOptionalInformation, bool pAddOptionalEventInformation)
        {
            Loan loan;
            int productId;
            int installmentTypeId;
            int loanOfficerId;
            int fundingLineId;

            const string q = @"SELECT Credit.id AS credit_id,
                                        Credit.package_id,
                                        Credit.amount,
                                        Credit.interest_rate,
                                        Credit.installment_type,
                                        Credit.nb_of_installment,
                                        Credit.non_repayment_penalties_based_on_overdue_principal,
                                        Credit.non_repayment_penalties_based_on_overdue_interest,
                                        Credit.non_repayment_penalties_based_on_olb,
                                        Credit.non_repayment_penalties_based_on_initial_amount,
                                        Credit.anticipated_total_repayment_penalties,
                                        Credit.anticipated_partial_repayment_penalties,
                                        Credit.anticipated_total_repayment_base,
                                        Credit.anticipated_partial_repayment_base,
                                        Credit.disbursed, Credit.loanofficer_id,
                                        Credit.fundingLine_id,
                                        Credit.grace_period,
                                        Credit.written_off,
                                        Credit.rescheduled,
                                        Credit.bad_loan,
                                        Credit.grace_period_of_latefees,
                                        Contracts.contract_code,
                                        Tiers.client_type_code,
                                        Credit.synchronize,
                                        Credit.schedule_changed,
                                        ISNULL(Credit.amount_under_loc, 0) AS amount_under_loc,
                                        Contracts.branch_code,
                                        Contracts.creation_date,
                                        Contracts.start_date,
                                        ISNULL(Contracts.align_disbursed_date, Contracts.start_date) AS align_disbursed_date,
                                        Contracts.close_date,
                                        Contracts.closed,
                                        Contracts.status,
                                        Contracts.credit_commitee_date,
                                        Contracts.credit_commitee_comment,
                                        Contracts.credit_commitee_code,
                                        Contracts.loan_purpose,
                                        Contracts.comments,
                                        Contracts.nsg_id,
                                        Contracts.activity_id,
                                        Contracts.preferred_first_installment_date,
                                        LoansLinkSavingsBook.loan_percentage,
                                        Credit.[amount_min],
                                        Credit.[amount_max],
                                        Credit.[ir_min],
                                        Credit.[ir_max],
                                        Credit.[nmb_of_inst_min],
                                        Credit.[nmb_of_inst_max],
                                        Credit.[loan_cycle],
                                        Credit.insurance
                                        FROM Contracts
                                        INNER JOIN Credit ON Contracts.id = Credit.id
                                        INNER JOIN Projects ON Contracts.project_id = Projects.id
                                        INNER JOIN Tiers ON Projects.tiers_id = Tiers.id
                                        LEFT JOIN LoansLinkSavingsBook ON LoansLinkSavingsBook.loan_id = Contracts.id
                                        WHERE Credit.id = @id";
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@id", pLoanId);
                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r == null || r.Empty) return null;

                    r.Read();
                    loan = _GetLoan(r);

                    installmentTypeId = r.GetInt("installment_type");
                    productId = r.GetInt("package_id");
                    loanOfficerId = r.GetInt("loanofficer_id");
                    fundingLineId = r.GetInt("fundingLine_id");
                }
            }

            loan.InstallmentType = _installmentTypeManagement.SelectInstallmentType(installmentTypeId);
            loan.InstallmentList = _installmentManagement.SelectInstallments(loan.Id);
            loan.EconomicActivity = _economicActivityManager.SelectEconomicActivity(loan.EconomicActivityId);
            loan.GivenTranches = SelectTranches(loan.Id);

            if (pAddGeneralInformation)
            {
                loan.Product = _packageManager.Select(productId);
                loan.Events = _eventManagement.SelectEvents(loan.Id);

//.........这里部分代码省略.........

        public List<Loan> SelectLoansByClientId(int clientId)
        {
            List<int> ids = new List<int>();
            const string q = @"SELECT Cont.id FROM Contracts AS Cont
                                     INNER JOIN Projects AS Pr ON Cont.project_id = Pr.id
                                     INNER JOIN Tiers AS Tr ON Tr.id = Pr.tiers_id
                                     WHERE Tr.id = @id";
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@id", clientId);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r == null || r.Empty) return new List<Loan>();

                    while (r.Read())
                        ids.Add(r.GetInt("id"));
                }
            }

            List<Loan> loans = new List<Loan>();
            foreach (int id in ids)
                loans.Add(SelectLoan(id, true, true, true));

            return loans;
        }

 public List<LoanEntryFee> SelectInstalledLoanEntryFees(int loanId)
 {
     List<LoanEntryFee> loanEntryFees = new List<LoanEntryFee>();
     string q =
         @"SELECT id
                 ,[entry_fee_id]
                 ,[fee_value]
           FROM [dbo].[CreditEntryFees]
           WHERE [credit_id][email protected]_id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@credit_id", loanId);
         using (OpenCbsReader r = c.ExecuteReader())
         {
             while (r.Read())
             {
                 LoanEntryFee lef = new LoanEntryFee();
                 lef.Id = r.GetInt("id");
                 lef.ProductEntryFeeId = r.GetInt("entry_fee_id");
                 lef.FeeValue = r.GetDecimal("fee_value");
                 loanEntryFees.Add(lef);
             }
         }
     }
     return loanEntryFees;
 }

 public List<DateTime> SelectInstallmentDatesForVillageActiveContracts(int villageId)
 {
     List<DateTime> installmentDates = new List<DateTime>();
     string q =
           @"SELECT
             Installments.expected_date
             FROM dbo.VillagesPersons vp
             INNER JOIN dbo.Projects pr ON pr.tiers_id = vp.person_id
             INNER JOIN dbo.Contracts c ON c.project_id = pr.id
             INNER JOIN dbo.Installments ON Installments.contract_id = c.id
             WHERE village_id = @village_id
             AND c.[status] = 5
             GROUP BY Installments.expected_date
             ORDER BY Installments.expected_date";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@village_id", villageId);
         using (OpenCbsReader r = c.ExecuteReader())
         {
             while (r.Read())
             {
                 installmentDates.Add(r.GetDateTime("expected_date"));
             }
         }
     }
     return installmentDates;
 }

        public List<Loan> SelectAllLoansOfClient(int pClientId)
        {
            const string sql = @"SELECT Contracts.id as id
                                 FROM Contracts
                                 INNER JOIN Projects ON Contracts.project_id = Projects.id
                                 WHERE Projects.tiers_id = @id
                                   AND Contracts.nsg_id IS NOT NULL";

            List<Loan> list = new List<Loan>();
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(sql, conn))
            {
                c.AddParam("@id", pClientId);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r == null || r.Empty) return list;

                    while (r.Read())
                        list.Add(SelectLoan(r.GetInt("id"), true, true, true));
                }
            }
            return list;
        }

        public List<Alert_v2> SelectAllAlerts(DateTime date, int userId)
        {
            List<Alert_v2> alerts = new List<Alert_v2>();
            string q = "SELECT * FROM dbo.Alerts(@date, @user_id, @branch_id)";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.CommandTimeout = 600;
                c.AddParam("@date", date.Date);
                c.AddParam("@user_id", userId);
                c.AddParam("@branch_id", 1);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r.Empty) return alerts;

                    while (r.Read())
                    {
                        Alert_v2 alert = new Alert_v2
                        {
                            Address = r.GetString("address")
                            , Amount = r.GetMoney("amount")
                            , City = r.GetString("city")
                            , ClientName = r.GetString("client_name")
                            , ContractCode = r.GetString("contract_code")
                            , Date = r.GetDateTime("date")
                            , Id = r.GetInt("id")
                            , LateDays = r.GetInt("late_days")
                            , LoanOfficer = new User {Id = r.GetInt("loan_officer_id")}
                            , Phone = r.GetString("phone")
                            , Status = (OContractStatus) r.GetInt("status")
                            , UseCents = r.GetBool("use_cents")
                            , Kind = (AlertKind) r.GetInt("kind")
                            , BranchName = r.GetString("branch_name")
                        };
                        alerts.Add(alert);
                    }
                }

                return alerts;
            }
        }

        /// <summary>
        /// Select the latest active loan for a client
        /// </summary>
        /// <param name="pClientId"></param>
        /// <returns></returns>
        public List<Loan> SelectActiveLoans(int pClientId)
        {
            const string q = @"SELECT Contracts.id as id
                                 FROM Contracts
                                 INNER JOIN Projects ON Contracts.project_id = Projects.id
                                 WHERE Projects.tiers_id = @id
                                   AND Contracts.status IN (@status1, @status2, @status3, @status4)
                                   AND Contracts.nsg_id IS NOT NULL";

            List<Loan> list = new List<Loan>();
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@id", pClientId);
                c.AddParam("@status1", (int)OContractStatus.Active);
                c.AddParam("@status2", (int)OContractStatus.Validated);
                c.AddParam("@status3", (int)OContractStatus.Pending);
                c.AddParam("@status4", (int)OContractStatus.Postponed);

                using (OpenCbsReader r = c.ExecuteReader())
                {
                    if (r == null || r.Empty) return list;

                    while (r.Read())
                        list.Add(SelectLoan(r.GetInt("id"), true, true, true));
                }
            }
            return list;
        }

        public List<ISavingsContract> SelectAllActive()
        {
            List<ISavingsContract> listSaving = new List<ISavingsContract>();

            string sqlText = @"SELECT SavingContracts.*,
                                            u2.first_name AS so_first_name,
                                            u2.last_name AS so_last_name,
                                            SavingProducts.product_type,
                                            Users.id AS user_id,
                                            Users.deleted,
                                            Users.user_name,
                                            Users.user_pass,
                                            Users.role_code,
                                            Users.first_name,
                                            Users.last_name
                                    FROM SavingContracts
                                    INNER JOIN SavingProducts ON SavingContracts.product_id = SavingProducts.id
                                    INNER JOIN Users AS u2 ON u2.id = SavingContracts.savings_officer_id
                                    INNER JOIN Users ON SavingContracts.user_id = Users.id
                                    WHERE SavingContracts.status != " + ((int) OSavingsStatus.Closed);
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
            {
                using (OpenCbsReader reader = select.ExecuteReader())
                {
                    if (!reader.Empty)
                    {
                        while (reader.Read())
                        {
                            listSaving.Add(GetSavingFromReader(reader));
                        }
                    }
                }
            }

            foreach (ISavingsContract saving in listSaving)
            {
                SelectSavingsBookContract((SavingBookContract)saving);
                saving.Product = _savingProductManager.SelectSavingProduct(saving.Product.Id);
                saving.Events.AddRange(_savingEventManager.SelectEvents(saving.Id, saving.Product));
                if (_clientManager != null)
                    saving.Client = _clientManager.SelectClientBySavingsId(saving.Id);
            }

            return listSaving;
        }