针对分页存储过程调用的C#类_c# 调用邹建存储过程分页代码示例-优快云博客

本文链接：https://blog.youkuaiyun.com/renyanbinnet/article/details/1958604

本文介绍了一种高效的数据库分页方法，特别适用于数据量大的场景。该方法通过存储过程实现快速分页查询，并支持单条件及多条件排序。此外，还提供了获取记录总数的功能。

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

#region 本分页方法在数据量比较大时，对表的分页速度最快,但只适用于单条件排序。

 /// <summary>
 /// 获取记录总数
 /// </summary>
 /// <param name="tblName">表或视图的名称</param>
 /// <param name="fldName">排序的字段名称</param>
 /// <param name="orderType">选取的字段名</param>
 /// <param name="orderType">升序或降序</param>
 /// <param name="Wherestr">查询条件</param>
 /// <param name="pager">分页控件</param>
 public static void GetPageRecords(string tblName, string fldName, string fidNameInfo, int orderType, string Wherestr, AspNetPager pager)
 {
 using (SqlConnection conn = new SqlConnection(dbString))
 {
 SqlCommand comm = new SqlCommand("GetPageRecordsField", conn);
 comm.CommandType = CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@fldName", fldName);
 comm.Parameters.Add("@fidNameInfo", fidNameInfo);
 comm.Parameters.Add("@IsCount", 1);
 comm.Parameters.Add("@OrderType", orderType);
 comm.Parameters.Add("@strWhere", Wherestr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 pager.RecordCount = (int)comm.ExecuteScalar();
 conn.Close();
 pager.CustomInfoText = "记录总数：" + pager.RecordCount.ToString() + "";
 pager.CustomInfoText += " 总页数：" + pager.PageCount.ToString() + "";
 pager.CustomInfoText += " 当前页：" + pager.CurrentPageIndex.ToString() + "";
 }

 }
 /// <summary>
 /// 绑定数据列表
 /// </summary>
 /// <param name="tblName">表或视图的名称</param>
 /// <param name="fldName">排序的字段名称</param>
 /// <param name="orderType">选取的字段名</param>
 /// <param name="orderType">升序或降序</param>
 /// <param name="Wherestr">查询条件</param>
 /// <param name="pager">分页控件</param>
 /// <param name="dataList">数据显示控件</param>
 public static void GetPageRecords(string tblName, string fldName, string fidNameInfo, int orderType, string Wherestr, AspNetPager pager, Repeater dataList)
 {
 using (SqlConnection conn = new SqlConnection(dbString))
 {
 SqlCommand comm = new SqlCommand("GetPageRecordsField", conn);
 comm.CommandType = CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@fldName", fldName);
 comm.Parameters.Add("@fidNameInfo", fidNameInfo);
 comm.Parameters.Add("@IsCount", 0);
 comm.Parameters.Add("@OrderType", orderType);
 comm.Parameters.Add("@strWhere", Wherestr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 dataList.DataSource = comm.ExecuteReader();
 dataList.DataBind();
 conn.Close();
 }
 }

 public static void GetPageRecords(string tblName, string fldName, string fidNameInfo, int orderType, string Wherestr, AspNetPager pager, DataGrid dataList)
 {
 using(SqlConnection conn=new SqlConnection(dbString))
 {
 SqlCommand comm=new SqlCommand("GetPageRecordsField", conn);
 comm.CommandType=CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@fldName", fldName);
 comm.Parameters.Add("@fidNameInfo", fidNameInfo);
 comm.Parameters.Add("@IsCount", 0);
 comm.Parameters.Add("@OrderType", orderType);
 comm.Parameters.Add("@strWhere", Wherestr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 dataList.DataSource=comm.ExecuteReader();
 dataList.DataBind();
 conn.Close();
 }
 }

 public static void GetPageRecords(string tblName, string fldName, string fidNameInfo, int orderType, string Wherestr, AspNetPager pager, BaseDataBoundControl dataList)
 {
 using (SqlConnection conn = new SqlConnection(dbString))
 {
 SqlCommand comm = new SqlCommand("GetPageRecordsField", conn);
 comm.CommandType = CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@fldName", fldName);
 comm.Parameters.Add("@fidNameInfo", fidNameInfo);
 comm.Parameters.Add("@IsCount", 0);
 comm.Parameters.Add("@OrderType", orderType);
 comm.Parameters.Add("@strWhere", Wherestr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 dataList.DataSource = comm.ExecuteReader();
 dataList.DataBind();
 conn.Close();

 }
 }
 #endregion

 #region 本分页方法在数据量比较大时，对表的分页速度较快,适用多单条件排序。

 /// <summary>
 /// 获取记录总数
 /// </summary>
 /// <param name="tblName">表或视图的名称</param>
 /// <param name="id">需要排序的不重复的ID号</param>
 /// <param name="Wherestr">查询条件</param>
 /// <param name="orderStr">排序条件</param>
 /// <param name="pager">分页控件</param>

 public static void GetPageRecordsMoreOrder(string tblName, string id, string whereStr, string orderStr, AspNetPager pager)
 {
 using (SqlConnection conn = new SqlConnection(dbString))
 {
 SqlCommand comm = new SqlCommand("GetPageRecordsByMoreOrder", conn);
 comm.CommandType = CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@IsCount", "1");
 comm.Parameters.Add("@id", id);
 comm.Parameters.Add("@strWhere", whereStr);
 comm.Parameters.Add("@OrderType", orderStr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 pager.RecordCount = Convert .ToInt32(comm.ExecuteScalar());
 conn.Close();
 pager.CustomInfoText = "记录总数：" + pager.RecordCount.ToString() + "";
 pager.CustomInfoText += " 总页数：" + pager.PageCount.ToString() + "";
 pager.CustomInfoText += " 当前页：" + pager.CurrentPageIndex.ToString() + "";
 }

 }
 /// <summary>
 /// 绑定数据列表
 /// </summary>
 /// <param name="tblName">表或视图的名称</param>
 /// <param name="id">需要排序的不重复的ID号</param>
 /// <param name="Wherestr">查询条件</param>
 /// <param name="orderStr">排序条件</param>
 /// <param name="pager">分页控件</param>
 /// <param name="dataList">列表控件</param>
 public static void GetPageRecordsMoreOrder(string tblName, string id, string whereStr, string orderStr, AspNetPager pager, BaseDataBoundControl dataList)
 {
 using (SqlConnection conn = new SqlConnection(dbString))
 {
 SqlCommand comm = new SqlCommand("GetPageRecordsByMoreOrder", conn);
 comm.CommandType = CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@IsCount", "0");
 comm.Parameters.Add("@id", id);
 comm.Parameters.Add("@strWhere", whereStr);
 comm.Parameters.Add("@OrderType", orderStr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 dataList.DataSource = comm.ExecuteReader();
 dataList.DataBind();
 conn.Close();
 }
 }

 public static void GetPageRecordsMoreOrder(string tblName, string id, string whereStr, string orderStr, AspNetPager pager, Repeater dataList)
 {
 using (SqlConnection conn = new SqlConnection(dbString))
 {
 SqlCommand comm = new SqlCommand("GetPageRecordsByMoreOrder", conn);
 comm.CommandType = CommandType.StoredProcedure;
 comm.Parameters.Add("@tblName", tblName);
 comm.Parameters.Add("@IsCount", "0");
 comm.Parameters.Add("@id", id);
 comm.Parameters.Add("@strWhere", whereStr);
 comm.Parameters.Add("@OrderType", orderStr);
 comm.Parameters.Add("@PageIndex", pager.CurrentPageIndex);
 comm.Parameters.Add("@PageSize", pager.PageSize);

 conn.Open();
 dataList.DataSource = comm.ExecuteReader();
 dataList.DataBind();
 conn.Close();
 }
 }

 #endregion