神奇的 SqlBulkCopy 高性能批量导入sqlserver

来源：互联网发布：app源码编辑：程序博客网时间：2024/05/18 02:33

因为有利的开发环境，让我有机会来折腾一下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数据库快更方便吧!