C ා to update the query table as a parameter to the database

Time:2020-7-13

1. Main method of importdata

Put the input as the object array type, and take out the corresponding parameters according to the subscript. Here are table and username

public object[] ImportData(object[] Param)
        {
            DataTable dt = (DataTable)Param[0];
            string msg1 = "", msg2 = "", msg3 = "";
            Hashtable ht = new Hashtable();
            Username = param [1]. Tostring(); // operator number
            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++)
            {
                //The columns that need to be operated on
                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)) // check whether the data exists according to the key, insert if it does not exist, and update if it exists
                    {                               
                        result = DoInsert(SN, ITEM_ NO, FAILURE, CORRECT, ISSUE, ISSUE_ Type, status); // insert operation
                        if (!result.Status)
                        {
                            MSG1 + = in "+ (I + 1). Tostring() +" line, serial_ NUMBER: " + SN + " ITEM_ NO:" + ITEM_ No + "insert data failed"+ result.Message ;
                            result.Status = false;
                            continue;
                        }
                    }
                    else
                    {
                        result = DoUpdate(SN, ITEM_ NO, FAILURE, CORRECT, ISSUE, ISSUE_ Type, status); // update operation
                        if (!result.Status)
                        {
                            Msg2 = "in line" + (I + 1). Tostring() + ", serial_ NUMBER: " + SN + " ITEM_ NO:" + ITEM_ No + "failed to update data:+ result.Message ;
                            result.Status = false;
                            continue;
                        }
                    }
                    #endregion
                }
                else
                {
                    Msg3 + = failure of row "+ (I + 1). Tostring() +" in Excel_ The analyze value is null! ";
                    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 whether there is any existing data in the database according to the primary key

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

Insert if the content of the root primary key check is empty

 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

Update according to the content of check in the primary key

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;

        }
    }

Recommended Today

Monkey patch monkey patch programming method and its application in Ruby

What ismonkey patch (Monkey Patch)? In a dynamic language, functions are added and changed without modifying the source code. Purpose of using monkey patch:1. Additional function2. Function change3. Fix program errors4. Add a hook to execute some other processing while executing a method, such as printing logs, realizing AOP, etc,5. Cache, when the amount of […]