public SQLPage <T> Query(int pageSize, int currentPage, SQLCondition condition = null, IEnumerable <DbParameter> param = null, string sql = null, string sqlTotal = null)
{
var sqlPage = SQLEnityResolve.Get.ConvertPage <T>(this,
_sqlExecute.Query(GetTableName, currentPage, pageSize, condition, param, sql, sqlTotal));
sqlPage.IsNext = sqlPage.Pagination > currentPage;
return(sqlPage);
}
public DataTable Query(string tblName, SQLCondition condition,
IEnumerable <DbParameter> param = null,
string sqlStr = null)
{
if (string.IsNullOrEmpty(sqlStr))
{
sqlStr = "select * from [{0}]";
}
var sql = string.Format(sqlStr, tblName);
if (condition != null)
{
sql = string.Format("{0} {1}", sql, condition.VirtualExpression);
}
return(Query(sql, param).Tables[0]);
}
public DataSet Query(string tblName, int current, int page,
SQLCondition condition,
IEnumerable <DbParameter> param = null,
string sqlStr = null,
string sqlTotal = null)
{
if (string.IsNullOrEmpty(sqlStr))
{
sqlStr = "select* from (select row_number()over(order by tempColumn)rownumber, * from(select top {0} tempColumn = 0, * from {1})a )b where rownumber > {2}";
}
if (string.IsNullOrEmpty(sqlTotal))
{
sqlTotal = "select count(0) as total,case when (count(0)%{0}) = 0 then (count(0)/{0}) else (count(0)/{0})+1 end as rows from {1}";
}
var exp = string.Empty;
var order = string.Empty;
if (condition != null)
{
exp = condition.VirtualExpression.ToLower();
if (exp.IndexOf("order") != -1)
{
order = exp.Substring(exp.IndexOf("order"));
exp = exp.Replace(order, string.Empty);
}
}
var sql = new StringBuilder().AppendFormat(
sqlStr
, current * page + page
, $"[{tblName}] {exp} {order}"
, current * page
);
sql.AppendFormat(
sqlTotal
, page
, $"[{tblName}] {exp}"
);
return(Query(sql.ToString(), param));
}
public IEnumerable <T> Query(SQLCondition condition = null, IEnumerable <DbParameter> param = null, string sql = null)
{
return(SQLEnityResolve.Get.Convert <T>(this, _sqlExecute.Query(GetTableName, condition, param, sql)));
}
public T QueryFirst(SQLCondition condition = null, IEnumerable <DbParameter> param = null, string sql = null)
{
return(Query(condition, param, sql).FirstOrDefault());
}
public void Mapping(IEnumerable <T> item, SQLCondition condition)
{
item = Query(condition);
}
public void Mapping(T item, SQLCondition condition)
{
item = Query(condition).FirstOrDefault();
}
public IEnumerable <SQLDynamicItem> GetDyncValueByDyncParam(string tblName, string filedNameName, SQLCondition param = null)
{
var where = param != null ? param.WhereExpression : string.Empty;
var sql = $"select {filedNameName} from [{tblName}] {where}";
var dataList = _dbHelper.ExecuteDataSetTp(sql).Tables[0];
if (dataList.Rows.Count > 0)
{
return(dataList.AsEnumerable().Select(t =>
{
return new SQLDynamicItem()
{
Name = filedNameName,
Value = t[filedNameName] != DBNull.Value ? t[filedNameName].ToString() : string.Empty
};
}));
}
return(null);
}