利用SqlBuikCopy实现数据批量写入

利用SqlBuikCopy实现不同数据库的同步

1，地区同步

View Code

 1  protected void btnSynchArea_Click(object sender, EventArgs e)
 2         {
 3             string strSql = "select [ID] ,[AreaId],[QuotationType],[GoodsTypeId],[ParentId]  from MD_Area;select [ID],[CnName],[EnName],[AreaCode] ,[Discription] ,[ParentId] ,[IsDelete] ,[ShowOrder] ,[LastModifyBy] ,[LastModifyDate] from MD_AreaInfo";
 4             using (DataSet ds = Query(strSql))
 5             {
 6                 if (ds == null) return;
 7                 if (ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0 || ds.Tables.Count < 2) return;
 8                 if (ds.Tables[1] == null || ds.Tables[1].Rows.Count == 0) return;
 9                 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString2);
10                 sb.MultipleActiveResultSets = true;
11                 using (SqlConnection conn = new SqlConnection(sb.ConnectionString))
12                 {
13                     conn.Open();
14                     using (SqlTransaction tran = conn.BeginTransaction())
15                     {
16                         int rowsAffect;
17                         using (SqlCommand cmd = conn.CreateCommand())
18                         {
19                             cmd.Transaction = tran;
20                             cmd.CommandText = "DELETE FROM MD_Area;DELETE FROM MD_AreaInfo";
21                             rowsAffect = cmd.ExecuteNonQuery();
22                         }
23                         SqlBulkCopy blkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
24                         blkCopy.ColumnMappings.Clear();
25                         blkCopy.DestinationTableName = "MD_Area";
26                         foreach (DataColumn dc in ds.Tables[0].Columns)
27                         {
28                             blkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
29                         }
30                         try
31                         {
32                             blkCopy.WriteToServer(ds.Tables[0]);
33                         }
34                         catch
35                         {
36                             throw;
37                         }
38                         blkCopy.ColumnMappings.Clear();
39                         blkCopy.DestinationTableName = "MD_AreaInfo";
40                         foreach (DataColumn dc in ds.Tables[1].Columns)
41                         {
42                             blkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
43                         }
44                         try
45                         {
46                             blkCopy.WriteToServer(ds.Tables[1]);
47                         }
48                         catch
49                         {
50                             throw;
51                         }
52                         tran.Commit();
53                     }
54                 }
55                 return;
56             }
57         }

2，交易品类型同步

View Code

 1 protected void btnSynchGoodsType_Click(object sender, EventArgs e)
 2         {
 3             string sql = "select [ID],[TypeID] ,[PropertyIndex] ,[DisplayName] ,[ShowOrder] from MD_GoodsMapping;" +
 4                          "select [ID],[GoodsTypeCnName] ,[GoodsTypeEnName] ,[ShowOrder] ,[CreateBy] ,[CreateTime],[LastModifyBy] ,[LastModifyTime] ,[IsDeleted] from MD_GoodsType;" +
 5                          "select [ID],[GoodsTypeID] ,[GroupID] from MD_GroupMapping";
 6             using (DataSet ds = Query(sql))
 7             {
 8                 if (ds == null) return;
 9                 if (ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0 || ds.Tables.Count < 3) return;
10                 if (ds.Tables[1] == null || ds.Tables[1].Rows.Count == 0) return;
11                 if (ds.Tables[2] == null || ds.Tables[2].Rows.Count == 0) return;
12                 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString2);
13                 sb.MultipleActiveResultSets = true;
14                 using (SqlConnection conn = new SqlConnection(sb.ConnectionString))
15                 {
16                     conn.Open();
17                     using (SqlTransaction tran = conn.BeginTransaction())
18                     {
19                         int rowsAffect;
20                         using (SqlCommand cmd = conn.CreateCommand())
21                         {
22                             cmd.Transaction = tran;
23                             cmd.CommandText = "DELETE FROM MD_GoodsMapping;DELETE FROM MD_GoodsType;DELETE FROM MD_GroupMapping;";
24                             rowsAffect = cmd.ExecuteNonQuery();
25                         }
26                         SqlBulkCopy blkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
27                         blkCopy.ColumnMappings.Clear();
28                         blkCopy.DestinationTableName = "MD_GoodsMapping";
29                         blkCopy.ColumnMappings.Add("ID", "ID");
30                         blkCopy.ColumnMappings.Add("TypeID", "TypeID");
31                         blkCopy.ColumnMappings.Add("PropertyIndex", "PropertyIndex");
32                         blkCopy.ColumnMappings.Add("DisplayName", "DisplayName");
33                         blkCopy.ColumnMappings.Add("ShowOrder", "ShowOrder");
34                         try
35                         {
36                             blkCopy.WriteToServer(ds.Tables[0]);
37                         }
38                         catch
39                         {
40 
41                             throw;
42                         }
43                         blkCopy.ColumnMappings.Clear();
44                         blkCopy.DestinationTableName = "MD_GoodsType";
45                         foreach (DataColumn dc in ds.Tables[1].Columns)
46                         {
47                             blkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
48                         }
49                         try
50                         {
51                             blkCopy.WriteToServer(ds.Tables[1]);
52                         }
53                         catch
54                         {
55 
56                             throw;
57                         }
58                         blkCopy.ColumnMappings.Clear();
59                         blkCopy.DestinationTableName = "MD_GroupMapping";
60                         foreach (DataColumn dc in ds.Tables[2].Columns)
61                         {
62                             blkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
63                         }
64                         try
65                         {
66                             blkCopy.WriteToServer(ds.Tables[2]);
67                         }
68                         catch
69                         {
70 
71                             throw;
72                         }
73                         tran.Commit();
74                     }
75                 }
76                 return;
77             }
78         }

