需求1. 将A服务器的sqlserver数据库item_mst表的数据同步到MySQL数据库Part表2. 监控Item_mst表中item,description,overview 的更改并同步到MySQL数据库 针对需求一，基本就是执行一次，单独写了个winform用来做这事针对需求二，写了个Windows service app
帮别人整得，挺简单的代码，用来学习Windows Service app 不错。
求点热心值，谢谢了！！！如有违规，删除即可。
Nuget安装
Dapper就是个ormSerilog用来记logSQLTableDependency用来检测表的改动，文档地址：https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency使用这个别忘了执行 alter database [<dbname>] set enable_broker with rollbackimmediate;1. 创建service2. 添加安装程序3. 如何安装serviceCreate a Windows service apphttps://docs.microsoft.com/en-us/dotnet/framework/windows-services/walkthrough-creating-a-windows-service-application-in-the-component-designer How to: Add Installers to Your Service Applicationhttps://docs.microsoft.com/zh-cn/dotnet/framework/windows-services/how-to-add-installers-to-your-service-applicationHow to: Install and uninstallWindows serviceshttps://docs.microsoft.com/en-us/dotnet/framework/windows-services/how-to-install-and-uninstall-services 源码目录结构
Models文件夹里的ItemMst.cs文件using System; usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks; namespaceSyncItemMstAllService.Models{ /// <summary> /// tablename: item_mst /// note:We do not need to specify all table columns but just the ones we areinterested: /// </summary> public class ItemMst { // internalNumber public string Item { get; set; } // name public string Description { get; set; } // description public string Overview { get; set; } }} App.config <appSettings> <add key="logFilesPath" value="C:\QMSCSyncData\logs\"/> <add key="webApiBaseAddress" value="http://localhost:5000/"/> </appSettings> <connectionStrings> <add name="JMP_APP_SqlServerConnStr" connectionString="data source=192.168.1.202\test;initialcatalog=JMP_APP;User Id=sa;Password=pwd;"/> <add name="QMS_MySqlServerConnStr" connectionString="server=localhost;Port=3306;Database=qms_test;UID=root;PWD=pwd;AllowUser Variables=True" /> </connectionStrings>AllowUser Variables=True"这个东西我不知道干啥的，但我知道没他就会出现错误- -不加会出现这样的情况
QMSSyncTiemMstService.csusing System; usingSystem.Configuration;usingSystem.Data.SqlClient;usingSystem.IO;usingSystem.Linq;usingSystem.Net.Http;usingSystem.Net.Http.Headers;usingSystem.ServiceProcess;usingSystem.Threading.Tasks;using Dapper; usingSyncItemMstAllService.Models;usingMySql.Data.MySqlClient;usingSerilog;usingTableDependency.SqlClient;usingTableDependency.SqlClient.Base.Enums;usingTableDependency.SqlClient.Base.EventArgs;usingSystem.Collections.Generic; namespaceQMSCSyncService{ public partial class QMSSyncItemMstService :ServiceBase { privateSqlTableDependency<ItemMst> _itemMstAllSyncDependency; private readonly string_jmp_app_ConnectionString; private readonly string_qms_mysql_ConnectionString; private readonly string _logFilesPath; private readonly string version = "1.0.20190730"; public QMSSyncItemMstService() { InitializeComponent(); _logFilesPath =ConfigurationManager.AppSettings["logFilesPath"]; _jmp_app_ConnectionString =ConfigurationManager.ConnectionStrings["JMP_APP_SqlServerConnStr"].ConnectionString; _qms_mysql_ConnectionString =ConfigurationManager.ConnectionStrings["QMS_MySqlServerConnStr"].ConnectionString; Log.Logger = newLoggerConfiguration() .MinimumLevel.Debug() .WriteTo.File($"{_logFilesPath}serviceLog.txt",rollingInterval: RollingInterval.Day) .CreateLogger(); } protected override void OnStart(string[] args) { initItemMstAllSyncDependency(); Log.Information($"QMSSyncService Started.Version{version}"); } protected override void OnStop() { Log.Information("QMSSyncService Stopped."); try { if (_itemMstAllSyncDependency != null) { _itemMstAllSyncDependency.Stop(); _itemMstAllSyncDependency.Dispose(); } } catch (Exception ex) { Log.Error($"Error occur when stopping service, {ex.Message} {ex.Source}"); } } private voidinitItemMstAllSyncDependency() { Log.Information($"run initItemMstAllSyncDependency"); try { if (_itemMstAllSyncDependency != null) { _itemMstAllSyncDependency.Stop(); _itemMstAllSyncDependency.Dispose(); } _itemMstAllSyncDependency= newSqlTableDependency<ItemMst>(_jmp_app_ConnectionString, "item_mst"); _itemMstAllSyncDependency.OnChanged +=ItemMstAllSyncDependency_OnChanged; _itemMstAllSyncDependency.OnError += ItemMstAllSyncDependency_OnError; _itemMstAllSyncDependency.Start(); } catch (Exception ex) { Log.Error($"Init SqlTableDependency for ItemMstSyncs failed. {ex.Message} {ex.Source}"); if (_itemMstAllSyncDependency != null) { _itemMstAllSyncDependency.Stop(); _itemMstAllSyncDependency.Dispose(); } } private voidItemMstAllSyncDependency_OnChanged(object sender, RecordChangedEventArgs<ItemMst> e) { if (e.ChangeType != ChangeType.None) { switch (e.ChangeType) { case ChangeType.Insert: case ChangeType.Update: saveItemMst(e.Entity.Item, e.Entity.Description, e.Entity.Overview); break; } } } private void saveItemMst(string item, string name, string description) { string UUID = Guid.NewGuid().ToString("D"); DateTime dt = DateTime.Now; bool hasExisted = isExisted(item); if (name == null) { name = item; } string insertSql = $@"INSERT INTO`part` ( `ParentPartId`, `PublisherId`, `UUID`, `Type`, `InternalNumber`, `SupplierNumber`, `Name`, `Description`, `Rev`, `RevType`, `Level`, `Category`, `Status`, `CreatedAt`, `CreatedBy`, `IsDeleted` ) VALUES ( 0, 149, @UUID, 1, @item, NULL, @name, 'A', 1, 1, '', 1, '{dt}', 701, 0 );"; string updateSql = $@"UPDATE`part` SET `Name`= @name, `Description` = @description, `UpdatedAt` = '{dt}', `UpdatedBy` = '701' WHERE InternalNumber = @item"; try { using (MySqlConnection qmsDBConnection = newMySqlConnection(_qms_mysql_ConnectionString)) { qmsDBConnection.Open(); using (MySqlCommand cmd = qmsDBConnection.CreateCommand()) { try { cmd.CommandText =hasExisted ? updateSql : insertSql; cmd.Parameters.Add(new MySqlParameter("@UUID",UUID)); cmd.Parameters.Add(new MySqlParameter("@name",name)); cmd.Parameters.Add(new MySqlParameter("@description",description)); cmd.Parameters.Add(new MySqlParameter("@item",item)); cmd.ExecuteNonQuery(); } catch (Exception ex) { Log.Error($"Sync from saveItemMst have error occur, {ex.Message} {ex.Source} { ex.StackTrace }"); } } } } catch (Exception ex) { Log.Error($"Sync from saveItemMst have error occur, {ex.Message} {ex.Source} { ex.StackTrace }"); } } private bool isExisted(string item) { string sSql = @"SELECT *FROM Part P WHERE P.PublisherId=149 AND P.InternalNumber = @item ANDP.IsDeleted=0"; try { using (MySqlConnection qmsDBConnection = newMySqlConnection(_qms_mysql_ConnectionString)) { qmsDBConnection.Open(); using (MySqlCommand cmd = new MySqlCommand(sSql, qmsDBConnection)) { try { cmd.Parameters.Add(new MySqlParameter("@item",item)); object result =cmd.ExecuteScalar(); if (result != null) { return true; } return false; } catch (Exception ex) { Log.Error($"IsExistedhave error occur, {ex.Message} {ex.Source} { ex.StackTrace }"); return false; } } } } catch (Exception ex) { Log.Error($"IsExisted have error occur, {ex.Message} {ex.Source} { ex.StackTrace }"); return false; } } } private voidItemMstAllSyncDependency_OnError(object sender, TableDependency.SqlClient.Base.EventArgs.ErrorEventArgse) { string innerException = e.Error.InnerException != null ?e.Error.InnerException.Message + e.Error.InnerException.Source : ""; Log.Error($"ItemMstAllSyncDependencyhave error occur, {e.Error.Message} {e.Error.Source} { innerException }"); try { this.OnStop(); this.OnStart(null); } catch (Exception ex) { Log.Debug("Restart failed " +ex.Message + ex.Source); } } }}
|
发表于 2019-7-26 15:08
|
发表于 2020-2-14 09:14
