/// <summary>批量插入</summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="list">实体列表</param>
/// <param name="columns">要插入的字段，默认所有字段</param>
/// <param name="session">指定会话，分表分库时必用</param>
/// <returns>
/// Oracle：当批量插入操作中有一条记录无法正常写入，则本次写入的所有数据都不会被写入（可以理解为自带事物）
/// MySQL：当批量插入操作中有一条记录无法正常写入，则本次写入的所有数据都不会被写入（可以理解为自带事物）
/// </returns>
public static Int32 BatchInsert <T>(this IEnumerable <T> list, IDataColumn[] columns = null, IEntitySession session = null) where T : IEntity
{
if (list == null || !list.Any())
{
return(0);
}
var entity = list.First();
var fact = entity.GetType().AsFactory();
if (columns == null)
{
columns = fact.Fields.Select(e => e.Field).ToArray();
// 第一列数据包含非零自增，表示要插入自增值
var id = columns.FirstOrDefault(e => e.Identity);
if (id != null)
{
if (entity[id.Name].ToLong() == 0)
{
columns = columns.Where(e => !e.Identity).ToArray();
}
}
// 每个列要么有脏数据，要么允许空。不允许空又没有脏数据的字段插入没有意义
//var dirtys = GetDirtyColumns(fact, list.Cast<IEntity>());
//if (fact.FullInsert)
// columns = columns.Where(e => e.Nullable || dirtys.Contains(e.Name)).ToArray();
//else
// columns = columns.Where(e => dirtys.Contains(e.Name)).ToArray();
if (!fact.FullInsert)
{
var dirtys = GetDirtyColumns(fact, list.Cast <IEntity>());
columns = columns.Where(e => dirtys.Contains(e.Name)).ToArray();
}
}
session ??= fact.Session;
session.InitData();
var dal = session.Dal;
dal.CheckDatabase();
//var tableName = dal.Db.FormatTableName(session.TableName);
var tracer = dal.Tracer ?? DAL.GlobalTracer;
using var span = tracer?.NewSpan($"db:{dal.ConnName}:BatchInsert:{session.TableName}");
try
{
return(dal.Session.Insert(session.Table, columns, list.Cast <IExtend>()));
}
catch (Exception ex)
{
span?.SetError(ex, list);
throw;
}
}
/// <summary>批量更新</summary>
/// <remarks>
/// 注意类似：XCode.Exceptions.XSqlException: ORA-00933: SQL 命令未正确结束
/// [SQL:Update tablen_Name Set FieldName=:FieldName W [:FieldName=System.Int32[]]][DB:AAA/Oracle]
/// 建议是优先检查表是否存在主键，如果由于没有主键导致，即使通过try...cache 依旧无法正常保存。
/// </remarks>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="list">实体列表</param>
/// <param name="columns">要更新的字段，默认所有字段</param>
/// <param name="updateColumns">要更新的字段，默认脏数据</param>
/// <param name="addColumns">要累加更新的字段，默认累加</param>
/// <param name="session">指定会话，分表分库时必用</param>
/// <returns></returns>
public static Int32 BatchUpdate <T>(this IEnumerable <T> list, IDataColumn[] columns = null, ICollection <String> updateColumns = null, ICollection <String> addColumns = null, IEntitySession session = null) where T : IEntity
{
if (list == null || !list.Any())
{
return(0);
}
var entity = list.First();
var fact = entity.GetType().AsFactory();
if (columns == null)
{
columns = fact.Fields.Select(e => e.Field).Where(e => !e.Identity).ToArray();
}
//if (updateColumns == null) updateColumns = entity.Dirtys.Keys;
if (updateColumns == null)
{
// 所有实体对象的脏字段作为更新字段
var dirtys = GetDirtyColumns(fact, list.Cast <IEntity>());
// 创建时间等字段不参与Update
dirtys = dirtys.Where(e => !e.StartsWithIgnoreCase("Create")).ToArray();
if (dirtys.Length > 0)
{
updateColumns = dirtys;
}
}
if (addColumns == null)
{
addColumns = fact.AdditionalFields;
}
if ((updateColumns == null || updateColumns.Count < 1) && (addColumns == null || addColumns.Count < 1))
{
return(0);
}
session ??= fact.Session;
session.InitData();
var dal = session.Dal;
dal.CheckDatabase();
//var tableName = dal.Db.FormatTableName(session.TableName);
var tracer = dal.Tracer ?? DAL.GlobalTracer;
using var span = tracer?.NewSpan($"db:{dal.ConnName}:BatchUpdate:{session.TableName}");
try
{
return(dal.Session.Update(session.Table, columns, updateColumns, addColumns, list.Cast <IExtend>()));
}
catch (Exception ex)
{
span?.SetError(ex, list);
throw;
}
}
/// <summary>批量插入或更新</summary>
/// <param name="entity">实体对象</param>
/// <param name="columns">要插入的字段，默认所有字段</param>
/// <param name="updateColumns">主键已存在时，要更新的字段</param>
/// <param name="addColumns">主键已存在时，要累加更新的字段</param>
/// <param name="session">指定会话，分表分库时必用</param>
/// <returns>
/// MySQL返回值：返回值相当于流程执行次数，及时insert失败也会累计一次执行（所以不建议通过该返回值确定操作记录数）
/// do insert success = 1次;
/// do update success =2次(insert 1次+update 1次)，
/// 简单来说：如果Insert 成功则返回1，如果需要执行的是update 则返回2，
/// </returns>
public static Int32 Upsert(this IEntity entity, IDataColumn[] columns = null, ICollection <String> updateColumns = null, ICollection <String> addColumns = null, IEntitySession session = null)
{
var fact = entity.GetType().AsFactory();
if (columns == null)
{
columns = fact.Fields.Select(e => e.Field).Where(e => !e.Identity).ToArray();
// 每个列要么有脏数据，要么允许空。不允许空又没有脏数据的字段插入没有意义
//var dirtys = GetDirtyColumns(fact, new[] { entity });
//if (fact.FullInsert)
// columns = columns.Where(e => e.Nullable || dirtys.Contains(e.Name)).ToArray();
//else
// columns = columns.Where(e => dirtys.Contains(e.Name)).ToArray();
if (!fact.FullInsert)
{
var dirtys = GetDirtyColumns(fact, new[] { entity });
columns = columns.Where(e => e.PrimaryKey || dirtys.Contains(e.Name)).ToArray();
}
}
if (updateColumns == null)
{
updateColumns = entity.Dirtys.Where(e => !e.StartsWithIgnoreCase("Create")).Distinct().ToArray();
}
if (addColumns == null)
{
addColumns = fact.AdditionalFields;
}
session ??= fact.Session;
session.InitData();
var dal = session.Dal;
dal.CheckDatabase();
//var tableName = dal.Db.FormatTableName(session.TableName);
var tracer = dal.Tracer ?? DAL.GlobalTracer;
using var span = tracer?.NewSpan($"db:{dal.ConnName}:Upsert:{session.TableName}");
try
{
return(dal.Session.Upsert(session.Table, columns, updateColumns, addColumns, new[] { entity as IExtend }));
}
catch (Exception ex)
{
span?.SetError(ex, entity);
throw;
}
}
/// <summary>批量插入或更新</summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="list">实体列表</param>
/// <param name="columns">要插入的字段，默认所有字段</param>
/// <param name="updateColumns">要更新的字段，默认脏数据</param>
/// <param name="addColumns">要累加更新的字段，默认累加</param>
/// <param name="session">指定会话，分表分库时必用</param>
/// <returns>
/// MySQL返回值：返回值相当于流程执行次数，及时insert失败也会累计一次执行（所以不建议通过该返回值确定操作记录数）
/// do insert success = 1次;
/// do update success =2次(insert 1次+update 1次)，
/// 简单来说：对于一行记录，如果Insert 成功则返回1，如果需要执行的是update 则返回2
/// Oracle返回值：无论是插入还是更新返回的都始终为-1
/// </returns>
public static Int32 Upsert <T>(this IEnumerable <T> list, IDataColumn[] columns = null, ICollection <String> updateColumns = null, ICollection <String> addColumns = null, IEntitySession session = null) where T : IEntity
{
if (list == null || !list.Any())
{
return(0);
}
var entity = list.First();
var fact = entity.GetType().AsFactory();
session ??= fact.Session;
// SqlServer的批量Upsert需要主键参与，哪怕是自增，构建update的where时用到主键
if (columns == null)
{
var dbt = session.Dal.DbType;
if (dbt == DatabaseType.SqlServer || dbt == DatabaseType.Oracle)
{
columns = fact.Fields.Select(e => e.Field).Where(e => !e.Identity || e.PrimaryKey).ToArray();
}
else if (dbt == DatabaseType.MySql)
{
columns = fact.Fields.Select(e => e.Field).ToArray(); //只有标识键的情况下会导致重复执行insert方法 目前只测试了Mysql库
}
else
{
columns = fact.Fields.Select(e => e.Field).Where(e => !e.Identity).ToArray();
}
// 每个列要么有脏数据，要么允许空。不允许空又没有脏数据的字段插入没有意义
//var dirtys = GetDirtyColumns(fact, list.Cast<IEntity>());
//if (fact.FullInsert)
// columns = columns.Where(e => e.Nullable || dirtys.Contains(e.Name)).ToArray();
//else
// columns = columns.Where(e => dirtys.Contains(e.Name)).ToArray();
if (!fact.FullInsert)
{
var dirtys = GetDirtyColumns(fact, list.Cast <IEntity>());
columns = columns.Where(e => e.PrimaryKey || dirtys.Contains(e.Name)).ToArray();
}
}
//if (updateColumns == null) updateColumns = entity.Dirtys.Keys;
if (updateColumns == null)
{
// 所有实体对象的脏字段作为更新字段
var dirtys = GetDirtyColumns(fact, list.Cast <IEntity>());
// 创建时间等字段不参与Update
dirtys = dirtys.Where(e => !e.StartsWithIgnoreCase("Create")).ToArray();
if (dirtys.Length > 0)
{
updateColumns = dirtys;
}
}
if (addColumns == null)
{
addColumns = fact.AdditionalFields;
}
// 没有任何数据变更则直接返回0
if ((updateColumns == null || updateColumns.Count <= 0) && (addColumns == null || addColumns.Count <= 0))
{
return(0);
}
session.InitData();
var dal = session.Dal;
dal.CheckDatabase();
//var tableName = dal.Db.FormatTableName(session.TableName);
var tracer = dal.Tracer ?? DAL.GlobalTracer;
using var span = tracer?.NewSpan($"db:{dal.ConnName}:BatchUpsert:{session.TableName}");
try
{
return(dal.Session.Upsert(session.Table, columns, updateColumns, addColumns, list.Cast <IExtend>()));
}
catch (Exception ex)
{
span?.SetError(ex, list);
throw;
}
}
/// <summary>批量替换</summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="list">实体列表</param>
/// <param name="columns">要插入的字段，默认所有字段</param>
/// <param name="session">指定会话，分表分库时必用</param>
/// <returns>
/// Oracle：当批量插入操作中有一条记录无法正常写入，则本次写入的所有数据都不会被写入（可以理解为自带事物）
/// MySQL：当批量插入操作中有一条记录无法正常写入，则本次写入的所有数据都不会被写入（可以理解为自带事物）
/// </returns>
public static Int32 BatchReplace <T>(this IEnumerable <T> list, IDataColumn[] columns = null, IEntitySession session = null) where T : IEntity
{
if (list == null || !list.Any())
{
return(0);
}
var entity = list.First();
var fact = entity.GetType().AsFactory();
if (columns == null)
{
columns = fact.Fields.Select(e => e.Field).ToArray();
// 第一列数据包含非零自增，表示要插入自增值
var id = columns.FirstOrDefault(e => e.Identity);
if (id != null)
{
if (entity[id.Name].ToLong() == 0)
{
columns = columns.Where(e => !e.Identity).ToArray();
}
}
// 每个列要么有脏数据，要么允许空。不允许空又没有脏数据的字段插入没有意义
if (!fact.FullInsert)
{
var dirtys = GetInsertColumns(fact, list.Cast <IEntity>());
columns = columns.Where(e => dirtys.Contains(e.Name)).ToArray();
}
}
session ??= fact.Session;
session.InitData();
var dal = session.Dal;
//dal.CheckDatabase();
var tracer = dal.Tracer ?? DAL.GlobalTracer;
using var span = tracer?.NewSpan($"db:{dal.ConnName}:BatchReplace:{session.TableName}");
try
{
if (span != null && list is ICollection collection)
{
span.Tag = collection.Count + "";
}
var rs = dal.Session.Replace(session.Table, columns, list.Cast <IExtend>());
// 清除脏数据，避免重复提交保存
foreach (var item in list)
{
item.Dirtys.Clear();
}
return(rs);
}
catch (Exception ex)
{
span?.SetError(ex, list);
throw;
}
}