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数据库c#教程
中是否存在已有的数据
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;
}
}