public DataTable GetDataTable(string fields = "*", Where objWhere = null, string orderField = null,
bool asc = true, int pageIndex = 0,
int pageSize = 10) {
using (EFDataContext dataContext = new EFDataContext()) {
orderField = orderField ?? PrimaryKey;
string sortDirection = asc ? "asc" : "desc";
string orderby = $" {orderField} {sortDirection} ";
if (objWhere == null) {
objWhere = new Where().Add(new Where.Item("1", "=", "1"));
}
string sql = $"select {fields} from {TableName} where {objWhere.Result}";
const string sqlPageBase = @"select * from(
select *,ROW_NUMBER() OVER (ORDER BY {1}) as rank from ({0})a
)as t where t.rank between {2} and {3}";
int startPageIndex = pageIndex*pageSize + 1;
int endPageIndex = pageIndex*pageSize + pageSize;
string sqlPage = string.Format(sqlPageBase, sql, orderby, startPageIndex, endPageIndex);
return dataContext.Database.SqlQueryForDataTatable(sqlPage, CreateWhereSqlParameters(objWhere));
}
}
public int GetRecordCount(Where objWhere = null) {
using (EFDataContext dataContext = new EFDataContext()) {
if (objWhere == null) {
objWhere = new Where().Add(new Where.Item("1", "=", "1"));
}
return dataContext.Database.SqlQuery<int>(
$"select count({PrimaryKey}) from {TableName} where {objWhere.Result}",
CreateWhereSqlParameters(objWhere)).First();
}
}
public List<MODEL.User_MODEL> GetList( Where objWhere = null, string orderField = null, bool asc = true,
int pageIndex = 0, int pageSize = 10) {
using (EFDataContext dataContext = new EFDataContext()) {
orderField = orderField ?? PrimaryKey;
string sortDirection = asc ? "asc" : "desc";
string orderby = $" {orderField} {sortDirection} ";
string where = (objWhere == null) ? "1=1" : objWhere.Result;
string sql = $"select * from {TableName} where {where}";
const string sqlPageBase = @"select * from(
select *,ROW_NUMBER() OVER (ORDER BY {1}) as rank from ({0})a
)as t where t.rank between {2} and {3}";
int startPageIndex = pageIndex*pageSize + 1;
int endPageIndex = pageIndex*pageSize + pageSize;
string sqlPage = string.Format(sqlPageBase, sql, orderby, startPageIndex, endPageIndex);
return (objWhere == null)
? dataContext.Database.SqlQuery<MODEL.User_MODEL>(sqlPage)
//.Select(data => new User_MODEL() {Name = data.Name})//选取需要的字段
.ToList()
: dataContext.Database.SqlQuery<MODEL.User_MODEL>(sqlPage, CreateWhereSqlParameters(objWhere))
//.Select(data => new User_MODEL() {Name = data.Name})
.ToList();
}
}
/// <summary>
/// 生成 where sqlparameter参数
/// </summary>
/// <param name="objWhere"></param>
/// <returns></returns>
public SqlParameter[] CreateWhereSqlParameters(Where objWhere) {
List<Where.Item> items = objWhere.WhereItems;
List<SqlParameter> parameters = items.Select(item => new SqlParameter($"@{item.Field}", item.Value)).ToList();
return parameters.ToArray();
}
public int GetRecordCount(Where objWhere = null) {
throw new NotImplementedException();
}
public DataTable GetDataTable(string fields = "*", Where objWhere = null, string orderField = null, bool asc = true,
int pageIndex = 0, int pageSize = 10) {
throw new NotImplementedException();
}
public List<User_MODEL> GetList(Where objWhere = null, string orderField = null, bool asc = true, int pageIndex = 0, int pageSize = 10) {
throw new NotImplementedException();
}