C# OpenCbsCommand.ExecuteScalar方法代码示例

        /// <summary>
        /// Method to add a package 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="colProduct">Package Object</param>
        /// <returns>The id of the package which has been added</returns>
        public int AddCollateralProduct(CollateralProduct colProduct)
        {
            string sqlText = @"INSERT INTO [CollateralProducts]
                                (
                                    [name]
                                    ,[desc]
                                    ,[deleted]
                                )
                                VALUES
                                (
                                    @name
                                    ,@desc
                                    ,@deleted
                                 )
                                 SELECT CONVERT(int, SCOPE_IDENTITY())";

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
            {
                cmd.AddParam("@name", colProduct.Name);
                cmd.AddParam("@desc", colProduct.Description);
                cmd.AddParam("@deleted", colProduct.Delete);
                colProduct.Id = int.Parse(cmd.ExecuteScalar().ToString());
            }

            foreach (CollateralProperty collateralProperty in colProduct.Properties)
                AddCollateralProperty(colProduct.Id, collateralProperty);

            return colProduct.Id;
        }

        /// <summary>
        /// Add Role in database
        /// </summary>
        /// <param name="pRole"></param>
        /// <returns>Role id</returns>
        public int AddRole(Role pRole)
        {
            const string q = @"INSERT INTO [Roles]
                                     ([deleted], [code], [description], [role_of_loan], [role_of_saving], [role_of_teller])
                                     VALUES(@deleted, @code, @description, @role_of_loan, @role_of_saving, @role_of_teller)
                                     SELECT SCOPE_IDENTITY()";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@deleted", false);
                    c.AddParam("@code", pRole.RoleName);
                    c.AddParam("@description", pRole.Description);
                    c.AddParam("@role_of_loan", pRole.IsRoleForLoan);
                    c.AddParam("@role_of_saving", pRole.IsRoleForSaving);
                    c.AddParam("@role_of_teller", pRole.IsRoleForTeller);

                    pRole.Id = int.Parse(c.ExecuteScalar().ToString());
                    SaveRoleMenu(pRole);
                    SaveAllowedActionsForRole(pRole);
                }
            }
            return pRole.Id;
        }

 public int AddCity(City pCity)
 {
     const string q = "INSERT INTO [City] ([name], [district_id],[deleted]) VALUES (@name,@district_id,0) SELECT SCOPE_IDENTITY()";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@name", pCity.Name);
         c.AddParam("@district_id", pCity.DistrictId);
         c.AddParam("@deleted", pCity.Deleted);
         return int.Parse(c.ExecuteScalar().ToString());
     }
 }

 public int AddDistrict(string pName, int pProvinceId)
 {
     const string q = "INSERT INTO [Districts] ([name],[province_id],[deleted]) VALUES( @name, @province,0) SELECT SCOPE_IDENTITY()";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@name", pName);
         c.AddParam("@province", pProvinceId);
         c.AddParam("@deleted", false);
         return int.Parse(c.ExecuteScalar().ToString());
     }
 }

        public int AddInstallmentType(InstallmentType installmentType)
        {
            const string q = @"INSERT INTO [InstallmentTypes]([name], [nb_of_days], [nb_of_months])
                                     VALUES(@name, @days, @months) SELECT SCOPE_IDENTITY()";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                SetInstallmentType(c, installmentType);
                return int.Parse(c.ExecuteScalar().ToString());
            }
        }

 public bool CodeExists(int id, string code)
 {
     const string q =
         @"select count(*)
     from dbo.Branches
     where code = @code and id <> @id";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@id", id);
         c.AddParam("@code", code);
         return Convert.ToBoolean(c.ExecuteScalar());
     }
 }

 public Branch Add(Branch branch, SqlTransaction t)
 {
     const string q = @"INSERT INTO dbo.Branches
                       (name, code, address, description)
                       VALUES (@name, @code, @address, @description)
                       SELECT SCOPE_IDENTITY()";
     using (OpenCbsCommand c = new OpenCbsCommand(q, t.Connection, t))
     {
         c.AddParam("@name", branch.Name);
         c.AddParam("@code", branch.Code);
         c.AddParam("@address", branch.Address);
         c.AddParam("@description", branch.Description);
         branch.Id = Convert.ToInt32(c.ExecuteScalar());
         return branch;
     }
 }

        /// <summary>
        /// Add an economic activity in database
        /// </summary>
        /// <param name="pEconomicActivity">the economic activity object to add</param>
        /// <returns>the id of the economic activity added</returns>
        public int AddEconomicActivity(EconomicActivity pEconomicActivity)
        {
            const string sqlText = @"INSERT INTO EconomicActivities ([name] , [parent_id] , [deleted])
                        VALUES (@name,@parentId,@deleted) SELECT SCOPE_IDENTITY()";

            using (SqlConnection connection = GetConnection())
            using (OpenCbsCommand insert = new OpenCbsCommand(sqlText, connection))
            {
                insert.AddParam("@name", pEconomicActivity.Name);
                insert.AddParam("@deleted", pEconomicActivity.Deleted);
                if (pEconomicActivity.Parent != null)
                    insert.AddParam("@parentId", pEconomicActivity.Parent.Id);
                else
                    insert.AddParam("@parentId", null);
                return int.Parse(insert.ExecuteScalar().ToString());
            }
        }

        public MenuObject AddNewMenu(string name)
        {
            MenuObject newMenu = new MenuObject();

            const string q = @"INSERT INTO [MenuItems]([component_name])
                               VALUES (@menu)
                               SELECT SCOPE_IDENTITY()";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@menu", name.Trim());
                    newMenu.Id = int.Parse(c.ExecuteScalar().ToString());
                    newMenu.NotSavedInDBYet = false;
                    newMenu.Name = name;
                }
            }
            return newMenu;
        }

        /// <summary>
        /// Add Role in database
        /// </summary>
        /// <param name="pRole"></param>
        /// <returns>Role id</returns>
        public int AddRole(Role pRole)
        {
            const string q = @"INSERT INTO [Roles]
                                     ([deleted], [code], [description], [default_start_view])
                                     VALUES(@deleted, @code, @description, @start_view)
                                     SELECT SCOPE_IDENTITY()";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@deleted", false);
                    c.AddParam("@code", pRole.RoleName);
                    c.AddParam("@description", pRole.Description);
                    c.AddParam("@start_view", pRole.DefaultStartPage.ToString());

                    pRole.Id = int.Parse(c.ExecuteScalar().ToString());
                    SaveRoleMenu(pRole);
                    SaveAllowedActionsForRole(pRole);
                }
            }
            return pRole.Id;
        }

 public void AddGeneralParameterIntoDatabase()
 {
     OpenCbsCommand insert = new OpenCbsCommand("INSERT INTO [GeneralParameters]([key], [value]) VALUES('ID_PATTERN', '/d/d/w/w') SELECT SCOPE_IDENTITY()", _connection);
     insert.ExecuteScalar();
 }

        public int Add(Project pProject, int pTiersId, SqlTransaction pSqlTransac)
        {
            const string q = @"INSERT INTO Projects([tiers_id],[status],[name],[code],[aim],[begin_date],
                [abilities],[experience],[market],[concurrence],[purpose],[corporate_name],[corporate_juridicStatus]
                ,[corporate_FiscalStatus],[corporate_siret],[corporate_CA],[corporate_nbOfJobs],[corporate_financialPlanType]
                ,[corporateFinancialPlanAmount],[corporate_financialPlanTotalAmount],[address],[city],[zipCode],[district_id]
                ,[home_phone],[personalPhone],[Email],[hometype],[corporate_registre])
                VALUES(@tiersId,@status,@name,@code,@aim,@beginDate,@abilities,@experience,@market,@concurrence,@purpose,
                @corporateName,@corporateJuridicStatus,@corporateFiscalStatus,@corporateSiret,@corporateCA,@corporateNbOfJobs,
                @corporateFinancialPlanType, @corporateFinancialPlanAmount, @corporateFinancialPlanTotalAmount,
                @address,@city, @zipCode, @districtId, @homePhone, @personalPhone,@Email,@hometype,@corporateRegistre) SELECT SCOPE_IDENTITY()";

            using (OpenCbsCommand c = new OpenCbsCommand(q, pSqlTransac.Connection, pSqlTransac))
            {
                c.AddParam("@tiersId", pTiersId);
                c.AddParam("@status", (int) pProject.ProjectStatus);
                c.AddParam("@name", pProject.Name);
                c.AddParam("@code", pProject.Code);
                c.AddParam("@aim", pProject.Aim);
                c.AddParam("@beginDate", pProject.BeginDate);
                c.AddParam("@abilities", pProject.Abilities);
                c.AddParam("@experience", pProject.Experience);
                c.AddParam("@market", pProject.Market);
                c.AddParam("@concurrence",  pProject.Concurrence);
                c.AddParam("@purpose", pProject.Purpose);

                c.AddParam("@corporateName", pProject.CorporateName);
                c.AddParam("@corporateJuridicStatus", pProject.CorporateJuridicStatus);
                c.AddParam("@corporateFiscalStatus", pProject.CorporateFiscalStatus);
                c.AddParam("@corporateRegistre", pProject.CorporateRegistre);
                c.AddParam("@corporateSiret", pProject.CorporateSIRET);
                c.AddParam("@corporateCA", pProject.CorporateCA);
                c.AddParam("@corporateNbOfJobs", pProject.CorporateNbOfJobs);
                c.AddParam("@corporateFinancialPlanType", pProject.CorporateFinancialPlanType);
                c.AddParam("@corporateFinancialPlanAmount", pProject.CorporateFinancialPlanAmount);
                c.AddParam("@corporateFinancialPlanTotalAmount", pProject.CorporateFinancialPlanTotalAmount);
                c.AddParam("@address", pProject.Address);
                c.AddParam("@city", pProject.City);
                c.AddParam("@zipCode", pProject.ZipCode);
                if (pProject.District != null)
                    c.AddParam("@districtId", pProject.District.Id);
                else
                    c.AddParam("@districtId", null);
                c.AddParam("@homePhone", pProject.HomePhone);
                c.AddParam("@personalPhone", pProject.PersonalPhone);
                c.AddParam("@Email", pProject.Email);
                c.AddParam("@hometype", pProject.HomeType);

                pProject.Id = Convert.ToInt32(c.ExecuteScalar());
            }
            foreach (FollowUp followUp in pProject.FollowUps)
            {
                _AddFollowUp(followUp, pProject.Id, pSqlTransac);
            }

            foreach (Loan credit in pProject.Credits)
            {
                _creditManager.Add(credit, pProject.Id, pSqlTransac);
            }
            return pProject.Id;
        }

        private void _AddFollowUp(FollowUp pUp, int pId, SqlTransaction pTransac)
        {
            const string q = @"INSERT INTO [FollowUp] ([project_id],[year],[CA],[Jobs1],[Jobs2],[PersonalSituation],[activity]
                ,[comment]) VALUES(@projectId,@year,@CA,@jobs1,@jobs2,@personalSituation,@activity,@comment)
                SELECT SCOPE_IDENTITY()";

            using (OpenCbsCommand c = new OpenCbsCommand(q, pTransac.Connection, pTransac))
            {
                c.AddParam("@projectId", pId);
                c.AddParam("@year", pUp.Year);
                c.AddParam("@CA", pUp.CA);
                c.AddParam("@jobs1", pUp.Jobs1);
                c.AddParam("@jobs2", pUp.Jobs2);
                c.AddParam("@personalSituation", pUp.PersonalSituation);
                c.AddParam("@activity", pUp.Activity);
                c.AddParam("@comment", pUp.Comment);
                pUp.Id = Convert.ToInt32(c.ExecuteScalar());
            }
        }

 public Project SelectProjectByCode(string code)
 {
     const string q = @"SELECT id FROM dbo.Projects
     WHERE code = @code";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@code", code);
         object val = c.ExecuteScalar();
         return null == val ? null : SelectProject(Convert.ToInt32(val));
     }
 }

 public OCurrency GetTellerBalance(Teller teller)
 {
     using (SqlConnection conn = GetConnection())
     {
         using (var c = new OpenCbsCommand("GetTellerBalance", conn).AsStoredProcedure())
         {
             c.AddParam("@teller_id", teller.Id);
             return decimal.Parse(c.ExecuteScalar().ToString());
         }
     }
 }