C#實現把查詢出的Table作為參數更新到資料庫

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;

        }
    }