1.该方法 基本不能用于实际开发中 ,仅供学习参考
public string Copy() { //要复制的表名 string table = "AAAAA"; //构造连接字符串 OracleConnectionStringBuilder builder1 = new OracleConnectionStringBuilder(); builder1.ConnectionString = "Data Source=192.168.0.141/orcl;User ID=MY_TP_ECDB;Password=MY_TP_ECDB;Unicode=true"; OracleConnectionStringBuilder builder2 = new OracleConnectionStringBuilder(); builder2.ConnectionString = "Data Source=192.168.0.141/orcl;User ID=BZECDB;Password=BZECDB;Unicode=true"; //调用复制数据库函数 InsertTable(builder1.ConnectionString, builder2.ConnectionString, table); return default(string); }
/// <summary> /// 将巴中商城拷贝到绵阳 /// </summary> private static void InsertTable(string conString1, string conString2, string tabStr) { OracleConnection connMY = new OracleConnection(); //connMY.ConnectionString = "Data Source=192.168.0.141/orcl;User ID=MY_TP_ECDB;Password=MY_TP_ECDB;Unicode=true"; connMY.ConnectionString = conString1; connMY.Open(); OracleConnection connBZ = new OracleConnection(); connBZ.ConnectionString = conString2; connBZ.Open(); //将巴中数据库拷贝到绵阳,那么先读出绵阳原来的数据保存在dataSet1中 OracleDataAdapter adapter1 = new OracleDataAdapter("select * from " + tabStr , connMY); DataSet dataSet1 = new DataSet(); if (dataSet1 != null) { adapter1.Fill(dataSet1, tabStr); } OracleDataAdapter adapter2 = new OracleDataAdapter("select * from " + tabStr , connBZ); DataSet dataSet2 = new DataSet();//巴中数据 OracleCommand cmd2 = new OracleCommand("select count(*) from " + tabStr, connBZ); Object res2 = cmd2.ExecuteScalar(); if (res2 != null) { int nCount = Convert.ToInt32(res2.ToString()); if (nCount == 0) { connBZ.Close(); connMY.Close(); return; } } //填充DataSet2 巴中的数据 if (dataSet2 != null) { adapter2.Fill(dataSet2, tabStr); } //复制数据 for (int j = 0; j < dataSet2.Tables[0].Rows.Count; j++) { dataSet1.Tables[0].LoadDataRow(dataSet2.Tables[0].Rows[j].ItemArray, false); } //将DataSet变换显示在与其关联的目标数据库 OracleCommandBuilder cb = new OracleCommandBuilder(adapter1); adapter1.Update(dataSet1, tabStr); cb.RefreshSchema(); Console.WriteLine("表" + tabStr + "复制成功!"); connBZ.Close(); connMY.Close(); }
2.巴中数据库同步到绵阳.rar 该项目为实际开发时的源码,因两地数据库不对外开发,Dblink 不能使用. 我的思路是巴中放一个windowsService
绵阳放一个webservice,巴中数据库相关表
建立触发器 对表的增删改 都记录在一张表中City_cfjlb(触发记录表),windowsService根据这张表的数据调用webservice 并将数据传递到绵阳.绵阳数据库也需另建立一张表,保存表名称,巴中对应表id ,绵阳对应表id
相关触发器代码 如下:
3,如果量数据库可以创建DBlink: 那就简单了:
Oracle两个数据库联合查询,使用Oracle DBLink
4.数据库创建主从关系:博主还未用到过次解决方案~~~