public void ReadTxtInsert() { threadStartEvent.Invoke(new object(), new EventArgs());//线程开始 string[] infotablename = FilePath.Split('/'); string infodate = ClsCom.GetDateForArr(infotablename[infotablename.Length - 1]); LHSM.Logs.Log.Logger.Error("表" + infotablename[infotablename.Length - 1] + "开始读取!"); List <string> txt = ReadTxtLine(FilePath); txt = txt.FindAll(d => d != "");//zhangping修改txt文件 去空行 if (txt != null && txt.Count > 1) { string[] colList = Regex.Split(txt[0], ClsReadTxtInfo.Separator, RegexOptions.IgnoreCase); string str_insert = "INSERT INTO " + TableName.ToUpper() + " ( "; string str_del = "DELETE FROM " + TableName.ToUpper(); string[] colPKList = null; Dictionary <string, int> colPKDel = new Dictionary <string, int>(); if (dr != null && dr.Length > 0) { if (dr[0]["TXT_PK"] != null && !string.IsNullOrEmpty(dr[0]["TXT_PK"].ToString())) { if (dr[0]["TXT_PK"].ToString().Contains("|")) { colPKList = dr[0]["TXT_PK"].ToString().Split('|'); } else { colPKList = new string[] { dr[0]["TXT_PK"].ToString() }; } } } string strcol = string.Empty; for (int col = 0; col < colList.Length; col++) { if (colList[col].Contains("/"))//字段带"/"处理 { strcol += '"' + colList[col].ToUpper() + '"' + ","; } else { strcol += colList[col].ToUpper() + ","; } foreach (string colpk in colPKList) { if (colList[col].ToUpper() == colpk.ToUpper()) { colPKDel.Add(colList[col].ToUpper(), col); break; } } } str_insert += strcol + "DLDATE) values"; StringBuilder sbsql = new StringBuilder(); sbsql.Append(" Begin "); bool boolinfo = true; for (int row = 1, startdba = 0; row < txt.Count; row++) { int frist = txt[row].IndexOf(ClsReadTxtInfo.Separator); string strtxt = txt[row].Replace("'", "''"); //替换特殊字符 string[] coldata = Regex.Split(strtxt, ClsReadTxtInfo.Separator, RegexOptions.IgnoreCase); //删除主键sql if (colPKDel != null && colPKDel.Count > 0) { sbsql.Append(str_del + " WHERE "); int countpk = colPKDel.Count; foreach (KeyValuePair <string, int> item in colPKDel) { countpk--; int colPosition = item.Value; if (frist == 0) { colPosition++; } if (countpk > 0) { sbsql.Append(item.Key + "='" + coldata[colPosition] + "' AND "); } else { sbsql.Append(item.Key + "='" + coldata[colPosition] + "';"); } } } //插入sql string colTemp = string.Empty; for (int i = 0; i < coldata.Length; i++) { if (frist == 0 && i == 0) { continue; } if (coldata[i].Contains("-")) { colTemp += "'" + ClsCom.GetNumberForArr(coldata[i]) + "',"; //对于SAP中的491.00-负数形式做处理 } else { colTemp += "'" + coldata[i] + "',"; } } colTemp = colTemp + DateTime.Now.ToString("yyyyMMdd"); sbsql.Append(str_insert + " ( " + colTemp + " ); "); startdba++; if (startdba * coldata.Length >= 50000) { startdba = 0; sbsql.Append(" end; "); //LHSM.Logs.Log.Logger.Error("sql:" + sbsql.ToString()); bool retfp = ClsUtility.ExecuteSqlToDb(sbsql.ToString()); if (!retfp) { boolinfo = false; ClsErrorLogInfo.WriteSapLog("0", "downLoad", TableName.ToUpper(), DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "读取" + TableName.ToUpper() + "表分批插入发生异常:\t\n"); ClsLogInfo.WriteSapLog("0", "downLoad", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "读取" + TableName.ToUpper() + "表分批插入失败!\t\n"); } sbsql.Length = 0; sbsql.Append(" Begin "); } } sbsql.Append(" end; "); if (sbsql.Length > 14) { //LHSM.Logs.Log.Logger.Error("sql:" + sbsql.ToString()); bool ret = ClsUtility.ExecuteSqlToDb(sbsql.ToString()); if (!ret) { boolinfo = false; ClsErrorLogInfo.WriteSapLog("0", "downLoad", TableName.ToUpper(), DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "读取" + TableName.ToUpper() + "表发生异常:\t\n"); ClsLogInfo.WriteSapLog("0", "downLoad", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "读取" + TableName.ToUpper() + "表失败!\t\n"); } else { ClsLogInfo.WriteSapLog("0", "downLoad", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "读取" + TableName.ToUpper() + "表成功!\t\n"); } } else { ClsLogInfo.WriteSapLog("0", "downLoad", DateTime.Now.ToString("yyyyMMdd HH:mm:ss"), "读取" + TableName.ToUpper() + "表成功!\t\n"); } if (boolinfo) { LHSM.Logs.Log.Logger.Error("表" + infotablename[infotablename.Length - 1] + "读取完成,读取成功!"); string updateSql = "UPDATE hb_txttable SET DLDATE='" + infodate + "' WHERE TXT_TABLENAME='" + TableName.ToUpper() + "'"; if (dr[0]["DLDATE"] != null && !string.IsNullOrEmpty(dr[0]["DLDATE"].ToString())) { updateSql += " AND DLDATE<'" + infodate + "'"; } bool ret = ClsUtility.ExecuteSqlToDb(updateSql); threadEndEvent.Invoke(null, new EventArgs());//线程结束 } else { LHSM.Logs.Log.Logger.Error("表" + infotablename[infotablename.Length - 1] + "读取失败!"); threadEndEvent.Invoke(infotablename[infotablename.Length - 1], new EventArgs());//线程结束 } } }