巧用Bulk Copy将大量的数据复制到数据库(对同一个数据的表或不同一个数据库的表都行)

最新推荐文章于 2022-03-31 20:28:44 发布

jimodushi

最新推荐文章于 2022-03-31 20:28:44 发布

阅读量1.1k

点赞数

文章标签：数据库 textbox string 数据库服务器服务器 server

转载地址为:
http://tech.ccidnet.com/art/1105/20071205/1298111_1.html

假如我们一次准备向数据库服务器提交多条记录 , 一般可能会执行多次Insert命令 , 但这样做就要为插入的每个记录执行一次与数据库服务器的往返 , 显然这会给服务器增加很多的压力 , 效率方面也会大大的降低。

.Net FrameWork 2.0新增功能Bulk Copy可以很快将大量数据加载到数据库中 , 现在我们来利用这一新功能来实现上述功能.

这里从SQL Server 2000的NorthWind 的Orders表加载数据到DateTable模拟要向数据库服务器提交的多条记录集 . 用Tempdb库来模拟目标数据库服务器.

首先我们需要在Tempdb 建一个表temp_orders

       
       
        
        　　USE TEMPDB 
        
        
　　CREATE TABLE TEMP_ORDERS 
        
        
　　( 
        
        
　　TEMP_ORDERID INT, 
        
        
　　TEMP_CUSTOMERID NCHAR(5), 
        
        
　　TEMP_ORDERDATE DATETIME, 
        
        
　　TEMP_SHIPNAME NVARCHAR(40) 
        
        
　　)下面为模拟程序 
        
        
　　protected void Page_Load(object sender, EventArgs e) 
        
        
　　{ 
        
        
　　#region 从NorthWind的Orders表获取要插入的数据 
        
        

        
        
　　DataTable dtNorthWindOrders = new DataTable(); 
        
        

        
        
　　using ( SqlConnection northWindConnection = new SqlConnection( "Data
        
        

        
        
 Source=.;Initial Catalog=NorthWind;Integrated Security=True" ) ) 
        
        
　　{ 
        
        
　　using ( SqlDataAdapter northWindAdapter = new SqlDataAdapter( "SELECT 
        
        

        
        
ORDERID,CUSTOMERID,ORDERDATE,SHIPNAME FROM ORDERS" , northWindConnection ) ) 
        
        
　　{ 
        
        
　　northWindAdapter.Fill( dtNorthWindOrders ); 
        
        
　　} 
        
        
　　} 
        
        
　　#endregion 
        
        
　　using ( SqlConnection tempdbConnection = new SqlConnection
        
        
( "Data Source=.;Initial Catalog=Tempdb;Integrated Security=True" ) ) 
        
        
　　{ 
        
        
　　tempdbConnection.Open( ); 
        
        
　　using ( SqlTransaction tran = tempdbConnection.BeginTransaction( ) ) 
        
        
　　{ 
        
        
　　SqlBulkCopy bulkCopyOrders = new SqlBulkCopy( tempdbConnection , 
        
        

        
        
SqlBulkCopyOptions.Default , tran ); 
        
        

        
        
　　bulkCopyOrders.DestinationTableName = "TEMP_ORDERS"; 
        
        
　　//将数据源表字段和目标表的字段做个映射 
        
        
　　bulkCopyOrders.ColumnMappings.Add( "ORDERID" , "TEMP_ORDERID" ); 
        
        
　　bulkCopyOrders.ColumnMappings.Add( "CUSTOMERID" , "TEMP_CUSTOMERID" ); 
        
        
　　bulkCopyOrders.ColumnMappings.Add( "ORDERDATE" , "TEMP_ORDERDATE" ); 
        
        
　　bulkCopyOrders.ColumnMappings.Add( "SHIPNAME" , "TEMP_SHIPNAME" ); 
        
        
　　bulkCopyOrders.BulkCopyTimeout = 1000; 
        
        
　　//每处理10行触发一个事件向页面上输出一个消息 
        
        
　　bulkCopyOrders.SqlRowsCopied += new SqlRowsCopiedEventHandler( onRowsCopy ); 
        
        
　　bulkCopyOrders.NotifyAfter = 10; 
        
        
　　try 
        
        
　　{ 
        
        
　　bulkCopyOrders.WriteToServer( dtNorthWindOrders ); 
        
        
　　tran.Commit( ); 
        
        
　　} 
        
        
　　catch ( Exception ex ) 
        
        
　　{ 
        
        
　　Response.Write( ex.ToString( ) ); 
        
        
　　} 
        
        
　　finally 
        
        
　　{ 
        
        
　　dtNorthWindOrders = null; 
        
        
　　} 
        
        
　　} 
        
        
　　} 
        
        
　　} 
        
        
　　private void onRowsCopy ( object Sender , SqlRowsCopiedEventArgs args ) 
        
        
　　{ 
        
        
　　Response.Write("已复制："+ args.RowsCopied.ToString( ) + " 
        
        
" );
        
        

        
        
}

我们通过SQL Server事件探察器发现执行的SQL为:

       
       
        
        insert bulk TEMP_ORDERS ([TEMP_ORDERID] Int, 
        
        
[TEMP_CUSTOMERID] NChar(5) COLLATE Chinese_PRC_CI_AS, 
        
        
[TEMP_ORDERDATE] DateTime, 
        
        
[TEMP_SHIPNAME] NVarChar(40) COLLATE Chinese_PRC_CI_AS)

当我们通过运行程序可以看出这个速度会特别的快 , 使用此方法的明显优点是 : 减少对数据库的访问次数。

注释：WriteToServer不仅可以处理DataTable对象 , 同时它还可以处理DataReader ,DataRow 对象数组。

-----------------------------------------------------------------------------------------------------------------------------------------------
下面是我自己写的针对跨服务器的数据库数据对考.但有个问题,一但数据太多,就会在复制的时候出现连接数据库超时,现在还没有找到解决办法.

首先.在后台代码里在写不同服务器的连接字符串的时候把用户名和密码写死(也可以通过在页面里填加),然后从页面输入要查询的服务器IP,数据库名称,表名和被插入的目标服务器IP,数据库名称,表名,这也就能更灵活的实现数据对考.
具体代码如下:
页面代码:

       <div>

        <fieldset>

            <legend>查询所需要的数据信息</legend>

              查询的服务器IP:<asp:TextBox ID="tbSelectServerIP" runat="server" />&nbsp;&nbsp;

            查询的数据库名称:<asp:TextBox ID="tbDatabaseName" runat="server" />&nbsp;&nbsp;

                查询表的名称:<asp:TextBox ID="tbTableName" runat="server" />

        </fieldset>

        <br />

        <br />

        <fieldset>

            <legend>更新所需要的数据信息</legend>

              更新的服务器IP:<asp:TextBox ID="tbUpdateServerIP" runat="server" />&nbsp;&nbsp;

            更新的数据库名称:<asp:TextBox ID="tbUpdateDatabaseName" runat="server" />&nbsp;&nbsp;

                更新表的名称:<asp:TextBox ID="tbUpdateTableName" runat="server" />

        </fieldset>

        <br />

        <br />

        <asp:Button ID="btnTransact" runat="server" Text="执行" OnClick="btnTransact_Click" />

    </div>

后台代码:
   /// 从指定数据库的表里读出数据,然后更新到指定数据库的指定表里
    /// </summary>
    /// <param name="selectServerIP">查询服务器的IP</param>
    /// <param name="selectDatabaseName">查询的数据库的名称</param>
    /// <param name="selectTableName">查询表的名称</param>
    /// <param name="updateServerIP">更新服务器的IP</param>
    /// <param name="updateDatabaseIP">更新数据库的名称</param>
    /// <param name="updateTableName">更新表的名称</param>
    public void UpdateTable(string selectServerIP,string selectDatabaseName ,string selectTableName,string updateServerIP ,string updateDatabaseName, string updateTableName)
    {
        #region 从指定数据库的表获取要插入的数据
        //查询出指定表里的列名
        string strTableColumnsName = "SELECT NAME FROM SYSCOLUMNS WHERE ID=(SELECT MAX(ID) FROM SYSOBJECTS WHERE XTYPE='U' AND NAME='" + selectTableName + "')";
        //查询出指定表里的数据
        string strTableData = "SELECT * FROM " + selectTableName + "";
        DataTable dsSelectTable = new DataTable();       //装载表里的数据
        DataTable dtTableColumnsName = new DataTable();  //装载表字段的名称

        using (SqlConnection conSelect = new SqlConnection("server=" + selectServerIP + ";uid=sa;pwd=sa;database=" + selectDatabaseName + ""))
        {
            SqlDataAdapter sdaColumnsName = new SqlDataAdapter(strTableColumnsName, conSelect);
            sdaColumnsName.Fill(dtTableColumnsName);
            SqlDataAdapter sdaTableData = new SqlDataAdapter(strTableData, conSelect);
            sdaTableData.Fill(dsSelectTable);
            conSelect.Close();
        }
        #endregion

        using (SqlConnection conUpdate = new SqlConnection("server=" + updateServerIP + ";uid=sa;pwd=sa;database=" + updateDatabaseName + ""))
        {
            conUpdate.Open();
            using (SqlTransaction tran = conUpdate.BeginTransaction()) //声明一个事务对象
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conUpdate, SqlBulkCopyOptions.Default, tran); //创建一个SqlBulkCopy对象
                bulkCopy.DestinationTableName = updateTableName;  //为SqlBulkCopy对象赋值，值为服务器上插入数据的目标表的名称
                for (int i = 0; i < dtTableColumnsName.Rows.Count; i++) //循环目标表中的字段的行数
                {
                    bulkCopy.ColumnMappings.Add(i, i);//通过序号指定源列和目标列，创建一个新的 System.Data.SqlClient.SqlBulkCopyColumnMapping 并将其添加到集合中。
                }
                bulkCopy.BulkCopyTimeout = 1000;
                //每处理10行触发一个事件向页面上输出一个消息
                bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);
                bulkCopy.NotifyAfter = 10;
                try
                {
                    Response.Write("共有数据："+dsSelectTable.Rows.Count.ToString()+"  <br/>");
                    bulkCopy.WriteToServer(dsSelectTable);
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    Response.Write(ex.ToString());
                }
                finally
                {
                    dsSelectTable = null;
                }
            }
        }
    }

    void bulkCopy_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
    {
        Response.Write("已复制:"+e.RowsCopied.ToString()+" ");
    }
    protected void btnTransact_Click(object sender, EventArgs e)
    {
        ///查询的服务器IP
        string selectServerIP = this.tbSelectServerIP.Text.Trim();
        ///查询的数据库名称
        string selectDatabaseName = this.tbDatabaseName.Text.Trim();
        //查询表的表名
        string selectTableName = this.tbTableName.Text.Trim();
        ///更新的服务器IP
        string updateServerIP = this.tbUpdateServerIP.Text.Trim();
        ///更新的数据库名称
        string updateDatabaseName = this.tbUpdateDatabaseName.Text.Trim();
        ///更新表的表名
        string updateTableName = this.tbUpdateTableName.Text.Trim();

        UpdateTable(selectServerIP, selectDatabaseName, selectTableName, updateServerIP, updateDatabaseName, updateTableName);
    }