数据访问层使用存储过程分页

最新推荐文章于 2021-11-21 10:38:04 发布

原创最新推荐文章于 2021-11-21 10:38:04 发布 · 603 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#c# #分页 #sql server

webform 专栏收录该内容

21 篇文章

订阅专栏

本文介绍了如何在C#应用程序中使用SQL Server的存储过程进行分页查询，详细阐述了存储过程的创建以及在C#代码中调用存储过程的方法，以实现高效的数据分页展示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

   //分页 
       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;
          }
     }