/// <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 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());
}