C# Manager.OpenCbsCommand类代码示例

 protected void DeleteDatasFromTable(string tableName, SqlTransaction transaction)
 {
     const string deleteSql = "DELETE FROM {0}";
     string query = string.Format(deleteSql, tableName);
     using (OpenCbsCommand command = new OpenCbsCommand(query, transaction.Connection, transaction))
         command.ExecuteNonQuery();
 }

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

        /// <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 RunSQL(string sqlText, SqlTransaction tran)
 {
     using (OpenCbsCommand sqlCommand = new OpenCbsCommand(sqlText, tran.Connection, tran))
     {
         sqlCommand.ExecuteNonQuery();
     }
 }

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

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

 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(District pDistrict)
 {
     const string q = "INSERT INTO [Districts]([name], [province_id],[deleted]) VALUES(@name,@provinceId,0) SELECT SCOPE_IDENTITY()";
     using (SqlConnection conn = GetConnection())
     using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
     {
         c.AddParam("@name", pDistrict.Name);
         c.AddParam("@provinceId", pDistrict.Province.Id);
         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 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 EditInstallmentType(InstallmentType installmentType)
        {
            const string q = @"UPDATE [InstallmentTypes] SET [name] = @name, [nb_of_days] = @days, [nb_of_months] = @months
                                     WHERE id = @id";

            using (SqlConnection conn = GetConnection())
            using (OpenCbsCommand c = new OpenCbsCommand(q, conn))
            {
                SetInstallmentType(c, installmentType);
                c.AddParam("@id", installmentType.Id);
                c.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());
     }
 }