C# OpenCbsCommand.ExecuteReader方法代码示例

        public MyInformation GetQuestionnaire()
        {
            const string sqlText = @"SELECT   Name
                                            , Country
                                            , Email
                                            , NumberOfClients
                                            , GrossPortfolio
                                            , PositionInCompony
                                            , PersonName
                                            , Phone
                                            , Skype
                                            , PurposeOfUsage
                                            , OtherMessages
                                            , is_sent
                                            FROM Questionnaire";

            MyInformation myInformation = new MyInformation();
            myInformation.MfiName = null;
            using (SqlConnection conn = GetConnection())
            using (var cmd = new OpenCbsCommand(sqlText, conn))
            {
                using(OpenCbsReader reader = cmd.ExecuteReader())
                {
                    if (!reader.Empty)
                    {
                        reader.Read();
                        myInformation.MfiName = reader.GetString("Name");
                        myInformation.Country = reader.GetString("Country");
                        myInformation.Email = reader.GetString("Email");
                        myInformation.NumberOfClients = reader.GetString("NumberOfClients");
                        myInformation.GrossPortfolio = reader.GetString("GrossPortfolio");
                        myInformation.PositionInCompany = reader.GetString("PositionInCompony");
                        myInformation.PersonName = reader.GetString("PersonName");
                        myInformation.Phone = reader.GetString("Phone");
                        myInformation.Skype = reader.GetString("Skype");
                        myInformation.PurposeOfUsage = reader.GetString("PurposeOfUsage");
                        myInformation.Comment = reader.GetString("OtherMessages");
                        myInformation.IsSent = reader.GetBool("is_sent");
                        return myInformation;
                    }
                    return null;
                }
            }
        }

        public InstallmentType SelectInstallmentTypeByName(string name)
        {
            const string q = "SELECT * FROM InstallmentTypes WHERE name = @name";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@name", name);

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

        private List<EconomicActivity> SelectChildren(int pParentId)
        {
            List<EconomicActivity> doaList = new List<EconomicActivity>();

            const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE parent_id = @id AND deleted = 0";

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, connection))
            {
                sqlCommand.AddParam("@id", pParentId);
                using (OpenCbsReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        EconomicActivity domain = new EconomicActivity
                                                      {
                                                          Id = reader.GetInt("id"),
                                                          Name = reader.GetString("name"),
                                                          Deleted = reader.GetBool("deleted")
                                                      };
                        doaList.Add(domain);
                    }
                }
            }
            for (int i = 0; i < doaList.Count; i++)
                doaList[i].Childrens = SelectChildren(doaList[i].Id);

            return doaList;
        }

        public EconomicActivity SelectEconomicActivity(string pName)
        {
            EconomicActivity doa;

            const string sqlText = "SELECT id, name, deleted FROM EconomicActivities WHERE name = @name";
            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand selectById = new OpenCbsCommand(sqlText, connection))
            {
                selectById.AddParam("@name", pName);
                using (OpenCbsReader reader = selectById.ExecuteReader())
                {
                    doa = GetEconomicActivity(reader);
                }
            }

            doa.Childrens = SelectChildren(doa.Id);
            return doa;
        }

        /// <summary>
        /// This methods allows us to find all domains of application
        /// </summary>
        /// <returns>hierarchic collection of DomainOfApplication
        /// </returns>
        public List<EconomicActivity> SelectAllEconomicActivities()
        {
            List<EconomicActivity> doaList = new List<EconomicActivity>();

            const string sqlText = "SELECT id FROM EconomicActivities WHERE parent_id IS NULL AND deleted = 0";

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand selectAll = new OpenCbsCommand(sqlText, connection))
            {
                using (OpenCbsReader reader = selectAll.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        EconomicActivity domain = new EconomicActivity
                                                      {Id = reader.GetInt("id")};
                        doaList.Add(domain);
                    }
                }
            }

            for (int i = 0; i < doaList.Count; i++)
                doaList[i] = SelectEconomicActivity(doaList[i].Id);

            return doaList;
        }

        public List<PaymentMethod> SelectPaymentMethodsForClosure()
        {
            string q = @"SELECT pm.[id]
                                  ,[name]
                                  ,[description]
                                  ,[pending]
                                  ,[account_id]
                            FROM [PaymentMethods] pm
                            INNER JOIN LinkBranchesPaymentMethods lbpm ON lbpm.payment_method_id = pm.id
                            ORDER BY pm.[id]";

            List<PaymentMethod> paymentMethods = new List<PaymentMethod>();

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            using (OpenCbsReader r = c.ExecuteReader())
            {
                if (r != null && !r.Empty)
                    while (r.Read())
                    {
                        paymentMethods.Add(GetPaymentMethodFromReader(r));
                    }
            }
            return paymentMethods;
        }

 public PaymentMethod SelectPaymentMethodByName(string name)
 {
     const string q = @"SELECT pm.[id]
                           ,[name]
                           ,[description]
                           ,[pending]
                           ,0 AS account_id
                     FROM [PaymentMethods] pm
                     WHERE [name] = @name";
     PaymentMethod pm = new PaymentMethod();
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@name", name);
         using (OpenCbsReader r = c.ExecuteReader())
         {
             if (r != null && !r.Empty)
             {
                 r.Read();
                 pm = GetPaymentMethodFromReader(r);
             }
         }
     }
     return pm;
 }

 private IEnumerable<FollowUp> SelectFollowUps(int pProjectId)
 {
     List<FollowUp> list = new List<FollowUp>();
     const string sqlText = "SELECT * FROM FollowUp WHERE project_id = @pId";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand select = new OpenCbsCommand(sqlText, conn))
     {
         select.AddParam("@pId", pProjectId);
         using (OpenCbsReader reader = select.ExecuteReader())
         {
             if (reader == null || reader.Empty) return new List<FollowUp>();
             while (reader.Read())
             {
                 FollowUp followUp = new FollowUp();
                 followUp.Id = reader.GetInt("id");
                 followUp.Year = reader.GetInt("year");
                 followUp.Jobs1 = reader.GetInt("Jobs1");
                 followUp.Jobs2 = reader.GetInt("Jobs2");
                 followUp.CA = reader.GetMoney("CA");
                 followUp.PersonalSituation = reader.GetString("PersonalSituation");
                 followUp.Activity = reader.GetString("activity");
                 followUp.Comment = reader.GetString("comment");
                 list.Add(followUp);
             }
             return list;
         }
     }
 }

        public Dictionary<int, List<int>> SelectBranchRel()
        {
            const string q = @"SELECT user_id, branch_id
            FROM dbo.UsersBranches
            ORDER BY user_id";
            Dictionary<int, List<int>> retval = new Dictionary<int, List<int>>();
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            using (OpenCbsReader r = c.ExecuteReader())
            {
                if (r.Empty) return retval;

                while (r.Read())
                {
                    int userId = r.GetInt("user_id");
                    if (!retval.ContainsKey(userId)) retval.Add(userId, new List<int>());
                    retval[userId].Add(r.GetInt("branch_id"));
                }
            }
            return retval;
        }

        public List<User> SelectAll()
        {
            const string q = @"SELECT
                                 id,
                                 deleted,
                                 user_name,
                                 first_name,
                                 last_name,
                                 user_pass,
                                 mail,
                                 sex,
                                 phone,
                                (SELECT COUNT(*)
                                 FROM dbo.Credit
                                 WHERE loanofficer_id = u.id) AS num_contracts
                             FROM dbo.Users AS u";

            List<User> users = new List<User>();
            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            using (OpenCbsReader r = c.ExecuteReader())
            {
                if (r.Empty) return users;

                while (r.Read())
                {
                    User u = new User
                    {
                        Id = r.GetInt("id"),
                        FirstName = r.GetString("first_name"),
                        LastName = r.GetString("last_name"),
                        IsDeleted = r.GetBool("deleted"),
                        UserName = r.GetString("user_name"),
                        Password = r.GetString("user_pass"),
                        Mail = r.GetString("mail"),
                        Sex = r.GetChar("sex"),
                        HasContract = r.GetInt("num_contracts") > 0
                    };
                    users.Add(u);
                }
            }
            return users;
        }

        public Dashboard GetDashboard(int branchId, int subordinateId, int loanProductId)
        {
            var dashboard = new Dashboard();
            using (var connection = GetConnection())
            using (var command = new OpenCbsCommand("GetDashboard", connection)
                .AsStoredProcedure()
                .With("@date", TimeProvider.Today)
                .With("@userId", User.CurrentUser.Id)
                .With("@subordinateId", subordinateId)
                .With("@branchId", branchId)
                .With("@loanProductId", loanProductId)
                .WithTimeout(200))
            using (var reader = command.ExecuteReader())
            {
                while (reader.Read())
                {

                    var portfolioLine = new PortfolioLine
                    {
                        Name = reader.GetString("name"),
                        Amount = reader.GetDecimal("amount"),
                        Quantity = reader.GetInt("quantity"),
                          Color = reader.GetString("color")
                    };
                    dashboard.PortfolioLines.Add(portfolioLine);
                }
                reader.NextResult();
                while (reader.Read())
                {
                    var stat = new ActionStat
                    {
                        Date = reader.GetDateTime("date"),
                        AmountDisbursed = reader.GetDecimal("amount_disbursed"),
                        NumberDisbursed = reader.GetInt("number_disbursed"),
                        AmountRepaid = reader.GetDecimal("amount_repaid"),
                        NumberRepaid = reader.GetInt("number_repaid"),
                    };
                    dashboard.ActionStats.Add(stat);
                }
            }
            return dashboard;
        }

        public CollateralProperty SelectCollateralProperty(int propertyId)
        {
            const string sqlPropertyText = @"SELECT
                                                  [type_id]
                                                , [name]
                                                , [desc]
                                             FROM CollateralProperties
                                             WHERE id = @id";

            CollateralProperty collateralProperty = new CollateralProperty();

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand selectProperty = new OpenCbsCommand(sqlPropertyText, connection))
            {
                selectProperty.AddParam("@id", propertyId);
                using (OpenCbsReader propertyReader = selectProperty.ExecuteReader())
                {
                    if (propertyReader.Empty) return null; // nothing is coming! (c)
                    propertyReader.Read();

                    collateralProperty.Id = propertyId;
                    collateralProperty.Type = (OCollateralPropertyTypes)Enum.ToObject(typeof(OCollateralPropertyTypes),
                        propertyReader.GetInt("type_id"));
                    collateralProperty.Name = propertyReader.GetString("name");
                    collateralProperty.Description = propertyReader.GetString("desc");

                    if (collateralProperty.Type == OCollateralPropertyTypes.Collection)
                    {
                        List<string> propertyList = new List<string>();
                        const string sqlListText = @"SELECT [value]
                                                     FROM CollateralPropertyCollections
                                                     WHERE property_id = @property_id";

                        using (SqlConnection conn = GetConnection())
                        using (OpenCbsCommand selectList = new OpenCbsCommand(sqlListText, conn))
                        {
                            selectList.AddParam("@property_id", collateralProperty.Id);
                            using (OpenCbsReader listReader = selectList.ExecuteReader())
                            {
                                if (listReader.Empty) return null;

                                while (listReader.Read())
                                {
                                    propertyList.Add(listReader.GetString("value"));
                                }

                                collateralProperty.Collection = propertyList;
                            }
                        }
                    }
                }
            }

            return collateralProperty;
        }

        public CollateralProduct SelectCollateralProductByPropertyId(int propertyId)
        {
            int productId;
            const string sqlProductIdText = @"SELECT product_id
                                             FROM [CollateralProperties]
                                             WHERE id = @id ";

               using (SqlConnection connection = GetConnection())
               using (OpenCbsCommand cmd = new OpenCbsCommand(sqlProductIdText, connection))
            {
                cmd.AddParam("@id", propertyId);
                using (OpenCbsReader reader = cmd.ExecuteReader())
                {
                    if (reader.Empty) return null; // nothing is coming... (c)
                    reader.Read();
                    productId = reader.GetInt("product_id");
                }
            }

            const string sqlText = @"SELECT
                                     [name]
                                    ,[desc]
                                    ,[deleted]
                                    FROM CollateralProducts
                                    WHERE id = @id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand selectProduct = new OpenCbsCommand(sqlText, conn))
            {
                selectProduct.AddParam("@id", productId);
                using (OpenCbsReader reader = selectProduct.ExecuteReader())
                {
                    if (reader.Empty) return null;
                    reader.Read();

                    CollateralProduct colProduct = new CollateralProduct
                        {
                            Id = productId,
                            Name = reader.GetString("name"),
                            Description = reader.GetString("desc"),
                            Delete = reader.GetBool("deleted")
                        };

                    return colProduct;
                }
            }
        }

        /// <summary>
        /// This method allows us to select a package from 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="colProductId">id's of package searched</param>
        /// <returns>A package Object if id matches with datas in database, null if not</returns>
        public CollateralProduct SelectCollateralProduct(int colProductId)
        {
            const string sqlText = @"SELECT
                                             [name]
                                            ,[desc]
                                            ,[deleted]
                                    FROM CollateralProducts
                                    WHERE id = @id";

            CollateralProduct colProduct = new CollateralProduct();
            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
            {
                cmd.AddParam("@id", colProductId);
                using (OpenCbsReader reader = cmd.ExecuteReader())
                {
                    if (reader.Empty) return null;
                    reader.Read();
                    colProduct.Id = colProductId;
                    colProduct.Name = reader.GetString("name");
                    colProduct.Description = reader.GetString("desc");
                    colProduct.Delete = reader.GetBool("deleted");
                    reader.Dispose();
                }
            }

            List<CollateralProperty> properties = new List<CollateralProperty>();
                    const string sqlPropertyText = @"SELECT
                                                             id
                                                            ,type_id
                                                            ,[name]
                                                            ,[desc]
                                                     FROM CollateralProperties
                                                     WHERE product_id = @product_id";

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand cmd = new OpenCbsCommand(sqlPropertyText, connection))
            {
                cmd.AddParam("@product_id", colProduct.Id);
                using (OpenCbsReader reader = cmd.ExecuteReader())
                {
                    if (reader.Empty) return null;

                    while (reader.Read())
                    {
                        CollateralProperty collateralProperty = new CollateralProperty();
                        collateralProperty.Id = reader.GetInt("id");
                        collateralProperty.Type = (OCollateralPropertyTypes)Enum.ToObject(typeof(OCollateralPropertyTypes),
                            reader.GetInt("type_id"));
                        collateralProperty.Name = reader.GetString("name");
                        collateralProperty.Description = reader.GetString("desc");

                        if (collateralProperty.Type == OCollateralPropertyTypes.Collection)
                        {
                            List<string> propertyList = new List<string>();
                            const string sqlListText = @"SELECT [value]
                                                         FROM CollateralPropertyCollections
                                                         WHERE property_id = @property_id";
                            using (SqlConnection conn = GetConnection())
                            using (OpenCbsCommand selectList = new OpenCbsCommand(sqlListText, conn))
                            {
                                selectList.AddParam("@property_id", collateralProperty.Id);
                                using (OpenCbsReader listReader = selectList.ExecuteReader())
                                {
                                    if (listReader.Empty) return null;

                                    while (listReader.Read())
                                    {
                                        propertyList.Add(listReader.GetString("value"));
                                    }
                                    collateralProperty.Collection = propertyList;
                                }
                            }
                        }
                        properties.Add(collateralProperty);
                    }
                    colProduct.Properties = properties;
                }
            }

            return colProduct;
        }

        /// <summary>
        /// Select all packages in database
        /// </summary>
        /// <param name="pShowAlsoDeleted"></param>
        /// <returns>a list contains all packages</returns>
        public List<CollateralProduct> SelectAllCollateralProducts(bool pShowAlsoDeleted)
        {
            List<CollateralProduct> packagesList = new List<CollateralProduct>();
            string sqlText = @"SELECT id
                               FROM CollateralProducts
                               WHERE 1 = 1";

            if (!pShowAlsoDeleted)
                sqlText += " AND deleted = 0";

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand selectPackages = new OpenCbsCommand(sqlText, connection))
            {
                using (OpenCbsReader reader = selectPackages.ExecuteReader())
                {
                    if (reader.Empty) return new List<CollateralProduct>();
                    while (reader.Read())
                    {
                        CollateralProduct pack = new CollateralProduct { Id = reader.GetInt("id") };
                        packagesList.Add(pack);
                    }
                }
            }
            for (int i = 0; i < packagesList.Count; i++)
            {
                packagesList[i] = SelectCollateralProduct(packagesList[i].Id);
            }
            return packagesList;
        }