SqlServer 批量插入的方式比较常用的插入方式有Insert、BatchInsert、SqlBulkCop

SqlServer 批量插入的方式比较常用的插入方式有Insert、BatchInsert、SqlBulkCop

1.普通的Insert插入方法

public static void Insert(IEnumerablepersons)
{
    using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
    {
        con.Open();
        foreach (var person in persons)
        {
            using (var com = new SqlCommand(
                "INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES(@Id,@Name,@Age,@CreateTime,@Sex)",
                con))
            {
                com.Parameters.AddRange(new[]
                {
                    new SqlParameter("@Id", SqlDbType.BigInt) {Value = person.Id},
                    new SqlParameter("@Name", SqlDbType.VarChar, 64) {Value = person.Name},
                    new SqlParameter("@Age", SqlDbType.Int) {Value = person.Age},
                    new SqlParameter("@CreateTime", SqlDbType.DateTime)
                        {Value = person.CreateTime ?? (object) DBNull.Value},
                    new SqlParameter("@Sex", SqlDbType.Int) {Value = (int)person.Sex},
                });
                com.ExecuteNonQuery();
            }
        }
    }
}

2.拼接BatchInsert插入语句

public static void BatchInsert(Person[] persons)
{
    using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
    {
        con.Open();
        var pageCount = (persons.Length - 1) / 1000 + 1;
        for (int i = 0; i < pageCount; i++)
        {
            var personList = persons.Skip(i * 1000).Take(1000).ToArray();
            var values = personList.Select(p =>
                $"({p.Id},'{p.Name}',{p.Age},{(p.CreateTime.HasValue ? $"'{p.CreateTime:yyyy-MM-dd HH:mm:ss}'" : "NULL")},{(int) p.Sex})");
            var insertSql =
                $"INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES{string.Join(",", values)}";
            using (var com = new SqlCommand(insertSql, con))
            {
                com.ExecuteNonQuery();
            }
        }
    }
}

3.SqlBulkCopy插入方案

public static void BulkCopy(IEnumerablepersons)
{
    using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
    {
        con.Open();
        var table = new DataTable();
        table.Columns.AddRange(new []
        {
            new DataColumn("Id", typeof(long)), 
            new DataColumn("Name", typeof(string)), 
            new DataColumn("Age", typeof(int)), 
            new DataColumn("CreateTime", typeof(DateTime)), 
            new DataColumn("Sex", typeof(int)), 
        });
        foreach (var p in persons)
        {
            table.Rows.Add(new object[] {p.Id, p.Name, p.Age, p.CreateTime, (int) p.Sex});
        }

        using (var copy = new SqlBulkCopy(con))
        {
            copy.DestinationTableName = "Person";
            copy.WriteToServer(table);
        }
    }
}

4.三种方案速度对比

三者插入效率对比，Insert明显比SqlBulkCopy要慢太多，大概20~40倍性能差距，下面我们将SqlBulkCopy封装一下，让批量插入更加方便。

1.初始化数据源类型(此处我使用DataTable类型)，一般是使用表中的列名作为数据源的列名

DataTable CopyData= new DataTable("TestTable");
CopyData.Columns.Add("id", typeof(Int));
CopyData.Columns.Add("Name", typeof(string));
CopyData.Columns.Add("Time", typeof(DateTime));
CopyData.Columns.Add("Content", typeof(string));

2.给数据填充数据

foreach (var dr in dt.Tables[0].Rows)
 {
     DataRow row = importDataTable.NewRow();
     row["id"] = Convert.ToInt(id);
     row["Name"] = RealName;//操作人
     row["Time"] = DateTime.Now;//操作时间
     row["Content"] = String.Join("=", dr.ItemArray);//内容
     CopyData.Rows.Add(row);
 }

3.打开数据库连接插入数据

string connectionStr = “数据库连接字符串”;
SqlConnection connection = new SqlConnection(connectionStr );
using (connection)
{
    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
    {
        try
        {
            sqlBulkCopy.DestinationTableName = “TestTable”;
            sqlBulkCopy.BatchSize = CopyData.Rows.Count;
            for (int i = 0; i < CopyData.Columns.Count; i++)
            {
                sqlBulkCopy.ColumnMappings.Add(CopyData.Columns[i].ColumnName, CopyData.Columns[i].ColumnName);
            }
            sqlBulkCopy.WriteToServer(CopyData);
            return "成功";
        }
        catch (Exception ex)
        {
            return "失败," + ex;
        }
    }
}

这样就批量插入完毕了，执行起来速度很快。
sqlBulkCopy.DestinationTableName------->指定数据库表名
sqlBulkCopy.BatchSize--------->每一批次的行数
sqlBulkCopy.ColumnMappings.Add------->指定数据库与数据源之间的对应关系