public static IReadOnlyCollection <TEntity> GetItemsByTempTable <TEntity, TKey>(this MySqlConnection conn, [NotNull] IEnumerable <IEntity <TKey> > entities, string sql, string strTableName = "Ids")
where TEntity : class, IEntity <TKey>
{
var keys = entities.Select(p => new IdEntity <TKey>(p.Id)).ToArray();
var itemList = new HashSet <IdEntity <TKey> >(keys);
if (itemList.Count == 0)
{
return(Enumerable.Empty <TEntity>().ToList().AsReadOnly());
}
using (var transaction = conn.BeginTransaction())
{
//不能建TEMPORARY 临时表
string strTempSQL = $" CREATE TABLE IF NOT EXISTS `#Temp{strTableName}` (Id {TypeToSqlDbTypeMapper.GetSqlDbTypeFromClrType(typeof(TKey))} NOT NULL PRIMARY KEY );";
conn.Execute(strTempSQL, transaction: transaction);
try { conn.BulkInsert(itemList.ToArray()); }catch (Exception ex)
{
}
string strSQL = $@"SELECT d.* FROM ({sql}) d
INNER JOIN `#Temp{strTableName}` i ON d.Id = i.Id;
DROP TABLE IF EXISTS `#Temp{strTableName}`;";
var result = conn.Query <TEntity>(strSQL,
transaction: transaction, commandTimeout: 3600)
.ToList().AsReadOnly();
transaction.Rollback(); // Or commit if you like
return(result);
}
}
public bool BatchInsert(IEnumerable <TempPatientBaselinesExtract> extracts)
{
var cn = GetConnectionString();
try
{
if (Context.Database.ProviderName.ToLower().Contains("SqlServer".ToLower()))
{
using (var connection = new SqlConnection(cn))
{
connection.BulkInsert(extracts);
return(true);
}
}
if (Context.Database.ProviderName.ToLower().Contains("MySql".ToLower()))
{
using (var connection = new MySqlConnection(cn))
{
connection.BulkInsert(extracts);
return(true);
}
}
return(false);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Log.Error(e, "Failed batch insert");
return(false);
}
}
//IList<T>数据源
//测试100000条数据 0.6s
//测试1000000条数据 5.1s
//适用于大批量数据
public JsonResult Index3(int count = 100)
{
var Count = count;
DateTime dt1 = DateTime.Now;
var data = new List <peoples>();
for (int i = 0; i < Count; i++)
{
data.Add(new peoples()
{
name = ("插入name" + i),
age = (Count + i),
sex = (Count + i) % 2
});
}
using (MySqlConnection conn =
new MySqlConnection("server=localhost;userid=root;pwd=123456;port=3306;database=test;sslmode=none;"))
{
var results = conn.BulkInsert(data, typeof(peoples).Name.ToString());
}
DateTime dt2 = DateTime.Now;
data = null;
return(Json(new { Count = Count, Hs = (dt2 - dt1).TotalSeconds }));
}
public bool BatchInsert(IEnumerable <TempMasterPatientIndex> extracts)
{
var cn = GetConnectionString();
try
{
if (GetConnectionProvider() == DatabaseProvider.MySql)
{
using (var connection = new MySqlConnection(cn))
{
connection.BulkInsert(extracts);
}
}
if (GetConnectionProvider() == DatabaseProvider.MsSql)
{
using (var connection = new SqlConnection(cn))
{
connection.BulkInsert(extracts);
}
}
return(true);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Log.Error(e, "Failed batch insert");
return(false);
}
}
public void BulkInsert()
{
var range = 10;
var dtos = Enumerable.Range(0, range).Select(i => new LocationDto
{
City = i.ToString(),
Latitude = 1.0,
Longitude = 3.0,
Zip = i.ToString(),
Extra = new[] { 10, 12 },
AnnoyingInterface = new AnnoyingInterface()
}).ToArray();
using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySql"].ConnectionString))
{
conn.Open();
conn.BulkInsert(dtos);
for (int i = 0; i < range; i++)
{
var dto = conn.Query <LocationDto>(
q => q.Where(w => w.Equal(x => x.Zip, i.ToString()))
).FirstOrDefault();
Assert.That(dto.City, Is.EqualTo(i.ToString()));
}
}
}
static void Main(string[] args)
{
using var conn = _conn;
var watch = System.Diagnostics.Stopwatch.StartNew();
watch.Start();
var elapsedMs = watch.ElapsedMilliseconds;
DapperPlusManager.Entity <User>().Table("User").Identity(x => x.ID);
DapperPlusManager.Entity <UserGroup>().Table("UserGroup").Identity(x => x.Id).
Ignore(x => x.User).AfterAction((kind, x) => {
if (kind == DapperPlusActionKind.Insert || kind == DapperPlusActionKind.Merge)
{
x.User.UserGroupId = x.Id;
}
});
var user = new User {
Account = "user1"
};
var userGroup = new UserGroup {
Name = "group1", User = user
};
_conn.BulkInsert(userGroup).ThenBulkInsert(x => x.User);
Console.WriteLine(elapsedMs);
watch.Stop();
}
public void InsertMany(IEnumerable <Animal> items)
{
var connString = "Server=localhost; Port=3306; Database=dapper; Uid=test; Pwd=test; SslMode=none; Max Pool Size=1000";
using (var connection = new MySqlConnection(connString))
{
connection.Open();
connection.BulkInsert(items);
}
}
public void BulkInsertZeroEntries()
{
var dtos = new LocationDto[0];
using (var conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySql"].ConnectionString))
{
conn.Open();
conn.BulkInsert(dtos);
Assert.That(conn.Count <LocationDto>(), Is.EqualTo(0));
}
}
static async Task Main(string[] args)
{
var port = 33061;
var server = "localhost";
var database = "homestead";
var user = "******";
var password = "******";
var connectionString = $"Server={server};Port={port};Database={database};Uid={user};Pwd={password};";
using (var connection = new MySqlConnection(connectionString)) {
var count = "SELECT COUNT(*) FROM messages";
connection.Open();
Stopwatch stopWatch = new Stopwatch();
var result = await connection.QueryAsync <Int32>(count);
Console.WriteLine(result.Sum());
var recordsToInsert = 1000000;
var workerCount = 100;
stopWatch.Start();
TimeSpan ts;
string elapsedTime = "";
int recordsInserted = 0;
void outputStatus()
{
Console.Clear();
Console.WriteLine("InsertingMessages...\n");
Console.WriteLine($"Total records in DB: {result.Sum() + recordsInserted}");
Console.WriteLine($"{recordsInserted}/{ recordsToInsert} {(int)((float)recordsInserted / (float)recordsToInsert * 100)}%");
ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
Console.WriteLine("\nRunTime " + elapsedTime);
}
List <Thread> threads = new List <Thread>();
for (int i = 0; i < workerCount; i++)
{
//var thread = new Thread(() => {
var taskCount = recordsToInsert / workerCount;
var messages = Enumerable.Range(0, taskCount)
.Select(x => {
var message = new Message {
id = (i * taskCount) + x,
contact_id = (i * taskCount) + x
};
recordsInserted++;
outputStatus();
return(message);
});
connection.BulkInsert <Message>(messages);
//});
//thread.Start();
//threads.Add(thread);
}
//int completedThreads = 0;
//while (completedThreads < workerCount) {
// threads.ForEach(x => {
// if (!x.IsAlive)
// {
// completedThreads++;
// }
// });
//}
outputStatus();
stopWatch.Stop();
connection.Dispose();
}
Console.WriteLine("\nDone.");
}