// 核心处理函数
private bool MktToSql(IniFiles inifile)
{
var file = File.Open(inifile.File, FileMode.Open);
List<string> txt = new List<string>();
List<Dictionary<int, string>> map_txt = new List<Dictionary<int, string>>();
string mdtext,stime_get,tmpsqldcmd,sqlcmd,sqlcmd_foreach,sqlcmd_last ;
string[] sheader;
SqlTransaction sqlTrans = (SqlTransaction)null;
int i = 0;
stime_get = "";
sqlcmd_foreach = "";
sqlcmd_last = "";
using (var stream = new StreamReader(file))
{
while (!stream.EndOfStream)
{
string stmp = stream.ReadLine();
if (stmp.Contains("HEADER") )// 文件头和尾不需要插入链表
{
sheader = stmp.Split('|');
stime_get = sheader[6];// 分割后的第7个字符串为时间戳
continue;
}
if(stmp.Contains("TRAILER"))
{
continue;
}
txt.Add(stmp);
}
stream.Close();// 关闭文件流
}
file.Close();// 关闭文件
tmpsqldcmd = "insert into " + inifile.Table + " (pubnum,bcasttype,setid,seqnum,recordtimestamp,mdtext) " + "(" ;
sqlcmd = tmpsqldcmd;
foreach (string sstmp in txt)
{
string[] sArray = sstmp.Split('|');
icount++;// 计数器加1
i++;
mdtext = "35=W" + SOH + "963= " + SOH + "1187=0" + SOH + "48=" + sArray[1].Trim() + SOH
+ "8506=" + sArray[2].Trim() + SOH + "8538=" + sArray[33] + SOH + "8504=" + ((sArray[4].Trim() == "")?zero2dec:sArray[4].Trim())
+ SOH + "387=" + sArray[3].Trim() + SOH + "268=16" + SOH + "269=4" + SOH + "270=" + ((sArray[6].Trim() == "") ? zero4dec : sArray[6].Trim())
+ SOH + "269=6" + SOH + "270=" + ((sArray[32].Trim() == "") ? zero4dec : sArray[32].Trim()) + SOH +
"269=7" + SOH + "270=" + ((sArray[9].Trim() == "") ? zero4dec : sArray[9].Trim()) + SOH + "269=8" + SOH + "270=" + ((sArray[10].Trim() == "") ? zero4dec : sArray[10].Trim())
+ SOH + "269=x" + SOH + "270=" + ((sArray[7].Trim() == "") ? zero4dec : sArray[7].Trim()) + SOH + "269=2" + SOH + "270=" + ((sArray[11].Trim() == "") ? zero4dec : sArray[11].Trim()) + SOH + "269=0" + SOH + "270=" + ((sArray[12].Trim() == "") ? "0.0000" : sArray[12].Trim())
+ SOH + "271=" + sArray[13].Trim() + SOH + "290=1" + SOH + "269=0" + SOH + "270=" + ((sArray[16].Trim() == "") ? "0.0000" : sArray[16].Trim())
+ SOH + "271=" + sArray[17].Trim() + SOH + "290=2" + SOH + "269=0" + SOH + "270=" + ((sArray[20].Trim() == "") ? "0.0000" : sArray[20].Trim())
+ SOH + "271=" + sArray[21].Trim() + SOH + "290=3" + SOH + "269=0" + SOH + "270=" + ((sArray[24].Trim() == "") ? "0.0000" : sArray[24].Trim())
+ SOH + "271=" + sArray[25].Trim() + SOH + "290=4" + SOH + "269=0" + SOH + "270=" + ((sArray[28].Trim() == "") ? "0.0000" : sArray[28].Trim())
+ SOH + "271=" + sArray[29].Trim() + SOH + "290=5" + SOH + "269=1" + SOH + "270=" + ((sArray[14].Trim() == "") ? "0.0000" : sArray[14].Trim())
+ SOH + "271=" + sArray[15].Trim() + SOH + "290=1" + SOH + "269=1" + SOH + "270=" + ((sArray[18].Trim() == "") ? "0.0000" : sArray[18].Trim())
+ SOH + "271=" + sArray[19].Trim() + SOH + "290=2" + SOH + "269=1" + SOH + "270=" + ((sArray[22].Trim() == "") ? "0.0000" : sArray[22].Trim())
+ SOH + "271=" + sArray[23].Trim() + SOH + "290=3" + SOH + "269=1" + SOH + "270=" + ((sArray[26].Trim() == "") ? "0.0000" : sArray[26].Trim())
+ SOH + "271=" + sArray[27].Trim() + SOH + "290=4" + SOH + "269=1" + SOH + "270=" + ((sArray[30].Trim() == "") ? "0.0000" : sArray[30].Trim())
+ SOH + "271=" + sArray[31].Trim() + SOH + "290=5" + SOH;
mdtext = "9=" + (mdtext.Length) + SOH + mdtext;// 一条数据的信息成功获得
sqlcmd_foreach = "SELECT " + icount + "," + "'" + "7H" + "'" + "," + "'" + "300" + "'" + "," + icount + "," + "'" + stime_get + "'" + "," + "'" + mdtext + "'";
if (i == inifile.CommitCount)
{
sqlcmd += (sqlcmd_foreach + ")");
try
{
// Todo执行插入操作
sqlTrans = sqlConn.GetTrans();
sqlConn.command.Transaction = sqlTrans;
if (sqlConn.RunNonQuery(sqlcmd) != 0)
{
sqlTrans.Commit();
}
else
{
GfLogManager.WriteLog(sqlcmd, 1);
sqlTrans.Rollback();
}
}
catch (Exception sqlex)
{
GfLogManager.WriteLog(sqlcmd+sqlex.Message, 1);
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
}
sqlcmd = tmpsqldcmd;
i = 0;
}
else
{
sqlcmd_last = sqlcmd;
sqlcmd += (sqlcmd_foreach + " UNION ALL ");
}
}
if (i != 0) // 如果 i不等于0说明还有余下的数据需要插入到数据库
{
sqlcmd_last += (sqlcmd_foreach + ")");
try
{
// Todo执行插入操作
sqlTrans = sqlConn.GetTrans();
sqlConn.command.Transaction = sqlTrans;
if (sqlConn.RunNonQuery(sqlcmd_last) != 0)
{
sqlTrans.Commit();
}
else
{
sqlTrans.Rollback();
}
}
catch (Exception sexlast)
{
GfLogManager.WriteLog(sqlcmd_last+sexlast.Message, 1);
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
}
}
return true;
}
private object getmaxpubnum(IniFiles inifile)
{
object result = null;
SqlTransaction sqlTrans = null;
string getmaxpubnum;
getmaxpubnum = "select isnull(max(pubnum),0) from " +inifile.Table;
try
{
sqlTrans = sqlConn.GetTrans();
sqlConn.command.Transaction = sqlTrans;
result = sqlConn.RunOneReCordQuery(getmaxpubnum);
if (result != null)
{
sqlTrans.Commit();
}
else
{
sqlTrans.Rollback();
}
}
catch (Exception exmes)
{
if (sqlTrans != null)
{
sqlTrans.Rollback();
}
}
return result;
}
private void saveSettings()
{
IniFiles ini = new IniFiles(Properties.Resources.cfgname);
ini.WriteString("SYSTEM", "Database", this.sDbConn);
ini.WriteInteger("SYSTEM", "SLEEPTIME", this.iSleeptTime);
ini.WriteString("SYSTEM", "Table", this.sTable);
ini.WriteInteger("SYSTEM", "CommitCount", this.CommitCount);
ini.WriteString("SYSTEM", "FILE", this.sFile);
}
private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
{
IniFiles inifile = new IniFiles(Properties.Resources.cfgname);// 获取配置文件
// GfLogManager.WriteLog("test", 1);
string sFile;
string dataFile;
int imax = 0;
object iresult = null;
DateTime DateCurr,DateComp;
dataFile = inifile.File;// 配置文件名称
DateCurr = File.GetLastWriteTime(dataFile);
sFile = inifile.File;
try
{
// 登录SqlServer
if (this.loginSqlServer(inifile.DbConn) == false)
{
sqlConn.CloseConnection();
MessageBox.Show("连接数据库失败");
this.bgWorker.CancelAsync();
e.Cancel = true;
return;
}
}
catch (Exception ex)
{
GfLogManager.WriteLog(ex.Message, 1);
MessageBox.Show(ex.Message, "连接数据库失败", MessageBoxButtons.OK,MessageBoxIcon.Error);
sqlConn.CloseConnection();
this.bgWorker.CancelAsync();
return;
}
// 查询表中最大流水号
iresult = this.getmaxpubnum(inifile);
if (iresult != null)
{
imax = (int)iresult;
}
else
{
imax = 0;
}
// 取表中的最大流水号完毕
icount = imax;
// CheckButton("START");// 启动按钮线程不安全
if (this.MktToSql(inifile) == false)//启动的时候先转文件
{
MessageBox.Show("启动转换文件失败");
return;
}
GfLogManager.WriteLog("初次转换文件到数据库成功", 0);
while (true)
{
if (this.bgWorker.CancellationPending)// 需要自己判断状态并退出
{
e.Cancel = true;
return;
}
DateComp = File.GetLastWriteTime(dataFile);
if (DateCurr.CompareTo(DateComp) == 0)
{
continue;
}
else
{
MessageBox.Show("文件已经更改");
//break;
}
DateCurr = DateComp;
if (this.MktToSql(inifile) == false)// 当文件时间戳更改的时候，再转文件
{
GfLogManager.WriteLog("转换文件到数据库失败，详细请见日志", 1);
return;
}
GfLogManager.WriteLog("转换文件到数据库成功", 0);
/* if (dataString == dataString_tmp)
{
continue;
}
dataString_tmp = dataString;*/
}
}
// 初始化赋值
public void initForm()
{
IniFiles infile = new IniFiles(Properties.Resources.cfgname);// 读取默认配置文件mktcfg.ini
this.textDbConn.Text = this.sDbConn = infile.DbConn;
// 读取睡眠时间
this.iSleeptTime = infile.SleeptTime;
this.textSleepTime.Text = this.iSleeptTime.ToString();
// 读取睡眠时间结束
this.textTableName.Text = this.sTable = infile.Table;
// 读取提交频率
this.iCommitCount = infile.CommitCount;
this.textCommitCount.Text = this.iCommitCount.ToString();
// 读取提交频率结束
this.sFile = infile.File;
this.textFile.Text = this.sFile;
}