域名預(yù)訂/競(jìng)價(jià)，好“米”不錯(cuò)過(guò)
這篇文章主要介紹了SQL Server 批量插入數(shù)據(jù)的完美解決方案,需要的朋友可以參考下
目錄
一、Sql Server插入方案介紹
二、SqlBulkCopy封裝代碼
1.方法介紹
2.實(shí)現(xiàn)原理
3.完整代碼
三、測(cè)試封裝代碼
1.測(cè)試代碼
四、代碼下載
一、Sql Server插入方案介紹
關(guān)于 SqlServer 批量插入的方式，有三種比較常用的插入方式，Insert、BatchInsert、SqlBulkCopy,下面我們對(duì)比以下三種方案的速度
1.普通的Insert插入方法
public static void Insert(IEnumerable<Person> persons)
{
using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
con.Open();
foreach (var person in persons)
{
using (var com = new SqlCommand(
"INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES(@Id,@Name,@Age,@CreateTime,@Sex)",
con))
{
com.Parameters.AddRange(new[]
{
new SqlParameter("@Id", SqlDbType.BigInt) {Value = person.Id},
new SqlParameter("@Name", SqlDbType.VarChar, 64) {Value = person.Name},
new SqlParameter("@Age", SqlDbType.Int) {Value = person.Age},
new SqlParameter("@CreateTime", SqlDbType.DateTime)
{Value = person.CreateTime ?? (object) DBNull.Value},
new SqlParameter("@Sex", SqlDbType.Int) {Value = (int)person.Sex},
});
com.ExecuteNonQuery();
}
}
}
}
2.拼接BatchInsert插入語(yǔ)句
public static void BatchInsert(Person[] persons)
{
using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
con.Open();
var pageCount = (persons.Length - 1) / 1000 + 1;
for (int i = 0; i < pageCount; i++)
{
var personList = persons.Skip(i * 1000).Take(1000).ToArray();
var values = personList.Select(p =>
$"({p.Id},'{p.Name}',{p.Age},{(p.CreateTime.HasValue ? $"'{p.CreateTime:yyyy-MM-dd HH:mm:ss}'" : "NULL")},{(int) p.Sex})");
var insertSql =
$"INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES{string.Join(",", values)}";
using (var com = new SqlCommand(insertSql, con))
{
com.ExecuteNonQuery();
}
}
}
}
3.SqlBulkCopy插入方案
public static void BulkCopy(IEnumerable<Person> persons)
{
using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
con.Open();
var table = new DataTable();
table.Columns.AddRange(new []
{
new DataColumn("Id", typeof(long)),
new DataColumn("Name", typeof(string)),
new DataColumn("Age", typeof(int)),
new DataColumn("CreateTime", typeof(DateTime)),
new DataColumn("Sex", typeof(int)),
});
foreach (var p in persons)
{
table.Rows.Add(new object[] {p.Id, p.Name, p.Age, p.CreateTime, (int) p.Sex});
}
using (var copy = new SqlBulkCopy(con))
{
copy.DestinationTableName = "Person";
copy.WriteToServer(table);
}
}
}
3.三種方案速度對(duì)比
兩者插入效率對(duì)比，Insert明顯比SqlBulkCopy要慢太多，大概20~40倍性能差距，下面我們將SqlBulkCopy封裝一下，讓批量插入更加方便
二、SqlBulkCopy封裝代碼
1.方法介紹
批量插入擴(kuò)展方法簽名
這個(gè)方法主要解決了兩個(gè)問(wèn)題：
免去了手動(dòng)構(gòu)建DataTable或者IDataReader接口實(shí)現(xiàn)類，手動(dòng)構(gòu)建的轉(zhuǎn)換比較難以維護(hù)，如果修改字段就得把這些地方都進(jìn)行修改，特別是還需要將枚舉類型特殊處理，轉(zhuǎn)換成他的基礎(chǔ)類型（默認(rèn)int）
不用親自創(chuàng)建SqlBulkCopy對(duì)象，和配置數(shù)據(jù)庫(kù)列的映射，和一些屬性的配置
此方案也是在我公司中使用，以滿足公司的批量插入數(shù)據(jù)的需求，例如第三方的對(duì)賬數(shù)據(jù)此方法使用的是Expression動(dòng)態(tài)生成數(shù)據(jù)轉(zhuǎn)換函數(shù)，其效率和手寫(xiě)的原生代碼差不多，和原生手寫(xiě)代碼相比，多余的轉(zhuǎn)換損失很小【最大的性能損失都是在值類型拆裝箱上】
此方案和其他網(wǎng)上的方案有些不同的是：不是將List先轉(zhuǎn)換成DataTable，然后寫(xiě)入SqlBulkCopy的，而是使用一個(gè)實(shí)現(xiàn)IDataReader的讀取器包裝List，每往SqlBulkCopy插入一行數(shù)據(jù)才會(huì)轉(zhuǎn)換一行數(shù)據(jù)
IDataReader方案和DataTable方案相比優(yōu)點(diǎn)
效率高：DataTable方案需要先完全轉(zhuǎn)換后，才能交由SqlBulkCopy寫(xiě)入數(shù)據(jù)庫(kù)，而IDataReader方案可以邊轉(zhuǎn)換邊交給SqlBulkCopy寫(xiě)入數(shù)據(jù)庫(kù)（例如：10萬(wàn)數(shù)據(jù)插入速度可提升30%）
占用內(nèi)存少：DataTable方案需要先完全轉(zhuǎn)換后，才能交由SqlBulkCopy寫(xiě)入數(shù)據(jù)庫(kù)，需要占用大量?jī)?nèi)存，而IDataReader方案可以邊轉(zhuǎn)換邊交給SqlBulkCopy寫(xiě)入數(shù)據(jù)庫(kù)，無(wú)須占用過(guò)多內(nèi)存
強(qiáng)大：因?yàn)槭沁厡?xiě)入邊轉(zhuǎn)換，而且EnumerableReader傳入的是一個(gè)迭代器，可以實(shí)現(xiàn)持續(xù)插入數(shù)據(jù)的效果
2.實(shí)現(xiàn)原理
① 實(shí)體Model與表映射
數(shù)據(jù)庫(kù)表代碼
CREATE TABLE [dbo].[Person](
[Id] [BIGINT] NOT NULL,
[Name] [VARCHAR](64) NOT NULL,
[Age] [INT] NOT NULL,
[CreateTime] [DATETIME] NULL,
[Sex] [INT] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
實(shí)體類代碼
public class Person
{
public long Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public DateTime? CreateTime { get; set; }
public Gender Sex { get; set; }
}
public enum Gender
{
Man = 0,
Woman = 1
}
創(chuàng)建字段映射【如果沒(méi)有此字段映射會(huì)導(dǎo)致數(shù)據(jù)填錯(cuò)位置，如果類型不對(duì)還會(huì)導(dǎo)致報(bào)錯(cuò)】【因?yàn)椋簺](méi)有此字段映射默認(rèn)是按照列序號(hào)對(duì)應(yīng)插入的】
創(chuàng)建映射使用的SqlBulkCopy類型的ColumnMappings屬性來(lái)完成，數(shù)據(jù)列與數(shù)據(jù)庫(kù)中列的映射
//創(chuàng)建批量插入對(duì)象
using (var copy = new SqlBulkCopy(connection, options, externalTransaction))
{
foreach (var column in ModelToDataTable<TModel>.Columns)
{
//創(chuàng)建字段映射
copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
}
② 實(shí)體轉(zhuǎn)換成數(shù)據(jù)行
將數(shù)據(jù)轉(zhuǎn)換成數(shù)據(jù)行采用的是：反射+Expression來(lái)完成
其中反射是用于獲取編寫(xiě)Expression所需程序類，屬性等信息
其中Expression是用于生成高效轉(zhuǎn)換函數(shù)其中ModelToDataTable類型利用了靜態(tài)泛型類特性，實(shí)現(xiàn)泛型參數(shù)的緩存效果
在ModelToDataTable的靜態(tài)構(gòu)造函數(shù)中，生成轉(zhuǎn)換函數(shù)，獲取需要轉(zhuǎn)換的屬性信息，并存入靜態(tài)只讀字段中，完成緩存
③ 使用IDataReader插入數(shù)據(jù)的重載
EnumerableReader是實(shí)現(xiàn)了IDataReader接口的讀取類，用于將模型對(duì)象，在迭代器中讀取出來(lái)，并轉(zhuǎn)換成數(shù)據(jù)行，可供SqlBulkCopy讀取
SqlBulkCopy只會(huì)調(diào)用三個(gè)方法：GetOrdinal、Read、GetValue其中GetOrdinal只會(huì)在首行讀取每個(gè)列所代表序號(hào)【需要填寫(xiě)：SqlBulkCopy類型的ColumnMappings屬性】
其中Read方法是迭代到下一行，并調(diào)用ModelToDataTable.ToRowData.Invoke()來(lái)將模型對(duì)象轉(zhuǎn)換成數(shù)據(jù)行object[]其中GetValue方法是獲取當(dāng)前行指定下標(biāo)位置的值
3.完整代碼
擴(kuò)展方法類
public static class SqlConnectionExtension
{
/// <summary>
/// 批量復(fù)制
/// </summary>
/// <typeparam name="TModel">插入的模型對(duì)象</typeparam>
/// <param name="source">需要批量插入的數(shù)據(jù)源</param>
/// <param name="connection">數(shù)據(jù)庫(kù)連接對(duì)象</param>
/// <param name="tableName">插入表名稱【為NULL默認(rèn)為實(shí)體名稱】</param>
/// <param name="bulkCopyTimeout">插入超時(shí)時(shí)間</param>
/// <param name="batchSize">寫(xiě)入數(shù)據(jù)庫(kù)一批數(shù)量【如果為0代表全部一次性插入】最合適數(shù)量【這取決于您的環(huán)境，尤其是行數(shù)和網(wǎng)絡(luò)延遲。就個(gè)人而言，我將從BatchSize屬性設(shè)置為1000行開(kāi)始，然后看看其性能如何。如果可行，那么我將使行數(shù)加倍（例如增加到2000、4000等），直到性能下降或超時(shí)。否則，如果超時(shí)發(fā)生在1000，那么我將行數(shù)減少一半（例如500），直到它起作用為止?！?lt;/param>
/// <param name="options">批量復(fù)制參數(shù)</param>
/// <param name="externalTransaction">執(zhí)行的事務(wù)對(duì)象</param>
/// <returns>插入數(shù)量</returns>
public static int BulkCopy<TModel>(this SqlConnection connection,
IEnumerable<TModel> source,
string tableName = null,
int bulkCopyTimeout = 30,
int batchSize = 0,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
SqlTransaction externalTransaction = null)
{
//創(chuàng)建讀取器
using (var reader = new EnumerableReader<TModel>(source))
{
//創(chuàng)建批量插入對(duì)象
using (var copy = new SqlBulkCopy(connection, options, externalTransaction))
{
//插入的表
copy.DestinationTableName = tableName ?? typeof(TModel).Name;
//寫(xiě)入數(shù)據(jù)庫(kù)一批數(shù)量
copy.BatchSize = batchSize;
//超時(shí)時(shí)間
copy.BulkCopyTimeout = bulkCopyTimeout;
//創(chuàng)建字段映射【如果沒(méi)有此字段映射會(huì)導(dǎo)致數(shù)據(jù)填錯(cuò)位置，如果類型不對(duì)還會(huì)導(dǎo)致報(bào)錯(cuò)】【因?yàn)椋簺](méi)有此字段映射默認(rèn)是按照列序號(hào)對(duì)應(yīng)插入的】
foreach (var column in ModelToDataTable<TModel>.Columns)
{
//創(chuàng)建字段映射
copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
//將數(shù)據(jù)批量寫(xiě)入數(shù)據(jù)庫(kù)
copy.WriteToServer(reader);
//返回插入數(shù)據(jù)數(shù)量
return reader.Depth;
}
}
}
/// <summary>
/// 批量復(fù)制-異步
/// </summary>
/// <typeparam name="TModel">插入的模型對(duì)象</typeparam>
/// <param name="source">需要批量插入的數(shù)據(jù)源</param>
/// <param name="connection">數(shù)據(jù)庫(kù)連接對(duì)象</param>
/// <param name="tableName">插入表名稱【為NULL默認(rèn)為實(shí)體名稱】</param>
/// <param name="bulkCopyTimeout">插入超時(shí)時(shí)間</param>
/// <param name="batchSize">寫(xiě)入數(shù)據(jù)庫(kù)一批數(shù)量【如果為0代表全部一次性插入】最合適數(shù)量【這取決于您的環(huán)境，尤其是行數(shù)和網(wǎng)絡(luò)延遲。就個(gè)人而言，我將從BatchSize屬性設(shè)置為1000行開(kāi)始，然后看看其性能如何。如果可行，那么我將使行數(shù)加倍（例如增加到2000、4000等），直到性能下降或超時(shí)。否則，如果超時(shí)發(fā)生在1000，那么我將行數(shù)減少一半（例如500），直到它起作用為止?！?lt;/param>
/// <param name="options">批量復(fù)制參數(shù)</param>
/// <param name="externalTransaction">執(zhí)行的事務(wù)對(duì)象</param>
/// <returns>插入數(shù)量</returns>
public static async Task<int> BulkCopyAsync<TModel>(this SqlConnection connection,
IEnumerable<TModel> source,
string tableName = null,
int bulkCopyTimeout = 30,
int batchSize = 0,
SqlBulkCopyOptions options = SqlBulkCopyOptions.Default,
SqlTransaction externalTransaction = null)
{
//創(chuàng)建讀取器
using (var reader = new EnumerableReader<TModel>(source))
{
//創(chuàng)建批量插入對(duì)象
using (var copy = new SqlBulkCopy(connection, options, externalTransaction))
{
//插入的表
copy.DestinationTableName = tableName ?? typeof(TModel).Name;
//寫(xiě)入數(shù)據(jù)庫(kù)一批數(shù)量
copy.BatchSize = batchSize;
//超時(shí)時(shí)間
copy.BulkCopyTimeout = bulkCopyTimeout;
//創(chuàng)建字段映射【如果沒(méi)有此字段映射會(huì)導(dǎo)致數(shù)據(jù)填錯(cuò)位置，如果類型不對(duì)還會(huì)導(dǎo)致報(bào)錯(cuò)】【因?yàn)椋簺](méi)有此字段映射默認(rèn)是按照列序號(hào)對(duì)應(yīng)插入的】
foreach (var column in ModelToDataTable<TModel>.Columns)
{
//創(chuàng)建字段映射
copy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
//將數(shù)據(jù)批量寫(xiě)入數(shù)據(jù)庫(kù)
await copy.WriteToServerAsync(reader);
//返回插入數(shù)據(jù)數(shù)量
return reader.Depth;
}
}
}
}
封裝的迭代器數(shù)據(jù)讀取器
/// <summary>
/// 迭代器數(shù)據(jù)讀取器
/// </summary>
/// <typeparam name="TModel">模型類型</typeparam>
public class EnumerableReader<TModel> : IDataReader
{
/// <summary>
/// 實(shí)例化迭代器讀取對(duì)象
/// </summary>
/// <param name="source">模型源</param>
public EnumerableReader(IEnumerable<TModel> source)
{
_source = source ?? throw new ArgumentNullException(nameof(source));
_enumerable = source.GetEnumerator();
}
private readonly IEnumerable<TModel> _source;
private readonly IEnumerator<TModel> _enumerable;
private object[] _currentDataRow = Array.Empty<object>();
private int _depth;
private bool _release;
public void Dispose()
{
_release = true;
_enumerable.Dispose();
}
public int GetValues(object[] values)
{
if (values == null) throw new ArgumentNullException(nameof(values));
var length = Math.Min(_currentDataRow.Length, values.Length);
Array.Copy(_currentDataRow, values, length);
return length;
}
public int GetOrdinal(string name)
{
for (int i = 0; i < ModelToDataTable<TModel>.Columns.Count; i++)
{
if (ModelToDataTable<TModel>.Columns[i].ColumnName == name) return i;
}
return -1;
}
public long GetBytes(int ordinal, long dataIndex, byte[] buffer, int bufferIndex, int length)
{
if (dataIndex < 0) throw new Exception($"起始下標(biāo)不能小于0！");
if (bufferIndex < 0) throw new Exception("目標(biāo)緩沖區(qū)起始下標(biāo)不能小于0！");
if (length < 0) throw new Exception("讀取長(zhǎng)度不能小于0！");
var numArray = (byte[])GetValue(ordinal);
if (buffer == null) return numArray.Length;
if (buffer.Length <= bufferIndex) throw new Exception("目標(biāo)緩沖區(qū)起始下標(biāo)不能大于目標(biāo)緩沖區(qū)范圍！");
var freeLength = Math.Min(numArray.Length - bufferIndex, length);
if (freeLength <= 0) return 0;
Array.Copy(numArray, dataIndex, buffer, bufferIndex, length);
return freeLength;
}
public long GetChars(int ordinal, long dataIndex, char[] buffer, int bufferIndex, int length)
{
if (dataIndex < 0) throw new Exception($"起始下標(biāo)不能小于0！");
if (bufferIndex < 0) throw new Exception("目標(biāo)緩沖區(qū)起始下標(biāo)不能小于0！");
if (length < 0) throw new Exception("讀取長(zhǎng)度不能小于0！");
var numArray = (char[])GetValue(ordinal);
if (buffer == null) return numArray.Length;
if (buffer.Length <= bufferIndex) throw new Exception("目標(biāo)緩沖區(qū)起始下標(biāo)不能大于目標(biāo)緩沖區(qū)范圍！");
var freeLength = Math.Min(numArray.Length - bufferIndex, length);
if (freeLength <= 0) return 0;
Array.Copy(numArray, dataIndex, buffer, bufferIndex, length);
return freeLength;
}
public bool IsDBNull(int i)
{
var value = GetValue(i);
return value == null || value is DBNull;
}
public bool NextResult()
{
//移動(dòng)到下一個(gè)元素
if (!_enumerable.MoveNext()) return false;
//行層+1
Interlocked.Increment(ref _depth);
//得到數(shù)據(jù)行
_currentDataRow = ModelToDataTable<TModel>.ToRowData.Invoke(_enumerable.Current);
return true;
}
public byte GetByte(int i) => (byte)GetValue(i);
public string GetName(int i) => ModelToDataTable<TModel>.Columns[i].ColumnName;
public string GetDataTypeName(int i) => ModelToDataTable<TModel>.Columns[i].DataType.Name;
public Type GetFieldType(int i) => ModelToDataTable<TModel>.Columns[i].DataType;
public object GetValue(int i) => _currentDataRow[i];
public bool GetBoolean(int i) => (bool)GetValue(i);
public char GetChar(int i) => (char)GetValue(i);
public Guid GetGuid(int i) => (Guid)GetValue(i);
public short GetInt16(int i) => (short)GetValue(i);
public int GetInt32(int i) => (int)GetValue(i);
public long GetInt64(int i) => (long)GetValue(i);
public float GetFloat(int i) => (float)GetValue(i);
public double GetDouble(int i) => (double)GetValue(i);
public string GetString(int i) => (string)GetValue(i);
public decimal GetDecimal(int i) => (decimal)GetValue(i);
public DateTime GetDateTime(int i) => (DateTime)GetValue(i);
public IDataReader GetData(int i) => throw new NotSupportedException();
public int FieldCount => ModelToDataTable<TModel>.Columns.Count;
public object this[int i] => GetValue(i);
public object this[string name] => GetValue(GetOrdinal(name));
public void Close() => Dispose();
public DataTable GetSchemaTable() => ModelToDataTable<TModel>.ToDataTable(_source);
public bool Read() => NextResult();
public int Depth => _depth;
public bool IsClosed => _release;
public int RecordsAffected => 0;
}
模型對(duì)象轉(zhuǎn)數(shù)據(jù)行工具類
/// <summary>
/// 對(duì)象轉(zhuǎn)換成DataTable轉(zhuǎn)換類
/// </summary>
/// <typeparam name="TModel">泛型類型</typeparam>
public static class ModelToDataTable<TModel>
{
static ModelToDataTable()
{
//如果需要剔除某些列可以修改這段代碼
var propertyList = typeof(TModel).GetProperties().Where(w => w.CanRead).ToArray();
Columns = new ReadOnlyCollection<DataColumn>(propertyList
.Select(pr => new DataColumn(pr.Name, GetDataType(pr.PropertyType))).ToArray());
//生成對(duì)象轉(zhuǎn)數(shù)據(jù)行委托
ToRowData = BuildToRowDataDelegation(typeof(TModel), propertyList);
}
/// <summary>
/// 構(gòu)建轉(zhuǎn)換成數(shù)據(jù)行委托
/// </summary>
/// <param name="type">傳入類型</param>
/// <param name="propertyList">轉(zhuǎn)換的屬性</param>
/// <returns>轉(zhuǎn)換數(shù)據(jù)行委托</returns>
private static Func<TModel, object[]> BuildToRowDataDelegation(Type type, PropertyInfo[] propertyList)
{
var source = Expression.Parameter(type);
var items = propertyList.Select(property => ConvertBindPropertyToData(source, property));
var array = Expression.NewArrayInit(typeof(object), items);
var lambda = Expression.Lambda<Func<TModel, object[]>>(array, source);
return lambda.Compile();
}
/// <summary>
/// 將屬性轉(zhuǎn)換成數(shù)據(jù)
/// </summary>
/// <param name="source">源變量</param>
/// <param name="property">屬性信息</param>
/// <returns>獲取屬性數(shù)據(jù)表達(dá)式</returns>
private static Expression ConvertBindPropertyToData(ParameterExpression source, PropertyInfo property)
{
var propertyType = property.PropertyType;
var expression = (Expression)Expression.Property(source, property);
if (propertyType.IsEnum)
expression = Expression.Convert(expression, propertyType.GetEnumUnderlyingType());
return Expression.Convert(expression, typeof(object));
}
/// <summary>
/// 獲取數(shù)據(jù)類型
/// </summary>
/// <param name="type">屬性類型</param>
/// <returns>數(shù)據(jù)類型</returns>
private static Type GetDataType(Type type)
{
//枚舉默認(rèn)轉(zhuǎn)換成對(duì)應(yīng)的值類型
if (type.IsEnum)
return type.GetEnumUnderlyingType();
//可空類型
if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>))
return GetDataType(type.GetGenericArguments().First());
return type;
}
/// <summary>
/// 列集合
/// </summary>
public static IReadOnlyList<DataColumn> Columns { get; }
/// <summary>
/// 對(duì)象轉(zhuǎn)數(shù)據(jù)行委托
/// </summary>
public static Func<TModel, object[]> ToRowData { get; }
/// <summary>
/// 集合轉(zhuǎn)換成DataTable
/// </summary>
/// <param name="source">集合</param>
/// <param name="tableName">表名稱</param>
/// <returns>轉(zhuǎn)換完成的DataTable</returns>
public static DataTable ToDataTable(IEnumerable<TModel> source, string tableName = "TempTable")
{
//創(chuàng)建表對(duì)象
var table = new DataTable(tableName);
//設(shè)置列
foreach (var dataColumn in Columns)
{
table.Columns.Add(new DataColumn(dataColumn.ColumnName, dataColumn.DataType));
}
//循環(huán)轉(zhuǎn)換每一行數(shù)據(jù)
foreach (var item in source)
{
table.Rows.Add(ToRowData.Invoke(item));
}
//返回表對(duì)象
return table;
}
}
三、測(cè)試封裝代碼
1.測(cè)試代碼
創(chuàng)表代碼
CREATE TABLE [dbo].[Person](
[Id] [BIGINT] NOT NULL,
[Name] [VARCHAR](64) NOT NULL,
[Age] [INT] NOT NULL,
[CreateTime] [DATETIME] NULL,
[Sex] [INT] NOT NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
實(shí)體類代碼
定義的實(shí)體的屬性名稱需要和SqlServer列名稱類型對(duì)應(yīng)
public class Person
{
public long Id { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public DateTime? CreateTime { get; set; }
public Gender Sex { get; set; }
}
public enum Gender
{
Man = 0,
Woman = 1
}
測(cè)試方法
//生成10萬(wàn)條數(shù)據(jù)
var persons = new Person[100000];
var random = new Random();
for (int i = 0; i < persons.Length; i++)
{
persons[i] = new Person
{
Id = i + 1,
Name = "張三" + i,
Age = random.Next(1, 128),
Sex = (Gender)random.Next(2),
CreateTime = random.Next(2) == 0 ? null : (DateTime?) DateTime.Now.AddSeconds(i)
};
}
//創(chuàng)建數(shù)據(jù)庫(kù)連接
using (var conn = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;"))
{
conn.Open();
var sw = Stopwatch.StartNew();
//批量插入數(shù)據(jù)
var qty = conn.BulkCopy(persons);
sw.Stop();
Console.WriteLine(sw.Elapsed.TotalMilliseconds + "ms");
}
執(zhí)行批量插入結(jié)果
226.4767ms
請(qǐng)按任意鍵繼續(xù). . .
四、代碼下載
GitHub代碼地址：https://github.com/liu-zhen-liang/PackagingComponentsSet/tree/main/SqlBulkCopyComponents
來(lái)源：腳本之家
鏈接：https://www.jb51.net/article/201634.htm
申請(qǐng)創(chuàng)業(yè)報(bào)道，分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處，共同探討創(chuàng)業(yè)新機(jī)遇！
