public User GetOne(Where @where, string fields) { using (SqlCommand command = new DbHelper().Command) { User user = new User(); string sql = $"select top 1 {fields} from {Table} where {where.Result};"; IDataReader reader = command.ExecuteReaderExt(sql, where); if (reader.Read()) { user = ReaderModel(reader, fields); } return user; } }
public List<User> GetList(Where where, string fields, string orderby, int pageIndex, int pageSize) { string sqlBase = $"select {fields} from {Table} where {@where.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 - 1)*pageSize + 1; int endPageIndex = pageIndex*pageSize; string sqlPage = string.Format(sqlPageBase, sqlBase, orderby, startPageIndex, endPageIndex); using (SqlCommand command = new DbHelper().Command) { List<User> users = new List<User>(); IDataReader reader = command.ExecuteReaderExt(sqlPage, where); while (reader.Read()) { User user = ReaderModel(reader, fields); users.Add(user); } return users; } }
public int GetRecordCount(Where @where) { string sql = $"select count(1) total from {Table} where {where.Result};"; using (SqlCommand command = new DbHelper().Command) { command.CommandText = sql; command.CommandType = CommandType.Text; IDataReader reader = command.ExecuteReaderExt(sql, where); if (reader.Read()) { object countObj = reader["total"]; return countObj.ToInt(); } return 0; } }