首页 > 代码库 > C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]
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 = http://www.mamicode.com/false;>
2. 再写个数据库类型类:
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 = http://www.mamicode.com/false;>
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()); } }注: 这里只写了对已有数据的不再插入数据,可以再提高为如果有数据更新,可以进行更新,那么一个数据库同步工具就可以完成了!
C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。