public async Task <PageResult <T> > GetListAsync <T>(FetchParameter fetchParameter)
{
var sql = new StringBuilder();
DynamicParameters param = new DynamicParameters();
if (string.IsNullOrWhiteSpace(fetchParameter.Selectfileld))
{
sql.Append($"SELECT * FROM {Adapter.LeftEscapeChar}{TypeMaper.GetTableName<T>()}{Adapter.RightEscapeChar} ");
}
else
{
sql.Append($"SELECT {fetchParameter.Selectfileld} FROM {Adapter.LeftEscapeChar}{TypeMaper.GetTableName<T>()}{Adapter.RightEscapeChar} ");
}
if (fetchParameter.HasWhereFilters)
{
sql.Append(" WHERE ");
foreach (var item in fetchParameter.WhereFilters)
{
sql.Append($" {item.FilterName} {item.FilterType} {Adapter.DbParmChar}{item.FilterName} ");
param.Add($"{Adapter.DbParmChar}{item.FilterName}", item.FilterValue);
}
}
if (string.IsNullOrWhiteSpace(fetchParameter.GroupBy) == false)
{
sql.Append($" GROUP BY {fetchParameter.GroupBy} ");
}
if (fetchParameter.HasHavingFilters)
{
sql.Append(" HAVING ");
foreach (var item in fetchParameter.HavingFilters)
{
sql.Append($" {item.FilterName} {item.FilterType} {Adapter.DbParmChar}{item.FilterName} ");
param.Add($"{Adapter.DbParmChar}{item.FilterName}", item.FilterValue);
}
}
if (string.IsNullOrWhiteSpace(fetchParameter.OrderBy) == false)
{
sql.Append($" ORDER BY {fetchParameter.OrderBy} ");
}
if (fetchParameter.UsePage)
{
fetchParameter.FixPageIndex();
sql.Append($" LIMIT {(fetchParameter.PageIndex - 1) * fetchParameter.PageItemCount},{ fetchParameter.PageItemCount }");
}
var result = new PageResult <T>();
result.TotalCount = await GetCountAsync <T>(fetchParameter);
result.PageIndex = fetchParameter.PageIndex;
result.PageItemCount = fetchParameter.PageItemCount;
result.Data = (await DBConnection.QueryAsync <T>(sql.ToString(), param, DBTransaction, _Config.CommandTimeout)).ToList();
return(result);
}
/// <summary>
/// 删除全部数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <returns></returns>
public async Task <int> DeleteAllAsync <T>()
{
var tableName = TypeMaper.GetTableName <T>();
var sql = _SQLBuilder.BuildDelete(tableName).ToSql();
return(await ExecuteNonQueryAsync(sql, null));
}
public async Task <int> GetCountAsync <T>(FetchParameter fetchParameter)
{
var sql = _SQLBuilder.BuildSelectCount(TypeMaper.GetTableName <T>());
DynamicParameters param = new DynamicParameters();
if (fetchParameter.HasWhereFilters)
{
sql.Where();
foreach (var item in fetchParameter.WhereFilters)
{
sql.AppendSql($" {item.FilterName} {item.FilterType} {Adapter.DbParmChar}{item.FilterName} ");
param.Add($"{Adapter.DbParmChar}{item.FilterName}", item.FilterValue);
}
}
if (string.IsNullOrWhiteSpace(fetchParameter.GroupBy) == false)
{
sql.AppendSql($" GROUP BY {fetchParameter.GroupBy} ");
}
if (fetchParameter.HasHavingFilters)
{
sql.Having();
foreach (var item in fetchParameter.HavingFilters)
{
sql.AppendSql($" {item.FilterName} {item.FilterType} {Adapter.DbParmChar}{item.FilterName} ");
param.Add($"{Adapter.DbParmChar}{item.FilterName}", item.FilterValue);
}
}
return(await ExecuteScalarAsync <int>(sql.ToSql(), param));
}
public async Task <int> GetCountAsync <T>(string whereSql, dynamic param)
{
if (whereSql.TrimStart().ToLower().StartsWith("where", StringComparison.InvariantCulture))
{
whereSql = _SQLBuilder.BuildSelectCount(TypeMaper.GetTableName <T>()).AppendSql(whereSql).ToSql();
}
return(await ExecuteScalarAsync <int>(whereSql, param));
}
public async Task <T> SingleOrDefaultAsync <T>(string whereSql, dynamic param)
{
if (whereSql.TrimStart().ToLower().StartsWith("where", StringComparison.InvariantCulture))
{
whereSql = _SQLBuilder.BuildSelect(TypeMaper.GetTableName <T>(), TypeMaper.GetPropertiesName(typeof(T))).AppendSql(whereSql).ToSql();
}
return(await SqlMapper.QueryFirstOrDefaultAsync <T>(DBConnection, whereSql, param, DBTransaction, _Config.CommandTimeout));
}
public async Task <List <T> > BatchInsertAsync <T>(List <T> entities)
{
var type = typeof(T);
var sql = _SQLBuilder.BuildInsert(TypeMaper.GetTableName <T>(), TypeMaper.GetPropertiesName(type)).ToSql();
await this.ExecuteNonQueryAsync(sql, entities);
return(entities);
}
public async Task <int> DeleteByIdAsync <T>(dynamic id)
{
var keyName = TypeMaper.GetSingleKeyName <T>();
var sql = _SQLBuilder.BuildDelete(TypeMaper.GetTableName <T>()).Where().AppendColumnNameEqualsValue(keyName).ToSql();
var param = new DynamicParameters();
param.Add($"{Adapter.DbParmChar}{keyName}", id);
return(await ExecuteNonQueryAsync(sql, param));
}
public string GetCreateTableSql(Type type, string dataBaseName)
{
var result = new StringBuilder();
result.Append($"CREATE TABLE {TypeMaper.GetTableName(type)} (");
result.Append(string.Join(",", TypeMaper.GetProperties(type).Select(p => $"{LeftEscapeChar}{p.Name.ToLower()}{RightEscapeChar} {GetType(p)}")));
result.Append($")");
return(result.ToString());
}
public async Task <T> SingleOrDefaultByIdAsync <T>(dynamic PrimaryKey)
{
var keyName = TypeMaper.GetSingleKeyName <T>();
var sql = _SQLBuilder.BuildSelect(TypeMaper.GetTableName <T>(), TypeMaper.GetPropertiesName(typeof(T))).Where().AppendColumnNameEqualsValue(keyName).ToSql();
var param = new DynamicParameters();
param.Add(Adapter.DbParmChar + keyName, PrimaryKey);
return(await SqlMapper.QuerySingleOrDefaultAsync <T>(DBConnection, sql, param, DBTransaction, _Config.CommandTimeout));
}
public async Task <List <T> > GetListAsync <T>(string whereSql, dynamic param)
{
if (whereSql.TrimStart().ToLower().StartsWith("where", StringComparison.InvariantCulture))
{
whereSql = _SQLBuilder.BuildSelect(TypeMaper.GetTableName <T>(), TypeMaper.GetPropertiesName(typeof(T))).AppendSql(whereSql).ToSql();
}
IEnumerable <T> result = await SqlMapper.QueryAsync <T>(DBConnection, whereSql, param, DBTransaction, _Config.CommandTimeout, CommandType.Text);
return(result.ToList <T>());
}
public async Task <int> DeleteAsync <T>(T entity)
{
if (entity == null)
{
return(0);
}
var tableName = TypeMaper.GetTableName <T>();
var sql = _SQLBuilder.BuildDelete(tableName).Where().AppendColumnNameEqualsValue(TypeMaper.GetSingleKeyName <T>()).ToSql();
return(await ExecuteNonQueryAsync(sql.ToString(), entity));
}
public async Task <int> UpdateAsync <T>(T entity)
{
var type = typeof(T);
var keyProperties = TypeMaper.GetSingleKey <T>();
var tableName = TypeMaper.GetTableName <T>();
var allProperties = TypeMaper.GetProperties(type);
var noKeyProperties = allProperties.Except(new List <PropertyInfo>()
{
keyProperties
}).ToList();
var sql = _SQLBuilder.BuildUpdate(tableName, noKeyProperties.Select(p => p.Name).ToList()).Where().AppendColumnNameEqualsValue(keyProperties.Name).ToSql();
return(await ExecuteNonQueryAsync(sql.ToString(), entity));
}
public async Task <int> UpdateAsync <T>(T entity, string fields)
{
if (string.IsNullOrWhiteSpace(fields))
{
return(0);
}
var type = typeof(T);
var keyProperties = TypeMaper.GetSingleKey <T>();
var tableName = TypeMaper.GetTableName <T>();
var allProperties = TypeMaper.GetProperties(type);
var arrayfields = fields.ToLower().Split(',');
allProperties = allProperties.Where(p => arrayfields.Contains(p.Name.ToLower())).ToList();
var noKeyProperties = allProperties.Except(new List <PropertyInfo>()
{
keyProperties
}).ToList();
var sql = _SQLBuilder.BuildUpdate(tableName, noKeyProperties.Select(p => p.Name).ToList()).Where().AppendColumnNameEqualsValue(keyProperties.Name).ToSql();
return(await ExecuteNonQueryAsync(sql.ToString(), entity));
}
public string GetCreateColumnSql(PropertyInfo propertyInfo, Type type, string dataBaseName)
{
return($"ALTER TABLE {TypeMaper.GetTableName(type)} ADD COLUMN {propertyInfo.Name} {GetType(propertyInfo)};");
}
public string GetTableExitSql(Type type, string dataBaseName)
{
return($"SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' and name = '{TypeMaper.GetTableName(type)}'");
}
public string GetColumnExitSql(PropertyInfo propertyInfo, Type type, string dataBaseName)
{
return($"SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = '{TypeMaper.GetTableName(type)}' AND sql LIKE '%{propertyInfo.Name}%'");
}
public async Task <List <T> > GetAllAsync <T>()
{
var sql = _SQLBuilder.BuildSelect(TypeMaper.GetTableName <T>(), TypeMaper.GetPropertiesName(typeof(T))).ToSql();
return((await DBConnection.QueryAsync <T>(sql, null, DBTransaction, _Config.CommandTimeout)).ToList());
}
public async Task <int> GetCountAsync <T>()
{
var sql = _SQLBuilder.BuildSelectCount(TypeMaper.GetTableName <T>()).ToSql();
return(await ExecuteScalarAsync <int>(sql, null));
}