C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9031字，纯文字阅读大概需要13分钟。

内容图文

下载网站:www.SyncNavigator.CN?

 客服QQ1793040

----------------------------------------------------------

关于HKROnline SyncNavigator 注册机价格的问题

HKROnline SyncNavigator 8.4.1 企业版数据同步软件 自2009年第一个版本开发出来以来，经过8年不断地根据客户需求，加强功能，修复bug，现在已经具备强大的数据库同步功能，以前官方syncnavigator授权码的价格是2800元一套，授权码是绑定电脑硬件的，更换硬件或者电脑，软件无法正常运行，需要重新购买授权码。

今年官方团队有其他项目，没有重点开发市场，其中一个以前官方团队的程序员开发了syncnavigator注册机，用这款注册机也能进行syncnavigator授权激活，功能和以前官方，没有任何影响，只是改变了授权方式。

因为这个版本的syncnavigator注册机是程序员自己开发的，因而成本比以前官方成本要小，并且没有做过多市场开发营销，所以价格相对以前来说优惠很多，这对于有数据同步需求的公司和团队来说，无疑是巨大的福音。

因为这款HKROnline SyncNavigator 软件是目前为止，国内做的最好的数据库同步软件，傻瓜式同步数据库，只需要你设置好来源数据库和目标数据库的账号和密码，一键开启，后台自动同步，断点续传，增量同步，几乎不占内存和CPU资源。并且还支持异构数据库，也可以同步部分表或者部分字段，都可以进行更为精准的设置操作。

未经允许不得转载：syncnavigator数据库同步|syncnavigator 授权码 » 关于HKROnline SyncNavigator 注册机价格的问题

C#同步SQL Server数据库中的数据
1. 先写个sql处理类：
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace PinkDatabaseSync
{
class DBUtility : IDisposable
{
private string Server;
private string Database;
private string Uid;
private string Password;
private string connectionStr;
private SqlConnection mySqlConn;

public void EnsureConnectionIsOpen()
{
if (mySqlConn == null)
{
mySqlConn = new SqlConnection(this.connectionStr);
mySqlConn.Open();
}
else if (mySqlConn.State == ConnectionState.Closed)
{
mySqlConn.Open();
}
}

public DBUtility(string server, string database, string uid, string password)
{
this.Server = server;
this.Database = database;
this.Uid = uid;
this.Password = password;
this.connectionStr = "Server=" + this.Server + ";Database=" + this.Database + ";User Id=" + this.Uid + ";Password=" + this.Password;
}

public int ExecuteNonQueryForMultipleScripts(string sqlStr)
{
this.EnsureConnectionIsOpen();
SqlCommand cmd = mySqlConn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
return cmd.ExecuteNonQuery();
}
public int ExecuteNonQuery(string sqlStr)
{
this.EnsureConnectionIsOpen();
SqlCommand cmd = new SqlCommand(sqlStr, mySqlConn);
cmd.CommandType = CommandType.Text;
return cmd.ExecuteNonQuery();
}

public object ExecuteScalar(string sqlStr)
{
this.EnsureConnectionIsOpen();
SqlCommand cmd = new SqlCommand(sqlStr, mySqlConn);
cmd.CommandType = CommandType.Text;
return cmd.ExecuteScalar();
}

public DataSet ExecuteDS(string sqlStr)
{
DataSet ds = new DataSet();
this.EnsureConnectionIsOpen();
SqlDataAdapter sda= new SqlDataAdapter(sqlStr,mySqlConn);
sda.Fill(ds);
return ds;
}

public void BulkCopyTo(string server, string database, string uid, string password, string tableName, string primaryKeyName)
{
string connectionString = "Server=" + server + ";Database=" + database + ";User Id=" + uid + ";Password=" + password;
// Create destination connection
SqlConnection destinationConnector = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand("SELECT * FROM " + tableName, destinationConnector);
// Open source and destination connections.
this.EnsureConnectionIsOpen();
destinationConnector.Open();

SqlDataReader readerSource = cmd.ExecuteReader();
bool isSourceContainsData = false;
string whereClause = " where ";
while (readerSource.Read())
{
isSourceContainsData = true;
whereClause += " " + primaryKeyName + "!=" + readerSource[primaryKeyName].ToString() + " and ";
}
whereClause = whereClause.Remove(whereClause.Length - " and ".Length, " and ".Length);
readerSource.Close();

whereClause = isSourceContainsData ? whereClause : string.Empty;

// Select data from Products table
cmd = new SqlCommand("SELECT * FROM " + tableName + whereClause, mySqlConn);
// Execute reader
SqlDataReader reader = cmd.ExecuteReader();
// Create SqlBulkCopy
SqlBulkCopy bulkData = new SqlBulkCopy(destinationConnector);
// Set destination table name
bulkData.DestinationTableName = tableName;
// Write data
bulkData.WriteToServer(reader);
// Close objects
bulkData.Close();
destinationConnector.Close();
mySqlConn.Close();
}

public void Dispose()
{
if (mySqlConn != null)
mySqlConn.Close();
}
}
}
2. 再写个数据库类型类：
using System;
using System.Collections.Generic;
using System.Text;

