SqlServer優化之批量插入（SqlBulkCopy、表值參數）

之前做項目需要用到資料庫的批量插入，于是就研究了一下，現在做個總結。

建立了一個用來測試的Student表：

CREATE TABLE [dbo].[Student](
	[ID] [int] PRIMARY KEY NOT NULL,
	[Num] [varchar](10) NULL,
	[Name] [nvarchar](64) NULL,
	[Age] [int] NULL
	)
           

一、SqlBulkCopy類：使用資料庫BCP協定進行資料的批量複制，每一批的數量大約800條。

/// <summary>
        /// 批量插入SqlBulkCopy
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tableName">表名</param>
        public static void BatchInsertBySqlBulkCopy(DataTable dt, string tableName)
        {
            using (SqlBulkCopy sbc = new SqlBulkCopy(connString))
            {
                sbc.BatchSize = dt.Rows.Count;
                sbc.BulkCopyTimeout = 10;
                sbc.DestinationTableName = tableName;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
                }
                //全部寫入資料庫
                sbc.WriteToServer(dt);
            }
        }
           

5萬條資料插入花了2秒的時間：

二、表值參數：也叫表變量參數，使用使用者定義的表類型來聲明，簡單了解就是可以把一個表當做參數傳遞。

CREATE TYPE [dbo].[mytb_student] AS TABLE(
	[ID] [int] NOT NULL,
	[Num] [varchar](10) NULL,
	[Name] [nvarchar](64) NULL,
	[Age] [int] NULL
)
           

/// <summary>
        /// 批量插入使用表值參數
        /// </summary>
        /// <param name="dt"></param>
        public static void BatchInsertByTableValue(DataTable dt, string sqlText)
        {
            using (SqlConnection sqlConn = new SqlConnection(connString))
            {
                using (SqlCommand sqlCmd = new SqlCommand(sqlText, sqlConn))
                {
                    //把DataTable當做參數傳入
                    SqlParameter sqlPar = sqlCmd.Parameters.AddWithValue("@dt", dt);
                    //指定表值參數中包含的構造資料的特殊資料類型。
                    sqlPar.SqlDbType = SqlDbType.Structured;
                    sqlPar.TypeName = "dbo.mytb_student";//表值參數名稱
                    sqlConn.Open();
                    sqlCmd.ExecuteNonQuery();
                }
            }
        }
           

同樣插入5萬條資料，也是花了2秒的時間。

總結：SqlServer資料庫批量插入除了使用SqlBulkCopy和表值參數，還可以使用SqlDataAdapter的Update方法，經過本人測試，在資料量越大的情況下，使用SqlBulkCopy的性能是最好的。