public int Count <T>(Expression <Func <T, bool> > whereBy) where T : class, IViewer, new()
{
try
{
Contract.Requires(whereBy != null);
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append("SELECT COUNT(*) FROM " + attr.Name);
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
sql.Append(" WHERE " + condition.Condition);
}
object count;
if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
{
count = DataAccess.ExecuteScalar(sql.ToString(), condition.SqlArguments.ToArray());
}
else
{
count = DataAccess.ExecuteScalar(sql.ToString());
}
return(Convert.ToInt32(count));
}
catch (Exception ex)
{
_log.Error(ex, new LogInfo
{
MethodInstance = MethodBase.GetCurrentMethod(),
ThreadInstance = Thread.CurrentThread
});
throw;
}
}
public List <T> Query <T>(IPager pager, Expression <Func <T, bool> > whereBy, string orderBy = null)
where T : class, IViewer, new()
{
try
{
Contract.Requires(pager != null);
Contract.Requires(whereBy != null);
var list = new List <T>();
var attr = GetTableAttr <T>();
// Generate WhereBy Clause
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
// Generate OrderBy Clause
var strOrderBy = !string.IsNullOrEmpty(attr.Sort) ? attr.Sort : attr.Key;
if (!string.IsNullOrEmpty(orderBy))
{
strOrderBy = orderBy;
}
// Get TotalCount First
var countSql = string.Format("SELECT COUNT({1}) AS TotalCount FROM {0} WHERE {2}", attr.Name, attr.Key,
condition.Condition);
DataSet ds;
if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
{
ds = DataAccess.ExecuteDataset(countSql, condition.SqlArguments.ToArray());
}
else
{
ds = DataAccess.ExecuteDataset(countSql);
}
pager.SetTotalCount((int)ds.Tables[0].Rows[0]["TotalCount"]);
// Build Sql and Execute
var innerSql = string.Format("(SELECT ROW_NUMBER() OVER(ORDER BY {1}) AS RowNo, * FROM {0} WHERE {2})",
attr.Name, strOrderBy, condition.Condition);
string sql =
$"SELECT * FROM {innerSql} AS t WHERE t.RowNo BETWEEN {pager.CurrentPage * pager.PagingSize + 1} AND {(pager.CurrentPage + 1) * pager.PagingSize}";
//sql += string.Format("SELECT COUNT({1}) AS TotalCount FROM {0} WHERE {2}", attr.Name, attr.Key, condition.Condition);
if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
{
ds = DataAccess.ExecuteDataset(sql, condition.SqlArguments.ToArray());
}
else
{
ds = DataAccess.ExecuteDataset(sql);
}
var dt = ds.Tables[0];
if (ds.Tables[0].Rows.Count > 0)
{
using (var reader = dt.CreateDataReader())
{
list = reader.DataReaderMapTo <T>().ToList();
}
}
//pager.SetTotalCount((int)ds.Tables[1].Rows[0]["TotalCount"]);
return(list);
}
catch (Exception ex)
{
_log.Error(ex, new LogInfo
{
MethodInstance = MethodBase.GetCurrentMethod(),
ThreadInstance = Thread.CurrentThread
});
throw;
}
}
public List <T> Query <T>(Expression <Func <T, bool> > whereBy) where T : class, IViewer, new()
{
try
{
Contract.Requires(whereBy != null);
var list = new List <T>();
var attr = GetTableAttr <T>();
var sql = new StringBuilder();
sql.Append("SELECT * FROM " + attr.Name);
var condition = new ConditionBuilder();
condition.Build(whereBy.Body);
if (!string.IsNullOrEmpty(condition.Condition))
{
//var where = string.Format(condition.Condition, condition.Arguments);
sql.Append(" WHERE " + condition.Condition);
}
if (!string.IsNullOrEmpty(attr.Sort))
{
sql.Append(" ORDER BY " + attr.Sort);
}
DataSet ds;
if (condition.SqlArguments != null && condition.SqlArguments.Count > 0)
{
ds = DataAccess.ExecuteDataset(sql.ToString(), condition.SqlArguments.ToArray());
}
else
{
ds = DataAccess.ExecuteDataset(sql.ToString());
}
var dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
using (var reader = dt.CreateDataReader())
{
list = reader.DataReaderMapTo <T>().ToList();
}
}
return(list);
}
catch (Exception ex)
{
_log.Error(ex, new LogInfo
{
MethodInstance = MethodBase.GetCurrentMethod(),
ThreadInstance = Thread.CurrentThread
});
throw;
}
}