public void GetListTest1()
{
DB.DeleteAll <ObjectX>();
for (int i = 0; i < 100; i++)
{
DB.Insert(new ObjectX()
{
ObjectXId = Guid.NewGuid().ToString(), Name = i.ToString(), Money = i
});
}
FetchParameter parameter = new FetchParameter();
//parameter.WhereFilters.Add(new FetchFilter() { FilterName = "Money", FilterType = ">", FilterValue = "50" });
parameter.PageIndex = 0;
parameter.PageItemCount = 15;
var result = DB.GetList <ObjectX>(parameter);
Assert.IsTrue(result.TotalCount == 100);
Assert.IsTrue(result.Data.Count == 15);
Assert.IsNotNull(result.Data);
parameter = new FetchParameter();
//parameter.WhereFilters.Add(new FetchFilter() { FilterName = "Money", FilterType = ">", FilterValue = "50" });
parameter.PageIndex = 1;
parameter.PageItemCount = 15;
result = DB.GetList <ObjectX>(parameter);
Assert.IsTrue(result.TotalCount == 100);
Assert.IsTrue(result.Data.Count == 15);
}
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 <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);
}
public void GetCountTest2()
{
DB.DeleteAll <ObjectX>();
DB.Insert(new ObjectX()
{
ObjectXId = Guid.NewGuid().ToString(), Name = "lover", NulldateTime = DateTime.Now, NullMoney = 45.23M
});
DB.Insert(new ObjectX()
{
ObjectXId = Guid.NewGuid().ToString(), Name = "lover", NulldateTime = DateTime.Now, NullMoney = 45M
});
DB.Insert(new ObjectX()
{
ObjectXId = Guid.NewGuid().ToString(), NulldateTime = DateTime.Now, NullMoney = 44.2M
});
FetchParameter parameter = new FetchParameter();
parameter.WhereFilters.Add(new FetchFilter()
{
FilterName = "name", FilterType = "=", FilterValue = "lover"
});
Assert.IsTrue(DB.GetCount <ObjectX>(parameter) == 2);
}
public PageResult <T> GetList <T>(FetchParameter fetchParameter) => GetListAsync <T>(fetchParameter).ConfigureAwait(false).GetAwaiter().GetResult();
public int GetCount <T>(FetchParameter fetchParameter) => GetCountAsync <T>(fetchParameter).ConfigureAwait(false).GetAwaiter().GetResult();
