Quickly insert millions of rows in SQL Server in .NET (edit)
https://www.softfluent.com/blog/dev/Quickly-insert-millions-of-rows-in-SQL-Server-in-NET
Ghi chú
Sổ chi tiết công nợ của một khách hàng
Sổ cái của một tài khoản
Bảng cân đối phát sinh công nợ của một tài khoản
https://docs.microsoft.com/en-us/aspnet/core/tutorials/index?view=aspnetcore-2.1#how-to-download-a-sample
https://github.com/aspnet/Docs/tree/master/aspnetcore/fundamentals/app-state/samples
https://github.com/aspnet/Docs/tree/master/aspnetcore/fundamentals/app-state/samples/2.x/SessionSample
Bulk Insert
https://chsakell.com/2014/07/13/insert-millions-of-records-in-sql-server-table-at-once/
https://blogs.msdn.microsoft.com/nikhilsi/2008/06/11/bulk-insert-into-sql-from-c-app/
https://www.codeproject.com/Articles/439843/Handling-BULK-Data-insert-from-CSV-to-SQL-Server
https://programmingwithmosh.com/csharp/using-sqlbulkcopy-for-fast-inserts/
https://www.codemag.com/article/1701101/Processing-Large-Datasets-Using-C
ObjectDataReader (BulkInsert)
https://gist.github.com/meziantou/174e2791dec966be837746750b87d069
1) SQL SCRIPT
CREATE TABLE [dbo].[Customer]( [Id] [uniqueidentifier] NOT NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DateOfBirth] [datetime2](7) NULL ) ON [PRIMARY] GO
2) Class Library: ObjectDataReader.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq.Expressions;
namespace DOTNETCoreClassLibrary1
{
public class ObjectDataReader : DbDataReader
{
private IEnumerator _iterator;
private IDictionary<string, int> _propertyNameToOrdinal = new Dictionary<string, int>();
private IDictionary<int, string> _ordinalToPropertyName = new Dictionary<int, string>();
private Func<T, object>[] _getPropertyValueFuncs;
public ObjectDataReader(IEnumerator items)
{
_iterator = items ?? throw new ArgumentNullException(nameof(items));
Initialize();
}
private void Initialize()
{
int ordinal = 0;
var properties = typeof(T).GetProperties();
_getPropertyValueFuncs = new Func<T, object>[properties.Length];
foreach (var property in properties)
{
string propertyName = property.Name;
_propertyNameToOrdinal.Add(propertyName, ordinal);
_ordinalToPropertyName.Add(ordinal, propertyName);
var parameterExpression = Expression.Parameter(typeof(T), "x");
var func = (Func<T, object>)Expression.Lambda(Expression.Convert(Expression.Property(parameterExpression, propertyName), typeof(object)), parameterExpression).Compile();
_getPropertyValueFuncs[ordinal] = func;
ordinal++;
}
}
public override object this[int ordinal] => GetValue(ordinal);
public override object this[string name] => GetValue(GetOrdinal(name));
public override int Depth => 0;
public override int FieldCount => _ordinalToPropertyName.Count;
public override bool HasRows => true;
public override bool IsClosed => _iterator != null;
public override int RecordsAffected => throw new NotImplementedException();
public override bool GetBoolean(int ordinal)
{
return (bool)GetValue(ordinal);
}
public override byte GetByte(int ordinal)
{
return (byte)GetValue(ordinal);
}
public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length)
{
throw new NotImplementedException();
}
public override char GetChar(int ordinal)
{
return (char)GetValue(ordinal);
}
public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length)
{
throw new NotImplementedException();
}
public override string GetDataTypeName(int ordinal)
{
throw new NotImplementedException();
}
public override DateTime GetDateTime(int ordinal)
{
return (DateTime)GetValue(ordinal);
}
public override decimal GetDecimal(int ordinal)
{
return (decimal)GetValue(ordinal);
}
public override double GetDouble(int ordinal)
{
return (double)GetValue(ordinal);
}
public override IEnumerator GetEnumerator()
{
throw new NotImplementedException();
}
public override Type GetFieldType(int ordinal)
{
var value = GetValue(ordinal);
if (value == null)
return typeof(object);
return value.GetType();
}
public override float GetFloat(int ordinal)
{
return (float)GetValue(ordinal);
}
public override Guid GetGuid(int ordinal)
{
return (Guid)GetValue(ordinal);
}
public override short GetInt16(int ordinal)
{
return (short)GetValue(ordinal);
}
public override int GetInt32(int ordinal)
{
return (int)GetValue(ordinal);
}
public override long GetInt64(int ordinal)
{
return (long)GetValue(ordinal);
}
public override string GetName(int ordinal)
{
if (_ordinalToPropertyName.TryGetValue(ordinal, out var name))
return name;
return null;
}
public override int GetOrdinal(string name)
{
if (_propertyNameToOrdinal.TryGetValue(name, out var ordinal))
return ordinal;
return -1;
}
public override string GetString(int ordinal)
{
return (string)GetValue(ordinal);
}
public override object GetValue(int ordinal)
{
var func = _getPropertyValueFuncs[ordinal];
return func(_iterator.Current);
}
public override int GetValues(object[] values)
{
int max = Math.Min(values.Length, FieldCount);
for (var i = 0; i < max; i++)
{
values[i] = IsDBNull(i) ? DBNull.Value : GetValue(i);
}
return max;
}
public override bool IsDBNull(int ordinal)
{
return GetValue(ordinal) == null;
}
public override bool NextResult()
{
return false;
}
public override bool Read()
{
return _iterator.MoveNext();
}
}
}
3) Models: Customer.cs
using System;
using System.Collections.Generic;
namespace DOTNETCoreClassLibrary1
{
public class Customer
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
public static IEnumerable Generate(int count)
{
for (int i = 0; i < count; i++)
{
yield return new Customer
{
Id = Guid.NewGuid(),
FirstName = "FirstName" + i,
LastName = "LastName" + i,
DateOfBirth = DateTime.UtcNow
};
}
}
public static IEnumerable Generate(int start, int count)
{
for (int i = start; i < count; i++)
{
yield return new Customer
{
Id = Guid.NewGuid(),
FirstName = "FirstName" + i,
LastName = "LastName" + i,
DateOfBirth = DateTime.UtcNow
};
}
}
}
}
4) Controllers: HomeController.cs
using ASPNETCoreWebApplication1.Models;
using DOTNETCoreClassLibrary1;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using System;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Threading;
using System.Threading.Tasks;
namespace ASPNETCoreWebApplication1.Controllers
{
public class HomeController : Controller
{
private readonly string _connectionString = "";
public HomeController(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public async Task Index()
{
await InsertAsync();
return View();
}
private async Task InsertAsync(CancellationToken ct = default(CancellationToken))
{
using (var connection = new SqlConnection(this._connectionString))
{
await connection.OpenAsync(ct);
using (var bulk = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, null))
{
var customers = Customer.Generate(1000000);
using (var enumerator = customers.GetEnumerator())
using (var customerReader = new ObjectDataReader(enumerator))
{
bulk.DestinationTableName = "Customer";
bulk.ColumnMappings.Add(nameof(Customer.Id), "Id");
bulk.ColumnMappings.Add(nameof(Customer.FirstName), "FirstName");
bulk.ColumnMappings.Add(nameof(Customer.LastName), "LastName");
bulk.ColumnMappings.Add(nameof(Customer.DateOfBirth), "DateOfBirth");
bulk.EnableStreaming = true;
bulk.BatchSize = 10000;
bulk.NotifyAfter = 1000;
bulk.SqlRowsCopied += (sender, e) => Console.WriteLine("RowsCopied: " + e.RowsCopied);
await bulk.WriteToServerAsync(customerReader, ct);
}
}
}
}
}
}
