SqlBulkCopy类

首先:web.config

  < connectionStrings >
     < add  name ="srcDBConnection"  connectionString ="server=.;database=pubs;uid=sa;pwd=" />
     < add  name ="desDBConnection"  connectionString ="server=.;database=NorthWind;uid=sa;pwd=" />
   </ connectionStrings >

C#文件: 前台不Copy了,就一个按钮,一个Label

using  System;
using  System.Data;
using  System.Configuration;
using  System.Collections;
using  System.Web;
using  System.Web.Security;
using  System.Web.UI;
using  System.Web.UI.WebControls;
using  System.Web.UI.WebControls.WebParts;
using  System.Web.UI.HtmlControls;
using  System.Data.SqlClient;

public   partial   class  ASP_NET : System.Web.UI.Page
... {
    private DateTime startTime;

    protected void Button1_Click(object sender, EventArgs e)
    ...{
        startTime = DateTime.Now;
        string srcConnString = "";
        string desConnString = "";
        SqlConnection srcConnection = new SqlConnection();
        SqlConnection desConnection = new SqlConnection();
        SqlCommand sqlcmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataTable dt = new DataTable();
        //srcConnString = ConfigurationManager.ConnectionStrings["srcDBConnection"].ConnectionString;
        desConnString = ConfigurationManager.ConnectionStrings["desDBConnection"].ToString();
        //srcConnection.ConnectionString = srcConnString;
        srcConnection.ConnectionString = desConnString;
        sqlcmd.Connection = srcConnection;
        //sqlcmd.CommandText = "select * from jobs";
        sqlcmd.CommandText = "select * from abc";
        sqlcmd.CommandType = CommandType.Text;
        sqlcmd.Connection.Open();
        da.SelectCommand = sqlcmd;
        da.Fill(dt);

        SqlBulkCopy sbc = new SqlBulkCopy(desConnString,SqlBulkCopyOptions.UseInternalTransaction);
        sbc.BulkCopyTimeout = 5000;
        sbc.SqlRowsCopied +=new SqlRowsCopiedEventHandler(OnRowsCopied);
        sbc.NotifyAfter = dt.Rows.Count;

        try
        ...{
           // sbc.DestinationTableName = "jobs";
            sbc.DestinationTableName = "bcd";
            sbc.WriteToServer(dt);
        }
        catch (Exception ex)
        ...{
            lblCounter.Text = ex.Message.ToString();
        }
        finally
        ...{
            sqlcmd.Clone();
            srcConnection.Close();
            desConnection.Close();
            
        }

    }
    private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
    ...{
        lblCounter.Text += args.RowsCopied.ToString() + " rows are copied<Br>";
        TimeSpan copyTime = DateTime.Now - startTime;
        lblCounter.Text += "Copy Time:" + copyTime.Seconds.ToString() + "." + copyTime.Milliseconds.ToString() + " seconds";
    }
}

代码分析:

SqlBulkCopy sbc  =   new  SqlBulkCopy(desConnString,SqlBulkCopyOptions.UseInternalTransaction);
先生成SqlBulkCopy 实例，构造函数指定了目标数据库，使用SqlBulkCopyOptions.UseInternalTransaction是指迁移动作指定在一个Transaction当中，如果数据迁移中产生错误或异常将发生回滚。

sbc.BulkCopyTimeout = 5000000;    //指定操作完成的Timeout时间

 sbc.SqlRowsCopied  += new  SqlRowsCopiedEventHandler(OnRowsCopied);
  sbc.NotifyAfter  =  dt.Rows.Count;

         try
         ... {
           // sbc.DestinationTableName = "jobs";
            sbc.DestinationTableName = "bcd";
            sbc.WriteToServer(dt);
        }
NotifyAfter 属性指定通知通知事件前处理的数据行数，在这里指定为表的行数，并添加SqlRowsCopied事件输出整个迁移过程的时间。 WriteToServer方法就是将数据源拷备到目标数据库。在使用WriteToServer方法之前必须先指定 DestinationTableName属性，也就是目标数据库的表名，

性能方面:我在Sql中用proc插入68万条数据花了近8分钟,用SqlBulkCopy花了53.234秒~,效率高了7倍耶!不过现在也不做这方面的底层了,呵呵,把自己写的一个测试存储过程也贴上吧,方便自己学习

create   table  abc
(
  aid  int   identity ( 1 , 1 )  primary   key ,
  adesc  varchar ( 50 )  not   null
)
go

/**/ /**********存储过程**********************/
create   proc  addData
as
declare   @i   int
set   @i = 1
while   @i   <   1000000
begin
insert   into  abc  values  ( ' testDescription ' )
set   @i   =   @i   +   1
end
go

select   *   into  titles  from  pubs.dbo.titles  where    1 >   3  复制跨数据库的表结构

 

 

SqlBulkCopy sqlBC = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction);
            sqlBC.DestinationTableName = "CSDN_Users";
            DataTable dt = new DataTable("CSDN_Users");
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("UserName", typeof(string));
            dt.Columns.Add("PassWord", typeof(string));
            dt.Columns.Add("Email", typeof(string));
            StreamReader sr = new StreamReader(@"D:\CSDN.txt");
            string[] userInfo;
            string readStr;
            int flag = 0;
            while (sr.Peek() > -1)
            {
                flag++;
                readStr = sr.ReadLine();
                userInfo = readStr.Split('#');
                dt.Rows.Add(0, userInfo[0], userInfo[1], userInfo[2]);
                if (flag % 100000 == 0)
                {
                    sqlBC.WriteToServer(dt);
                    dt.Clear();
                }
            }
            sqlBC.WriteToServer(dt);
            dt.Dispose();
            sqlBC.Close();
            sr.Close();

转载于:https://www.cnblogs.com/jie566/archive/2012/08/13/2636317.html