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;
}
}
