使用SqlBulkCopy類批量複製大資料

來源:互聯網
上載者:User

本文轉載:http://zhoufoxcn.blog.51cto.com/792419/166052

參考http://www.cnblogs.com/scottckt/archive/2011/02/16/1955862.html

 用途說明:

前些日子,公司要求做一個資料匯入程式,要求將Excel資料,大批量的匯入到資料庫中,盡量少的訪問資料庫,高效能的對資料庫進行儲存。於是在網上進行尋找,發現了一個比較好的解決方案,就是採用SqlBulkCopy來處理儲存資料。SqlBulkCopy儲存大批量的資料非常的高效,就像這個方法的名字一樣,可以將記憶體中的資料表直接的一次性的儲存到資料庫中,而不需要一次一次的向資料庫Insert資料。初次實驗,百萬層級的資料表,也只需幾秒時間內就可以完全的存入資料庫中,其速度,比傳統的Insert方法不止快很多倍

 

       MSDN摘要:Microsoft SQL Server 提供一個稱為 bcp 的流行的命令提示字元工具 + 生產力,用於將資料從一個表移動到另一個表(表既可以在同一個伺服器上,也可以在不同伺服器上)。SqlBulkCopy 類允許編寫提供類似功能的Managed 程式碼解決方案。還有其他將資料載入到 SQL Server 表的方法(例如 INSERT 語句),但相比之下 SqlBulkCopy 提供明顯的效能優勢。

使用 SqlBulkCopy 類只能向 SQL Server 表寫入資料。但是,資料來源不限於 SQL Server;可以使用任何資料來源,只要資料可載入到 DataTable 執行個體或可使用 IDataReader 執行個體讀取資料。

 

  /// <summary>
        /// 
        /// </summary>
        /// <param name="connectionString">目標串連字元</param>
        /// <param name="TableName">目標表</param>
        /// <param name="dt">來源資料</param>
        private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy =new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(dt);
                    }
                    catch (System.Exception ex)
                    {
                        throw ex;
                    }
                }
            }
        }

 

 

 SqlBulkCopy使用使用用於自訂表格類型作為預存程序參數,批量寫入資料。
 
  如果,大家使用SQL Server 2008,它提供一個新的功能表變數(Table Parameters)可以將整個表資料彙集成一個參數傳遞給預存程序或SQL語句。它的注意效能開銷是將資料彙集成參數(O(資料量))。
 
  現在,我們修改之前的代碼,在SQL Server中定義我們的表變數,具體定義如下:
 -- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Declares a user table paramter.
-- =============================================
CREATE TYPE jk_users_bulk_insert AS TABLE (
    user_login varchar(60),
    user_pass varchar(64),
    user_nicename varchar(50),
    user_email varchar(100),
    user_url varchar(100),
    user_activation_key varchar(60),
    user_status int,
    display_name varchar(250)
)
 
  上面,我們定義了一個表參數jk_users_bulk_insert,接著我們定義一個預存程序接受表參數jk_users_bulk_insert,具體定義如下:
 -- =============================================
-- Author:        JKhuang
-- Create date: 08/16/2012
-- Description:    Creates a stored procedure, receive
-- a jk_users_bulk_insert argument.
-- =============================================
CREATE PROCEDURE sp_insert_jk_users 
@usersTable jk_users_bulk_insert READONLY 
AS
INSERT INTO jk_users (user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, user_registered) 
SELECT user_login, user_pass, user_nicename, user_email, user_url, 
user_activation_key, user_status, display_name, GETDATE() 
FROM @usersTable 
  接下我們在用戶端代碼中,調用預存程序並且將表作為參數方式傳遞給預存程序。
 var sw = Stopwatch.StartNew();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLCONN2"].ToString()))
{
    conn.Open();
    //// Invokes the stored procedure.
    using (var cmd = new SqlCommand("sp_insert_jk_users", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        //// Adding a "structured" parameter allows you to insert tons of data with low overhead
        var param = new SqlParameter("@userTable", SqlDbType.Structured) { Value = dt };
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
}
sw.Stop(); 
  現在,我們重新執行寫入操作發現寫入效率與SqlBulkCopy相當。

 

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.