Ado.net批量插入数据

采用的是SqlBulkCopy方法：数据库是sql server。
示例代码地址:

https://gitee.com/Alexander360/LearnAdoNet

SqlBulkCopy批量插入的方法如下，包括list转datatable方法：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace LearnAdoNet
{
    public static class SqlHelper
    {
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="list">源数据</param>
        public  static void BulkCopy<T>(IDbConnection conn, IEnumerable<T> list)
        {
            var dt = list.ToDataTable();

            using (conn)
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                using (var sqlbulkcopy = new SqlBulkCopy((SqlConnection)conn))
                {
                    sqlbulkcopy.DestinationTableName = dt.TableName;
                    for (var i = 0; i < dt.Columns.Count; i++)
                    {
                        sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }
                    sqlbulkcopy.WriteToServer(dt);
                }
            }
        }

        /// <summary>
        /// 把列表转换为DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        public static DataTable ToDataTable<T>(this IEnumerable<T> list)
        {
            var type = typeof(T);

            var properties = type.GetProperties().ToList();

            var newDt = new DataTable(type.Name);

            properties.ForEach(propertie =>
            {
                Type columnType;
                if (propertie.PropertyType.IsGenericType && propertie.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    columnType = propertie.PropertyType.GetGenericArguments()[0];
                }
                else
                {
                    columnType = propertie.PropertyType;
                }

                newDt.Columns.Add(propertie.Name, columnType);
            });

            foreach (var item in list)
            {
                var newRow = newDt.NewRow();

                properties.ForEach(propertie =>
                {
                    newRow[propertie.Name] = propertie.GetValue(item, null) ?? DBNull.Value;
                });

                newDt.Rows.Add(newRow);
            }

            return newDt;
        }
    }

}

测试方法：

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBulkCopy_Click(object sender, EventArgs e)
        {
            List<Student> students = new List<Student>();
            for (int i = 0; i < 1000; i++)
            {
                students.Add(new Student()
                {
                    Name = "User" + i,
                    Age = i % 5 + 15,
                    EnrollmentDate=DateTime.Now
                });
            }

            try
            {
                string strConn = Properties.Settings.Default.SchoolConn;
                IDbConnection conn = new SqlConnection(strConn);
                SqlHelper.BulkCopy<Student>(conn, students);
                MessageBox.Show("批量插入成功");
            }
            catch (Exception)
            {
                MessageBox.Show("批量插入失败");
            }

        }
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public DateTime EnrollmentDate { get; set; }

    }