Ado.net批量插入数据

谁践踏了优雅 2021-07-27 00:52 340阅读 0赞

采用的是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; }
    
        }