在ASP.NET中，使用SQL存储过程实现分页是一种高效且常用的方法，本文将详细介绍如何创建和调用一个名为P_viewPage的存储过程，以实现分页功能。
一、创建存储过程
我们需要在SQL Server中创建一个存储过程P_viewPage，该存储过程接受多个参数，用于指定表名、字段列表、主键、查询条件、排序规则等。
1. 存储过程定义
CREATE PROCEDURE [dbo].[P_viewPage]
@TableName VARCHAR(200), -表名
@FieldList VARCHAR(2000), -显示列名，可以是*或特定字段
@PrimaryKey VARCHAR(100), -单一主键或唯一标识字段
@Where VARCHAR(2000), -查询条件，不包含'where'关键字
@Order VARCHAR(1000), -排序规则，不包含'order by'关键字
@SortType INT, -排序类型：1正序，2倒序，3多列排序
@RecorderCount INT, -记录总数，0表示返回总记录数
@PageSize INT, -每页显示的记录数
@PageIndex INT, -当前页码
@TotalCount INT OUTPUT, -返回的总记录数
@TotalPageCount INT OUTPUT -返回的总页数
AS
BEGIN
SET NOCOUNT ON;
IF ISNULL(@TotalCount, '') = '' SET @TotalCount = 0;
SET @Order = RTRIM(LTRIM(@Order));
SET @PrimaryKey = RTRIM(LTRIM(@PrimaryKey));
SET @FieldList = REPLACE(RTRIM(LTRIM(@FieldList)), ' ', '');
WHILE CHARINDEX(', ', @Order) > 0 OR CHARINDEX(' ,', @Order) > 0
BEGIN
SET @Order = REPLACE(@Order, ', ', ',')
SET @Order = REPLACE(@Order, ' ,', ',')
END
IF ISNULL(@TableName, '') = '' OR ISNULL(@FieldList, '') = ''
OR ISNULL(@PrimaryKey, '') = ''
OR @SortType < 1 OR @SortType > 3
OR @RecorderCount < 0 OR @PageSize < 0 OR @PageIndex < 0
BEGIN
PRINT('ERR_00');
RETURN;
END
IF @SortType = 3
BEGIN
IF (UPPER(RIGHT(@Order, 4)) != ' ASC' AND UPPER(RIGHT(@Order, 5)) != ' DESC')
BEGIN PRINT('ERR_02'); RETURN; END
END
DECLARE @new_where1 VARCHAR(1000);
DECLARE @new_where2 VARCHAR(1000);
DECLARE @new_order1 VARCHAR(1000);
DECLARE @new_order2 VARCHAR(1000);
DECLARE @new_order3 VARCHAR(1000);
DECLARE @Sql NVARCHAR(MAX);
DECLARE @SqlCount NVARCHAR(MAX);
IF ISNULL(@Where, '') = ''
BEGIN
SET @new_where1 = '';
SET @new_where2 = ' WHERE ';
END
ELSE
BEGIN
SET @new_where1 = ' WHERE ' + @Where;
SET @new_where2 = '';
END
-根据SortType设置排序规则
IF @SortType = 1
BEGIN
SET @new_order1 = @Order;
SET @new_order2 = '';
SET @new_order3 = '';
END
ELSE IF @SortType = 2
BEGIN
SET @new_order1 = REVERSE(@Order);
SET @new_order2 = '';
SET @new_order3 = '';
END
ELSE IF @SortType = 3
BEGIN
SET @new_order1 = REVERSE(SUBSTRING(@Order, 1, LEN(@Order) FINDCHAR(',', @Order)));
SET @new_order2 = REVERSE(SUBSTRING(@Order, FINDCHAR(',', @Order) + 1, LEN(@Order)));
SET @new_order3 = '';
END
-计算总记录数和总页数
SET @SqlCount = 'SELECT @TotalCount=COUNT(*) FROM ' + @TableName + @new_where1;
EXEC sp_executesql @SqlCount, N'@TotalCount INT OUTPUT', @TotalCount OUTPUT;
SET @TotalPageCount = CEILING((CONVERT(FLOAT, @TotalCount) / CONVERT(FLOAT, @PageSize)) + 1);
-生成分页SQL语句
SET @Sql = 'SELECT ' + @FieldList + ' FROM ( SELECT ' + @FieldList + ', ROW_NUMBER() OVER (ORDER BY ' + @new_order1 + ' ' + @new_order2 + ' ' + @new_order3 + ') AS rowId FROM ' + @TableName + @new_where2 + ' ) AS tbl WHERE rowId BETWEEN ' + CAST((@PageIndex 1) * @PageSize + 1 AS NVARCHAR) + ' AND ' + CAST(@PageIndex * @PageSize AS NVARCHAR);
EXEC sp_executesql @Sql, N'', @TotalCount OUTPUT;
END;
GO
2. 参数说明
@TableName: 表名，用于指定查询的数据表。
@FieldList: 显示的列名列表，可以是通配符表示所有字段，或者指定特定字段。
@PrimaryKey: 单一主键或唯一标识字段，用于数据的唯一标识。
@Where: 查询条件，不包含where关键字，例如"id > 10 and len(userid) > 9"。
@Order: 排序规则，不包含order by关键字，格式如"id asc, userid desc"，但必须指定排序方向（asc或desc）。
@SortType: 排序类型，1代表升序，2代表降序，3表示多列排序。
@RecorderCount: 记录总数，如果为0则计算总记录。
@PageSize: 每页显示的记录数。
@PageIndex: 当前页码。
@TotalCount: 输出的总记录数，作为OUTPUT参数。
@TotalPageCount: 输出的总页数，作为OUTPUT参数。
二、在ASP.NET中调用存储过程
在ASP.NET后台代码中，我们可以使用ADO.NET来调用这个存储过程，以下是详细的步骤和示例代码：
1. 配置数据库连接字符串
确保在Web.config文件中配置了数据库连接字符串：
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=服务器地址;Initial Catalog=数据库名;User ID=用户名;Password=密码" providerName="System.Data.SqlClient"/>
</connectionStrings>
2. 调用存储过程的示例代码
using System;
using System.Data.SqlClient;
using System.Configuration;
namespace AspNetPagingExample
{
public partial class PagingExample : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindGrid()
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// 定义存储过程参数
SqlCommand command = new SqlCommand("usp_ViewPage", connection);
command.CommandType = CommandType.StoredProcedure;
// 设置参数值
command.Parameters.AddWithValue("@TableName", "YourTableName"); // 替换为实际表名
command.Parameters.AddWithValue("@FieldList", "*"); // 替换为实际字段列表
command.Parameters.AddWithValue("@PrimaryKey", "YourPrimaryKey"); // 替换为实际主键或唯一值键
command.Parameters.AddWithValue("@Where", "YourWhereClause"); // 替换为实际查询条件，不含'where'字符
command.Parameters.AddWithValue("@Order", "YourOrderClause"); // 替换为实际排序规则，不含'order by'字符，如 id asc, userid desc
command.Parameters.AddWithValue("@SortType", 1); // 正序，可以改为2（倒序）或3（多列排序）
command.Parameters.AddWithValue("@RecorderCount", 0); // 如果需要返回总记录数，设置为0；否则设置为非0值
command.Parameters.AddWithValue("@PageSize", pageSize); // 每页显示的记录数，根据实际情况设置
command.Parameters.AddWithValue("@PageIndex", pageIndex); // 当前页码，根据实际情况设置
// 输出参数
command.Parameters.Add("@TotalCount", SqlDbType.Int).Direction = ParameterDirection.Output;
command.Parameters.Add("@TotalPageCount", SqlDbType.Int).Direction = ParameterDirection.Output;
// 执行存储过程并读取结果集
SqlDataReader reader = command.ExecuteReader();
// 根据需求处理结果集，例如绑定到GridView控件上显示分页数据
gridView.DataSource = reader;
gridView.DataBind();
// 获取总记录数和总页数，根据需求动态生成分页导航链接
int totalCount = (int)command.Parameters["@TotalCount"].Value;
int totalPageCount = (int)command.Parameters["@TotalPageCount"].Value;
GeneratePaginationLinks(totalPageCount);
}
}
private void GeneratePaginationLinks(int totalPageCount)
{
// 根据总页数动态生成分页导航链接，这里简单示例如下：
string paginationHtml = "";
for (int i = 1; i <= totalPageCount; i++)
{
paginationHtml += "<a href=\"?pageIndex=" + i + "\">" + i + "</a> ";
}
paginationLinks.InnerHtml = paginationHtml; // paginationLinks是一个占位符，用于显示分页导航链接
}
}
}
三、优化与注意事项
1、性能优化：当@RecorderCount设置为0时，存储过程会计算总记录数，这可能会对性能产生影响，在实际应用中，如果总记录数不常变动，可以考虑在第一次查询时计算并缓存。
2、防止SQL注入：确保输入参数经过验证和过滤，避免SQL注入攻击，特别是@Where和@Order参数，应严格检查其格式和内容。
3、排序规则：@Order参数中必须包含主键，以确保分页的正确性，多列排序时需注意各列的顺序和方向。
4、错误处理：在实际应用中，应添加错误处理机制，捕获并处理可能出现的异常情况，如数据库连接失败、SQL执行错误等。
5、事务管理：如果存储过程中涉及对数据库的修改操作，建议使用事务管理，以确保数据的一致性和完整性，可以在存储过程开始时使用BEGIN TRAN开启事务，并在适当位置使用COMMIT提交事务或ROLLBACK回滚事务。
6、日志记录：为了便于调试和维护，建议在存储过程中添加日志记录功能，记录关键操作和错误信息，可以使用SQL Server的日志功能或其他第三方日志库来实现。
7、性能监控：定期监控存储过程的执行时间和资源消耗情况，及时发现并解决性能瓶颈问题，可以使用SQL Server的性能监控工具或第三方性能分析工具来进行监控和分析。
8、安全性考虑：除了防止SQL注入外，还应注意存储过程中的数据访问权限控制，确保只有授权用户才能调用存储过程，并对敏感数据进行加密处理，对于涉及敏感操作的存储过程（如删除、更新等），应添加额外的安全验证机制。
各位小伙伴们，我刚刚为大家分享了有关“asp.net SQL存储过程分页”的知识，希望对你们有所帮助。如果您还有其他相关问题需要解决，欢迎随时提出哦！