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());
}
}
注： 这里只写了对已有数据的不再插入数据，可以再提高为如果有数据更新，可以进行更新，那么一个数据库同步工具就可以完成了！
