简单源码适合学习_C# 后台服务监控SQL Server数据库表改动并同步到MySQL数据库表

MartinLee 发表于 2019-7-26 15:08

需求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"这个东西我不知道干啥的，但我知道没他就会出现错误- -不加会出现这样的情况https://blog.csdn.net/qq_36279445/article/details/97126518
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); } } }}

zzcl558 发表于 2020-2-15 08:44

本帖最后由 zzcl558 于 2020-2-15 08:46 编辑

MartinLee 发表于 2020-2-14 09:14
点餐记录和菜是有关联吧，你可以在点餐记录表上加个软删除标志，例如 isDeleted。在删除菜的时候， ...
因为我刚刚接触这方面，所以

你说的可以在点餐记录表上加个软删除标志，例如 isDeleted。在删除菜的时候，通过菜与点餐记录的查询，然后把点餐记录删除就行了

这个我不知道应该怎么弄

我把点菜窗口代码保存到txt文本上，传上来，麻烦你把里面怎么改，改一下再传上来给我，非常感谢！
因为这点菜窗口代码有7/8千行，所以就保存到txt文本上了，刚才上次txt文件一直提示错误，后来压缩才上传成功

我这个软件里点菜记录在这（ comanda / comandalinea /venta / ventalinea ） 4个表格里都记录的

然后下面这两段代码在删菜的时候能删除 venta 和ventalinea 两个表格里的记录

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Printing;
using System.IO.Ports;
using System.Runtime.CompilerServices;
using System.Windows.Forms;
using Microsoft.VisualBasic.CompilerServices;
using MySql.Data.MySqlClient;

namespace ORIENT
{
   // Token: 0x0200000C RID: 12

   public partial class BarVentas : Form
   {
            // Token: 0x06000270 RID: 624 RVA: 0x000163C4 File Offset: 0x000145C4
            public void BorraVenta(object VentaId)
            {
                     MySqlCommand mySqlCommand = new MySqlCommand(Conversions.ToString(Operators.ConcatenateObject("delete from venta where VentaId=", VentaId)), Module1.conn);
                     Module1.conn.Open();
                     mySqlCommand.ExecuteNonQuery();
                     Module1.conn.Close();
            }
   }
}

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Printing;
using System.IO.Ports;
using System.Runtime.CompilerServices;
using System.Windows.Forms;
using Microsoft.VisualBasic.CompilerServices;
using MySql.Data.MySqlClient;

namespace ORIENT
{
   // Token: 0x0200000C RID: 12

   public partial class BarVentas : Form
   {
            // Token: 0x06000270 RID: 624 RVA: 0x000163C4 File Offset: 0x000145C4
            public void BorraVentaLinea(object VentaId)
            {
                     MySqlCommand mySqlCommand = new MySqlCommand(Conversions.ToString(Operators.ConcatenateObject("delete from ventalinea where VentaId=", VentaId)), Module1.conn);
                     Module1.conn.Open();
                     mySqlCommand.ExecuteNonQuery();
                     Module1.conn.Close();
            }
   }
}
然后我添加下面这样代码，进软件输入桌号就提示

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Printing;
using System.IO.Ports;
using System.Runtime.CompilerServices;
using System.Windows.Forms;
using Microsoft.VisualBasic.CompilerServices;
using MySql.Data.MySqlClient;

namespace ORIENT
{
   // Token: 0x0200000C RID: 12

   public partial class BarVentas : Form
   {
            // Token: 0x06000270 RID: 624 RVA: 0x000163C4 File Offset: 0x000145C4
            public void BorraComandaLinea(object VentaId)
            {
                     MySqlCommand mySqlCommand = new MySqlCommand(Conversions.ToString(Operators.ConcatenateObject("delete from comandalinea where VentaId=", VentaId)), Module1.conn);
                     Module1.conn.Open();
                     mySqlCommand.ExecuteNonQuery();
                     Module1.conn.Close();
            }
   }
}

zzcl558 发表于 2020-2-20 23:31

MartinLee 发表于 2020-2-20 13:07
你删除的时候把这个 isDelete 设置为 true ，然后查询的时候过滤掉 isDelete = true的...