namespace PinkDatabaseSync
{
public class SQLDBSystemType
{
public static Dictionary<string, string> systemTypeDict
{
get{
var systemTypeDict = new Dictionary<string, string>();
systemTypeDict.Add("34", "image");
systemTypeDict.Add("35", "text");
systemTypeDict.Add("36", "uniqueidentifier");
systemTypeDict.Add("40", "date");
systemTypeDict.Add("41", "time");
systemTypeDict.Add("42", "datetime2");
systemTypeDict.Add("43", "datetimeoffset");
systemTypeDict.Add("48", "tinyint");
systemTypeDict.Add("52", "smallint");
systemTypeDict.Add("56", "int");
systemTypeDict.Add("58", "smalldatetime");
systemTypeDict.Add("59", "real");
systemTypeDict.Add("60", "money");
systemTypeDict.Add("61", "datetime");
systemTypeDict.Add("62", "float");
systemTypeDict.Add("98", "sql_variant");
systemTypeDict.Add("99", "ntext");
systemTypeDict.Add("104", "bit");
systemTypeDict.Add("106", "decimal");
systemTypeDict.Add("108", "numeric");
systemTypeDict.Add("122", "smallmoney");
systemTypeDict.Add("127", "bigint");
systemTypeDict.Add("240-128", "hierarchyid");
systemTypeDict.Add("240-129", "geometry");
systemTypeDict.Add("240-130", "geography");
systemTypeDict.Add("165", "varbinary");
systemTypeDict.Add("167", "varchar");
systemTypeDict.Add("173", "binary");
systemTypeDict.Add("175", "char");
systemTypeDict.Add("189", "timestamp");
systemTypeDict.Add("231", "nvarchar");
systemTypeDict.Add("239", "nchar");
systemTypeDict.Add("241", "xml");
systemTypeDict.Add("231-256", "sysname");
return systemTypeDict;
}
}
}
}
3. 写个同步数据库中的数据：
public void BulkCopyTo(string server, string database, string uid, string password, string tableName, string primaryKeyName)
{
string connectionString = "Server=" + server + ";Database=" + database + ";User Id=" + uid + ";Password=" + password;
// Create destination connection
SqlConnection destinationConnector = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand("SELECT * FROM " + tableName, destinationConnector);
// Open source and destination connections.
this.EnsureConnectionIsOpen();
destinationConnector.Open();

SqlDataReader readerSource = cmd.ExecuteReader();
bool isSourceContainsData = false;
string whereClause = " where ";
while (readerSource.Read())
{
isSourceContainsData = true;
whereClause += " " + primaryKeyName + "!=" + readerSource[primaryKeyName].ToString() + " and ";
}
whereClause = whereClause.Remove(whereClause.Length - " and ".Length, " and ".Length);
readerSource.Close();

whereClause = isSourceContainsData ? whereClause : string.Empty;

// Select data from Products table
cmd = new SqlCommand("SELECT * FROM " + tableName + whereClause, mySqlConn);
// Execute reader
SqlDataReader reader = cmd.ExecuteReader();
// Create SqlBulkCopy
SqlBulkCopy bulkData = new SqlBulkCopy(destinationConnector);
// Set destination table name
bulkData.DestinationTableName = tableName;
// Write data
bulkData.WriteToServer(reader);
// Close objects
bulkData.Close();
destinationConnector.Close();
mySqlConn.Close();
}

4. 最后执行同步函数：
private void SyncDB_Click(object sender, EventArgs e)
{
string server = "localhost";
string dbname = "pinkCRM";
string uid = "sa";
string password = "password";
string server2 = "server2";
string dbname2 = "pinkCRM2";
string uid2 = "sa";
string password2 = "password2";
try
{
LogView.Text = "DB data is syncing!";
DBUtility db = new DBUtility(server, dbname, uid, password);
DataSet ds = db.ExecuteDS("SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'");
DataRowCollection drc = ds.Tables[0].Rows;
foreach (DataRow dr in drc)
{
string tableName = dr[0].ToString();
LogView.Text = LogView.Text + Environment.NewLine + " syncing table:" + tableName + Environment.NewLine;
DataSet ds2 = db.ExecuteDS("SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo." + tableName + "')");
DataRowCollection drc2 = ds2.Tables[0].Rows;
string primaryKeyName = drc2[0]["name"].ToString();
db.BulkCopyTo(server2, dbname2, uid2, password2, tableName, primaryKeyName);

LogView.Text = LogView.Text +"Done sync data for table:"+ tableName+ Environment.NewLine;
}
MessageBox.Show("Done sync db data successfully!");
}
catch (Exception exc)
{
MessageBox.Show(exc.ToString());
}
}
注： 这里只写了对已有数据的不再插入数据，可以再提高为如果有数据更新，可以进行更新，那么一个数据库同步工具就可以完成了！
---------------------
作者：yangzhenping
来源：CSDN
原文：https://blog.csdn.net/yangzhenping/article/details/40380333
版权声明：本文为博主原创文章，转载请附上博文链接！

内容总结

以上是互联网集市为您收集整理的C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]全部内容，希望文章能够帮你解决C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端