用C#写一个简单的数据同步Windows服务（一）

需求概述

从某中间数据库中取得指定的未同步的数据，同步到另一个库的某些表中。使用一个编号字段进行匹配，同步完成后将未同步的数据标识为已同步状态。该服务可定时自动执行。

设计理念

程序、数据、配置分开，以使功能更灵活。数据库操作类，使用通用的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