欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#实现把查询出的Table作为参数更新到数据库

程序员文章站 2023-09-28 17:40:03
1.ImportData主方法 把传入为object数组类型,按照下标取出对应的参数,此处为Table和Username public object[] ImportData(object[] Param) { DataTable dt = (DataTable)Param[0]; string m ......

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;

        }
    }