C# 数据库批量插入数据之 —— 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>
/// 批量插入Sqlsbc
/// </summary>
/// <param name="dt"></param>
/// <param name="tableName">表名</param>
public static void BatchInsertBySqlsbc(DataTable dt, string tableName)
{
    using (SqlBulkCopy sbc = new SqlBulkCopy(connString))
    {
        sbc.BatchSize = dt.Rows.Count;    //分批提交记录数，可不设
        sbc.sbcTimeout = 10;    //超时时间设置
        sbc.DestinationTableName = tableName;    // 设置目标表名称
        // 列名映射  格式：ColumnMappings.Add("源数据列", "目标表对应列名称");
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, i);
        }
        //也可以像下面这样进行映射
        //sbc.ColumnMappings.Add("ID", "ID");
        //sbc.ColumnMappings.Add("Num", "Num");
        //sbc.ColumnMappings.Add("Name", "Name");
        //sbc.ColumnMappings.Add("Age", "Age");
        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秒的时间。 

static void Main(string[] args)
{
    Console.WriteLine("开始时间:"+DateTime.Now);
    DataTable dt=GetDataTable();
    Console.WriteLine("插入数量:"+dt.Rows.Count+"条");
    string sqlText=@"insert into Student(ID,Num,Name,Age)
        select t.ID,t.Num,t.Name,t.Age from @dt as t";
    SqlHelper.BatchInsertByTableValue(dt,sqlText);
    Console.WriteLine("结束时间:"+DateTime.Now);
    Console.ReadKey();
}

总结：SqlServer数据库批量插入除了使用SqlBulkCopy和表值参数，还可以使用SqlDataAdapter的Update方法，经过本人测试，在数据量越大的情况下，使用SqlBulkCopy的性能是最好的。