Begin End在oracle資料庫中大量操作應用

oracle大量操作：

文法begin end

上代碼：

UPDATE_IS_DEFLUAT

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

前台代碼

 1  Dictionary<string, string> dict = new Dictionary<string, string>(); 2         if (this.gv_Menu.Rows.Count <= 0) { return; } 3         for (int i = 0; i < this.gv_Menu.Rows.Count; i++) 4         { 5             HiddenField hidId = (HiddenField)this.gv_Menu.Rows[i].FindControl("hidId"); 6             HiddenField hidDefault = (HiddenField)this.gv_Menu.Rows[i].FindControl("hidDefault"); 7             CheckBox cbDefault = (CheckBox)this.gv_Menu.Rows[i].FindControl("chDefault"); 8             dict.Add(hidId.Value, cbDefault.Checked ? "T" : "F"); 9         }10 11         bll.UpdateIsDefault(dict)；

BLL

 1  public static string UPDATE_IS_DEFLUAT = " UPDATE NEORULE SET ISDEFAULT=:ISDEFAULT{0} WHERE ID=:ID{0}"; 2       #region 大量操作資料庫 3         /// <summary> 4         /// 大量操作資料庫 5         /// </summary> 6         /// <param name="dict"></param> 7         /// <returns></returns> 8         public bool UpdateIsDefault(Dictionary<string, string> dict) 9         {10             OracleParameter[] completeParms = new OracleParameter[dict.Count * 2];11             StringBuilder query = new StringBuilder();12             int index = 0;13             int i = 0;14             query.Append("BEGIN ");15             foreach (KeyValuePair<string, string> item in dict)16             {17                 completeParms[index] = new OracleParameter(":ISDEFAULT" + i, item.Value);18                 index = index + 1;19                 completeParms[index] = new OracleParameter(":ID"+i,item.Key);20                 index = index + 1;21                 query.Append(string.Format(UPDATE_IS_DEFLUAT,i));22                 query.Append(";");23                 i = i + 1;24             }25             query.Append("End;");26             return dal.UpdateIsDefaultByID(query.ToString(), completeParms);27         }28         #endregion

 1  public bool UpdateIsDefaultByID(string query,OracleParameter[] param) 2         { 3             4             using (OracleConnection con =new OracleConnection(ConnectionString)) 5             { 6                 con.Open(); 7                 using (OracleTransaction tran=con.BeginTransaction()) 8                 { 9                     try10                     {11                         OracleCommand cmd = con.CreateCommand();12                         cmd.CommandText = query;13                         cmd.Transaction = tran;14                         cmd.Parameters.AddRange(param);15                         cmd.ExecuteNonQuery();16                         tran.Commit();17                         return true;18                     }19                     catch (Exception ex)20                     {21                         tran.Rollback();22                         log.Error(ex.Message,ex);23                         return false;24                     }25                 }26                 27             }28         }