Пример #1
0
        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));
            }
        }
Пример #2
0
 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();
     }
 }
Пример #3
0
        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();
            }
        }
Пример #4
0
 /// <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();
 }
Пример #5
0
 public int GetRecordCount(Where objWhere = null) {
     throw new NotImplementedException();
 }
Пример #6
0
 public DataTable GetDataTable(string fields = "*", Where objWhere = null, string orderField = null, bool asc = true,
     int pageIndex = 0, int pageSize = 10) {
     throw new NotImplementedException();
 }
Пример #7
0
 public List<User_MODEL> GetList(Where objWhere = null, string orderField = null, bool asc = true, int pageIndex = 0, int pageSize = 10) {
     throw new NotImplementedException();
 }