/// <summary>Gets a row result from the specified table.</summary>
/// <typeparam name="T"></typeparam>
/// <param name="tableName">Name of the table.</param>
/// <param name="throwError">Throw an exception on multiple results.</param>
/// <returns></returns>
/// <remarks>
/// This can only be used to get single row results! For multi row results use
/// <see cref="GetTable(string)" />, <see cref="GetTable{T}(string)" />
/// or the <see cref="Tables" /> property.
/// </remarks>
public T GetRow <T>(string tableName = null, bool throwError = false)
where T : struct
{
if (tableName == null)
{
tableName = TableAttribute.GetName(typeof(T));
}
T? value = null;
ITable table = GetTable(tableName);
foreach (Row row in table.GetRows())
{
if (throwError && value != null)
{
throw new InvalidDataException(string.Format("Expected single result but got multiple datasets at table {0}!", tableName));
}
value = row.GetStruct <T>(RowLayout.CreateTyped(typeof(T)));
}
if (value == null)
{
if (throwError)
{
throw new InvalidDataException(string.Format("No dataset at table {0}", tableName));
}
return(default(T));
}
return(value.Value);
}
public override string Delete <TEntity>(Expression <Func <TEntity, bool> > filter)
{
DbContext.TableName = TableAttribute.GetName(typeof(TEntity));
DbContext.SqlStatement = $"DELETE {filter.Parameters[0].Name} From {DbContext.TableName} {filter.Parameters[0].Name} {LambdaToSql.ConvertWhere(filter, out IDictionary<string, object> parameters)}".TrimEnd();
DbContext.Parameters = parameters;
return(DbContext.SqlStatement);
}
public override string Update <TEntity>(TEntity entity)
{
Context.TableName = TableAttribute.GetName(typeof(TEntity));
Context.Parameters = new Dictionary <string, object>();
StringBuilder builder_front = new StringBuilder(), builder_behind = new StringBuilder();
builder_front.Append("UPDATE ");
builder_front.Append(Context.TableName);
builder_front.Append(" SET ");
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//自增字段不拼进字段
if (propertyInfo.GetCustomAttribute(typeof(AutoAttribute), true) is AutoAttribute)
{
//默认主键是Where条件
if (propertyInfo.GetCustomAttribute(typeof(KeyAttribute), true) is KeyAttribute keyColumn)
{
builder_behind.Append(" WHERE ");
builder_behind.Append(keyColumn.GetName(propertyInfo.Name));
builder_behind.Append("=");
builder_behind.Append($"@t");
columnName = keyColumn.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "");
builder_behind.Append(columnName);
Context.Parameters.AddOrUpdate($"@t{columnName}", propertyInfo.GetValue(entity));
}
continue;
}
//Column
if (propertyInfo.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute column)
{
builder_front.Append(column.GetName(propertyInfo.Name));
builder_front.Append("=");
builder_front.Append($"@t");
columnName = column.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "");
builder_front.Append(columnName);
builder_front.Append(",");
Context.Parameters.AddOrUpdate($"@t{columnName}", propertyInfo.GetValue(entity));
}
//in the end,remove the redundant symbol of ','
if (propertyInfos.Last() == propertyInfo)
{
builder_front.Remove(builder_front.Length - 1, 1);
}
}
//传入生成的sql语句
return(Context.SqlText = builder_front.Append(builder_behind.ToString()).ToString().TrimEnd());
}
public override string Update <TEntity>(TEntity entity, out Expression <Func <TEntity, bool> > filter)
{
DbContext.Parameters = new Dictionary <string, object>();
DbContext.TableName = TableAttribute.GetName(typeof(TEntity));
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
filter = CreateUpdateWhereFilter <TEntity>(entity);
//开始构造赋值的sql语句
StringBuilder builder_front = new StringBuilder();
builder_front.Append("UPDATE ");
builder_front.Append(DbContext.TableName);
builder_front.Append(" ");
//查询语句中表的别名，例如“t”
string alias = filter.Parameters[0].Name;
builder_front.Append(alias);
builder_front.Append(" SET ");
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//AutoIncrease : if property is auto increase attribute skip this column.
if (propertyInfo.GetCustomAttribute(typeof(AutoIncreaseBaseAttribute), true) is AutoIncreaseBaseAttribute autoIncreaseAttr)
{
}
//Column :
else if (propertyInfo.GetCustomAttribute(typeof(ColumnBaseAttribute), true) is ColumnBaseAttribute columnAttr)
{
builder_front.Append(columnAttr.GetName(propertyInfo.Name));
builder_front.Append("=");
builder_front.Append($"{ParametricFlag}{alias}");
columnName = columnAttr.GetName(propertyInfo.Name).Replace("`", "");
builder_front.Append(columnName);
builder_front.Append(",");
DbContext.Parameters.AddOrUpdate($"{ParametricFlag}{alias}{columnName}", propertyInfo.GetValue(entity));
}
//in the end,remove the redundant symbol of ','
if (propertyInfos.Last() == propertyInfo)
{
builder_front.Remove(builder_front.Length - 1, 1);
}
}
//Generate SqlStatement
return(DbContext.SqlStatement = builder_front.Append($"{LambdaToSql.ConvertWhere(filter, DbContext.Parameters)}").ToString().TrimEnd());
}
/// <summary>
/// 更新数据到缓存（Add）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="entity"></param>
internal void AddCache <TEntity>(TEntity entity)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//如果存在表级别缓存，则更新数据到缓存
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName), out List <TEntity> entities))
{
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
entities.Add(entity);
//如果过期时间为0，则取上下文的过期时间
CacheStorageManager.Put(GetTableCacheKey(tableName), entities, tableCacheTimeSpan == TimeSpan.Zero ? CacheOptions.TableCacheExpiredTimeSpan : tableCacheTimeSpan);
}
}
}
public static void QueryList <TEntity>(DbContext dbContext, Expression <Func <TEntity, bool> > filter) where TEntity : class
{
dbContext.TableName = TableAttribute.GetName(typeof(TEntity));
switch (dbContext.DataBaseType)
{
case DataBaseType.SqlServer:
case DataBaseType.MySql:
dbContext.SqlStatement = $"SELECT * FROM {dbContext.TableName} {filter.Parameters[0].Name} {LambdaToSql.ConvertWhere(filter)}"; break;
case DataBaseType.Oracle:
dbContext.SqlStatement = string.Empty; break;
default:
dbContext.SqlStatement = string.Empty; break;
}
}
public override string Add <TEntity>(TEntity entity)
{
DbContext.TableName = TableAttribute.GetName(typeof(TEntity));
DbContext.Parameters = new Dictionary <string, object>();
StringBuilder builder_front = new StringBuilder(), builder_behind = new StringBuilder();
builder_front.Append("INSERT INTO ");
builder_front.Append(DbContext.TableName);
builder_front.Append(" (");
builder_behind.Append(" VALUES (");
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//AutoIncrease : if property is auto increase attribute skip this column.
if (propertyInfo.GetCustomAttribute(typeof(AutoIncreaseBaseAttribute), true) is AutoIncreaseBaseAttribute autoIncreaseAttr)
{
}
//Column
else if (propertyInfo.GetCustomAttribute(typeof(ColumnBaseAttribute), true) is ColumnBaseAttribute column)
{
builder_front.Append(column.GetName(propertyInfo.Name));
builder_front.Append(",");
builder_behind.Append(ParametricFlag);
columnName = column.GetName(propertyInfo.Name).Replace("`", "");
builder_behind.Append(columnName);
builder_behind.Append(",");
DbContext.Parameters.AddOrUpdate($"{ParametricFlag}{columnName}", propertyInfo.GetValue(entity));
}
//in the end,remove the redundant symbol of ','
if (propertyInfos.Last() == propertyInfo)
{
builder_front.Remove(builder_front.Length - 1, 1);
builder_front.Append(")");
builder_behind.Remove(builder_behind.Length - 1, 1);
builder_behind.Append(")");
}
}
//Generate SqlStatement
return(DbContext.SqlStatement = builder_front.Append(builder_behind.ToString()).ToString().TrimEnd());
}
public static void QueryOrderBy <TEntity>(DbContext dbContext, Expression <Func <TEntity, bool> > filter, Expression <Func <TEntity, object> > orderBy, bool isDESC) where TEntity : class
{
dbContext.TableName = TableAttribute.GetName(typeof(TEntity));
switch (dbContext.DataBaseType)
{
case DataBaseType.SqlServer:
case DataBaseType.MySql:
string desc = isDESC ? "DESC" : "ASC";
dbContext.SqlStatement = $"SELECT * FROM {dbContext.TableName} {filter.Parameters[0].Name} {LambdaToSql.ConvertWhere(filter)} ORDER BY {LambdaToSql.ConvertOrderBy(orderBy)} {desc}"; break;
case DataBaseType.Oracle:
dbContext.SqlStatement = string.Empty; break;
default:
dbContext.SqlStatement = string.Empty; break;
}
}
public override string Add <TEntity>(TEntity entity)
{
Context.TableName = TableAttribute.GetName(typeof(TEntity));
Context.Parameters = new Dictionary <string, object>();
StringBuilder builder_front = new StringBuilder(), builder_behind = new StringBuilder();
builder_front.Append("INSERT INTO ");
builder_front.Append(Context.TableName);
builder_front.Append(" (");
builder_behind.Append(" VALUES (");
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//自增字段不拼进字段
if (propertyInfo.GetCustomAttribute(typeof(AutoAttribute), true) is AutoAttribute)
{
continue;
}
//Column
if (propertyInfo.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute column)
{
builder_front.Append(column.GetName(propertyInfo.Name));
builder_front.Append(",");
builder_behind.Append("@");
columnName = column.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "");
builder_behind.Append(columnName);
builder_behind.Append(",");
Context.Parameters.AddOrUpdate($"@{columnName}", propertyInfo.GetValue(entity));
}
}
//去除末尾‘,’
builder_front.Remove(builder_front.Length - 1, 1);
builder_front.Append(")");
builder_behind.Remove(builder_behind.Length - 1, 1);
builder_behind.Append(")");
//传入生成的sql语句
return(Context.SqlText = builder_front.Append(builder_behind.ToString()).ToString().TrimEnd());
}
/// <summary>
/// 更新数据到缓存（Add）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="values"></param>
internal void AddCache <TEntity>(IEnumerable <TEntity> values)
{
if (DbContext.OpenTableCache)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//如果存在表级别缓存，则更新数据到缓存
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName), out List <TEntity> entities))
{
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
//如果过期时间为0，则取上下文的过期时间
TimeSpan timeSpan = tableCacheTimeSpan == TimeSpan.Zero ? DbContext.TableCacheExpiredTimeSpan : tableCacheTimeSpan;
entities.AddRange(values);
CacheStorageManager.Put(GetTableCacheKey(tableName), entities, tableCacheTimeSpan);
}
}
}
}
public static void QueryPaging <TEntity>(DbContext dbContext, int pageIndex, int pageSize, Expression <Func <TEntity, bool> > filter, Expression <Func <TEntity, object> > orderBy, bool isDESC) where TEntity : class
{
dbContext.TableName = TableAttribute.GetName(typeof(TEntity));
string desc = isDESC ? "DESC" : "ASC";
switch (dbContext.DataBaseType)
{
case DataBaseType.SqlServer:
dbContext.SqlStatement = $"SELECT TOP {pageSize} * FROM (SELECT ROW_NUMBER() OVER (ORDER BY {LambdaToSql.ConvertOrderBy(orderBy)} {desc}) AS RowNumber,* FROM {dbContext.TableName} {orderBy.Parameters[0].Name} {LambdaToSql.ConvertWhere(filter)}) AS TTTTTT WHERE RowNumber > {pageSize * (pageIndex - 1)}"; break;
case DataBaseType.MySql:
dbContext.SqlStatement = $"SELECT * FROM {dbContext.TableName} {filter.Parameters[0].Name} {LambdaToSql.ConvertWhere(filter)} ORDER BY {LambdaToSql.ConvertOrderBy(orderBy)} {desc} LIMIT {pageIndex * pageSize},{pageSize}"; break;
case DataBaseType.Oracle:
dbContext.SqlStatement = string.Empty; break;
default:
dbContext.SqlStatement = string.Empty; break;
}
}
/// <summary>
/// 从缓存中获取数据，如果没有，则后台执行扫描表任务
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="filter"></param>
/// <returns></returns>
internal List <TEntity> GetEntitiesFromCache <TEntity>(Expression <Func <TEntity, bool> > filter) where TEntity : class
{
Ensure.ArgumentNotNullOrEmpty(filter, nameof(filter));
//1.如果TableCache里面有该缓存键，则直接获取
if (CacheStorageManager.IsExist(GetTableCacheKey(TableAttribute.GetName(typeof(TEntity))), out List <TEntity> entities))
{
DbContext.IsFromCache = true;
return(entities.Where(filter.Compile()).ToList());
}
//2.则判断是否需要对该表进行扫描（含有TableCachingAttribute的标记的类才可以有扫描全表的权限）
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
//执行扫描全表数据任务
ScanTableBackground <TEntity>(tableCacheTimeSpan);
}
return(null);
}
/// <summary>
/// 更新数据到缓存（Delete）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="entity"></param>
internal void DeleteCache <TEntity>(TEntity entity)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//如果存在表级别缓存，则更新数据到缓存
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName), out List <TEntity> entities))
{
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
//如果过期时间为0，则取上下文的过期时间
TimeSpan timeSpan = tableCacheTimeSpan == TimeSpan.Zero ? CacheOptions.TableCacheExpiredTimeSpan : tableCacheTimeSpan;
//从缓存集合中寻找该记录，如果找到，则更新该记录
var val = entities.Find(t => t.Equals(entity));
if (val != null)
{
entities.Remove(val);
CacheStorageManager.Put(GetTableCacheKey(tableName), entities, tableCacheTimeSpan);
}
}
}
}
/// <summary>
/// 后台扫描全表数据
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext">上下文</param>
/// <param name="tableCacheTimeSpan">tableCache过期时间</param>
private void ScanTableBackground <TEntity>(TimeSpan tableCacheTimeSpan) where TEntity : class
{
string scanKey = $"{CachingConst.CacheKey_TableScanning}{DbContext.CollectionName}";
//1.判断正在扫描键是否存在，如果存在，则返回null，继续等待扫描任务完成
if (CacheStorageManager.IsExist(scanKey))
{
return;
}
//设置扫描键，标识当前正在进行扫描
CacheStorageManager.Put(scanKey, 1, CachingConst.SpanScaningKeyExpiredTime);
//2.如果没有扫描键，则执行后台扫描任务
Task.Factory.StartNew(() =>
{
//对扫描任务加锁，防止多线程环境多次执行任务
lock (tableScaningLocker)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//双重校验当前缓存是否存在TableCache，防止重复获取
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName)))
{
return;
}
//如果过期时间为0，则取上下文的过期时间
TimeSpan timeSpan = tableCacheTimeSpan == TimeSpan.Zero ? CacheOptions.TableCacheExpiredTimeSpan : tableCacheTimeSpan;
//执行扫描全表任务，并将结果存入缓存中
var data = DbContext.GetFullCollectionData <TEntity>();
//如果没查到，会存个空的集合
CacheStorageManager.Put(GetTableCacheKey(tableName), data ?? new List <TEntity>(), CacheOptions.TableCacheExpiredTimeSpan);
}
//将扫描键移除，表示已经扫描完成
CacheStorageManager.Delete(scanKey);
});
}
/// <summary>
/// 更新数据到缓存（Delete）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="filter"></param>
internal void DeleteCache <TEntity>(Expression <Func <TEntity, bool> > filter)
{
if (DbContext.OpenTableCache)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//如果存在表级别缓存，则更新数据到缓存
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName), out List <TEntity> entities))
{
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
//从缓存集合中寻找该记录，如果找到，则更新该记录
var list = entities.Where(filter.Compile()).ToList();
if (list != null && list.Any())
{
entities.RemoveAll(t => list.Contains(t));
//如果过期时间为0，则取上下文的过期时间
CacheStorageManager.Put(GetTableCacheKey(tableName), entities, tableCacheTimeSpan == TimeSpan.Zero ? DbContext.TableCacheExpiredTimeSpan : tableCacheTimeSpan);
}
}
}
}
}
/// <summary>
/// 更新数据到缓存（Update）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="entity"></param>
/// <param name="filter"></param>
internal void UpdateCache <TEntity>(TEntity entity, Expression <Func <TEntity, bool> > filter, IEnumerable <string> updateCloumns = null)
{
if (DbContext.OpenTableCache)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//如果存在表级别缓存，则更新数据到缓存
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName), out List <TEntity> oldEntities))
{
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
//如果过期时间为0，则取上下文的过期时间
TimeSpan timeSpan = tableCacheTimeSpan == TimeSpan.Zero ? DbContext.TableCacheExpiredTimeSpan : tableCacheTimeSpan;
//从缓存集合中寻找该记录，如果找到，则更新该记录
var list = oldEntities.Where(filter.Compile()).ToList();
if (list != null && list.Any())
{
List <TEntity> newEntities;
oldEntities.RemoveAll(t => list.Contains(t));
if (null == updateCloumns)
{
//改变了多条，更新对应字段
newEntities = UpdateEntitiesField(oldEntities, entity, updateCloumns);
}
else
{
//只改变了传入的唯一一条
newEntities = oldEntities;
newEntities.Add(entity);
}
CacheStorageManager.Put(GetTableCacheKey(tableName), newEntities, timeSpan);
}
}
}
}
}
/// <summary>
/// 更新数据到缓存（Update）
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="dbContext"></param>
/// <param name="entity"></param>
/// <param name="filter"></param>
internal void UpdateCache <TEntity>(TEntity entity, Expression <Func <TEntity, bool> > filter)
{
if (DbContext.OpenTableCache)
{
var tableName = TableAttribute.GetName(typeof(TEntity));
//如果存在表级别缓存，则更新数据到缓存
if (CacheStorageManager.IsExist(GetTableCacheKey(tableName), out List <TEntity> entities))
{
if (TableCachingAttribute.IsExistTaleCaching(typeof(TEntity), out TimeSpan tableCacheTimeSpan))
{
//如果过期时间为0，则取上下文的过期时间
TimeSpan timeSpan = tableCacheTimeSpan == TimeSpan.Zero ? DbContext.TableCacheExpiredTimeSpan : tableCacheTimeSpan;
//从缓存集合中寻找该记录，如果找到，则更新该记录
var val = entities.Where(filter.Compile()).FirstOrDefault();
if (val != null)
{
val = entity;
CacheStorageManager.Put(GetTableCacheKey(tableName), entities, timeSpan);
}
}
}
}
}
public override string Delete <TEntity>(TEntity entity)
{
Context.Parameters = new Dictionary <string, object>();
Context.TableName = TableAttribute.GetName(typeof(TEntity));
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
//get property which is key
var property = propertyInfos.Where(t => t.GetCustomAttribute(typeof(KeyAttribute), true) is KeyAttribute)?.FirstOrDefault();
if (property == null)
{
throw new Exception($"table '{Context.TableName}' not found key column");
}
string colunmName = property.Name;
var value = property.GetValue(entity);
if (property.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute columnAttr)
{
colunmName = columnAttr.GetName(property.Name);
}
Context.Parameters.AddOrUpdate($"@t{colunmName}", value);
return(Context.SqlText = $"DELETE t FROM {Context.TableName} t WHERE t.{colunmName} = @t{colunmName}".TrimEnd());
}
public static void Update <TEntity>(DbContext dbContext, Expression <Func <TEntity, bool> > filter, TEntity entity, out Dictionary <string, object> paramsDic) where TEntity : class
{
dbContext.TableName = TableAttribute.GetName(typeof(TEntity));
paramsDic = new Dictionary <string, object>();
StringBuilder builder_front = new StringBuilder();
builder_front.Append("UPDATE ");
//Mysql和sqlserver的分别处理
if (dbContext.DataBaseType == DataBaseType.MySql)
{
builder_front.Append(dbContext.TableName);
builder_front.Append(" ");
}
builder_front.Append(filter.Parameters[0].Name);
builder_front.Append(" SET ");
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//AutoIncrease : if property is auto increase attribute skip this column.
if (propertyInfo.GetCustomAttribute(typeof(AutoIncreaseAttribute), true) is AutoIncreaseAttribute autoIncreaseAttr)
{
}
//AutoIncrease : if property is auto increase attribute skip this column.
else if (propertyInfo.GetCustomAttribute(typeof(KeyAttribute), true) is KeyAttribute keyAttr)
{
}
//Column :
else if (propertyInfo.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute columnAttr)
{
builder_front.Append(columnAttr.GetName(propertyInfo.Name));
builder_front.Append("=");
builder_front.Append("@");
//multitype database support
switch (dbContext.DataBaseType)
{
case DataBaseType.SqlServer:
columnName = columnAttr.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "");
break;
case DataBaseType.MySql:
columnName = columnAttr.GetName(propertyInfo.Name).Replace("`", "");
break;
default:
//default 兼容mysql和sqlserver的系统字段
columnName = columnAttr.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "").Replace("`", "");
break;
}
builder_front.Append(columnName);
builder_front.Append(",");
if (!paramsDic.ContainsKey(columnName))
{
paramsDic.Add(columnName, propertyInfo.GetValue(entity));
}
}
//in the end,remove the redundant symbol of ','
if (propertyInfos.Last() == propertyInfo)
{
builder_front.Remove(builder_front.Length - 1, 1);
}
}
//SqlServer和Mysql的sql语句分别处理
if (dbContext.DataBaseType == DataBaseType.SqlServer)
{
builder_front.Append(" FROM ");
builder_front.Append(dbContext.TableName);
builder_front.Append(" ");
builder_front.Append(filter.Parameters[0].Name);
}
//Generate SqlStatement
dbContext.SqlStatement = builder_front.Append($"{LambdaToSql.ConvertWhere(filter)}").ToString();
}
/// <summary>
/// 获取TableName，并将其重新赋值
/// </summary>
protected void ReSetTableName()
{
_dbContext.TableName = TableAttribute.GetName(typeof(TEntity));
}
protected override string TableName(Type tEntityType)
{
return($"{TableAttribute.GetName(tEntityType).ToUpper()}");
}
public IMongoCollection <TEntity> GetCollectionEntity <TEntity>() where TEntity : class
{
CollectionName = TableAttribute.GetName(typeof(TEntity));
return(DataBase.GetCollection <TEntity>(CollectionName));
}
/// <summary> /// MongoDb Bson集合 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <returns></returns> private IMongoCollection <BsonDocument> GetCollectionBson <TEntity>() where TEntity : class => DataBase.GetCollection <BsonDocument>(TableAttribute.GetName(typeof(TEntity)));
/// <summary> /// Get table name from TEntity /// </summary> /// <typeparam name="TEntity"></typeparam> /// <returns></returns> public string GetTableName <TEntity>() where TEntity : class => TableAttribute.GetName(typeof(TEntity));
protected IMongoCollection <TEntity> GetCollectionEntity <TEntity>() where TEntity : class
{
TableName = TableAttribute.GetName(typeof(TEntity));
return(DataBase.GetCollection <TEntity>(TableName));
}
public static void Add <TEntity>(DbContext dbContext, TEntity entity, out Dictionary <string, object> paramsDic) where TEntity : class
{
dbContext.TableName = TableAttribute.GetName(typeof(TEntity));
paramsDic = new Dictionary <string, object>();
StringBuilder builder_front = new StringBuilder(), builder_behind = new StringBuilder();
builder_front.Append("INSERT INTO ");
builder_front.Append(dbContext.TableName);
builder_front.Append(" (");
builder_behind.Append(" VALUES (");
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//if not column mark exist,jump to next
if (ColumnIgnoreAttribute.Exist(typeof(TEntity)))
{
}
//AutoIncrease : if property is auto increase attribute skip this column.
else if (propertyInfo.GetCustomAttribute(typeof(AutoIncreaseAttribute), true) is AutoIncreaseAttribute autoIncreaseAttr)
{
}
//Column
else if (propertyInfo.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute column)
{
builder_front.Append(column.GetName(propertyInfo.Name));
builder_front.Append(",");
builder_behind.Append("@");
//multitype database support
switch (dbContext.DataBaseType)
{
case DataBaseType.SqlServer:
columnName = column.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "");
break;
case DataBaseType.MySql:
columnName = column.GetName(propertyInfo.Name).Replace("`", "");
break;
default:
//default 兼容mysql和sqlserver的系统字段
columnName = column.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "").Replace("`", "");
break;
}
builder_behind.Append(columnName);
builder_behind.Append(",");
if (!paramsDic.ContainsKey(columnName))
{
paramsDic.Add(columnName, propertyInfo.GetValue(entity));
}
}
//in the end,remove the redundant symbol of ','
if (propertyInfos.Last() == propertyInfo)
{
builder_front.Remove(builder_front.Length - 1, 1);
builder_front.Append(")");
builder_behind.Remove(builder_behind.Length - 1, 1);
builder_behind.Append(")");
}
}
//Generate SqlStatement
dbContext.SqlStatement = builder_front.Append(builder_behind.ToString()).ToString();
}
private IMongoCollection <TEntity> GetCollectionEntity()
{
return(_MongoDatabase.GetCollection <TEntity>(TableAttribute.GetName(typeof(TEntity))));
}
public override string Update <TEntity>(TEntity entity, out Expression <Func <TEntity, bool> > filter)
{
DbContext.Parameters = new Dictionary <string, object>();
DbContext.TableName = TableAttribute.GetName(typeof(TEntity));
PropertyInfo[] propertyInfos = GetPropertiesDicByType(typeof(TEntity));
//查找主键以及主键对应的值，如果用该方法更新数据，主键是必须存在的
//get property which is key
var keyProperty = propertyInfos.Where(t => t.GetCustomAttribute(typeof(KeyAttribute), true) is KeyAttribute)?.FirstOrDefault();
if (keyProperty == null)
{
throw new TableKeyNotFoundException($"table '{DbContext.TableName}' not found key column");
}
//主键的key
string keyName = keyProperty.Name;
//主键的value
var keyValue = keyProperty.GetValue(entity);
if (keyProperty.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute columnAttr1)
{
keyName = columnAttr1.GetName(keyProperty.Name);
}
//Generate Expression of update via key : t=>t.'Key'== value
ParameterExpression param = Expression.Parameter(typeof(TEntity), "t");
MemberExpression left = Expression.Property(param, keyProperty);
ConstantExpression right = Expression.Constant(keyValue);
BinaryExpression where = Expression.Equal(left, right);
filter = Expression.Lambda <Func <TEntity, bool> >(where, param);
//将主键的查询参数加到字典中
DbContext.Parameters.AddOrUpdate($"@t{keyName}", keyValue);
//开始构造赋值的sql语句
StringBuilder builder_front = new StringBuilder();
builder_front.Append("UPDATE ");
//查询语句中表的别名，例如“t”
string alias = filter.Parameters[0].Name;
builder_front.Append(alias);
builder_front.Append(" SET ");
string columnName = string.Empty;
foreach (PropertyInfo propertyInfo in propertyInfos)
{
//AutoIncrease : if property is auto increase attribute skip this column.
if (propertyInfo.GetCustomAttribute(typeof(AutoIncreaseAttribute), true) is AutoIncreaseAttribute autoIncreaseAttr)
{
}
//Column :
else if (propertyInfo.GetCustomAttribute(typeof(ColumnAttribute), true) is ColumnAttribute columnAttr)
{
builder_front.Append(columnAttr.GetName(propertyInfo.Name));
builder_front.Append("=");
builder_front.Append($"@{alias}");
columnName = columnAttr.GetName(propertyInfo.Name).Replace("[", "").Replace("]", "");
builder_front.Append(columnName);
builder_front.Append(",");
DbContext.Parameters.AddOrUpdate($"@{alias}{columnName}", propertyInfo.GetValue(entity));
}
//in the end,remove the redundant symbol of ','
if (propertyInfos.Last() == propertyInfo)
{
builder_front.Remove(builder_front.Length - 1, 1);
}
}
builder_front.Append(" FROM ");
builder_front.Append(DbContext.TableName);
builder_front.Append(" ");
builder_front.Append(alias);
//Generate SqlStatement
return(DbContext.SqlStatement = builder_front.Append($"{LambdaToSql.ConvertWhere(filter)}").ToString().TrimEnd());
}