1.ImportData主方法
把傳入為object陣列型別，按照下標取出對應的引數，此處為Table和Username
public object[] ImportData(object[] Param) { DataTable dt = (DataTable)Param[0]; string msg1 = "", msg2 = "", msg3 = ""; Hashtable ht = new Hashtable(); username = Param[1].ToString();//操作人工號 ExecutionResult result = new ExecutionResult(); result.Message = ""; dbTools = new InfoLightDBTools(this.ClientInfo, this.GetClientInfo(ClientInfoType.LoginDB).ToString()); string SN = ""; string ITEM_NO = ""; string FAILURE = ""; string CORRECT = ""; string ISSUE = ""; string STATUS = ""; string ISSUE_TYPE = ""; for (int i = 0; i < dt.Rows.Count; i++) { //需要進行操作的列 SN = dt.Rows[i]["SERIAL_NUMBER"].ToString(); ITEM_NO = dt.Rows[i]["ITEM_NO"].ToString(); FAILURE = dt.Rows[i]["FAILURE_ANALYSE"].ToString(); CORRECT = dt.Rows[i]["CORRECT_ACTION"].ToString(); ISSUE = dt.Rows[i]["ISSUE_OWNER"].ToString(); STATUS = dt.Rows[i]["STATUS"].ToString(); ISSUE_TYPE = dt.Rows[i]["ISSUE_TYPE"].ToString(); if (CheckFA(FAILURE)) { #region Oprator if (!CheckValue(SN, ITEM_NO))//根據Key進行Check資料是否存在，不存在進行Insert，存在則進行Update { result = DoInsert(SN, ITEM_NO, FAILURE, CORRECT, ISSUE, ISSUE_TYPE, STATUS);//Insert操作 if (!result.Status) { msg1 += "在第" + (i + 1).ToString() + "行,SERIAL_NUMBER: " + SN + " ITEM_NO:" + ITEM_NO + " Insert資料時失敗:" + result.Message; result.Status = false; continue; } } else { result = DoUpdate(SN, ITEM_NO, FAILURE, CORRECT, ISSUE, ISSUE_TYPE, STATUS);//Update 操作 if (!result.Status) { msg2 = "在第" + (i + 1).ToString() + "行,SERIAL_NUMBER: " + SN + " ITEM_NO:" + ITEM_NO + " Update資料時失敗:" + result.Message; result.Status = false; continue; } } #endregion } else { msg3 += "Excel中第" + (i + 1).ToString() + "行的FAILURE_ANALYSE值為空!"; result.Status = false; continue; } } result.Message = ""; if (!string.IsNullOrEmpty(msg1)) { result.Message += " Insert Error: " + msg1; } if (!string.IsNullOrEmpty(msg2)) { result.Message += " Update Error: " + msg2; } if (!string.IsNullOrEmpty(msg3)) { result.Message += msg3; } if (result.Message == "") { return new object[] { 0, "OK", result.Message }; } else { return new object[] { 0, "NG", result.Message }; } }
2.CheckValue
根據主鍵進行Check資料庫中是否存在已有的資料
public bool CheckValue(string sn, string item_no) { dbTools = new InfoLightDBTools(this.ClientInfo, this.GetClientInfo(ClientInfoType.LoginDB).ToString()); DataTable dt = new DataTable(); DataSet ds = new DataSet(); Hashtable ht = new Hashtable(); ExecutionResult result = new ExecutionResult(); string sql = @" SELECT * FROM SFISM4.R_FAILURE_ANALYSIS_T T where T.Serial_Number = :sn and T.Item_No = :item_no "; ht.Clear(); ht.Add("sn", sn); ht.Add("item_no", item_no); result = this.dbTools.ExecuteUpdateHt(sql, ht); ds = (DataSet)this.dbTools.ExecuteQueryDSHt(sql, ht).Anything; if (ds != null && ds.Tables[0].Rows.Count > 0) { return true; } else { return false; } }
3.DoInsert
根主鍵Check的內容為空則進行Insert
public ExecutionResult DoInsert(string sn, string item_no, string failure, string correct, string issue, string issue_type, string status) { Hashtable ht = new Hashtable(); ExecutionResult result = new ExecutionResult(); this.dbTools = new InfoLightDBTools(this.ClientInfo, this.GetClientInfo(ClientInfoType.LoginDB).ToString()); string sql = @" insert into SFISM4.R_FAILURE_ANALYSIS_T (SERIAL_NUMBER, ITEM_NO, FAILURE_ANALYSE, CORRECT_ACTION, ISSUE_OWNER, ISSUE_TYPE, STATUS, CREATE_USER, CREATE_TIME) values (:sn, :item_no, :failure, :correct, :issue, :issue_type, :status, :username, sysdate) "; ht.Clear(); ht.Add("SN", sn); ht.Add("item_no", item_no); ht.Add("failure", failure); ht.Add("correct", correct); ht.Add("issue", issue); ht.Add("issue_type", issue_type); ht.Add("status", status); ht.Add("username", username); result = this.dbTools.ExecuteUpdateHt(sql, ht); return result; }
4.DoUpdate
根據主鍵Check的內容非空則進行Update
public ExecutionResult DoUpdate(string sn, string item_no, string failure, string correct, string issue, string issue_type, string status) { Hashtable ht = new Hashtable(); ExecutionResult result = new ExecutionResult(); this.dbTools = new InfoLightDBTools(this.ClientInfo, this.GetClientInfo(ClientInfoType.LoginDB).ToString()); string sql = @" update SFISM4.R_FAILURE_ANALYSIS_T T set T.Failure_Analyse = :failure, T.Correct_Action = :correct, T.Issue_Owner = :issue, T.ISSUE_TYPE = :issue_type, T.Status = :status, T.Update_User = :username, T.Update_Time = sysdate, T.STATE_FLAG = 0 where T.Serial_Number = :sn and T.ITEM_NO = :item_no "; ht.Clear(); ht.Add("SN", sn); ht.Add("item_no", item_no); ht.Add("failure", failure); ht.Add("correct", correct); ht.Add("issue", issue); ht.Add("issue_type", issue_type); ht.Add("status", status); ht.Add("username", username); result = this.dbTools.ExecuteUpdateHt(sql, ht); return result; } }
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/79133.html
標籤：C#
上一篇：要用C#做一個大概這樣的列印系統(桌面應用程式)，主要列印PDF檔案，需要呼叫的是列印機驅動的API介面，還是要呼叫“用于打開PDF檔案的應用程式的API介面，