public void BulkInsert__DataOfCustomTypePassed__MappedToTableAndInserted(bool async)
{
Name.Gender ConvertGender(bool boolean) => boolean ? Name.Gender.Male : Name.Gender.Female;
// Arrange
List <User> data = new Faker <User>()
.RuleFor(u => u.Gender, (f, u) => f.Random.Bool())
.RuleFor(u => u.FirstName, (f, u) => f.Name.FirstName(ConvertGender(u.Gender)))
.RuleFor(u => u.LastName, (f, u) => f.Name.FirstName(ConvertGender(u.Gender)))
.RuleFor(u => u.Age, (f, u) => f.Random.Number(18, 99))
.Generate(1000);
// Act
if (async)
{
_connection.BulkInsertAsync(data, commandTimeout: 10).GetAwaiter().GetResult();
}
else
{
_connection.BulkInsert(data, commandTimeout: 10);
}
List <User> inserted = _connection.Query <User>("SELECT * FROM [User]").ToList();
// Assert
Assert.IsTrue(inserted.All(u => u.Id != 0));
Assert.IsTrue(data.SequenceEqual(inserted, new UserComparer()));
}
public static int BulkInsertOracleDb <T>(this IDbConnection con, IEnumerable <T> dataList, Func <PropertyInfo, Boolean> filter, int linesPerBatch = 1000, int timeout = 0, DMLOptions options = null)
{
DMLOptions curOptions = options ?? OracleDefaultOptions;
string commandText = filter != null ? Statements <T> .GetFilteredInsert(filter, curOptions) : Statements <T> .GetInsert(curOptions);
return(con.BulkInsert(dataList, commandText, linesPerBatch, timeout, curOptions));
}
public virtual void Insert(TEntity entity)
{
var result = _connection.BulkInsert(new List <TEntity>()
{
entity
});
}
private void btnImport_Click(object sender, RoutedEventArgs e)
{
try
{
DapperPlusManager.Entity <Country>().Table("Country");
List <Country> country = dataGrid.ItemsSource as List <Country>;
if (country != null)
{
using (IDbConnection db = sqlcon)
{
db.Open();
db.BulkInsert(country);
db.Close();
System.Windows.MessageBox.Show("Data Import Into Database Successfully");
MainWindow win = new MainWindow();
win.Show();//Open previouse page
Window sc = (Window)this.Parent;
sc.Close();
}
}
}
catch (Exception ex)
{
System.Windows.MessageBox.Show(ex.Message, "Message", MessageBoxButton.OK, System.Windows.MessageBoxImage.Error);
}
}
public IEnumerable <T> BulkInsert <T>(IEnumerable <T> ts)
{
Write();
DapperPlusActionSet <T> result = connection.BulkInsert <T>(ts);
return(result.Current);
}
protected void CreateMany(List <T> item)
{
DapperPlusManager.Entity <T>().Table(typeof(T).Name);
using (IDbConnection db = CreateConnection(_connectionString))
{
db.BulkInsert(item);
}
}
public DBResult BulkInsert(List <object> list)
{
using (IDbConnection dbConnection = Connection)
{
var result = dbConnection.BulkInsert(list);
}
return(new DBResult());
}
public DBResult BulkInsert(object obj)
{
using (IDbConnection dbConnection = Connection)
{
var result = dbConnection.BulkInsert(obj);
}
return(new DBResult());
}
/// <summary>
/// Run all defined data migrations that have not been run yet
/// </summary>
/// <param name="db">current database connection</param>
/// <returns>Count of migrations run</returns>
public static int RunMigrations(IDbConnection db)
{
var migrationsRun = 0;
var migrationTypes = GetMigrationTypes();
var alreadyExecutedMigrations = db.Query <DataMigration>("select * from [{0}]".Fmt(db.GetTableName <DataMigration>())).ToList();
if (!alreadyExecutedMigrations.Any(x => x.Name == typeof(InitializeDbMigration).Name))
{
// Create all data, run data init migration
var schemaBuilder = new SchemaBuilder(db);
schemaBuilder.GenerateSchema(false);
var dataInitMigration = new InitializeDbMigration();
dataInitMigration.Execute(db);
// Populate all migrations as run
var migrations = migrationTypes.Select(x => new DataMigration
{
Name = x.Name
});
return(db.BulkInsert(migrations));
}
var scriptedObjectsToRecreate = new List <Type>();
foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order))
{
// Collect the scripted objects to recreate after migrations are run
if (migration.ScriptedObjectsToRecreate != null && migration.ScriptedObjectsToRecreate.Any())
{
foreach (var viewType in migration.ScriptedObjectsToRecreate.Where(viewType => !scriptedObjectsToRecreate.Any(v => v == viewType)))
{
scriptedObjectsToRecreate.Add(viewType);
}
}
migration.Execute(db);
// add migration to database
var migrationData = new DataMigration
{
CreatedOn = DateTime.UtcNow,
Name = migration.GetType().Name
};
db.Save(migrationData);
migrationsRun++;
}
if (scriptedObjectsToRecreate.Any())
{
foreach (var viewType in scriptedObjectsToRecreate)
{
db.RecreateScriptedObject(viewType);
}
}
return(migrationsRun);
}
/// <summary>
/// Run all defined data migrations that have not been run yet
/// </summary>
/// <param name="db">current database connection</param>
/// <returns>Count of migrations run</returns>
public static int RunMigrations(IDbConnection db)
{
var migrationsRun = 0;
var migrationTypes = GetMigrationTypes();
var alreadyExecutedMigrations = db.Query<DataMigration>("select * from [{0}]".Fmt(db.GetTableName<DataMigration>())).ToList();
if (!alreadyExecutedMigrations.Any(x => x.Name == typeof(InitializeDbMigration).Name))
{
// Create all data, run data init migration
var schemaBuilder = new SchemaBuilder(db);
schemaBuilder.GenerateSchema(false);
var dataInitMigration = new InitializeDbMigration();
dataInitMigration.Execute(db);
// Populate all migrations as run
var migrations = migrationTypes.Select(x => new DataMigration
{
Name = x.Name
});
return db.BulkInsert(migrations);
}
var scriptedObjectsToRecreate = new List<Type>();
foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order))
{
// Collect the scripted objects to recreate after migrations are run
if (migration.ScriptedObjectsToRecreate != null && migration.ScriptedObjectsToRecreate.Any())
{
foreach (var viewType in migration.ScriptedObjectsToRecreate.Where(viewType => !scriptedObjectsToRecreate.Any(v => v == viewType)))
{
scriptedObjectsToRecreate.Add(viewType);
}
}
migration.Execute(db);
// add migration to database
var migrationData = new DataMigration
{
CreatedOn = DateTime.UtcNow,
Name = migration.GetType().Name
};
db.Save(migrationData);
migrationsRun++;
}
if (scriptedObjectsToRecreate.Any())
{
foreach (var viewType in scriptedObjectsToRecreate)
{
db.RecreateScriptedObject(viewType);
}
}
return migrationsRun;
}
/// <summary>
/// This saves the holiday in the office.
/// </summary>
/// <param name="currentOffice">Current office to be saved.</param>
/// <param name="holidayDto">List of vacation for the current office.</param>
/// <returns>return a task for saving the holidays</returns>
private async Task SaveHolidayOfficeAsync(IDbConnection connection, OFICINAS currentOffice, IEnumerable <HolidayDto> holidayDto)
{
Contract.Requires(connection != null, "Connection is not null");
Contract.Requires(currentOffice != null, "Current office is not null");
Contract.Requires(holidayDto != null, "HolidayDto is not null");
IEnumerable <FESTIVOS_OFICINA> holidayOffice = _mapper.Map <IEnumerable <HolidayDto>, IEnumerable <FESTIVOS_OFICINA> >(holidayDto);
IQueryStore store = _queryStoreFactory.GetQueryStore();
store.AddParam(QueryType.HolidaysByOffice, currentOffice.CODIGO);
var query = store.BuildQuery();
bool saved = false;
// First i want fetch the current festivo oficina.
// we shall insert or merge.
try
{
IEnumerable <FESTIVOS_OFICINA> currentHolidays = await connection.QueryAsync <FESTIVOS_OFICINA>(query);
if (currentHolidays.Count <FESTIVOS_OFICINA>() == 0)
{
connection.BulkInsert(holidayOffice);
}
else
{
// FIXME : check for concurrent optimistic lock.
var holidaysToBeInserted = holidayOffice.Except(currentHolidays);
connection.BulkInsert <FESTIVOS_OFICINA>(holidaysToBeInserted);
var holidaysToBeUpdated = holidayOffice.Intersect(currentHolidays);
connection.BulkUpdate <FESTIVOS_OFICINA>(holidaysToBeUpdated);
}
saved = true;
}
catch (System.Exception e)
{
connection.Close();
connection.Dispose();
throw new DataLayerException(e.Message, e);
}
Contract.Ensures(saved);
}
private void btnImport_Click(object sender, EventArgs e)
{
try
{
DapperPlusManager.Entity <ExcelClassList>().Table("ClassList");
List <ExcelClassList> excelClassLists = classListBindingSource.DataSource as List <ExcelClassList>;
if (excelClassLists != null)
{
using (IDbConnection db = connection.GetSqlConnection())
{
db.BulkInsert(excelClassLists);
}
}
MessageBox.Show("Finish", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// <summary>
/// Run all defined data migrations that have not been run yet
/// </summary>
/// <param name="db">current database connection</param>
/// <returns>Count of migrations run</returns>
public static int RunMigrations(IDbConnection db)
{
var migrationsRun = 0;
var migrationTypes = GetMigrationTypes();
if (!db.TableExists(db.GetTableName <DataMigration>()))
{
// Create all data, run data init migration
var schemaBuilder = new SchemaBuilder(db);
schemaBuilder.GenerateSchema(false);
var dataInitMigration = new InitializeDbMigration();
dataInitMigration.Execute(db);
// Populate all migrations as run
var migrations = migrationTypes.Select(x => new DataMigration
{
Name = x.Name
});
return(db.BulkInsert(migrations));
}
var alreadyExecutedMigrations = db.Query <DataMigration>("select * from [{0}]".Fmt(db.GetTableName <DataMigration>()));
foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order))
{
migration.Execute(db);
// add migration to database
var migrationData = new DataMigration
{
CreatedOn = DateTime.UtcNow,
Name = migration.GetType().Name
};
db.Save(migrationData);
migrationsRun++;
}
return(migrationsRun);
}
/// <summary>
/// Run all defined data migrations that have not been run yet
/// </summary>
/// <param name="db">current database connection</param>
/// <returns>Count of migrations run</returns>
public static int RunMigrations(IDbConnection db)
{
var migrationsRun = 0;
var migrationTypes = GetMigrationTypes();
if (!db.TableExists(db.GetTableName<DataMigration>()))
{
// Create all data, run data init migration
var schemaBuilder = new SchemaBuilder(db);
schemaBuilder.GenerateSchema(false);
var dataInitMigration = new InitializeDbMigration();
dataInitMigration.Execute(db);
// Populate all migrations as run
var migrations = migrationTypes.Select(x => new DataMigration
{
Name = x.Name
});
return db.BulkInsert(migrations);
}
var alreadyExecutedMigrations = db.Query<DataMigration>("select * from [{0}]".Fmt(db.GetTableName<DataMigration>()));
foreach (var migration in migrationTypes.Where(t => !alreadyExecutedMigrations.Any(m => m.Name == t.Name)).Select(x => (IMigration)Activator.CreateInstance(x)).OrderBy(x => x.Order))
{
migration.Execute(db);
// add migration to database
var migrationData = new DataMigration
{
CreatedOn = DateTime.UtcNow,
Name = migration.GetType().Name
};
db.Save(migrationData);
migrationsRun++;
}
return migrationsRun;
}
/// <summary>
/// Thực hiện thêm
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
/// createdby ngochtb(02/12/2020)
public int Add(TEntity entity)
{
_dbConnection.Open();
using (var transaction = _dbConnection.BeginTransaction())
{
entity.GetType().GetProperty($"{_tableName}Id").SetValue(entity, Guid.NewGuid());
var rowAffects = _dbConnection.BulkInsert(entity);
//try
//{
// var parameters = MappingDbType(entity);
// // Thực hiện thêm khách hàng:
// rowAffects = _dbConnection.Execute($"Proc_Insert{_tableName}", parameters, commandType: CommandType.StoredProcedure);
// transaction.Commit();
//}
//catch (Exception ex)
//{
// transaction.Rollback();
//}
transaction.Commit();
return(rowAffects.Actions.Count());
}
// Trả về kết quả (số bản ghi thêm mới được)
}
/// <summary>
/// 批量插入 待测试
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
/// <returns></returns>
public int InsertRange <T>(IEnumerable <T> entities)
{
var res = Connection.BulkInsert <T>(entities.ToArray());
return(res.Current.Count());
}
/// <summary>
/// Внести значения
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entities"></param>
public override void Insert <T>(T[] entities)
{
_inMemoryConnection.BulkInsert(entities);
}
/// <summary>
/// An IDbConnection extension method to INSERT entities in a database table or a view.
/// </summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="connection">The connection to act on.</param>
/// <param name="items">items to insert.</param>
/// <returns>A DapperPlusActionSet<T></returns>
public static DapperPlusActionSet <T> BulkInsert <T>(this IDbConnection connection, params T[] items)
{
return(connection.BulkInsert <T>(null, items));
}
/// <summary>
/// An IDbConnection extension method to INSERT entities in a database table or a view.
/// </summary>
/// <typeparam name="T">Generic type parameter.</typeparam>
/// <param name="connection">The connection to act on.</param>
/// <param name="items">items to insert.</param>
/// <param name="selectors">The selection of entities to insert.</param>
/// <returns>A DapperPlusActionSet<T></returns>
public static DapperPlusActionSet <T> BulkInsert <T>(this IDbConnection connection, IEnumerable <T> items, params Func <T, object>[] selectors)
{
return(connection.BulkInsert(null, items, selectors));
}
/// <summary>
/// Efficiently inserts multiple rows, in batches as necessary.
/// </summary>
public static int BulkInsert <TInsert>(this IDbConnection connection, string sql, IEnumerable <TInsert> insertParams, IDbTransaction transaction = null, int?batchSize = null)
{
return(connection.BulkInsert(sql, (object)null, insertParams, transaction, batchSize));
}
public static int BulkInsertSql <T>(this IDbConnection con, IEnumerable <T> dataList, Func <PropertyInfo, Boolean> columnfilter, int linesPerBatch = 1000, int timeout = 0, DMLOptions options = null)
{
var columns = typeof(T).GetMembers(columnfilter != null ? columnfilter : null);
return(con.BulkInsert(dataList, columns, linesPerBatch, timeout, options));
}
public static int BulkInsertSql <T>(this IDbConnection con, IEnumerable <T> dataList, int linesPerBatch = 1000, int timeout = 0, DMLOptions options = null)
{
return(con.BulkInsert(dataList, typeof(T).GetMembers <PropertyInfo>(), linesPerBatch, timeout, options));
}
public static int BulkInsertOracleDb <T>(this IDbConnection con, IEnumerable <T> dataList, int linesPerBatch = 1000, int timeout = 0, DMLOptions options = null)
{
DMLOptions curOptions = options ?? OracleDefaultOptions;
return(con.BulkInsert(dataList, Statements <T> .GetInsert(curOptions), linesPerBatch, timeout, curOptions));
}
