C# 使用DapperExtensions实现大批数据插入_dapper批量插入数据库

要插入5万多条数据

第一、使用Dapper，遥遥无期

public static int InsertByTran(List<Model.LidarPointDeformation> list, string lidarcode)
        {
            string connectionString = DataBaseApp.GetBusinessDbStringByLidarcode(lidarcode);
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                int record = 0;
                using (var trans = connection.BeginTransaction())
                {
                    try
                    {
                        var sql = "INSERT INTO LidarPointDeformation(id, pointId, row, col, reffile, deformation, lidarcode, monitoringtime) VALUES (@id,@pointId,@row,@col,@reffile,@deformation,@lidarcode,@monitoringtime)";
                        record = connection.Execute(sql, list, trans, 60, CommandType.Text);
                    }
                    catch (DataException e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                    trans.Commit();
                }
                return record;
            }
 
        }

第二、使用sql语句拼接，但是最多只支持1000条，差强人意

public static void InsertBySQL(List<Model.LidarPointDeformation> list, string lidarcode)
        {
            try
            {
                string connectionString = DataBaseApp.GetBusinessDbStringByLidarcode(lidarcode);
                using (IDbConnection connection = new SqlConnection(connectionString))
                {
                    if (connection.State != ConnectionState.Open)
                    {
                        connection.Open();
                    }
 
                    string basesql = "INSERT INTO LidarPointDeformation(id, pointId, row, col, reffile, deformation, lidarcode, monitoringtime) VALUES";
                    StringBuilder sb = new StringBuilder();
                    int count = 0;
 
                    for (int i = 0; i < list.Count; i++)
                    {
                        var item = list[i];
                        string contractsql = String.Format(" ('{0}',{1},{2},{3},'{4}',{5},'{6}','{7}'),", item.id, item.pointId, item.row, item.col, item.reffile, item.deformation, item.lidarcode, item.monitoringtime);
                        sb.Append(contractsql);
                        if (i % 990 == 1)
                        {
                            sb.Remove(sb.Length - 1, 1);
                            connection.Execute(basesql + sb.ToString());
                            sb.Clear();
                            continue;
                        }
                        else if (i == list.Count - 1)
                        {
                            sb.Remove(sb.Length - 1, 1);
                            connection.Execute(basesql + sb.ToString());
 
                        }
                        else
                        {
                            continue;
                        }
                    }
 
 
 
                }
 
            }
            catch (Exception e)
            {
 
                throw;
            }
        }

第三、找了很久，说是用DapperExtensions，效率很不错

 /// <summary>
        /// 最后确定使用DapperExtensions，效率高很多
        /// </summary>
        /// <param name="list"></param>
        /// <param name="lidarcode"></param>
        public static void InsertByDapperExtension(List<Model.LidarPointDeformation> list, string lidarcode)
        {
            string connectionString = DataBaseApp.GetBusinessDbStringByLidarcode(lidarcode);
            using (IDbConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                InsertBatch<Model.LidarPointDeformation>(connection, list);
            }
        }        
        /// <summary>
        /// 批量插入
        /// </summary>
        public static void InsertBatch<T>(IDbConnection conn, IEnumerable<T> entityList, IDbTransaction transaction = null) where T : class
        {
            var tblName = string.Format("dbo.{0}", typeof(T).Name);
            var tran = (SqlTransaction)transaction;
            using (var bulkCopy = new SqlBulkCopy(conn as SqlConnection, SqlBulkCopyOptions.TableLock, tran))
            {
 
                try
                {
                    bulkCopy.BatchSize = entityList.Count();
                    bulkCopy.DestinationTableName = tblName;
                    var table = new DataTable();
                    DapperExtensions.Sql.ISqlGenerator sqlGenerator = new SqlGeneratorImpl(new DapperExtensionsConfiguration());
                    var classMap = sqlGenerator.Configuration.GetMap<T>();
                    var props = classMap.Properties.Where(x => x.Ignored == false).ToArray();
                    foreach (var propertyInfo in props)
                    {
                        bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                        table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyInfo.PropertyType) ?? propertyInfo.PropertyInfo.PropertyType);
                    }
                    var values = new object[props.Count()];
                    foreach (var itemm in entityList)
                    {
                        for (var i = 0; i < values.Length; i++)
                        {
                            values[i] = props[i].PropertyInfo.GetValue(itemm, null);
                        }
                        table.Rows.Add(values);
                    }
                    bulkCopy.WriteToServer(table);
                }
                catch (Exception e)
                {
 
                    throw e;
                }
            }
        }