C#同步SQLServer数据库中的数据--数据库同步⼯具[同步新数
据]
C#同步SQL Server数据库中的数据
1. 先写个sql处理类:
1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.Data.SqlClient;裴擒虎出装
5using System.Text;
6
7namespace PinkDatabaseSync
8{
9 class DBUtility : IDisposable
10 {
11 private string Server;
12 private string Database;
13 private string Uid;
14 private string Password;
15 private string connectionStr;
16 private SqlConnection mySqlConn;
17
18 public void EnsureConnectionIsOpen()
19 {
20 if (mySqlConn == null)
21 {
22 mySqlConn = new tionStr);
23 mySqlConn.Open();
24 }
25 else if (mySqlConn.State == ConnectionState.Closed)
26 {
27 mySqlConn.Open();
28 }
29 }
30
31 public DBUtility(string server, string database, string uid, string password)
32 {
33 this.Server = server;
34 this.Database = database;
35 this.Uid = uid;
36 this.Password = password;
37 tionStr = "Server=" + this.Server + ";Database=" + this.Database + ";User Id=" + this.Uid + ";Password=" + this.Password;
38 }
39
40 public int ExecuteNonQueryForMultipleScripts(string sqlStr)
41 {
42 this.EnsureConnectionIsOpen();
43 SqlCommand cmd = mySqlConn.CreateCommand();
44 cmd.CommandType = CommandType.Text;
45 cmd.CommandText = sqlStr;
46 return cmd.ExecuteNonQuery();
47 }
48 public int ExecuteNonQuery(string sqlStr)
49 {
50 this.EnsureConnectionIsOpen();
51 SqlCommand cmd = new SqlCommand(sqlStr, mySqlConn);
52 cmd.CommandType = CommandType.Text;
53 return cmd.ExecuteNonQuery();2022年5月3日五行穿衣指南
56
57 public object ExecuteScalar(string sqlStr)
58 {
59 this.EnsureConnectionIsOpen();
60 SqlCommand cmd = new SqlCommand(sqlStr, mySqlConn);
61 cmd.CommandType = CommandType.Text;
62 return cmd.ExecuteScalar();
63 }
64
65 public DataSet ExecuteDS(string sqlStr)
66 {
67 DataSet ds = new DataSet();
68 this.EnsureConnectionIsOpen();
69 SqlDataAdapter sda= new SqlDataAdapter(sqlStr,mySqlConn);
70 sda.Fill(ds);
71 return ds;
72 }
73
74 public void BulkCopyTo(string server, string database, string uid, string password, string tableName, string primaryKeyName)
75 {
76 string connectionString = "Server=" + server + ";Database=" + database + ";User Id=" + uid + ";Password=" + password;
77 // Create destination connection
78 SqlConnection destinationConnector = new SqlConnection(connectionString);
79
80 SqlCommand cmd = new SqlCommand("SELECT * FROM " + tableName, destinationConnector);
81 // Open source and destination connections.
82 this.EnsureConnectionIsOpen();
83 destinationConnector.Open();
84
85 SqlDataReader readerSource = cmd.ExecuteReader();
86 bool isSourceContainsData = false;
87 string whereClause = " where ";
88 while (readerSource.Read())
89 {
90 isSourceContainsData = true;
91 whereClause += " " + primaryKeyName + "!=" + readerSource[primaryKeyName].ToString() + " and ";
92 }
93 whereClause = whereClause.Remove(whereClause.Length - " and ".Length, " and ".Length);
94 readerSource.Close();
95
96 whereClause = isSourceContainsData ? whereClause : string.Empty;
97
98 // Select data from Products table
99 cmd = new SqlCommand("SELECT * FROM " + tableName + whereClause, mySqlConn);
100 // Execute reader
101 SqlDataReader reader = cmd.ExecuteReader();
102 // Create SqlBulkCopy
103 SqlBulkCopy bulkData = new SqlBulkCopy(destinationConnector);
104 // Set destination table name
105 bulkData.DestinationTableName = tableName;
106 // Write data
107 bulkData.WriteToServer(reader);
108 // Close objects
109 bulkData.Close();
110 destinationConnector.Close();
111 mySqlConn.Close();
112 }
113
114 public void Dispose()
115 {
116 if (mySqlConn != null)
117 mySqlConn.Close();
118 }
2. 再写个数据库类型类:
1using System;
2using System.Collections.Generic;
3using System.Text;
4
5namespace PinkDatabaseSync
6{
7 public class SQLDBSystemType
8 {
9 public static Dictionary<string, string> systemTypeDict
10 {
11 get{
12 var systemTypeDict = new Dictionary<string, string>();
13 systemTypeDict.Add("34", "image");
14 systemTypeDict.Add("35", "text");
15 systemTypeDict.Add("36", "uniqueidentifier");
16 systemTypeDict.Add("40", "date");
17 systemTypeDict.Add("41", "time");
18 systemTypeDict.Add("42", "datetime2");
19 systemTypeDict.Add("43", "datetimeoffset");
20 systemTypeDict.Add("48", "tinyint");
21 systemTypeDict.Add("52", "smallint");
22 systemTypeDict.Add("56", "int");
23 systemTypeDict.Add("58", "smalldatetime");
24 systemTypeDict.Add("59", "real");
25 systemTypeDict.Add("60", "money");
26 systemTypeDict.Add("61", "datetime");
27 systemTypeDict.Add("62", "float");
金属防腐蚀28 systemTypeDict.Add("98", "sql_variant");
29 systemTypeDict.Add("99", "ntext");
30 systemTypeDict.Add("104", "bit");
31 systemTypeDict.Add("106", "decimal");
32 systemTypeDict.Add("108", "numeric");
33 systemTypeDict.Add("122", "smallmoney");
34 systemTypeDict.Add("127", "bigint");
35 systemTypeDict.Add("240-128", "hierarchyid");
36 systemTypeDict.Add("240-129", "geometry");
37 systemTypeDict.Add("240-130", "geography");
38 systemTypeDict.Add("165", "varbinary");
39 systemTypeDict.Add("167", "varchar");
40 systemTypeDict.Add("173", "binary");
41 systemTypeDict.Add("175", "char");
吊带裙42 systemTypeDict.Add("189", "timestamp");
43 systemTypeDict.Add("231", "nvarchar");
44 systemTypeDict.Add("239", "nchar");
45 systemTypeDict.Add("241", "xml");
46 systemTypeDict.Add("231-256", "sysname");
47 return systemTypeDict;
48 }
49 }
50 }
51}
1public void BulkCopyTo(string server, string database, string uid, string password, string tableName, string primaryKeyName)
2 {
oppox13 string connectionString = "Server=" + server + ";Database=" + database + ";User Id=" + uid + ";Password=" + password;
4 // Create destination connection
5 SqlConnection destinationConnector = new SqlConnection(connectionString);
6
7 SqlCommand cmd = new SqlCommand("SELECT * FROM " + tableName, destinationConnector);
8 // Open source and destination connections.
9 this.EnsureConnectionIsOpen();
10 destinationConnector.Open();
11
12 SqlDataReader readerSource = cmd.ExecuteReader();
13 bool isSourceContainsData = false;
14 string whereClause = " where ";
15 while (readerSource.Read())
16 {
17 isSourceContainsData = true;
18 whereClause += " " + primaryKeyName + "!=" + readerSource[primaryKeyName].ToString() + " and ";
19 }
20 whereClause = whereClause.Remove(whereClause.Length - " and ".Length, " and ".Length);
21 readerSource.Close();
22
23 whereClause = isSourceContainsData ? whereClause : string.Empty;
24
25 // Select data from Products table
26 cmd = new SqlCommand("SELECT * FROM " + tableName + whereClause, mySqlConn);
27 // Execute reader
28 SqlDataReader reader = cmd.ExecuteReader();
29 // Create SqlBulkCopy
30 SqlBulkCopy bulkData = new SqlBulkCopy(destinationConnector);
31 // Set destination table name
32 bulkData.DestinationTableName = tableName;
33 // Write data
34 bulkData.WriteToServer(reader);
35 // Close objects
36 bulkData.Close();
exe文件打不开37 destinationConnector.Close();
38 mySqlConn.Close();
39 }
4. 最后执⾏同步函数:
1private void SyncDB_Click(object sender, EventArgs e)
2 {
3 string server = "localhost";
4 string dbname = "pinkCRM";
5 string uid = "sa";
6 string password = "password";
7 string server2 = "server2";
8 string dbname2 = "pinkCRM2";
9 string uid2 = "sa";
10 string password2 = "password2";
11 try
12 {
13 LogView.Text = "DB data is syncing!";
14 DBUtility db = new DBUtility(server, dbname, uid, password);
15 DataSet ds = db.ExecuteDS("SELECT sobjects.name FROM sysobjects sobjects pe = 'U'");
16 DataRowCollection drc = ds.Tables[0].Rows;
17 foreach (DataRow dr in drc)
18 {
19 string tableName = dr[0].ToString();
20 LogView.Text = LogView.Text + Environment.NewLine + " syncing table:" + tableName + Environment.NewLine;
21 DataSet ds2 = db.ExecuteDS("SELECT * lumns WHERE object_id = OBJECT_ID('dbo." + tableName + "')");
22 DataRowCollection drc2 = ds2.Tables[0].Rows;
23 string primaryKeyName = drc2[0]["name"].ToString();
24 db.BulkCopyTo(server2, dbname2, uid2, password2, tableName, primaryKeyName);
25
26 LogView.Text = LogView.Text +"Done sync data for table:"+ tableName+ Environment.NewLine;
27 }
28 MessageBox.Show("Done sync db data successfully!");
29 }
30 catch (Exception exc)
31 {
32 MessageBox.Show(exc.ToString());
33 }
34 }
注: 这⾥只写了对已有数据的不再插⼊数据,可以再提⾼为如果有数据更新,可以进⾏更新,那么⼀个数据库同步⼯具就可以完成了!
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论