使用SqlBulkCopy数据导入和复制
因?yàn)橛欣拈_(kāi)發(fā)環(huán)境，讓我有機(jī)會(huì)來(lái)折騰一下ADO中的SqlBulkCopy這個(gè)功能。因?yàn)樵?jīng)公司做過(guò)庫(kù)遷移，把數(shù)據(jù)從MSSQL2005遷到ORACLE下，當(dāng)時(shí)本人用C#寫(xiě)了一個(gè)遷移工具，因當(dāng)時(shí)對(duì).NET操作數(shù)據(jù)庫(kù)只是一般的熟練,沒(méi)能發(fā)揮它的最佳性能和方法,寫(xiě)出來(lái)的工具在導(dǎo)數(shù)據(jù)的時(shí)候顯示有點(diǎn)慢,后來(lái)改用BCP來(lái)導(dǎo),BCP做數(shù)據(jù)導(dǎo)入還是非?？?。時(shí)過(guò)幾個(gè)月，心想自己再優(yōu)化一下算法和方法，讓數(shù)據(jù)遷移更快更方便，于是今天就先折騰一下.NET中的BCP批量導(dǎo)入功能. 測(cè)試環(huán)境: 1、WINDOWS SERVER 2003 +MSSQL2005（服務(wù)器） 2、LINUX+ORACLE 10G（服務(wù)器） 3、本人電腦配置：Pentium D 2.8G+2G內(nèi)存 4、 本人操作系統(tǒng)：WINDOWS XP+VS2005+.NET FRAMEWORK2.0 5、從ORACLE數(shù)據(jù)庫(kù)中導(dǎo)一張82萬(wàn)的數(shù)據(jù)到MSSQL2005中? 程序代碼: view plaincopy to clipboardprint?
using System;??
using System.Data.SqlClient;??
using System.Data.OracleClient;??
?
class Program??
?
{??
??? static void Main()??
?
??? {??
??????? string connectionString = GetOracleConnectionString();??
?
?????? try?
?
??????? {??
?
??????? using (OracleConnection sourceConnection = new OracleConnection(connectionString))??
?
??????? {??
?
??????????? sourceConnection.Open();??
?
??????????? OracleCommand commandRowCount = new OracleCommand( "SELECT COUNT(*) FROM 表", sourceConnection);??
?
??????????? long countStart = System.Convert.ToInt32(??
?
??????????? commandRowCount.ExecuteScalar());??
?
??????????? Console.WriteLine("Starting row count = {0}", countStart);??
?
??????? DateTime Dt1=DateTime.Now;??
?
??????????? Console.WriteLine("Start Reading Data....");??
?
??????????? string Sqltxt="select * from 表";??
?
??????????? OracleCommand commandSourceData = new OracleCommand(Sqltxt, sourceConnection);??
?
??????????? OracleDataReader reader =??
?
??????????? commandSourceData.ExecuteReader();??
?
?????? string desconnectionString=GetMsConnectionString();??
?
??????????? Console.WriteLine("Start Moveing Data....");??
?
??????????? using (SqlConnection destinationConnection = new SqlConnection(desconnectionString))??
?
??????????? {??
?
??????????????? destinationConnection.Open();??
?
??????????????? using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))??
?
??????????????? {??
?
?????????????????? bulkCopy.BulkCopyTimeout=5000000;??
?
?
??????????????????? bulkCopy.DestinationTableName ="dbo.表";??
?
??????????????????? try?
?
??????????????????? {??
?
??????????????????????? bulkCopy.WriteToServer(reader);??
?
??????????????????? }??
?
??????????????????? catch (Exception ex)??
?
??????????????????? {??
?
??????????????????????? Console.WriteLine(ex.Message);??
?
??????????????????? }??
?
??????????????????? finally?
?
??????????????????? {??
?
??????????????????????? reader.Close();??
?
??????????????????? }??
?
?
??????????????? }??
?
??????? DateTime Dt2=DateTime.Now;??
?
??????? TimeSpan CountTime=Dt2.Subtract(Dt1);??
?
??????????????? long countEnd = System.Convert.ToInt32(??
?
??????????????? commandRowCount.ExecuteScalar());??
?
??????????????? Console.WriteLine("Ending row count = {0}", countEnd);??
?
??????????????? Console.WriteLine("{0} rows were added.", countEnd - countStart);??
?
??????????????? Console.WriteLine("用時(shí):"+CountTime.Minutes.ToString()+"分"+CountTime.Seconds.ToString()+"秒"+CountTime.Milliseconds.ToString()+"毫秒");??
?
??????????????? Console.WriteLine("Press Enter to finish.");??
?
??????????????? Console.ReadLine();??
?
??????????? }??
?
??????? }??
?
??? }??
?
??? catch(Exception ex)??
?
??? {??
?
??????? Console.Write(ex.ToString());??
?
??? }??
?
? }??
?
??? private static string GetMsConnectionString()??
?
??? {??
??????? return "uid=用戶名;password=密碼;database=庫(kù);server=192.168.X.X\\sql2005";??
?
??? }??
?
??? private static string GetOracleConnectionString()??
?
??? {??
?
??????? return "Data Source=庫(kù);User ID=用戶名;Password=密碼";??
?
??? }??
?
}?
using System;
using System.Data.SqlClient;
using System.Data.OracleClient;
class Program
{
??? static void Main()
??? {
??????? string connectionString = GetOracleConnectionString();
?????? try
??????? {
??????? using (OracleConnection sourceConnection = new OracleConnection(connectionString))
??????? {
??????????? sourceConnection.Open();
??????????? OracleCommand commandRowCount = new OracleCommand( "SELECT COUNT(*) FROM 表", sourceConnection);
??????????? long countStart = System.Convert.ToInt32(
??????????? commandRowCount.ExecuteScalar());
??????????? Console.WriteLine("Starting row count = {0}", countStart);
???? DateTime Dt1=DateTime.Now;
??????????? Console.WriteLine("Start Reading Data....");
??????????? string Sqltxt="select * from 表";
??????????? OracleCommand commandSourceData = new OracleCommand(Sqltxt, sourceConnection);
??????????? OracleDataReader reader =
??????????? commandSourceData.ExecuteReader();
??? string desconnectionString=GetMsConnectionString();
??????????? Console.WriteLine("Start Moveing Data....");
??????????? using (SqlConnection destinationConnection = new SqlConnection(desconnectionString))
??????????? {
??????????????? destinationConnection.Open();
??????????????? using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection))
??????????????? {
?????????????? ??? bulkCopy.BulkCopyTimeout=5000000;
??????????????????? bulkCopy.DestinationTableName ="dbo.表";
??????????????????? try
??????????????????? {
??????????????????????? bulkCopy.WriteToServer(reader);
??????????????????? }
??????????????????? catch (Exception ex)
??????????????????? {
??????????????????????? Console.WriteLine(ex.Message);
??????????????????? }
??????????????????? finally
??????????????????? {
??????????????????????? reader.Close();
??????????????????? }
??????????????? }
??DateTime Dt2=DateTime.Now;
??TimeSpan CountTime=Dt2.Subtract(Dt1);
??????????????? long countEnd = System.Convert.ToInt32(
??????????????? commandRowCount.ExecuteScalar());
??????????????? Console.WriteLine("Ending row count = {0}", countEnd);
??????????????? Console.WriteLine("{0} rows were added.", countEnd - countStart);
??????????????? Console.WriteLine("用時(shí):"+CountTime.Minutes.ToString()+"分"+CountTime.Seconds.ToString()+"秒"+CountTime.Milliseconds.ToString()+"毫秒");
??????????????? Console.WriteLine("Press Enter to finish.");
??????????????? Console.ReadLine();
??????????? }
??????? }
??? }
??? catch(Exception ex)
??? {
??? ?Console.Write(ex.ToString());
??? }
? }
??? private static string GetMsConnectionString()
??? {
??????? return "uid=用戶名;password=密碼;database=庫(kù);server=192.168.X.X\\sql2005";
??? }
??? private static string GetOracleConnectionString()
??? {
??????? return "Data Source=庫(kù);User ID=用戶名;Password=密碼";
??? }
}經(jīng)測(cè)試通過(guò) 從ORACLE中導(dǎo)一張82萬(wàn)條數(shù)據(jù)的表到SQL2005中只花了2分鐘多一點(diǎn)，當(dāng)時(shí)我我非常驚訝，就連BCP導(dǎo)入也沒(méi)這么快過(guò)，讓我驚嘆不已。 數(shù)據(jù)源可以是任意，但目標(biāo)數(shù)據(jù)庫(kù)一定要是MSSQL，這讓我感到有點(diǎn)不爽，因?yàn)镺RACLE.DATA.ORACLECLIENT命名空間沒(méi)有這個(gè)類,也就是說(shuō)你可以 從任何源數(shù)據(jù)導(dǎo)到MSSQL,這或許是微軟的一種戰(zhàn)略,讓別人從其它數(shù)據(jù)庫(kù)切換到MS數(shù)據(jù)庫(kù)快更方便吧!
?
轉(zhuǎn)載于:https://www.cnblogs.com/jordan2009/archive/2010/01/11/1644265.html
總結(jié)
以上是生活随笔為你收集整理的使用SqlBulkCopy数据导入和复制的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
