二.受到老同事数据库同步软件的启发,我制作了一个基于触发器的同步。
基本风格和老同事的一样,我们还是先来看界面
继续沿用了前人的智慧。。。
第一次界面跳转时,需要的时间比较长。。。(后台工作比较多)
刚刚几个图形不好截取,就是工具里面的,包括:表比较,完整备份,差异备份和增量备份。
这个数据库同步实现的原理是:
在主数据库服务器的指定数据库上面建一个基于库的触发器来记录数据库的库结构改变,然后直接基于数据库连接在从数据库上执行一遍;在数据库内所有的数据表上面建立一个基于表内容得触发器来记录各个表的数据变化情况。在退出软件时删掉所有的触发器。
相关代码:
#region 设置同步
//同步是思路:为主服务器上的每个表都创建三个触发器,用来做插入、更新、删除操作。
private void SetSynchronous()
{
//获取主机服务器上数据库中的所有数据表
GetHostserverDatatable();
}
#endregion
#region 获取主服务器数据库的表列表
private void GetHostserverDatatable()
{
listMessage.Items.Add("启动同步程序!");
try
{
listMessage.Items.Add("读取配置文件!");
XmlHelper xmlHelper;
try
{
xmlHelper = new XmlHelper("//config.xml");//配置文件存在或者错误
}
catch
{
listMessage.Items.Add("读取配置文件出错,或配置文件不错在!");
listMessage.Items.Add("创建默认配置文件!");
//配置文件不存在
//创建配置文件
XmlHelper xml = new XmlHelper();
xml.createXml();
//读取配置文件
listMessage.Items.Add("读取默认配置文件!");
xmlHelper = new XmlHelper("//config.xml");
}
XmlNode rootNode = xmlHelper.GetSubIndexNode("systemconfig", 0);//获取根节点
XmlNodeList hostServer = rootNode.ChildNodes[0].ChildNodes[0].ChildNodes;//获取主服务器配置节点
XmlNodeList hostTableNode = rootNode.ChildNodes[1].ChildNodes[0].ChildNodes;//获取服务器中所有的表
listMessage.Items.Add("准备配置表同步!");
#region 创建日志表
//判断日志表是否存在
string sql0 = @"IF not EXISTS (select 1 from sysobjects where id = object_id('ddl_log2') and type = 'U')
CREATE TABLE ddl_log2 (PostTime DATETIME NOT NULL DEFAULT (GETDATE()), TSQL text);";
string connectionString1 = "Data Source=192.168.0.104//sql2005;database=bobuitest2;User Id=sa;Password=123456";
//删除存在的触发器
string sql1 = @"IF EXISTS (SELECT * FROM sys.triggers WHERE parent_class = 0 AND name = 'Log') DROP TRIGGER Log ON DATABASE;";
SqlHelper.ExecuteNonQuery(connectionString1, CommandType.Text, sql0);
SqlHelper.ExecuteNonQuery(connectionString1, CommandType.Text, sql1);
#endregion
#region 数据同步
foreach (XmlNode xn in hostTableNode)
{
//创建表的触发器
DeleteTablesTrigger(xn.InnerText, connectionString1);
CreateTablesTrigger(xn.InnerText, connectionString1);
listMessage.Items.Add(xn.InnerText.Trim() + "配置成功!");
}
#endregion
#region 创建数据库库结构同步
//创建可能不存在的表
string sql2 = @"IF not EXISTS (select 1 from sysobjects where id = object_id('ddl_log2') and type = 'U')"+
"CREATE TABLE ddl_log2 (PostTime datetime, TSQL nvarchar(2000));";
//创建结构同步触发器
string sql3 = @"CREATE TRIGGER Log ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML SET @data = EVENTDATA() INSERT ddl_log2 (PostTime, TSQL) VALUES (GETDATE(), @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(2000)') ) ;";
SqlHelper.ExecuteNonQuery(connectionString1, CommandType.Text, sql2);
SqlHelper.ExecuteNonQuery(connectionString1, CommandType.Text, sql3);
#endregion
}
catch
{
}
}
#region 删除表的三种触发器
private void DeleteTablesTrigger(string tableName, string connectionString1)
{
if (tableName != "ddl_log2")
{
string sql1 = @"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TR_" + tableName + "]') and OBJECTPROPERTY(id, N'IsTrigger') = 1" +
") drop trigger TR_" + tableName;
SqlHelper.ExecuteNonQuery(connectionString1, CommandType.Text, sql1);
}
}
#endregion
#endregion
#region 创建表的三种触发器
private void CreateTablesTrigger(string tableName, string connectionString1)
{
if (tableName != "ddl_log2")
{
string sql2 = @" CREATE TRIGGER TR_" + tableName + " ON " + tableName +
" FOR INSERT,DELETE,UPDATE " +
" AS " +
" SET NOCOUNT ON " +
" CREATE TABLE #T_" + tableName + " ( " +
" EVENTTYPE VARCHAR(20)," +
" PARAMETERS INT, " +
" EVENTINFO VARCHAR(8000) " +
" ) " +
" DECLARE @SPID VARCHAR(20)" +
" SET @SPID=CAST(@@SPID AS VARCHAR)" +
" INSERT #T_" + tableName + " EXEC('DBCC INPUTBUFFER ('+@SPID+')') " +
" INSERT ddl_log2 ( " +
" TSQL " +
" ) " +
" SELECT EVENTINFO" +
" FROM #T_" + tableName;
SqlHelper.ExecuteNonQuery(connectionString1, CommandType.Text, sql2);
}
}
#endregion
#region 循环读取更改记录
private void timer1_Tick(object sender, EventArgs e)
{
//将更改值获取写入记事本中
SqlConnection conn = new SqlConnection(@"Data Source=192.168.0.104/sql2005;database=bobuitest2;User Id=sa;Password=123456;");
conn.Open();
DateTime dt = DateTime.Now;
string sql = "select * from [ddl_log2] where PostTime between '" + dt.AddSeconds(-30) + "' and '" + dt + "'";
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds, "ds");
conn.Close();
sda.Dispose();
int count = ds.Tables[0].Rows.Count;
try
{
string path1 = System.Windows.Forms.Application.StartupPath + "//Log//" + dt.AddSeconds(-30).Ticks + ".txt";
if (File.Exists(path1))
{
// File.Delete(path1);
}
//制作文件名
string path3 = System.Windows.Forms.Application.StartupPath + "//Log";
if (!Directory.Exists(path3))
{
Directory.CreateDirectory(path3);
}
string path2 = System.Windows.Forms.Application.StartupPath + "//Log//" + dt.Ticks + ".txt";
//保存文件
//实例化一个文件流--->与写入文件相关联
FileStream fs = new FileStream(path2, FileMode.Create);
//实例化一个StreamWriter-->与fs相关联
StreamWriter sw = new StreamWriter(fs);
//开始写入
for (int i = 0; i < count; i++)
{
string dateTime = ds.Tables[0].Rows[i][0].ToString().Trim();
string sqllog = ds.Tables[0].Rows[i][1].ToString().Trim();
sw.WriteLine(dateTime + "," + sqllog);
}
//清空缓冲区
sw.Flush();
//关闭流
sw.Close();
fs.Close();
}
catch
{
}
}
#endregion
private void 完全备份ToolStripMenuItem_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=192.168.0.104/sql2005;database=bobuitest2;User Id=sa;Password=123456;");
// string path=Application.StartupPath+"//"+(DateTime.Now).ToLongTimeString()+".bak";
DateTime dt = DateTime.Now;
string path = Application.StartupPath + "//" + dt.Year + "_" + dt.Month + "_" + dt.Day + ".bak";
if (File.Exists(path))
{
File.Delete(path);
}
//#region 产生一个随机的密码字符
//int password;
//Random random = new Random();
//password = random.Next(1000, 9999);
//string bakup = @"Backup Database bobuitest2 To disk='" + path + "' with password=" + password + "";
//#endregion
string bakup = @"Backup Database bobuitest2 To disk='" + path + "'";
conn.Open();
try
{
listMessage.Items.Add("开始备份");
listMessage.Items.Add("正在备份...");
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, bakup);
listMessage.Items.Add("备份成功!");
}
catch
{
listMessage.Items.Add("备份出错!");
}
finally
{
conn.Close();
}
}
private void 差异备份ToolStripMenuItem_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=192.168.0.104/sql2005;database=bobuitest2;User Id=sa;Password=123456;");
// string path=Application.StartupPath+"//"+(DateTime.Now).ToLongTimeString()+".bak";
DateTime dt = DateTime.Now;
string path = Application.StartupPath + "//diff" + dt.Year + "_" + dt.Month + "_" + dt.Day + ".bak";
if (File.Exists(path))
{
File.Delete(path);
}
string bakup = @"Backup Database bobuitest2 To disk='" + path + "' with differential ";
conn.Open();
try
{
listMessage.Items.Add("开始备份");
listMessage.Items.Add("正在进行差异备份...");
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, bakup);
listMessage.Items.Add("备份成功!");
}
catch
{
listMessage.Items.Add("备份出错!");
}
finally
{
conn.Close();
}
}
这样就完成了基于触发器的数据库同步,不过这样存在很多问题。
问题1:在互联网模式下完全依靠sql的链接是行不通的,特别是数据量大的情况
问题2:在数据库上加触发器破坏了数据库的完整性
问题3:使用触发器,程序的可控性太差,发生错误很难检查。特别是当从数据库上执行错误时将导致主数据库也不能执行成功。
上面三个问题告诉我们这种方法行不通。我们必须寻找另外的方法。