批量插入

安装相应DB驱动插件

Package NuGet Stable Downloads
SmartSql.Bulk.SqlServer SmartSql.Bulk.SqlServer SmartSql.Bulk.SqlServer
SmartSql.Bulk.PostgreSql SmartSql.Bulk.PostgreSql SmartSql.Bulk.PostgreSql
SmartSql.Bulk.MySql SmartSql.Bulk.MySql SmartSql.Bulk.MySql
SmartSql.Bulk.MySqlConnector SmartSql.Bulk.MySqlConnector SmartSql.Bulk.MySqlConnector

BulkExtensions

因为IBulkInsert对象只支持DataTable数据源,所以写了个扩展: BulkExtensions。

 public static class BulkExtensions
    {
        public static DataTable ToDataTable<TEntity>(this IEnumerable<TEntity> list);
        public static void Insert<TEntity>(this IBulkInsert bulkInsert, IEnumerable<TEntity> list);
        public static async Task InsertAsync<TEntity>(this IBulkInsert bulkInsert, IEnumerable<TEntity> list);
    }

SqlServer

using (var dbSession= SqlMapper.SessionStore.Open())
            {
                var data = SqlMapper.GetDataTable(new RequestContext
                {
                    Scope = nameof(AllPrimitive),
                    SqlId = "Query",
                    Request = new { Taken = 100 }
                });
                data.TableName = "T_AllPrimitive";
                IBulkInsert bulkInsert = new BulkInsert(dbSession);
                bulkInsert.Table = data;
                bulkInsert.Insert();
            }

PostgreSql

            using (var dbSession = dbSessionFactory.Open())
            {
                var data = list.ToDataTable();
                data.Columns.RemoveAt(0);
                data.Columns["UserName"].ColumnName = "user_name";
                data.Columns["Status"].ColumnName = "status";
                data.TableName = "t_user";
                BulkInsert bulkInsert = new BulkInsert(dbSession);
                bulkInsert.Table = data;
                bulkInsert.Insert();
            }

MySql

            using (var dbSession = dbSessionFactory.Open())
            {
                var data = list.ToDataTable();
                data.TableName = "t_user";
                BulkInsert bulkInsert = new BulkInsert(dbSession)
                {
                    SecureFilePriv = "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads",
                    Table = data
                };
                bulkInsert.Insert();
            }