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;

        }
    }