需求概述
从某中间数据库中取得指定的未同步的数据,同步到另一个库的某些表中。使用一个编号字段进行匹配,同步完成后将未同步的数据标识为已同步状态。该服务可定时自动执行。
设计理念
程序、数据、配置分开,以使功能更灵活。数据库操作类,使用通用的OldDB相关类,以增强兼容性。关键操作记录到日志中,以方便在出现问题时进行排查。
配置文件
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<appSettings>
<add key="log" value="D:\Code_Zhangzhou\Code\CostMoniter\CostMoniter\bin\Debug\logs"/>
<add key="fromdatabase" value="Provider=MSDAORA;Data Source=localhost/orcl;Persist Security Info=True;User ID=ZHANGZHOU;Password=zhangzhou"/>
<add key="todatabase" value="Provider=MSDAORA;Data Source=localhost/orcl;Persist Security Info=True;User ID=ZHANGZHOU;Password=zhangzhou"/>
<add key="fromtable" value="GH_MID_COST"/>
<add key="totables" value="AAA,BBB"/>
<add key="interval" value="8"/>
</appSettings>
</configuration>
核心代码
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading.Tasks;
using System.Timers;
namespace CostMoniter
{
public partial class CostMonitorService : ServiceBase
{
public CostMonitorService()
{
InitializeComponent();
}
string filePath = "";
protected override void OnStart(string[] args)
{
try
{
Log("Service Starts");
long lInterval = 3000;
string sInterval = ConfigurationSettings.AppSettings["interval"].ToString();
long lIntervalConfig = 0;
if (long.TryParse(sInterval, out lIntervalConfig) && lIntervalConfig > 0)
lInterval = lIntervalConfig * 1000;
Timer t = new Timer();
t.Interval = lInterval;
t.Elapsed += new System.Timers.ElapsedEventHandler(CopyData);
t.AutoReset = true;
t.Enabled = true;
}
catch (Exception e)
{
Log(e.Message);
}
}
public void Log(string s)
{
try
{
string folder = ConfigurationSettings.AppSettings["log"];
if (!System.IO.Directory.Exists(folder) && null == System.IO.Directory.CreateDirectory(folder))
return;
string fileName = string.Format("{0}.log", DateTime.Today.ToString("yyyy_MM_dd"));
string filePath = System.IO.Path.Combine(folder, fileName);
FileStream fileStream = new FileStream(filePath, FileMode.Append, FileAccess.Write);
StreamWriter streamWriter = new StreamWriter(fileStream);
streamWriter.WriteLine(string.Format("{0} -- {1}", DateTime.Now.ToString("HH:mm:ss"), s));
streamWriter.Close();
fileStream.Close();
}
catch { }
}
protected override void OnStop()
{
Log("Service Stops");
}
private void CopyData(object source, System.Timers.ElapsedEventArgs e)
{
string FromConnectionString = ConfigurationSettings.AppSettings["fromdatabase"];
string ToConnectionString = ConfigurationSettings.AppSettings["todatabase"];
OleDbConnection FromConnection = new OleDbConnection(FromConnectionString);
OleDbConnection ToConnection = new OleDbConnection(ToConnectionString);
try
{
FromConnection.Open();
ToConnection.Open();
List<string> ToTables = ConfigurationSettings.AppSettings["totables"].Split(',').ToList();
string FromTable = ConfigurationSettings.AppSettings["fromtable"];
string FromSelectCommandText = string.Format("SELECT GHCODE,ZBCOST,ZMCOST,ZSCOST FROM {0} WHERE ISGET=1 AND USEORNOT=1", FromTable);
Log(string.Format( "Execute SQL : {0}",FromSelectCommandText));
OleDbCommand FromSelectCommand = FromConnection.CreateCommand();
FromSelectCommand.CommandText = FromSelectCommandText;
OleDbDataReader FromDataReader = FromSelectCommand.ExecuteReader();
if (FromDataReader.HasRows)
{
while (FromDataReader.Read())
{
string sCode = FromDataReader[0].ToString();
if (string.IsNullOrEmpty(sCode))
continue;
string ZhengDi = FromDataReader[1].ToString();
string ZaoDi = FromDataReader[2].ToString();
string XiaoShou = FromDataReader[3].ToString();
foreach (string ToFeatureClass in ToTables)
{
string ToUptateCommandText = string.Format("UPDATE {0} SET ZHENGDICB={1},ZAODICB={2},XIAOSHOUSY={3} WHERE MARKS={4}", ToFeatureClass, ZhengDi, ZaoDi, XiaoShou, sCode);
Log(string.Format( "Execute SQL : {0}",ToUptateCommandText));
OleDbCommand ToCommand = ToConnection.CreateCommand();
ToCommand.CommandText = ToUptateCommandText;
int ToRowCount = ToCommand.ExecuteNonQuery();
Log(string.Format("{0} Row(s) Updated",ToRowCount));
}
}
}
string FromUpdateCommandText = string.Format("UPDATE {0} SET ISGET=2 WHERE ISGET=1 AND USEORNOT=1", FromTable);
Log(string.Format( "Execute SQL : {0}",FromUpdateCommandText)) ;
OleDbCommand FromUpdateCommand = FromConnection.CreateCommand();
FromUpdateCommand.CommandText = FromUpdateCommandText;
int FromRowCount = FromUpdateCommand.ExecuteNonQuery();
if (0 == FromRowCount)
Log("No Rows Synchronized");
else
Log(string.Format("{0} Row(s) Synchronized",FromRowCount));
}
catch (Exception ex)
{
Log("ERROR : " + ex.Message);
}
finally
{
Log("Finished");
FromConnection.Close();
ToConnection.Close();
}
}
}
}
服务名称
注册与卸载脚本
注册(install.bat)
%SystemRoot%\Microsoft.NET\Framework\v4.0.30319\installutil.exe D:\abc\CostMoniter.exe
Net Start CostMoniter
sc config CostMoniter start= auto
pause
卸载(Uninstall.bat)
%SystemRoot%\Microsoft.NET\Framework\v4.0.30319\installutil.exe /u D:\abc\CostMoniter.exe
pause