上面是软删 ...

using System;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Printing;
using System.IO.Ports;
using System.Runtime.CompilerServices;
using System.Windows.Forms;
using Microsoft.VisualBasic.CompilerServices;
using MySql.Data.MySqlClient;

namespace ORIENT
{
   // Token: 0x0200000C RID: 12

   public partial class BarVentas : Form
   {
            // Token: 0x06000270 RID: 624 RVA: 0x000163C4 File Offset: 0x000145C4
            public void BorraVenta(object VentaId)
            {
                     MySqlCommand mySqlCommand = new MySqlCommand(Conversions.ToString(Operators.ConcatenateObject("delete from venta where VentaId=", VentaId)), Module1.conn);
                     Module1.conn.Open();
                     mySqlCommand.ExecuteNonQuery();
                     Module1.conn.Close();
            }
   }
}

最好是像这个一样，直接删除记录，不过我添加删除comanda的代码，不行

yxj4050 发表于 2019-8-15 11:14

Mark，有空测试下，有问题的话向你请教

zzcl558 发表于 2020-2-14 04:27

谢谢分享！

zzcl558 发表于 2020-2-14 04:30

你好，请问一下，餐饮管理软件，点餐后又把菜删除了，然后MySQL里有记录，如果在删菜的同时也把MySQL里的点餐记录一起删除掉，这样的MySQL删除语句要怎么写？
希望你百忙之中能抽出一点时间教我一下
非常感谢！

zzcl558 发表于 2020-2-14 04:31

MartinLee 发表于 2020-2-14 09:14

zzcl558 发表于 2020-2-14 04:30
你好，请问一下，餐饮管理软件，点餐后又把菜删除了，然后MySQL里有记录，如果在删菜的同时也把MySQL里的点 ...

点餐记录和菜是有关联吧，你可以在点餐记录表上加个软删除标志，例如 isDeleted。在删除菜的时候，通过菜与点餐记录的查询，然后把点餐记录删除就行了

MartinLee 发表于 2020-2-19 14:07

zzcl558 发表于 2020-2-15 08:44
因为我刚刚接触这方面，所以

你说的可以在点餐记录表上加个软删除标志，例如 isDeleted。在删除 ...

不好意思，公司复工了，一直在忙。没注意帖子。

代码我就不看了，大同小异，CURD。

加软删除标志，isDeleted 就是个字段名，数据库加一个字段
Id CreationTime CreatorUserId OrganizationUnitId TenantId UserId IsDeleted
1 2020-02-19 13:53:42.1278528 2 7 1 3 0
2 2020-02-19 13:53:42.2641598 2 5 1 4 0
3 2020-02-19 13:54:53.6464668 2 1 1 3 0

MartinLee 发表于 2020-2-19 14:10

zzcl558 发表于 2020-2-15 08:44
因为我刚刚接触这方面，所以

你说的可以在点餐记录表上加个软删除标志，例如 isDeleted。在删除 ...

删除菜的时候，根据菜品的唯一标识去join 点菜记录表，将数据更新到点菜记录表。做个软删除。当然直接把点菜记录表对应删了也行。不用多想。

MySQL 语句的话大概就是
根据菜品id 去找点菜记录中的记录，然后把点菜记录删掉

zzcl558 发表于 2020-2-19 19:59

本帖最后由 zzcl558 于 2020-2-19 23:15 编辑

MartinLee 发表于 2020-2-19 14:07
不好意思，公司复工了，一直在忙。没注意帖子。

代码我就不看了，大同小异，CURD。

你千万不能这么说，你能回复我，我都不知道说什么好了，非常感谢你的解释。
但是还不是很明白，就是说在这个数据库表格里添加 IsDeleted这个字段，然后在软件里删菜的时候就会把要删除掉的菜的点菜记录直接删除了，是这样吗？
刚才在数据库表格里添加了字段 isDeleted ，但是软件里删除菜后再进数据库，还是和之前一样的，这是为什么呢？

谢谢！

页: [1] 2

吾爱破解 - 52pojie.cn's Archiver

简单源码适合学习_C# 后台服务监控SQL Server数据库表改动并同步到MySQL数据库表