//分页
public List<Model.HKSJ_Main> LoadPageData(int pageIndex, int pageSize, out int total)
{
DataSet ds = new DataSet();
SqlParameter totalParameter = new SqlParameter("@total", SqlDbType.Int);
totalParameter.Direction = ParameterDirection.Output;
//如果用了输出参数,那么就用SqlDataAdapter就可以了,用sqlDataReader时候拿不到输出参数的值。
using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
{
//conn.Open();
using (SqlDataAdapter adapter = new SqlDataAdapter("P_LoadPageData", conn))
{
adapter.SelectCommand.Parameters.Add(new SqlParameter("@pageIndex", pageIndex));
adapter.SelectCommand.Parameters.Add(new SqlParameter("@pageSize", pageSize));
adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
//输出参数的用法
adapter.SelectCommand.Parameters.Add(totalParameter);
adapter.Fill(ds);
}
}
total = (int)totalParameter.Value;//拿到输出参数的值
return this.DataTableToList(ds.Tables[0]);
}
/// <summary>
/// 获得数据列表
/// </summary>
public List<Model.HKSJ_Main> DataTableToList(DataTable dt)
{
List<Model.HKSJ_Main> modelList = new List<Model.HKSJ_Main>();
int rowsCount = dt.Rows.Count;
if (rowsCount > 0)
{
WebDemo.Model.HKSJ_Main model;
for (int n = 0; n < rowsCount; n++)
{
model = DataRowToModel(dt.Rows[n]);
if (model != null)
{
modelList.Add(model);
}
}
}
return modelList;
}
/// <summary>
/// 将行转化成一个对象实体
/// </summary>
public WebDemo.Model.HKSJ_Main DataRowToModel(DataRow row)
{
WebDemo.Model.HKSJ_Main model = new WebDemo.Model.HKSJ_Main();
if (row != null)
{
if (row["ID"] != null && row["ID"].ToString() != "")
{
model.ID = int.Parse(row["ID"].ToString());
}
if (row["title"] != null)
{
model.title = row["title"].ToString();
}
if (row["content"] != null)
{
model.content = row["content"].ToString();
}
if (row["type"] != null)
{
model.type = row["type"].ToString();
}
if (row["Date"] != null && row["Date"].ToString() != "")
{
model.Date = DateTime.Parse(row["Date"].ToString());
}
if (row["people"] != null)
{
model.people = row["people"].ToString();
}
if (row["picUrl"] != null)
{
model.picUrl = row["picUrl"].ToString();
}
}
return model;
}
create proc proc_paging
@pageSize int,
@pageIndex int,
@total int output
As
select * from (select *,ROW_NUMBER() over(order by Id) as num from ss) as new_tb
where num between (@pageSize*(@pageIndex-1)+1) and (@pageSize*@pageIndex);
select @total= COUNT(*) from ss
declare @pp int
exec proc_paging 2,2,@pp
EF分页
public IQueryable<T> GetPageEntities<S>(int pageSize, int pageIndex, out int total, Expression<Func<T, bool>> whereLambda, Expression<Func<T, S>> orderByLambda, bool IsAsc)
{
total = Db.Set<T>().Where(whereLambda).Count();
if (IsAsc)
{
var temp = Db.Set<T>().Where(whereLambda).OrderBy(orderByLambda).Skip(pageSize * (pageIndex - 1)).Take(pageSize).AsQueryable();
return temp;
}
else
{
var temp = Db.Set<T>().Where(whereLambda).OrderByDescending(orderByLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize).AsQueryable();
return temp;
}
}