C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]
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()); } }
注: 这里仅仅写了对已有数据的不再插入数据,能够再提高为假设有数据更新,能够进行更新,那么一个数据库同步工具就能够完毕了!