使用 Sql BulkCopy 數據導入和復制
來源：程序員人生 發布時間：2014-04-03 19:30:22 閱讀次數：4759次
因為有利的開發環境，讓我有機會來折騰一下ADO中的SqlBulkCopy這個功能。因為曾經公司做過庫遷移，把數據從MSSQL2005遷到ORACLE下，當時本人用C#寫了一個遷移工具，因當時對.NET操作數據庫只是一般的熟練,沒能發揮它的最佳性能和方法,寫出來的工具在導數據的時候顯示有點慢,后來改用BCP來導,BCP做數據導入還是非常快。
時過幾個月，心想自己再優化一下算法和方法，讓數據遷移更快更方便，于是今天就先折騰一下.NET中的BCP批量導入功能.
測試環境:
1、WINDOWS SERVER 2003 +MSSQL2005（服務器）
2、LINUX+ORACLE 10G（服務器）
3、本人電腦配置：Pentium D 2.8G+2G內存
4、 本人操作系統：WINDOWS XP+VS2005+.NET FRAMEWORK2.0
5、從ORACLE數據庫中導一張82萬的數據到MSSQL2005中
程序代碼：
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("用時:"+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=庫;server=192.168.X.Xsql2005";
}
private static string GetOracleConnectionString()
{
return "Data Source=庫;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("用時:"+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=庫;server=192.168.X.Xsql2005";
}
private static string GetOracleConnectionString()
{
return "Data Source=庫;User ID=用戶名;Password=密碼";
}
}
經測試通過 從ORACLE中導一張82萬條數據的表到SQL2005中只花了2分鐘多一點，當時我我非常驚訝，就連BCP導入也沒這么快過，讓我驚嘆不已。 數據源可以是任意，但目標數據庫一定要是MSSQL，這讓我感到有點不爽，因為ORACLE.DATA.ORACLECLIENT命名空間沒有這個類,也就是說你可以 從任何源數據導到MSSQL,這或許是微軟的一種戰略,讓別人從其它數據庫切換到MS數據庫快更方便吧!
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
