public ActionResult Edit(MeetMaintenanceModels meetMaintenanceModels) { string sMsg = ""; string retval = ""; bool bretsuc = false; if (Session["userid"] == null) { return Json(new { result = 0, errmsg = "登陸超時請重新登陸!" }); } string sysid = meetMaintenanceModels.sysid;// Request.RequestContext.RouteData.Values["id"].ToString(); string sql = "select * from tb_meet where sys_id=@sys_id"; DataSet ds = DBCommon.SqlHelper.ExecuteDataset(DBCommon.SqlHelper.ConntionString, CommandType.Text, sql , new SqlParameter("@sys_id", meetMaintenanceModels.sysid)); if (ds.Tables[0].Rows.Count > 0) { SqlConnection conn=new SqlConnection(); conn.ConnectionString = DBCommon.SqlHelper.ConntionString; conn.Open(); SqlTransaction sqlTransaction = conn.BeginTransaction(); //刪除明細 try { //刪除所有事項列表 sql = "delete from tb_meet_detail where mt_id=@mt_id"; DBCommon.SqlHelper.ExecuteNonQuery(sqlTransaction, CommandType.Text, sql , new SqlParameter("@mt_id", meetMaintenanceModels.sysid)); //刪除會議參與人員 sql = "delete from tb_meet_joinlist where mt_id=@mt_id and type in(0,1,2,3)"; DBCommon.SqlHelper.ExecuteNonQuery(sqlTransaction, CommandType.Text, sql , new SqlParameter("@mt_id", meetMaintenanceModels.sysid)); //更新tb_meet數據 sql = "update tb_meet set mt_dte=@mt_dte,mt_time=@mt_time,addr=@addr,title=@title,modi_by=@modi_by,modi_dte=getdate(),last_ip=@last_ip where sys_id=@sys_id and is_del=0"; DBCommon.SqlHelper.ExecuteNonQuery(sqlTransaction, CommandType.Text, sql , new SqlParameter("@sys_id", meetMaintenanceModels.sysid) , new SqlParameter("@mt_dte", meetMaintenanceModels.date) , new SqlParameter("@mt_time", meetMaintenanceModels.time) , new SqlParameter("@addr", meetMaintenanceModels.addr) , new SqlParameter("@title", meetMaintenanceModels.name) , new SqlParameter("@modi_by", Session["userid"].ToString()) , new SqlParameter("@last_ip", GetIP())); //插入明細 bretsuc = AddDetail(meetMaintenanceModels, conn, sqlTransaction, out sMsg); sqlTransaction.Commit(); sMsg = null; retval = "1"; } catch (Exception e) { sqlTransaction.Rollback(); retval = "0"; sMsg = e.Message; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); conn.Dispose(); } } } else { sMsg = "記錄不存在或已被刪除!"; retval = "0"; } return Json(new { result = retval, errmsg = sMsg }); }
public bool AddDetail(MeetMaintenanceModels meetMaintenanceModels,SqlConnection sqlConnection,SqlTransaction sqlTransaction, out string errMsg) { bool retval = false; errMsg = null; try { //-->插入tb_meet_joinlist參會人員的數據 DataTable dt = new DataTable(); dt.Columns.AddRange(new DataColumn[] { new DataColumn("mt_id", typeof (string)), new DataColumn("wor_num", typeof (string)), new DataColumn("type", typeof (Int16)) }); string[] aJoinLists; //主席 aJoinLists = meetMaintenanceModels.master.Split(','); for (int i = 0; i < aJoinLists.Length; i++) { DataRow r = dt.NewRow(); r["mt_id"] = meetMaintenanceModels.sysid; r["wor_num"] = aJoinLists[i]; r["type"] = 0; dt.Rows.Add(r); } //出席 Array.Clear(aJoinLists, 0, aJoinLists.Length); aJoinLists = meetMaintenanceModels.joins.Split(','); for (int i = 0; i < aJoinLists.Length; i++) { DataRow r = dt.NewRow(); r["mt_id"] = meetMaintenanceModels.sysid; r["wor_num"] = aJoinLists[i]; r["type"] = 1; dt.Rows.Add(r); } //未出席 Array.Clear(aJoinLists, 0, aJoinLists.Length); aJoinLists = meetMaintenanceModels.unjoins.Split(','); for (int i = 0; i < aJoinLists.Length; i++) { DataRow r = dt.NewRow(); r["mt_id"] = meetMaintenanceModels.sysid; r["wor_num"] = aJoinLists[i]; r["type"] = 2; dt.Rows.Add(r); } //-->共享列表 Array.Clear(aJoinLists, 0, aJoinLists.Length); aJoinLists = meetMaintenanceModels.sharelist.Split(','); for (int i = 0; i < aJoinLists.Length; i++) { DataRow r = dt.NewRow(); r["mt_id"] = meetMaintenanceModels.sysid; r["wor_num"] = aJoinLists[i]; r["type"] = 3; dt.Rows.Add(r); } retval=DBCommon.SqlHelper.BulkToDb(sqlConnection, sqlTransaction, dt, "tb_meet_joinlist", out errMsg); //-->插入tb_meet_detail的數據 dt = new DataTable(); dt.Columns.AddRange(new DataColumn[] { new DataColumn("mt_id", typeof (string)), new DataColumn("dtl_id", typeof (string)), new DataColumn("detail", typeof (string)) }); List<MeetListContent> lists = meetMaintenanceModels.listitems; foreach (MeetListContent l in lists) { DataRow r = dt.NewRow(); r["mt_id"] = meetMaintenanceModels.sysid; r["dtl_id"] = l.listsysid; r["detail"] = l.content; dt.Rows.Add(r); } retval=DBCommon.SqlHelper.BulkToDb(sqlConnection,sqlTransaction ,dt, "tb_meet_detail",out errMsg); //--< //--< return true; } catch (Exception e) { errMsg = e.Message; throw; //return false; } return retval; }
public ActionResult Action(MeetMaintenanceModels meetMaintenanceModels) { //空缺 //安全判斷是否有空值傳入 //暫時未做驗證 if (!ValidInput()) { return Json(new {result = "0"}); } if (Session["userid"] == null) { return Json(new {result = "0", errmsg = "登陸超時請重新登陸!"}); } StringBuilder sbMeetSql = new StringBuilder(); //-->數據插入操作 string ip = GetIP(); int isshare = 0; isshare = string.IsNullOrEmpty(meetMaintenanceModels.sharelist) || meetMaintenanceModels.sharelist=="" ? 0 : 1; string sMsg = "", retSuc="0"; bool bResult= true; SqlConnection conn=new SqlConnection(DBCommon.SqlHelper.ConntionString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(); try { //-->插入tb_meet數據 sbMeetSql.Append( "insert into tb_meet(sys_id,mt_id,title,mt_dte,mt_time,addr,create_by,last_ip,is_share)"); sbMeetSql.Append("values(@sys_id,@mt_id,@title,@mt_dte,@mt_time,@addr,@create_by,@last_ip,@is_share)"); int r1 = DBCommon.SqlHelper.ExecuteNonQuery(DBCommon.SqlHelper.ConntionString, CommandType.Text, sbMeetSql.ToString() , new SqlParameter("@sys_id", meetMaintenanceModels.sysid) , new SqlParameter("@mt_id", meetMaintenanceModels.id) , new SqlParameter("@title", meetMaintenanceModels.name) , new SqlParameter("@mt_dte", string.Format(meetMaintenanceModels.date, "yyyy-MM-dd")) , new SqlParameter("@mt_time", meetMaintenanceModels.time) , new SqlParameter("@addr", meetMaintenanceModels.addr) , new SqlParameter("@create_by", Session["userid"].ToString()) , new SqlParameter("@last_ip", ip) , new SqlParameter("@is_share", isshare)); //--< //以下插入明細數據 bResult = AddDetail(meetMaintenanceModels, conn, tran, out sMsg); //數據插入操作--< tran.Commit(); retSuc = bResult == true ? "1" : "0"; //retSuc = "1"; } catch (Exception e) { tran.Rollback(); retSuc = "0"; sMsg = e.Message; } finally { if (conn.State == ConnectionState.Open) { conn.Close(); } } return Json(new {result = retSuc,errmsg=sMsg}); }