public override int Insert(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, TableInfo tableInfo, object entityToInsert)
{
var cmd = new StringBuilder(PartsQryGenerator.InsertQuery(tableInfo));
connection.Execute(cmd.ToString(), entityToInsert, transaction, commandTimeout);
var r = connection.Query("Select LAST_INSERT_ID() id", transaction: transaction, commandTimeout: commandTimeout);
var id = r.First().id;
if (id == null)
{
return(0);
}
var propertyInfos = tableInfo.KeyColumns?.Select(f => f.Property).ToArray();
if (propertyInfos?.Length == 0)
{
return(Convert.ToInt32(id));
}
var idp = propertyInfos[0];
idp.SetValue(entityToInsert, Convert.ChangeType(id, idp.PropertyType), null);
return(Convert.ToInt32(id));
}
/// <summary>
///
/// </summary>
/// <param name="connection"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <param name="tableInfo"></param>
/// <param name="entityToInsert"></param>
public override bool BulkInsert(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, TableInfo tableInfo, IList entityToInsert)
{
int batchSize = 1000;
bool isTrans = transaction == null;
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
transaction = transaction ?? connection.BeginTransaction();
var count = entityToInsert.Count;
var batch = (int)Math.Ceiling(((double)count) / batchSize);
var sqlpre = $"insert into { PartsQryGenerator.EscapeTableName(tableInfo)} ({PartsQryGenerator.EscapeColumnList(tableInfo.InsertColumns)}) values ";
int last = count - ((batch - 1) * batchSize);
var paramLength = (batch == 1 ? last : batchSize);
var paramList = new List <string>();
for (var b = 0; b < paramLength; b++)
{
paramList.Add($"({PartsQryGenerator.EscapeParameters(tableInfo.InsertColumns, b.ToString())})");
}
var bathSql = sqlpre + string.Join(",", paramList);
var lastSql = sqlpre + string.Join(",", paramList.Take(last));
for (var i = 0; i < batch; i++)
{
var parameters = new DynamicParameters();
var exLength = (i * batchSize);
var length = (i == batch - 1) ? last : batchSize;
for (var p = 0; p < length; p++)
{
foreach (var column in tableInfo.InsertColumns)
{
parameters.Add(column.ColumnName + p, column.Property.GetValue(entityToInsert[exLength + p], null));
}
}
try
{
var sql = (i == batch - 1) ? lastSql : bathSql;
connection.Execute(sql, parameters, transaction, commandTimeout);
}
catch (Exception ex)
{
if (isTrans)
{
transaction.Rollback();
}
throw ex;
}
}
if (isTrans)
{
transaction.Commit();
}
if (connection.State == ConnectionState.Open && isTrans)
{
connection.Close();
}
return(true);
}
