public List<UserModel> GetList(int pageSize, int pageIndex, string where, string orderBy, out int recordCount) { if (string.IsNullOrEmpty(orderBy)) throw new ArgumentNullException(); StringBuilder sb = new StringBuilder(); sb.Append("select count(1) from dbo.Member_Account a left join dbo.Member_BaseInfo b on a.Id=b.AccountId left join Organ_Type c on a.OrganId = c.Id where a.Delflag = 0 "); if (!string.IsNullOrEmpty(where)) sb.Append(where); recordCount = Convert.ToInt32(MSEntLibSqlHelper.ExecuteScalarBySql(sb.ToString())); int start = (pageIndex - 1) * pageSize + 1; int end = pageIndex * pageSize; StringBuilder sql = new StringBuilder(); sql.Append("select * from (select a.Id as UserId,a.UserName,a.Password,a.NickName,a.Status,c.Title as OrganTitle,ROW_NUMBER() over (order by " + orderBy + ") as RowNum from dbo.Member_Account a left join Organ_Detail c on a.OrganId = c.Id where a.Delflag = 0 "); if (!string.IsNullOrEmpty(where)) sql.Append(where); sql.Append(")as T where RowNum between @start and @end"); SqlParameter[] para ={ new SqlParameter("@start",start), new SqlParameter("@end",end) }; List<UserModel> list = new List<UserModel>(); using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql.ToString(),para)) { while (reader.Read()) { UserModel model = new UserModel(); ConvertToModel(reader, model); list.Add(model); } } return list; }
private void ConvertToModel(IDataReader reader, UserModel model) { if (reader["UserId"] != DBNull.Value) model.UserId = Convert.ToInt32(reader["UserId"]); if (reader["UserName"] != DBNull.Value) model.UserName = reader["UserName"].ToString(); if (reader["PassWord"] != DBNull.Value) model.PassWord = reader["PassWord"].ToString(); if (reader["NickName"] != DBNull.Value) model.NickName = reader["NickName"].ToString(); if (reader["Status"] != DBNull.Value) model.Status = Convert.ToInt32(reader["Status"]); if (reader["OrganTitle"] != DBNull.Value) model.OrganTitle = reader["OrganTitle"].ToString(); }