/// <summary>
/// 生成insert语句
/// </summary>
public static string GenerateInsertql <T>() where T : BaseEntity
{
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
StringBuilder sb = new StringBuilder();
sb.Append($"INSERT INTO {tableMapper.TableName} ");
sb.Append("(");
StringBuilder cols = new StringBuilder();
var columns = tableMapper.Columns.Where(p => p.Value.IsAuto == false).Select(p => p.Value).ToList();
foreach (var item in columns)
{
cols.Append($"{item.ColumnName},");
}
sb.Append(cols.ToString().Trim(','));
sb.Append(") ");
sb.Append("VALUES (");
cols = new StringBuilder();
foreach (var item in columns)
{
cols.Append($"@{item.PropertyName},");
}
sb.Append(cols.ToString().Trim(','));
sb.Append(");");
return(sb.ToString());
}
/// <summary>
/// 通过主键值查询单个实体
/// </summary>
public static async Task <T> SingleByIdAsync <T>(this DbConnection connection, object idValue) where T : BaseEntity
{
if (idValue == null || string.IsNullOrWhiteSpace(idValue.ToString()))
{
throw new ArgumentNullException(nameof(idValue));
}
TableMapper mapper = MappingCachePool.GetOrAdd <T>();
ColumnMapper column = mapper.KeyColumn;
if (column == null)
{
throw new Exception($"The {mapper.TypeName} class does not define a primary key.");
}
else
{
string sql = $"select * from {mapper.TableName} where {column.ColumnName} = @p;";
object dbValue = idValue;
if (column.ValueConvert != null)
{
dbValue = column.ValueConvert.Write(idValue);
}
List <T> list = await connection.SelectAsync <T>(sql, new { p = dbValue });
if (list.Count == 0)
{
return(null);
}
else
{
return(list[0]);
}
}
}
public static TableMapper GetOrAdd <T>() where T : BaseEntity
{
return(Cache.GetOrAdd(typeof(T), t =>
{
TableMapper mapper = ReflectionHelper.ToMapper <T>();
return mapper;
}));
}
public static string CreateTableSQL <T>() where T : BaseEntity
{
TableMapper mapper = MappingCachePool.GetOrAdd <T>();
StringBuilder sb = new StringBuilder();
sb.AppendLine($"CREATE TABLE {mapper.TableName}(");
sb.AppendLine(CreateColumns <T>());
sb.AppendLine(");");
return(sb.ToString());
}
public static string GenerateDeleteSql <T>() where T : BaseEntity
{
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
ColumnMapper keyColumn = tableMapper.KeyColumn;
if (keyColumn == null)
{
throw new Exception($"{tableMapper.TypeName} class does not define a primary key");
}
return($"delete from {tableMapper.TableName} where {keyColumn.ColumnName} = @{keyColumn.PropertyName};");
}
public static string CreateIndexSQL <T>() where T : BaseEntity
{
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
StringBuilder sb = new StringBuilder();
var columns = tableMapper.Columns.Where(p => p.Value.IsIndex == true).ToList();
foreach (var item in columns)
{
var column = item.Value;
sb.AppendLine($"CREATE INDEX idx_{tableMapper.TableName}_{column.ColumnName} on {tableMapper.TableName}({column.ColumnName});");
}
return(sb.ToString());
}
/// <summary>
/// 使用实体类为命令追加参数
/// </summary>
public static void AppendEntityParameters <T>(this DbCommand command, T obj) where T : BaseEntity
{
if (command == null)
{
throw new ArgumentNullException(nameof(command));
}
if (obj == null)
{
throw new ArgumentNullException(nameof(obj));
}
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
var columns = tableMapper.Columns;
foreach (var item in columns)
{
DbParameter parameter = command.CreateParameter();
parameter.ParameterName = "@" + item.Value.PropertyName;
object value = item.Value.GetPropertyValue(obj);
if (value == null)
{
parameter.Value = DBNull.Value;
}
else
{
BaseValueConvert valueConvert = item.Value.ValueConvert as BaseValueConvert;
if (valueConvert != null)
{
parameter.Value = valueConvert.Write(value);
}
else
{
if (item.Value.IsEnum)
{
parameter.Value = Convert.ToInt16(value);
}
else if (item.Value.IsGuidString)
{
parameter.Value = Guid.Parse(value.ToString());
}
else
{
parameter.Value = value;
}
}
}
command.Parameters.Add(parameter);
}
}
/// <summary>
/// 删除表中所有数据
/// </summary>
public static async Task <int> DeleteAllAsync <T>(this DbConnection connection, DbTransaction transaction = null) where T : BaseEntity
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
using (DbCommand command = connection.CreateCommand())
{
command.Transaction = transaction;
command.CommandText = $"DELETE FROM {tableMapper.TableName};";
command.Parameters.Clear();
return(await command.ExecuteNonQueryAsync());
}
}
/// <summary>
/// 获取实体表内的记录总数
/// </summary>
public static async Task <int> CountAsync <T>(this DbConnection connection) where T : BaseEntity
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
using (DbCommand command = connection.CreateCommand())
{
string sql = $"select count(*) from {tableMapper.TableName};";
command.CommandText = sql;
object result = await command.ExecuteScalarAsync();
return(Convert.ToInt32(result));
}
}
/// <summary>
/// 将DbDataReader转换为实体
/// </summary>
public static T ToEntity <T>(this DbDataReader reader) where T : BaseEntity
{
TableMapper mapper = MappingCachePool.GetOrAdd <T>();
T obj = Activator.CreateInstance <T>();
foreach (var item in mapper.Columns)
{
ColumnMapper column = item.Value;
PropertyInfo property = column.PropertyInfo;
object value = reader[column.ColumnName];
if (value == DBNull.Value)
{
property.SetValue(obj, null);
}
else
{
if (column.ValueConvert != null)
{
property.SetValue(obj, column.ValueConvert.Read(value));
}
else
{
if (column.IsGuidString)
{
Guid guid = (Guid)value;
property.SetValue(obj, guid.ToString());
}
else if (column.IsEnum)
{
property.SetValue(obj, Enum.ToObject(property.PropertyType, Convert.ToInt32(value)), null);
}
else
{
property.SetValue(obj, value);
}
}
}
}
return(obj);
}
private static string CreateColumns <T>() where T : BaseEntity
{
StringBuilder sb = new StringBuilder();
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
// 生成主键
if (tableMapper.KeyColumn != null)
{
sb.AppendLine($"{tableMapper.KeyColumn.ColumnName} {GetDbType(tableMapper.KeyColumn)} PRIMARY KEY,");
}
// 生成其他列
var columns = tableMapper.Columns.Where(p => p.Value.IsPrimarykey == false).ToList();
foreach (var item in columns)
{
var column = item.Value;
sb.AppendLine(CreateColumn(column));
}
string sql = sb.ToString().Trim().Trim(',');
return(sql);
}
/// <summary>
/// 获取表中的所有记录
/// </summary>
public static async Task <List <T> > GetAllAsync <T>(this DbConnection connection) where T : BaseEntity
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
List <T> objList = new List <T>();
TableMapper mapper = MappingCachePool.GetOrAdd <T>();
using (DbCommand command = connection.CreateCommand())
{
command.CommandText = $"select * from {mapper.TableName};";
using (DbDataReader reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
T obj = reader.ToEntity <T>();
objList.Add(obj);
}
}
}
return(objList);
}
/// <summary>
/// 通过ID值删除一个对象
/// </summary>
public static async Task <int> DeleteByIdAsync <T>(this DbConnection connection, object idValue, DbTransaction transaction = null) where T : BaseEntity
{
if (idValue == null || string.IsNullOrWhiteSpace(idValue.ToString()))
{
throw new ArgumentNullException(nameof(idValue));
}
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
ColumnMapper keyColumn = tableMapper.KeyColumn;
if (tableMapper.KeyColumn == null)
{
throw new Exception(tableMapper.TypeName + " does not define a primary key");
}
object dbValue = idValue;
if (keyColumn.ValueConvert != null)
{
dbValue = keyColumn.ValueConvert.Write(idValue);
}
string sql = $"delete from {tableMapper.TableName} where {keyColumn.ColumnName} = @p;";
return(await ExecuteNonQueryAsync(connection, sql, new { p = dbValue }, transaction));
}
/// <summary>
/// 从数据库中删除一个对象
/// </summary>
public static async Task <int> DeleteAsync <T>(this DbConnection connection, T obj, DbTransaction transaction = null) where T : BaseEntity
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (obj == null)
{
throw new ArgumentNullException(nameof(obj));
}
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
using (DbCommand command = connection.CreateCommand())
{
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandText = CodeSegmentHelper.GenerateDeleteSql <T>();
command.Parameters.Clear();
command.AppendEntityParameters <T>(obj);
return(await command.ExecuteNonQueryAsync());
}
}
public static string GenerateUpdateSql <T>() where T : BaseEntity
{
TableMapper tableMapper = MappingCachePool.GetOrAdd <T>();
ColumnMapper keyColumn = tableMapper.KeyColumn;
if (keyColumn == null)
{
throw new Exception($"{tableMapper.TypeName} class does not define a primary key");
}
StringBuilder sb = new StringBuilder();
sb.Append($"UPDATE {tableMapper.TableName} SET ");
var columns = tableMapper.Columns.Where(p => p.Value.IsPrimarykey == false).ToList();
StringBuilder cols = new StringBuilder();
foreach (var item in columns)
{
cols.Append($"{item.Value.ColumnName}=@{item.Value.PropertyName},");
}
sb.Append(cols.ToString().Trim(','));
sb.Append(" WHERE ");
sb.Append($"{keyColumn.ColumnName} = @{keyColumn.PropertyName};");
return(sb.ToString());
}
public static TableMapper ToMapper <T>() where T : BaseEntity
{
Type type = typeof(T);
TableAttribute tableAttribute = type.GetCustomAttribute <TableAttribute>();
string tableName;
if (tableAttribute != null && !string.IsNullOrWhiteSpace(tableAttribute.Name))
{
tableName = tableAttribute.Name.Trim();
}
else
{
tableName = GetName(type.Name);
}
TableMapper tableMapper = new TableMapper()
{
TableName = tableName, Type = type, TypeName = type.Name
};
List <PropertyInfo> pList = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList();
foreach (PropertyInfo property in pList)
{
// 检查 IgnoreAttribute
IgnoreAttribute ignore = property.GetCustomAttribute <IgnoreAttribute>();
if (ignore != null)
{
continue;
}
// 检查 ColumnAttribute
bool isKey;
bool isAuto = false;
KeyAttribute key = property.GetCustomAttribute <KeyAttribute>();
if (key != null)
{
isKey = true;
isAuto = key.IsAuto;
}
else if (string.Equals(property.Name, "id", StringComparison.OrdinalIgnoreCase))
{
// 当属性名为ID，但又未指定Key特性时，按以下规则判断，如果是整数，则为自动增量，否则不是自动增量。
string propertyTypeName = property.PropertyType.FullName;
if (propertyTypeName == "System.Int32" || propertyTypeName == "System.UInt32" || propertyTypeName == "System.Int64" || propertyTypeName == "System.UInt64")
{
isAuto = true;
}
else
{
isAuto = false;
}
isKey = true;
}
else
{
isKey = false;
isAuto = false;
}
ColumnAttribute columnAttribute = property.GetCustomAttribute <ColumnAttribute>();
IndexAttribute indexAttribute = property.GetCustomAttribute <IndexAttribute>();
string columnName = "";
BaseValueConvert valueConvert = null;
if (columnAttribute != null)
{
// 获取列名
if (!string.IsNullOrWhiteSpace(columnAttribute.Name))
{
columnName = columnAttribute.Name.Trim();
}
else
{
columnName = GetName(property.Name);
}
// 获取 ValueConvert
if (columnAttribute.Convert != null)
{
Type convertType = columnAttribute.Convert;
if (convertType.IsAbstract)
{
throw new Exception($"{property.Name} value convert cannot be an abstract class.");
}
if (!convertType.IsSubclassOf(typeof(BaseValueConvert)))
{
string name = typeof(BaseValueConvert).Name;
throw new Exception($"{property.Name} value convert must inherit {name}.");
}
valueConvert = Activator.CreateInstance(convertType) as BaseValueConvert;
}
}
else
{
columnName = GetName(property.Name);
}
// 创建映射实例
ColumnMapper columnMapper = new ColumnMapper()
{
ColumnName = columnName,
IsPrimarykey = isKey,
PropertyInfo = property,
PropertyName = property.Name,
ValueConvert = valueConvert,
IsAuto = isAuto,
IsEnum = property.PropertyType.IsEnum,
IsGuidString = property.PropertyType == typeof(string) && columnName.ToLower().Contains("id"),
IsCanNull = IsNullable(property.PropertyType),
IsIndex = indexAttribute != null,
DbType = (columnAttribute != null && !string.IsNullOrWhiteSpace(columnAttribute.DbType)) ? columnAttribute.DbType : "",
DbDefaultValue = (columnAttribute != null && !string.IsNullOrWhiteSpace(columnAttribute.DbDefaultValue)) ? columnAttribute.DbDefaultValue : ""
};
tableMapper.Add(columnMapper);
}
return(tableMapper);
}