C# OpenCbsCommand.AddParam方法代码示例

 /// <summary>
 /// Add a new picture in the database.<br/>
 /// If pName is null or empty, an automatic name will be generated.
 /// </summary>
 /// <param name="pGroup">Picture group</param>
 /// <param name="pId">Picture Id</param>
 /// <param name="pictureSubId">Picture subId</param>
 /// <param name="pPicture">PNG picture binary data</param>
 /// <param name="pThumbnail">PNG thumbnail picture binary data</param>
 /// <param name="pName">Picture name</param>
 /// <returns></returns>
 public int AddPicture(string pGroup, int pId, int pictureSubId, byte[] pPicture, byte[] pThumbnail, string pName)
 {
     // Find the first available subid
     List<int> subIds = GetPictureSubIds(pGroup, pId);
     int foundPlace = subIds.Count;
     for (int i = 0; i < subIds.Count; i++)
     {
         if (subIds[i] != i)
         {
             foundPlace = i;
             break;
         }
     }
     // Add row
     string q =
         @"INSERT INTO Pictures ([group], [id] ,[subid] ,[picture] ,[thumbnail] ,[name])
         VALUES (@group ,@id ,@subid ,@picture ,@thumbnail ,@name)";
     using (OpenCbsCommand c = new OpenCbsCommand(q, AttachmentsConnection))
     {
         c.AddParam("group", pGroup);
         c.AddParam("id", pId);
         c.AddParam("subid", pictureSubId);
         c.AddParam("picture", pPicture);
         c.AddParam("thumbnail", pThumbnail);
         if (pName.Length < 50)
             c.AddParam("name", pName);
         else
             c.AddParam("name", pName.Substring(0, 49));
         c.ExecuteNonQuery();
     }
     return foundPlace;
 }

        /// <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;
        }

 public void AddPublicHoliday(DictionaryEntry entry)
 {
     const string sqlText = "INSERT INTO [PublicHolidays]([date], [name]) VALUES(@date, @name)";
     using(SqlConnection connection = GetConnection())
     using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
     {
         cmd.AddParam("@date",Convert.ToDateTime(entry.Key));
         cmd.AddParam("@name", entry.Value.ToString());
         cmd.ExecuteNonQuery();
     }
 }

 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 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 void AddCollectionItem(int fieldId, string colItem)
 {
     string sqlListText = @"INSERT INTO [AdvancedFieldsCollections]
                             ([field_id], [value])
                            VALUES (@field_id, @col_item)";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand insertCmd = new OpenCbsCommand(sqlListText, conn))
     {
         insertCmd.AddParam("@field_id", fieldId);
         insertCmd.AddParam("@col_item", colItem);
         insertCmd.ExecuteNonQuery();
     }
 }

 public void AddPaymentMethodToBranch(PaymentMethod paymentMethod)
 {
     const string q =
         @"INSERT INTO LinkBranchesPaymentMethods (branch_id, payment_method_id, account_id)
                                     VALUES (@branch_id, @payment_method_id, @account_id)";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@branch_id", paymentMethod.Branch.Id);
         c.AddParam("@payment_method_id", paymentMethod.Id);
         c.AddParam("@account_id", paymentMethod.Account.Id);
         c.ExecuteNonQuery();
     }
 }

 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 void AddAllowedItem(int pRoleId, int pActionId, bool pAllowed)
        {
            const string q = @"INSERT INTO AllowedRoleActions (action_item_id, role_id, allowed)
                                        VALUES (@actionId, @roleId, @allowed)";

            using (SqlConnection conn = GetConnection())
            {
                using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
                {
                    c.AddParam("@allowed", pAllowed);
                    c.AddParam("@actionId", pActionId);
                    c.AddParam("@roleId", pRoleId);
                    c.ExecuteNonQuery();
                }
            }
        }

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

        public int AddUser(User pUser)
        {
            const string sqlText = @"INSERT INTO [Users] (
                                       [deleted],
                                       [role_code],
                                       [user_name],
                                       [user_pass],
                                       [first_name],
                                       [last_name],
                                       [mail],
                                       [sex],
                                       [phone])
                                     VALUES(
                                       @deleted,
                                       @roleCode,
                                       @username,
                                       @userpass,
                                       @firstname,
                                       @lastname,
                                       @mail,
                                       @sex,
                                       @phone)
                                     SELECT SCOPE_IDENTITY()";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, conn))
            {
                sqlCommand.AddParam("@deleted", false);
                SetUser(sqlCommand, pUser);
                pUser.Id = int.Parse(sqlCommand.ExecuteScalar().ToString());
                SaveUsersRole(pUser.Id, pUser.UserRole.Id);
            }
            return pUser.Id;
        }

        /// <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 Teller Add(Teller teller, SqlTransaction t)
        {
            const string sqlText =
                @"INSERT INTO dbo.Tellers (name, [desc], account_id, branch_id, user_id, currency_id,
                                            amount_min, amount_max, deposit_amount_min, deposit_amount_max, withdrawal_amount_min, withdrawal_amount_max)
                                   VALUES (@name, @desc, @account_id, @branch_id, @user_id, @currency_id,
                                            @amount_min, @amount_max, @deposit_amount_min, @deposit_amount_max, @withdrawal_amount_min, @withdrawal_amount_max)
                                            SELECT SCOPE_IDENTITY()";
            using (var c = new OpenCbsCommand(sqlText, t.Connection, t))
            {

                c.AddParam("@name", teller.Name);
                c.AddParam("@desc", teller.Description);

                if (teller.Branch != null)
                    c.AddParam("@branch_id", teller.Branch.Id);
                else
                    c.AddParam("@branch_id", null);

                if (teller.Account != null)
                    c.AddParam("@account_id", teller.Account.Id);
                else
                    c.AddParam("@account_id", null);

                if (teller.User != null)
                    c.AddParam("@user_id", teller.User.Id);
                else
                    c.AddParam("@user_id", null);

                if (teller.Currency != null)
                    c.AddParam("@currency_id", teller.Currency.Id);
                else
                    c.AddParam("@currency_id", null);

                c.AddParam("@amount_min", teller.MinAmountTeller);
                c.AddParam("@amount_max", teller.MaxAmountTeller);
                c.AddParam("@deposit_amount_min", teller.MinAmountDeposit);
                c.AddParam("@deposit_amount_max", teller.MaxAmountDeposit);
                c.AddParam("@withdrawal_amount_min", teller.MinAmountWithdrawal);
                c.AddParam("@withdrawal_amount_max", teller.MaxAmountWithdrawal);

                teller.Id = Convert.ToInt32(c.ExecuteScalar());
            }
            return teller;
        }

        public bool CreateMFI(MFI pMFI)
        {
            if (SelectMFI().Login == null)
            {
                string sqlText = "INSERT INTO [MFI] ([name], [login], [password]) VALUES(@name,@login,@password)";

                using (SqlConnection connection = GetConnection())
                using (OpenCbsCommand cmd = new OpenCbsCommand(sqlText, connection))
                {
                    cmd.AddParam("@name", pMFI.Name);
                    cmd.AddParam("@login", pMFI.Login);
                    cmd.AddParam("@password",  pMFI.Password);
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            return false;
        }

        public void DeleteInstallmentType(InstallmentType installmentType)
        {
            const string q = @"DELETE FROM [InstallmentTypes] WHERE id = @id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                c.AddParam("@id", installmentType.Id);
                c.ExecuteNonQuery();
            }
        }