代码片段: 文末附链接。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
using DataSync.Core;
using Furion.Logging.Extensions;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using System.Data;
namespace DataSync.Application.DataSync.Services
{
    public class DataSyncServices : IDataSyncData, ITransient
    {
        private readonly object lockObj = new object();
        /// <summary>
        /// 客户端向服务端同步
        /// </summary>
        /// <param name="clientConn"></param>
        /// <param name="serviceConn">目标数据库</param>
        /// <returns></returns>
        public string SyncDataForClient(string clientConn, string serviceConn)
        {
            return SyncData(clientConn, serviceConn);
        }
        /// <summary>
        /// 服务端向客户端同步
        /// </summary>
        /// <param name="serviceConn"></param>
        /// <param name="clientConn"></param>
        /// <returns></returns>
        public string SyncDataForServer(string serviceConn, string clientConn)
        {
            return SyncData(serviceConn, clientConn);
        }
        /// <summary>
        /// 数据同步
        /// </summary>
        private string SyncData(string sourceConn, string targetConn)
        {
            try
            {
 
                //源数据库 数据源链接
                SqlSugarScope sourceDb = new SqlSugarScope(new ConnectionConfig()
                {
                    DbType = SqlSugar.DbType.SqlServer,
                    ConnectionString = sourceConn,
                    IsAutoCloseConnection = true,
                    AopEvents = new AopEvents
                    {
                        OnLogExecuting = (sql, ps) =>
                        {
#if DEBUG
                            Log.Information($"语句:{sql},参数:{(ps.Any() ? "[" : string.Empty) + string.Join("|", ps.Select(m => $"{m.ParameterName}={m.Value}")) + (ps.Any() ? "]" : string.Empty)}");
#endif
                        }
                    }
                });
                //目标数据库 数据源链接
                SqlSugarScope targetDb = new SqlSugarScope(new ConnectionConfig()
                {
                    DbType = SqlSugar.DbType.SqlServer,
                    ConnectionString = targetConn,
                    IsAutoCloseConnection = true,
                    AopEvents = new AopEvents
                    {
                        OnLogExecuting = (sql, ps) =>
                        {
#if DEBUG
                            Log.Information($"语句:{sql},参数:{(ps.Any() ? "[" : string.Empty) + string.Join("|", ps.Select(m => $"{m.ParameterName}={m.Value}")) + (ps.Any() ? "]" : string.Empty)}");
#endif
                        }
                    }
                });
                //使用sqlsugar 初始化目标数据库
                targetDb.DbMaintenance.CreateDatabase();
                var tableNames = sourceDb.DbMaintenance.GetTableInfoList(false).Select(t => t.Name).ToList(); // 调用函数获取所有表名
                var syncBlackTable = App.GetConfig<List<string>>("SyncBlackTable");
                tableNames = tableNames.Except(syncBlackTable).ToList();
                //多线程
                Parallel.ForEach(tableNames, tableName =>
                {
                    lock (lockObj)
                    {
                        // 根据表名从源数据库中获取数据并存入 DataTable
                        var targetdataTable = DataTableHelper.FetchDataFromTable(tableName, sourceDb);
                        //判断数据表在目标库是否存在
                        var flagTargetTab = targetDb.DbMaintenance.IsAnyTable(tableName);
                        if (!flagTargetTab)
                        {
                            // 创建表的SQL语句
                            var createTableSql = $"CREATE TABLE {tableName} (";
                            if (targetdataTable != null && targetdataTable.Rows.Count > 0)
                            {
                                //目标数据库写入-先移除数据同步标识
                                DataBaseInfoService.DatatableRemoveCloumns(targetdataTable);
                                // 遍历DataTable的列
                                foreach (DataColumn column in targetdataTable.Columns)
                                {
                                    string columnName = column.ColumnName;
                                    string dataType = DataBaseInfoService.GetSqlDataType(column.DataType);
 
                                    createTableSql += $"{columnName} {dataType}, ";
                                }
                                createTableSql = createTableSql.TrimEnd(',', ' ') + ")";
                                // 创建表
                                targetDb.Ado.ExecuteCommand(createTableSql);
 
                                ("TargetTable : " + tableName + ",创建成功").LogInformation();
                                //    }
                                //}
                            }
                        }
                        //AppSys
                        if (tableName.ToUpper().Equals("APPSYS"))
                        {
                            AppSysDataSync.SyncData(tableName, sourceDb, targetDb);
                        }
                        var selectCountSql = $"SELECT COUNT(*) FROM {tableName} ";
 
                        var sourceCount = sourceDb.Ado.GetInt(selectCountSql);
 
                        var middleCount = targetDb.Ado.GetInt(selectCountSql);
                        //增量
                        if (sourceCount > middleCount)
                        {
                            //  commandTarget.Connection = connTarget;
                            // commandTarget.CommandType = CommandType.Text;
                            //查询数据
                            var selectTableSql = $"SELECT * FROM {tableName}";
                            //创建datatable(源数据)
                            var sourceDataTable = sourceDb.Ado.GetDataTable(selectTableSql);
 
                            if (sourceDataTable != null && sourceDataTable.Rows.Count > 0)
                            {
                                //新增列 MD5
                                DataBaseInfoService.DataTableAddColumsMd5(sourceDataTable);
                            }
                            //创建datatable(目标表数据)
                            var targetDataTable = targetDb.Ado.GetDataTable(selectTableSql);
 
                            if (targetDataTable != null && targetDataTable.Rows.Count > 0)
                            {
                                //新增列 MD5
                                DataBaseInfoService.DataTableAddColumsMd5(targetDataTable);
                            }
                            // 计算差集
                            var tempTable = new DataTable();
                            var tempExceptTable = (from source in sourceDataTable.AsEnumerable()
                                                   where
                                                   !(from target in targetDataTable.AsEnumerable() select target.Field<string>("MD5")).Contains(
                                                   source.Field<string>("MD5"))
                                                   select source);
                            if (tempExceptTable != null && tempExceptTable.Count() > 0)
                            {
                                tempTable = tempExceptTable.CopyToDataTable();
                            }
                            //批量插入数据
                            if (tempTable != null && tempTable.Rows.Count > 0)
                            {
                                //目标数据库写入-先移除数据同步标识,MD5标识
                                DataBaseInfoService.DatatableRemoveCloumns(tempTable);
                                var connTarget = new SqlConnection(targetConn);
                                DataBaseInfoService.DataBulkCopy(connTarget, tableName, tempTable);
 
                                //  TargetDataScope.Db.Fastest<DataTable>().AS(tableName).BulkCopy(tempTable);
                            }
                        }
                        //删除
                        else if (sourceCount < middleCount)
                        {
                            //查询数据
                            var selectTableSql = $"SELECT * FROM {tableName}";
                            //创建datatable(源数据)
                            var sourceDataTable = sourceDb.Ado.GetDataTable(selectTableSql);
 
                            if (sourceDataTable != null && sourceDataTable.Rows.Count > 0)
                            {
                                //新增列 MD5
                                DataBaseInfoService.DataTableAddColumsMd5(sourceDataTable);
                            }
                            //创建datatable
                            var taergetTable = targetDb.Ado.GetDataTable(selectTableSql);
                            if (taergetTable != null && taergetTable.Rows.Count > 0)
                            {
                                //新增列 MD5
                                DataBaseInfoService.DataTableAddColumsMd5(taergetTable);
                            }
                            // 计算差集
                            var tempTable = new DataTable();
                            var tempExceptTable = (from target in taergetTable.AsEnumerable()
                                                   where
                                                   !(from source in sourceDataTable.AsEnumerable() select source.Field<string>("MD5")).Contains(
                                                   target.Field<string>("MD5"))
                                                   select target);
                            if (tempExceptTable != null && tempExceptTable.Count() > 0)
                            {
                                tempTable = tempExceptTable.CopyToDataTable();
                            }
                            if (tempTable != null && tempTable.Rows.Count > 0)
                            {
                                //获取主键字段
                                var PrimaryKeyName = targetDb.DbMaintenance.GetPrimaries(tableName);
                                //DataTableHelper.GetPrimaryKeyFieldName(tableName, connTarget);
                                //获取自增列
                                var Identities = targetDb.DbMaintenance.GetIsIdentities(tableName);
                                if (PrimaryKeyName != null && PrimaryKeyName.Count > 0)
                                {
                                    foreach (DataRow row in tempTable.Rows)
                                    {
                                        var deleteDataSql = DataTableHelper.ConstructDeleteSql(tableName, PrimaryKeyName, Identities, row);
                                        //$"DELETE FROM {tableName} WHERE {PrimaryKeyName} ='{row[PrimaryKeyName[0]]}'";
                                        //目标数据数据操作对象
                                        targetDb.Ado.ExecuteCommand(deleteDataSql);
 
                                    }
                                }
                            }
                        }
                        //更新
                        else
                        {
                            //判断是否存在需要更新的记录
                            //和目标表比较取差集
                            //查询数据
                            var selectTableSql = $"SELECT * FROM {tableName}";
                            //创建datatable(源数据)
                            var sourceDataTable = sourceDb.Ado.GetDataTable(selectTableSql);
                            if (sourceDataTable != null && sourceDataTable.Rows.Count > 0)
                            {
                                //新增列 MD5
                                DataBaseInfoService.DataTableAddColumsMd5(sourceDataTable);
                            }
                            //创建datatable(目标表数据)
                            var targetDataTable = targetDb.Ado.GetDataTable(selectTableSql);
                            if (targetDataTable != null && targetDataTable.Rows.Count > 0)
                            {
                                //新增列 MD5
                                DataBaseInfoService.DataTableAddColumsMd5(targetDataTable);
                            }
                            // 计算差集
                            var tempTable = new DataTable();
                            var tempExceptTable = (from source in sourceDataTable.AsEnumerable()
                                                   where
                                                   !(from target in targetDataTable.AsEnumerable() select target.Field<string>("MD5")).Contains(
                                                   source.Field<string>("MD5"))
                                                   select source);
                            if (tempExceptTable != null && tempExceptTable.Count() > 0)
                            {
                                tempTable = tempExceptTable.CopyToDataTable();
                            }
                            if (tempTable != null && tempTable.Rows.Count > 0)
                            {
                                //删除标识列和MD5列
                                DataBaseInfoService.DatatableRemoveCloumns(tempTable);
                                //获取目标表主键字段
                                var PrimaryKeyName = targetDb.DbMaintenance.GetPrimaries(tableName);
                                //获取自增列
                                var Identities = targetDb.DbMaintenance.GetIsIdentities(tableName);
                                //DataTableHelper.GetPrimaryKeyFieldName(tableName, connTarget);
                                foreach (DataRow dataRow in tempTable.Rows)
                                {
                                    var updateDataSql = DataTableHelper.ConstructUpdateSql(tableName, PrimaryKeyName, Identities, dataRow);
                                    targetDb.Ado.ExecuteCommand(updateDataSql);
 
                                }
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                ("Error occurred while connecting to database or fetching data from tables.\n" + ex.Message).LogError();
                return "同步失败。详见错误日志!";
            }
            return "同步完成!";
        }
 
    }
}

  Gitee: https://gitee.com/ltf_free/sync-data.git