public DataTable QuerySurveyRecMain_1(ArrayList ParameterList) { try { DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB); return dbo.QuerySurveyRecMain_1(ParameterList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 基本表單刪除BCO /// </summary> /// <param name="ParameterList">刪除變數</param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> /// <returns>回傳影響筆數</returns> public void DeleteMultiMasterAndDetail(ArrayList ParameterList, DataTable dtMaster, DbTransaction RootDBT ) { bool IsRootTranscation = false; try { MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion #region 逐筆刪除MasterAndDetail by every master data foreach (DataRow drMaster in dtMaster.Rows) { DeleteMasterAndDetail(ParameterList, drMaster, DBT); } #endregion #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
/// <summary> /// 基本表單刪除BCO /// </summary> /// <param name="ParameterList">刪除變數</param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> /// <returns>回傳影響筆數</returns> public void DeleteMasterAndDetail(ArrayList ParameterList, DataRow drMaster, DbTransaction RootDBT ) { bool IsRootTranscation = false; try { MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion int rtnValue = dbo.doDelete(ParameterList, drMaster, DBT); if (rtnValue > 0) { DeleteDetailByPID(drMaster["ID"].ToString(), ParameterList[0].ToString(), DBT); } #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
/// <summary> /// 基本表單更新BCO /// </summary> /// <param name="ParameterList">更新變數</param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> /// <returns>回傳成功與否</returns> public bool UpdateMaster(ArrayList ParameterList, DataRow drMaster, DbTransaction RootDBT) { bool IsRootTranscation = false; bool bResult = false; try { //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion MKT_MarketSurveyRecordsDBO MainDBO = new MKT_MarketSurveyRecordsDBO(ref USEDB); MainDBO.doUpdate(ParameterList, drMaster, DBT); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion bResult = true; return bResult; } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }
public ArrayList UpdateTmpToDB(string s_LoginUser) { ArrayList arl_Return = new ArrayList(); ArrayList ParameterList = new ArrayList(); int iRight = 0; int iWrong = 0; try { MKT_MarketSurveyRecordsDBO DBO = new MKT_MarketSurveyRecordsDBO(ref USEDB); //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); ParameterList.Clear(); ParameterList.Add(s_LoginUser); ParameterList.Add(DBNull.Value); ParameterList.Add("1"); ParameterList.Add(999999); DataTable dtTmp = DBO.doQueryTmp(ParameterList); if (dtTmp.Rows.Count > 0) { string strZ_O = string.Empty; string strCHAN_NO = string.Empty; string strSTORE = string.Empty; string strSURVEY_DATE = string.Empty; string strNow = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"); for (int i = 0; i < dtTmp.Rows.Count; i++) { if ((dtTmp.Rows[i]["Z_O"].ToString() != strZ_O) && (dtTmp.Rows[i]["CHAN_NO"].ToString() != strCHAN_NO) && (dtTmp.Rows[i]["STORE"].ToString() != strSTORE) && (dtTmp.Rows[i]["SURVEY_DATE"].ToString() != strSURVEY_DATE)) { //更新主檔,如果不是同一個主檔的key(一個excel可能包含不同主檔的資料),才再進行主檔更新,沒有需要一直更新重複的資料 ParameterList.Clear(); if (dtTmp.Rows[i]["Z_O_ALL"].ToString() == "全部") ParameterList.Add("1"); //0.V_OLD_Z_O else ParameterList.Add(dtTmp.Rows[i]["Z_O"].ToString()); //0.V_OLD_Z_O ParameterList.Add(dtTmp.Rows[i]["CHAN_NO"].ToString()); //1.V_OLD_CHAN_NO ParameterList.Add(dtTmp.Rows[i]["STORE"].ToString()); //2.V_OLD_STORE ParameterList.Add(dtTmp.Rows[i]["SURVEY_DATE"].ToString()); //3.V_OLD_SURVEY_DATE ParameterList.Add(s_LoginUser); //4.V_NEW_UPDATEUID ParameterList.Add(strNow); //5.V_NEW_UPDATEUDATE DBO.doTempToDB_MAIN(ParameterList, DBT); } //更新明細檔 strZ_O = dtTmp.Rows[i]["Z_O"].ToString(); strCHAN_NO = dtTmp.Rows[i]["CHAN_NO"].ToString(); strSTORE = dtTmp.Rows[i]["STORE"].ToString(); strSURVEY_DATE = dtTmp.Rows[i]["SURVEY_DATE"].ToString(); ParameterList.Clear(); if (dtTmp.Rows[i]["Z_O_ALL"].ToString() == "全部") ParameterList.Add("1"); //0.V_OLD_Z_O else ParameterList.Add(strZ_O); //0.V_OLD_Z_O ParameterList.Add(strCHAN_NO); //1.V_OLD_CHAN_NO ParameterList.Add(strSTORE); //2.V_OLD_STORE ParameterList.Add(strSURVEY_DATE); //3.V_OLD_SURVEY_DATE ParameterList.Add(dtTmp.Rows[i]["ITEM"].ToString()); //4.V_OLD_ITEM ParameterList.Add(dtTmp.Rows[i]["PERIOD"].ToString()); //5.V_OLD_PERIOD ParameterList.Add(dtTmp.Rows[i]["STOCKS_QTY"].ToString()); //6.V_STOCKS_QTY ParameterList.Add(dtTmp.Rows[i]["MEMO"].ToString()); //7.V_MEMO ParameterList.Add(s_LoginUser); //8.V_NEW_UPDATEUID ParameterList.Add(strNow); //9.V_NEW_UPDATEUDATE DBO.doTempToDB_DTL(ParameterList, DBT); iRight++; } } //獨立呼叫Transcation成立 DBT.Commit(); arl_Return.Add("TRUE"); arl_Return.Add(dtTmp.Rows.Count); arl_Return.Add(iRight); arl_Return.Add(iWrong); } catch (Exception ex) { if (DBT != null) DBT.Rollback();//獨立呼叫Transcation失敗 arl_Return.Add("FALSE"); arl_Return.Add(ex.Message); } finally { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) Conn.Close(); } return arl_Return; }
public ArrayList FileToTmp(DataTable dtExcel, DateTime d_CreateDate, string s_LoginUser, string s_AP_FileName) { ArrayList ParameterList = new ArrayList(); ArrayList arl_Return = new ArrayList(); int i_FileToTemp_Total_count = 0; int i_FileToTemp_Right_count = 0; int i_FileToTemp_Wrong_count = 0; try { MKT_MarketSurveyRecordsDBO DBO = new MKT_MarketSurveyRecordsDBO(ref USEDB); Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); ParameterList.Clear(); ParameterList.Add(s_LoginUser); DBO.doDeleteTmp(ParameterList, DBT); foreach (DataRow dr in dtExcel.Rows) { string V_CREATEDATE = d_CreateDate.ToString("yyyy/MM/dd"); string V_CREATEUID = s_LoginUser; string V_Z_O_ALL = dr["Z_O_ALL"].ToString().Trim(); string V_Z_O = dr["Z_O"].ToString().Trim(); string V_Z_O_NAME = dr["Z_O_NAME"].ToString().Trim(); string V_SURVEY_DATE = dr["SURVEY_DATE"].ToString().Trim(); string V_ITEM = dr["ITEM"].ToString().Trim(); string V_ITEM_NAME = dr["ITEM_NAME"].ToString().Trim(); string V_PERIOD = dr["PERIOD"].ToString().Trim(); string V_CHAN_NO = dr["CHAN_NO"].ToString().Trim(); string V_CHAN_NAME = dr["CHAN_NAME"].ToString().Trim(); string V_STORE = dr["STORE"].ToString().Trim(); string V_STORE_NAME = dr["STORE_NAME"].ToString().Trim(); string V_STOCKS_QTY = dr["STOCKS_QTY"].ToString().Trim(); string V_MEMO = dr["MEMO"].ToString().Trim(); string V_ERROR = dr["ERROR"].ToString().Trim(); ParameterList.Clear(); ParameterList.Add(V_CREATEDATE); ParameterList.Add(V_CREATEUID); ParameterList.Add(V_Z_O); ParameterList.Add(V_Z_O_NAME); ParameterList.Add(V_SURVEY_DATE); ParameterList.Add(V_ITEM); ParameterList.Add(V_ITEM_NAME); ParameterList.Add(V_PERIOD); ParameterList.Add(V_CHAN_NO); ParameterList.Add(V_CHAN_NAME); ParameterList.Add(V_STORE); ParameterList.Add(V_STORE_NAME); ParameterList.Add(V_STOCKS_QTY); ParameterList.Add(V_MEMO); ParameterList.Add(V_ERROR); if (V_ERROR != "") ParameterList.Add("2"); else ParameterList.Add("1"); ParameterList.Add("1"); ParameterList.Add(SubStr(s_AP_FileName, 0, 50)); ParameterList.Add(V_Z_O_ALL); DBO.doInsertTMP(ParameterList, DBT); } DBT.Commit(); bool bCheck = false; DataRow[] FindRows = dtExcel.Select("ERROR='' or ERROR is null"); i_FileToTemp_Total_count = dtExcel.Rows.Count; i_FileToTemp_Right_count = FindRows.Length; i_FileToTemp_Wrong_count = i_FileToTemp_Total_count - i_FileToTemp_Right_count; if (i_FileToTemp_Total_count == i_FileToTemp_Right_count) bCheck = true; arl_Return.Add("TRUE"); arl_Return.Add(bCheck); arl_Return.Add(i_FileToTemp_Total_count); arl_Return.Add(i_FileToTemp_Right_count); arl_Return.Add(i_FileToTemp_Wrong_count); } catch (Exception ex) { if (DBT != null) DBT.Rollback(); arl_Return.Add("FALSE"); arl_Return.Add(ex.Message); } return arl_Return; }
public DataTable CheckImportExist(ArrayList ParameterList) { try { DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB); return dbo.doCheckImportExist(ParameterList); } catch (Exception ex) { throw ex; } }
/// <summary> /// 測試SP交易失敗 /// </summary> /// <param name="ParameterList">變數</param> /// <param name="RootDBT">是否有主交易,無主交易輸入null</param> public void TestTranscation(ArrayList ParameterList1, ArrayList ParameterList2, ArrayList ParameterList3, DbTransaction RootDBT ) { bool IsRootTranscation = false; try { DBO.MKT_MarketSurveyRecordsDBO dbo = new MKT_MarketSurveyRecordsDBO(ref USEDB); //判斷是否有傳入Root Transcation IsRootTranscation = (RootDBT == null) ? true : false; #region 啟動交易或指定RootTranscation if (IsRootTranscation) { //獨立呼叫啟動Transcation Conn = USEDB.CreateConnection(); Conn.Open(); DBT = Conn.BeginTransaction(); } else { DBT = RootDBT; } #endregion //dbo.doCreate(ParameterList1, DBT); //dbo.doUpdate(ParameterList2, DBT); //dbo.doDelete(ParameterList3, DBT); throw new Exception("交易失敗"); #region 交易成功 if (IsRootTranscation) { //獨立呼叫Transcation成立 DBT.Commit(); } #endregion } catch (Exception ex) { #region 交易失敗 if (IsRootTranscation) { //獨立呼叫Transcation失敗 DBT.Rollback(); } #endregion throw ex; } finally { #region 判斷是否關閉交易連線 if (IsRootTranscation) { //獨立呼叫Transcation,關閉連線 if (Conn.State == ConnectionState.Connecting) { Conn.Close(); } } #endregion } }