批量插入

 简单使用方法

SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(dbconnstr, SqlBulkCopyOptions.UseInternalTransaction);//| SqlBulkCopyOptions.FireTriggers
                    sqlbulkcopy.DestinationTableName = tableName;//数据库中的表名
                    //SqlBulkCopy.BulkCopyTimeout = this.timeout;  //超时时间 
                    //sqlbulkcopy.BatchSize = 3000;  //每次传输3000行 
                    sqlbulkcopy.WriteToServer(source);

 

 封装

//
        /// <summary>
        /// 批量插入
        /// </summary>
        /// <param name="entitys"></param>
        public void BulkInsert(List<T> entitys, bool disposeconn)
        {
            var db = _objectSetFactory.GetDbContext();

            if (db.Database.Connection.State != ConnectionState.Open)
            {
                db.Database.Connection.Open(); //打开Connection连接
            }

            //调用BulkInsert方法,将entitys集合数据批量插入到数据库的tolocation表中
            BulkInsert<T>((SqlConnection)db.Database.Connection, entitys);

            if (db.Database.Connection.State != ConnectionState.Closed && disposeconn)
            {
                db.Database.Connection.Close(); //关闭Connection连接
            }


        }

        /// <summary>
        /// 批量插入
        /// </summary>
        /// <typeparam name="T">泛型集合的类型</typeparam>
        /// <param name="conn">连接对象</param>
        /// <param name="tableName">将泛型集合插入到本地数据库表的表名</param>
        /// <param name="list">要插入大泛型集合</param>
        private void BulkInsert<T>(SqlConnection conn, IList<T> list)
        {
            //typeof(T).GetAttributeValue((TableAttribute ta) => ta.Name);
            string tableName = typeof(T).GetAttributeValue((TableAttribute ta) => ta.Name);
            if (string.IsNullOrWhiteSpace(tableName))
            {
                //没有特性,默认类名
                tableName = typeof(T).Name;
            }

            using (var bulkCopy = new SqlBulkCopy(conn))
            {
                bulkCopy.BatchSize = list.Count;
                bulkCopy.DestinationTableName = tableName;

                var table = new DataTable();
                var props = TypeDescriptor.GetProperties(typeof(T))

                    .Cast<PropertyDescriptor>()
                    .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                    .ToArray();

                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name,
                        Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                }

                var values = new object[props.Length];
                foreach (var item in list)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].GetValue(item);
                    }

                    table.Rows.Add(values);
                }

                bulkCopy.WriteToServer(table);
            }
        }

 

资料

https://docs.microsoft.com/zh-cn/dotnet/api/system.data.sqlclient.sqlbulkcopy.-ctor?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev15.query%3FappId%3DDev15IDEF1%26l%3DZH-CN%26k%3Dk(System.Data.SqlClient.SqlBulkCopy.%2523ctor);k(DevLang-csharp)%26rd%3Dtrue&view=netframework-4.7.2

https://docs.microsoft.com/zh-cn/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017#examples

https://www.cnblogs.com/knowledgesea/p/6232461.html