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."); }