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