3，交易品同步

View Code

  1   protected void btnSynchGoods_Click(object sender, EventArgs e)
  2         {
  3             string sql = "select [ID],[TypeID] ,[PropertyIndex] ,[DisplayName] ,[ShowOrder] from MD_GoodsMapping;" +
  4                         "select [ID],[GoodsTypeCnName] ,[GoodsTypeEnName] ,[ShowOrder] ,[CreateBy] ,[CreateTime],[LastModifyBy] ,[LastModifyTime] ,[IsDeleted] from MD_GoodsType;" +
  5                         "select [ID],[GoodsTypeID] ,[GroupID] from MD_GroupMapping;" +
  6                         "select [ID],[CnName],[EnName],[ClassCode],[ClassName],[Code],[property0],[property1],[property2],[property3],[property4],[property5],[property6],[property7],[property8],[property9],[Typeid],[isdelete],[istrade],[createby],[createdate],[lastmodifiedby],[lastmodifieddate],[showOrder] from MD_Goods;";
  7             using (DataSet ds = Query(sql))
  8             {
  9                 if (ds == null) return;
 10                 if (ds.Tables[0] == null || ds.Tables[0].Rows.Count == 0 || ds.Tables.Count < 4) return;
 11                 if (ds.Tables[1] == null || ds.Tables[1].Rows.Count == 0) return;
 12                 if (ds.Tables[2] == null || ds.Tables[2].Rows.Count == 0) return;
 13                 if (ds.Tables[3] == null || ds.Tables[3].Rows.Count == 0) return;
 14                 SqlConnectionStringBuilder sb = new SqlConnectionStringBuilder(connectionString2);
 15                 sb.MultipleActiveResultSets = true;
 16                 using (SqlConnection conn = new SqlConnection(sb.ConnectionString))
 17                 {
 18                     conn.Open();
 19                     using (SqlTransaction tran = conn.BeginTransaction())
 20                     {
 21                         int rowsAffect;
 22                         using (SqlCommand cmd = conn.CreateCommand())
 23                         {
 24                             cmd.Transaction = tran;
 25                             cmd.CommandText = "DELETE FROM MD_GoodsMapping;DELETE FROM MD_GoodsType;DELETE FROM MD_GroupMapping;DELETE FROM Quotation_TradeGoods;";
 26                             rowsAffect = cmd.ExecuteNonQuery();
 27                         }
 28                         SqlBulkCopy blkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
 29                         blkCopy.ColumnMappings.Clear();
 30                         blkCopy.DestinationTableName = "MD_GoodsMapping";
 31                         blkCopy.ColumnMappings.Add("ID", "ID");
 32                         blkCopy.ColumnMappings.Add("TypeID", "TypeID");
 33                         blkCopy.ColumnMappings.Add("PropertyIndex", "PropertyIndex");
 34                         blkCopy.ColumnMappings.Add("DisplayName", "DisplayName");
 35                         blkCopy.ColumnMappings.Add("ShowOrder", "ShowOrder");
 36                         try
 37                         {
 38                             blkCopy.WriteToServer(ds.Tables[0]);
 39                         }
 40                         catch
 41                         {
 42                             
 43                             throw;
 44                         }
 45                         blkCopy.ColumnMappings.Clear();
 46                         blkCopy.DestinationTableName = "MD_GoodsType";
 47                         foreach (DataColumn  dc in ds.Tables[1].Columns)
 48                         {
 49                             blkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
 50                         }
 51                         try
 52                         {
 53                             blkCopy.WriteToServer(ds.Tables[1]);
 54                         }
 55                         catch 
 56                         {
 57                             
 58                             throw;
 59                         }
 60 
 61                         blkCopy.ColumnMappings.Clear();
 62                         blkCopy.DestinationTableName = "MD_GroupMapping";
 63                         foreach (DataColumn dc in ds.Tables[2].Columns)
 64                         {
 65                             blkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
 66                         }
 67                         try
 68                         {
 69                             blkCopy.WriteToServer(ds.Tables[2]);
 70                         }
 71                         catch 
 72                         {
 73                             
 74                             throw;
 75                         }
 76                         blkCopy.ColumnMappings.Clear();
 77                         blkCopy.DestinationTableName = "Quotation_TradeGoods";
 78                         blkCopy.ColumnMappings.Add("ID", "TradeGoodsID");
 79                         blkCopy.ColumnMappings.Add("Typeid", "typeId");
 80                         blkCopy.ColumnMappings.Add("CnName", "GoodsChineseName");
 81                         blkCopy.ColumnMappings.Add("EnName", "GoodsEnglishName");
 82                         blkCopy.ColumnMappings.Add("property0", "property0");
 83                         blkCopy.ColumnMappings.Add("property1", "property1");
 84                         blkCopy.ColumnMappings.Add("property2", "property2");
 85                         blkCopy.ColumnMappings.Add("property3", "property3");
 86                         blkCopy.ColumnMappings.Add("property4", "property4");
 87                         blkCopy.ColumnMappings.Add("property5", "property5");
 88                         blkCopy.ColumnMappings.Add("property6", "property6");
 89                         blkCopy.ColumnMappings.Add("property7", "property7");
 90                         blkCopy.ColumnMappings.Add("property8", "property8");
 91                         blkCopy.ColumnMappings.Add("property9", "property9");
 92                         blkCopy.ColumnMappings.Add("ShowOrder", "ShowOrder");
 93                         blkCopy.ColumnMappings.Add("isdelete", "IsEnable");
 94                         try
 95                         {
 96                             blkCopy.WriteToServer(ds.Tables[3]);
 97                         }
 98                         catch
 99                         {
100                             throw;
101                         }
102                         tran.Commit();
103                     }
104 
105                 }
106             }
107         }
108     }

