/// <summary>
/// 根据主键查询，主键类型自定义
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="criteria"></param>
/// <returns></returns>
public async Task <T> QueryByPk <T>(T criteria) where T : class, new()
{
if (criteria == null)
{
throw new DalException(ErrorCode.PrimaryKeyIsEmpty, "Query criteria is empty");
}
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var primaryKeys = info.Columns.FindAll(p => p.Attribute.PrimaryKey == true);
// 再一次校验是否是所有的主键都进行了设置
foreach (var pk in primaryKeys)
{
if (pk.ColumnProperty.GetValue(criteria) == null)
{
throw new DalException(ErrorCode.PrimaryKeyIsEmpty, $"Query primary key is empty : {pk.Attribute.Name}");
}
}
var list = await Query <T>(criteria);
if (list == null || list.Count == 0)
{
return(null);
}
return(list[0]);
}
/// <summary>
/// 获取Where条件只有主键的查询条件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="criteria"></param>
/// <returns></returns>
private static (string, List <DatabaseParameter>) GeneratePrimaryKeyClause <T>(T criteria)
{
var parameters = new List <DatabaseParameter>();
var sb = new StringBuilder(" WHERE ");
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var primaryKeys = info.Columns.FindAll(p => p.Attribute.PrimaryKey == true);
foreach (var pk in primaryKeys)
{
sb.Append($"`{pk.Attribute.Name}` = ?");
sb.Append(" AND ");
// 主键不能为空
var value = pk.ColumnProperty.GetValue(criteria);
if (value == null)
{
throw new DalException(ErrorCode.InvalidSqlParameters,
$"Primary key can not be empty : {pk.Attribute.Name}");
}
;
parameters.Add(new DatabaseParameter()
{
Name = pk.Attribute.Name,
Value = value
});
}
sb.Remove(sb.Length - 4, 4);
sb.Append(";");
return(sb.ToString(), parameters);
}
private T BuildCriteria <T, PKType>(PKType value) where T : class, new()
{
if (value == null)
{
throw new DalException(ErrorCode.PrimaryKeyIsEmpty, "Primary key is empty");
}
var info = EntityMetaDataMapper.GetInstance().Get <T>();
// 检查主键类型，必须为字符串
var identity = info.Columns.Find(p => p.Attribute.PrimaryKey == true);
var property = identity.ColumnProperty.PropertyType;
if (property == typeof(PKType) ||
(property.IsGenericType &&
property.GetGenericTypeDefinition() == typeof(Nullable <>) &&
property.GetGenericArguments()[0] == typeof(PKType)))
{
var criteria = new T();
identity.ColumnProperty.SetValue(criteria, value);
return(criteria);
}
throw new DalException(ErrorCode.PrimaryKeyIsEmpty,
$"Primary key type mismatch, source type : {typeof(PKType)}, " +
$"target type : {identity.ColumnProperty.PropertyType}");
}
public async Task <List <T> > QueryLike <T>(IDatabaseParameterLink link, int?recordCount = null) where T : class, new()
{
var mapper = EntityMetaDataMapper.GetInstance();
// 获取组装好的sql和参数
(string sql, List <DatabaseParameter> parameters) =
SqlBuilderFactory.GetInstance <T>().BuildSelect <T>(link, recordCount);
var dataSet = await DalClientFactory.GetClient(GetLogicDatabaseName <T>()).Query(sql, parameters);
return(mapper.Convert <T>(dataSet));
}
public (string, List <DatabaseParameter>) BuildSelect <T>(T criteria, int?recordCount) where T : new()
{
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var sb = new StringBuilder(GetPrimarySql <T>());
if (criteria == null)
{
sb.Append(";");
return(sb.ToString(), null);
}
var parameters = new List <DatabaseParameter>();
var isFirstCondition = true;
foreach (var column in info.Columns)
{
var value = column.ColumnProperty.GetValue(criteria);
if (value == null)
{
continue;
}
if (isFirstCondition == true)
{
sb.Append(" WHERE ");
isFirstCondition = false;
}
else
{
sb.Append(" AND ");
}
parameters.Add(new DatabaseParameter
{
Name = column.Attribute.Name,
Value = value
});
sb.Append($"`{column.Attribute.Name}` = ?");
}
if (recordCount != null && recordCount > 0)
{
sb.Append($" limit {recordCount};");
}
else
{
sb.Append(";");
}
return(sb.ToString(), parameters);
}
/// <summary>
/// 根据泛型T，组装该类型的查询语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public static ISqlBuilder GetInstance <T>()
{
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var dalClient = DalClientFactory.GetClient(info.DatabaseName);
if (!sqlBuilders.ContainsKey(dalClient.DatabaseType))
{
throw new DalException(ErrorCode.SqlBuilderNotFound, $"There's no sql builder for {dalClient.DatabaseType}");
}
return(sqlBuilders[dalClient.DatabaseType]);
}
private string GetLogicDatabaseName <T>() where T : class, new()
{
var entityInfo = EntityMetaDataMapper.GetInstance().Get <T>();
var logicName = databaseName;
if (string.IsNullOrWhiteSpace(logicName))
{
logicName = entityInfo.DatabaseName;
}
return(logicName);
}
public (string, List <DatabaseParameter>) BuildDelete <T>(T criteria)
{
if (criteria == null)
{
throw new DalException(ErrorCode.InvalidSqlParameters, "Invalid sql parameters, parameter is null");
}
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var sb = new StringBuilder($"DELETE FROM {info.TableName} ");
(string sql, List <DatabaseParameter> pkParameters) = GeneratePrimaryKeyClause(criteria);
sb.Append(sql);
return(sb.ToString(), pkParameters);
}
private string GetPrimarySql <T>()
{
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var sb = new StringBuilder("SELECT ", 200);
foreach (var column in info.Columns)
{
sb.Append($"`{column.Attribute.Name}`, ");
}
// 删除最后一个,
sb.Remove(sb.Length - 2, 2);
sb.Append($" FROM `{info.TableName}` ");
return(sb.ToString());
}
public (string, List <DatabaseParameter>) BuildUpdate <T>(T criteria)
{
if (criteria == null)
{
throw new DalException(ErrorCode.InvalidSqlParameters, "Invalid sql parameters, parameter is null");
}
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var sb = new StringBuilder($"UPDATE {info.TableName} SET ");
var parameters = new List <DatabaseParameter>();
foreach (var column in info.Columns)
{
// 主键不参与更新
if (column.Attribute.PrimaryKey == true)
{
continue;
}
var value = column.ColumnProperty.GetValue(criteria);
if (value == null)
{
continue;
}
sb.Append($"`{column.Attribute.Name}` = ?, ");
parameters.Add(new DatabaseParameter()
{
Name = column.Attribute.Name,
Value = value
});
}
;
// 删除最后一个,
sb.Remove(sb.Length - 2, 2);
(string sql, List <DatabaseParameter> pkParameters) = GeneratePrimaryKeyClause(criteria);
sb.Append(sql);
sb.Append(";");
parameters.AddRange(pkParameters);
return(sb.ToString(), parameters);
}
/// <summary>
/// 条件查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="criteria"></param>
/// <returns></returns>
public async Task <List <T> > Query <T>(T criteria, int?recordCount = null) where T : class, new()
{
var mapper = EntityMetaDataMapper.GetInstance();
// 获取组装好的sql和参数
(string sql, List <DatabaseParameter> parameters) =
SqlBuilderFactory.GetInstance <T>().BuildSelect(criteria, recordCount);
var logicName = databaseName;
if (string.IsNullOrWhiteSpace(logicName))
{
logicName = mapper.Get <T>().DatabaseName;
}
var client = DalClientFactory.GetClient(logicName);
var dataSet = await client.Query(sql, parameters);
return(mapper.Convert <T>(dataSet));
}
public async Task <int> Insert <T>(T entity) where T : class, new()
{
// 获取组装好的sql和参数
(string sql, List <DatabaseParameter> parameters) =
SqlBuilderFactory.GetInstance <T>().BuildInsert(entity);
var client = DalClientFactory.GetClient(GetLogicDatabaseName <T>());
// 将主键返回给实体
var hints = DalHints.Create();
// 如果当前实例含有自增主键，且自增主键的实体不为空，那么需要数据库返回自增住居
var identity = EntityMetaDataMapper.GetInstance().Get <T>().Columns.Find(p => p.Attribute.Identity);
if (identity != null && identity.Attribute.Identity && identity.ColumnProperty.GetValue(entity) == null)
{
hints.Add(DalHint.SetIdentity);
}
var result = await client.Execute(sql, parameters, hints);
// 如果数据库返回了主键，那么将主键设置回实体
// 暂时先这么写
if (result.Identity > 0)
{
if (identity.ColumnProperty.PropertyType == typeof(int?))
{
int.TryParse(result.Identity.ToString(), out var value);
identity.ColumnProperty.SetValue(entity, value);
}
else
{
long.TryParse(result.Identity.ToString(), out var value);
identity.ColumnProperty.SetValue(entity, value);
}
}
return(result.ReturnCode);
}
public (string, List <DatabaseParameter>) BuildInsert <T>(T criteria)
{
if (criteria == null)
{
throw new DalException(ErrorCode.InvalidSqlParameters, "Invalid sql parameters, parameter is null");
}
var info = EntityMetaDataMapper.GetInstance().Get <T>();
var sb = new StringBuilder($"INSERT INTO {info.TableName} (");
var questionMarks = new List <string>();
var parameters = new List <DatabaseParameter>();
foreach (var column in info.Columns)
{
var value = column.ColumnProperty.GetValue(criteria);
if (value == null)
{
continue;
}
sb.Append($"`{column.Attribute.Name}`, ");
questionMarks.Add("?");
parameters.Add(new DatabaseParameter()
{
Name = column.Attribute.Name,
Value = value
});
}
;
// 删除最后一个,
sb.Remove(sb.Length - 2, 2);
sb.Append($") VALUES ({string.Join(",", questionMarks)});");
return(sb.ToString(), parameters);
}