学步园

oracle批量操作：

语法begin end

上代码：

  public static string UPDATE_IS_DEFLUAT = " UPDATE NEORULE SET ISDEFAULT=:ISDEFAULT{0} WHERE ID=:ID{0}";

 Dictionary<string, string> dict = new Dictionary<string, string>();
        if (this.gv_Menu.Rows.Count <= 0) { return; }
        for (int i = 0; i < this.gv_Menu.Rows.Count; i++)
        {
            HiddenField hidId = (HiddenField)this.gv_Menu.Rows[i].FindControl("hidId");
            HiddenField hidDefault = (HiddenField)this.gv_Menu.Rows[i].FindControl("hidDefault");
            CheckBox cbDefault = (CheckBox)this.gv_Menu.Rows[i].FindControl("chDefault");
            dict.Add(hidId.Value, cbDefault.Checked ? "T" : "F");
        }

        bll.UpdateIsDefault(dict)；

 public static string UPDATE_IS_DEFLUAT = " UPDATE NEORULE SET ISDEFAULT=:ISDEFAULT{0} WHERE ID=:ID{0}";
      #region 批量操作数据库
        /// <summary>
        /// 批量操作数据库
        /// </summary>
        /// <param name="dict"></param>
        /// <returns></returns>
        public bool UpdateIsDefault(Dictionary<string, string> dict)
        {
            OracleParameter[] completeParms = new OracleParameter[dict.Count * 2];
            StringBuilder query = new StringBuilder();
            int index = 0;
            int i = 0;
            query.Append("BEGIN ");
            foreach (KeyValuePair<string, string> item in dict)
            {
                completeParms[index] = new OracleParameter(":ISDEFAULT" + i, item.Value);
                index = index + 1;
                completeParms[index] = new OracleParameter(":ID"+i,item.Key);
                index = index + 1;
                query.Append(string.Format(UPDATE_IS_DEFLUAT,i));
                query.Append(";");
                i = i + 1;
            }
            query.Append("End;");
            return dal.UpdateIsDefaultByID(query.ToString(), completeParms);
        }
        #endregion

 public bool UpdateIsDefaultByID(string query,OracleParameter[] param)
        {
           
            using (OracleConnection con =new OracleConnection(ConnectionString))
            {
                con.Open();
                using (OracleTransaction tran=con.BeginTransaction())
                {
                    try
                    {
                        OracleCommand cmd = con.CreateCommand();
                        cmd.CommandText = query;
                        cmd.Transaction = tran;
                        cmd.Parameters.AddRange(param);
                        cmd.ExecuteNonQuery();
                        tran.Commit();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        log.Error(ex.Message,ex);
                        return false;
                    }
                }
                
            }
        }