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; } }
下一篇: 数组初始