public List <T> ToList()
{
Type type = typeof(T);
List <PropertyInfo> propertyInfoList = StoreBase.GetPropertyInfoList(type);
string tableName = StoreBase.GetTableName(type);
string columnJoinString = StoreBase.GetColumnJoinString(type);
Sql sql = ExpressionFactory.ToWhereSql(_predicate);
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append($"SELECT {columnJoinString} FROM {tableName} WITH(NOLOCK)");
if (!string.IsNullOrWhiteSpace(sql.CommandText))
{
sqlStringBuilder.Append($" WHERE {sql.CommandText}");
}
if (!string.IsNullOrWhiteSpace(_orderField) && !string.IsNullOrWhiteSpace(_orderBy))
{
sqlStringBuilder.Append($" ORDER BY {_orderField} {_orderBy}");
}
else
{
sqlStringBuilder.Append($" ORDER BY id");
}
sqlStringBuilder.Append($" OFFSET 0 ROWS FETCH NEXT 9999999 ROWS ONLY;");
List <T> list = _dbSQLHelper.ExecuteList <T>(_consolePrintSql, propertyInfoList, sqlStringBuilder.ToString(), _dbSQLHelper.Convert(sql.Parameters));
return(list);
}
public T FirstOrDefault()
{
Type type = typeof(T);
List <PropertyInfo> propertyInfoList = StoreBase.GetPropertyInfoList(type);
string tableName = StoreBase.GetTableName(type);
string columnJoinString = StoreBase.GetColumnJoinString(type);
Sql sql = ExpressionFactory.ToWhereSql(_predicate);
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append($"SELECT {columnJoinString} FROM {tableName}");
if (!string.IsNullOrWhiteSpace(sql.CommandText))
{
sqlStringBuilder.Append($" WHERE {sql.CommandText}");
}
if (!string.IsNullOrWhiteSpace(_orderField) && !string.IsNullOrWhiteSpace(_orderBy))
{
sqlStringBuilder.Append($" ORDER BY {_orderField} {_orderBy}");
}
sqlStringBuilder.Append($" LIMIT 0,1;");
T instance = _dbSQLHelper.ExecuteList <T>(_consolePrintSql, propertyInfoList, sqlStringBuilder.ToString(), _dbSQLHelper.Convert(sql.Parameters)).FirstOrDefault();
return(instance);
}
public List <T> ToList(int pageIndex, int pageSize)
{
Type type = typeof(T);
string tableName = StoreBase.GetTableName(type);
string columnJoinString = StoreBase.GetColumnJoinString(type);
List <PropertyInfo> propertyInfoList = StoreBase.GetPropertyInfoList(type);
int pageStart = (pageIndex - 1) * pageSize;
Sql sql = ExpressionFactory.ToWhereSql(_predicate);
StringBuilder sqlStringBuilder = new StringBuilder();
sqlStringBuilder.Append($"SELECT {columnJoinString} FROM {tableName}");
if (!string.IsNullOrWhiteSpace(sql.CommandText))
{
sqlStringBuilder.Append($" WHERE {sql.CommandText}");
}
if (!string.IsNullOrWhiteSpace(_orderField) && !string.IsNullOrWhiteSpace(_orderBy))
{
sqlStringBuilder.Append($" ORDER BY {_orderField} {_orderBy}");
}
sqlStringBuilder.Append($" LIMIT {pageStart},{pageSize};");
List <T> list = _dbSQLHelper.ExecuteList <T>(_consolePrintSql, propertyInfoList, sqlStringBuilder.ToString(), _dbSQLHelper.Convert(sql.Parameters));
return(list);
}
public T SingleOrDefault()
{
Type type = typeof(T);
List <PropertyInfo> propertyInfoList = StoreBase.GetPropertyInfoList(type);
string tableName = StoreBase.GetTableName(type);
string columnJoinString = StoreBase.GetColumnJoinString(type);
Sql sql = ExpressionFactory.ToWhereSql(_predicate);
StringBuilder sqlStringBuilder = new StringBuilder($"SELECT {columnJoinString} FROM {tableName}");
StringBuilder pageStringBuilder = new StringBuilder($"SELECT COUNT(*) FROM {tableName}");
if (!string.IsNullOrWhiteSpace(sql.CommandText))
{
sqlStringBuilder.Append($" WHERE {sql.CommandText}");
pageStringBuilder.Append($" WHERE {sql.CommandText}");
}
pageStringBuilder.Append(";");
var parameters = _dbSQLHelper.Convert(sql.Parameters);
int total = Convert.ToInt32(_dbSQLHelper.ExecuteScalar(_consolePrintSql, pageStringBuilder.ToString(), parameters));
if (total > 1)
{
throw new SingleOrDefaultException();
}
sqlStringBuilder.Append($" LIMIT 0,1;");
T instance = _dbSQLHelper.ExecuteList <T>(_consolePrintSql, propertyInfoList, sqlStringBuilder.ToString(), parameters).FirstOrDefault();
return(instance);
}
public List <T> ToList(int pageIndex, int pageSize, out int total, out int totalPage)
{
Type type = typeof(T);
List <PropertyInfo> propertyInfoList = StoreBase.GetPropertyInfoList(type);
string tableName = StoreBase.GetTableName(type);
string columnJoinString = StoreBase.GetColumnJoinString(type);
Sql sql = ExpressionFactory.ToWhereSql(_predicate);
int pageStart = (pageIndex - 1) * pageSize;
StringBuilder sqlStringBuilder = new StringBuilder($"SELECT {columnJoinString} FROM {tableName} WITH(NOLOCK)");
StringBuilder pageStringBuilder = new StringBuilder($"SELECT COUNT(*) FROM {tableName} WITH(NOLOCK)");
if (!string.IsNullOrWhiteSpace(sql.CommandText))
{
sqlStringBuilder.Append($" WHERE {sql.CommandText}");
pageStringBuilder.Append($" WHERE {sql.CommandText}");
}
pageStringBuilder.Append(";");
var parameters = _dbSQLHelper.Convert(sql.Parameters);
total = Convert.ToInt32(_dbSQLHelper.ExecuteScalar(_consolePrintSql, pageStringBuilder.ToString(), parameters));
totalPage = (total % pageSize == 0) ? (total / pageSize) : (total / pageSize + 1);
if (!string.IsNullOrWhiteSpace(_orderField) && !string.IsNullOrWhiteSpace(_orderBy))
{
sqlStringBuilder.Append($" ORDER BY {_orderField} {_orderBy}");
}
else
{
sqlStringBuilder.Append($" ORDER BY id");
}
sqlStringBuilder.Append($" OFFSET {pageStart} ROWS FETCH NEXT {pageSize} ROWS ONLY;");
List <T> list = _dbSQLHelper.ExecuteList <T>(_consolePrintSql, propertyInfoList, sqlStringBuilder.ToString(), parameters);
return(list);
}