为了提高性能，在链接字符串中引入MultipleActiveResultSets属性为true，目的是使用多活动结果集与关联的连接相关联
对于SqlBuikCopy注意，ColumnMappings，DestinationTableName两个属性，第一个表示数据源中列与目标列的之间的关系；第二个目标表的名称
-------

4,两个新闻表同步

View Code

 1 string strSql = "SELECT * FROM RSS";
 2             string strDelete = "DELETE FROM NEWRSS  where link in (select link from Rss )";
 3             using (SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=DEMO;Persist Security Info=True;User ID=sa;PassWord=pass@word1"))
 4             {
 5                 con.Open();
 6                 using (SqlTransaction tran = con.BeginTransaction())
 7                 {
 8                     try
 9                     {
10                         SqlCommand cmd = con.CreateCommand();
11                         cmd.Transaction = tran;
12                         cmd.CommandText = strDelete;
13                         cmd.ExecuteNonQuery();
14                         cmd.CommandText = strSql;
15                         DataSet ds = new DataSet();
16                         SqlDataAdapter da = new SqlDataAdapter(cmd);
17                         da.Fill(ds);
18                         SqlBulkCopy blc = new SqlBulkCopy(con, SqlBulkCopyOptions.CheckConstraints, tran);
19                         blc.DestinationTableName = "newRss";
20                         SqlBulkCopyColumnMapping sccm = new SqlBulkCopyColumnMapping();
21                         sccm.SourceColumn = "link";
22                         sccm.DestinationColumn = "link";
23                         blc.ColumnMappings.Add(sccm);
24                         blc.ColumnMappings.Add("title", "title");
25                         blc.ColumnMappings.Add("summary", "summary");
26                         blc.ColumnMappings.Add("author", "author");
27                         blc.ColumnMappings.Add("content", "content");
28                         blc.ColumnMappings.Add("published", "published");
29                         blc.WriteToServer(ds.Tables[0]);
30                         tran.Commit();
31                     }
32                     catch (Exception ex)
33                     {
34                         tran.Rollback();
35                     }
36                 }
37 
38             }

OracleBuikCopy

app/Administrator/product/11.2.0/client_1/ODACDoc/DocumentationLibrary/doc/win.112/e23174/OracleBulkCopyClass.htm

OracleBuikCopy

 1 protected void btnOracle_Click(object sender, EventArgs e)
 2         {
 3             string strSelect = "SELECT * FROM SUPPLY_INFO";
 4             using (OracleConnection con = new OracleConnection("Data Source=localhost/orcl;User ID=simmspstest;PassWord=simmspstest"))
 5             {
 6                 con.Open();
 7                 OracleCommand cmd = con.CreateCommand();
 8                 cmd.CommandText = strSelect;
 9                 OracleDataAdapter da = new OracleDataAdapter(cmd);
10                 DataSet ds = new DataSet();
11                 da.Fill(ds);
12                 OracleBulkCopy obc = new OracleBulkCopy(con);
13                 obc.DestinationTableName = "newsupplyInfo";
14                 obc.ColumnMappings.Add("ID", "ID");
15                 obc.ColumnMappings.Add("SUPPLYNAME", "SUPPLYNAME");
16                 obc.ColumnMappings.Add("ADDRESS", "ADDRESS");
17                 obc.ColumnMappings.Add("CONTACTTEL", "CONTACTTEL");
18                 obc.WriteToServer(ds.Tables[0]);
19             }
20         }