神奇的 SqlBulkCopy 高效能大量匯入sqlserver

因為有利的開發環境，讓我有機會來折騰一下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中  程式碼: 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("用時:"+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.X//sql2005";  
 
    }  
 
    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.X//sql2005";

    }

    private static string GetOracleConnectionString()

    {

        return "Data Source=庫;User ID=使用者名稱;Password=密碼";

    }

}經測試通過 從ORACLE中導一張82萬條資料的表到SQL2005中只花了2分鐘多一點，當時我我非常驚訝，就連BCP匯入也沒這麼快過，讓我驚歎不已。 資料來源可以是任意，但目標資料庫一定要是MSSQL，這讓我感到有點不爽，因為ORACLE.DATA.ORACLECLIENT命名空間沒有這個類,也就是說你可以 從任何來源資料導到MSSQL,這或許是微軟的一種戰略,讓別人從其它資料庫切換到MS資料庫快更方便吧!