这是我的第一篇博客,不晓得咋写,先写一个分页的存储过程吧,这个比较常用,也是从别处Copy的。
在使用ado.net做列表分页时,使用存储过程还是很有必要的。
以下为存储过程代码
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[DB_SlitPage] Script Date: 2018-03-22 15:47:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Pro_Pagination]
@TableName varchar(300), --表名
@Fields varchar(5000) = '*', --字段名(全部字段为*)
@OrderField varchar(5000), --排序字段(必须!支持多字段)
@sqlWhere varchar(5000) = Null, --条件语句(不用加where)
@pageSize int, --每页多少条记录
@pageIndex int = 1 , --指定当前为第几页
@totalRecord int output,
@TotalPage int output --返回总页数
AS
BEGIN
Begin Tran --开始事务
Declare @sql nvarchar(4000);
--if @totalRecord<=0 begin
--计算总记录数
if (@sqlWhere='' or @sqlWhere IS NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数
--end
--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@pageSize)
if (@sqlWhere='' or @sqlWhere IS NULL)
if(@OrderField=''or @OrderField is null)
set @sql = 'Select * FROM (select ROW_NUMBER() as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
else
if(@OrderField=''or @OrderField is null)
set @sql = 'Select * FROM (select ROW_NUMBER() as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @sqlWhere
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @sqlWhere
--处理页数超出范围情况
if @pageIndex<=0
Set @pageIndex = 1
--if @pageIndex>@TotalPage
-- Set @pageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@pageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
set @sql = @sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
print @sql
Exec(@sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回记录总数
End
END
下为后台在调用此存储过程时的代码
//声明变量,赋值 string strwhere = "1=1"; string pageIndex = "1"; //当前页码 string Fields = ""; //需要得到的字段 string TableName = ""; //需要查看的表名 string OrderField = ""; //排序的字段名 IDataParameter[] para ={ new SqlParameter("@TableName",SqlDbType.NVarChar,500), new SqlParameter("@Fields",SqlDbType.NVarChar,500), new SqlParameter("@OrderField",SqlDbType.NVarChar,500), new SqlParameter("@sqlWhere",SqlDbType.NVarChar,500), new SqlParameter("@pageSize",SqlDbType.Int), new SqlParameter("@pageIndex",SqlDbType.Int), new SqlParameter("@totalRecord",SqlDbType.Int), new SqlParameter("@TotalPage",SqlDbType.Int) }; para[0].Value = TableName; para[1].Value = Fields; para[2].Value = OrderField; para[3].Value = strwhere; para[4].Value = 20; para[5].Value = pageIndex; para[6].Direction = ParameterDirection.Output; para[7].Direction = ParameterDirection.Output; string sql = "Pro_Pagination"; //下为存储过程调用的方法 /// <summary> /// 执行存储过程,返回SqlDataReader ( 注意:调用该方法后,一定要对SqlDataReader进行Close ) /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters) { SqlConnection connection = Conn(); SqlDataReader returnReader; SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; //returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); SqlDataAdapter da = new SqlDataAdapter(command); DataSet ds = new DataSet (); da.Fill(ds); return ds; } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> public SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) { if (parameter != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null)) { parameter.Value = DBNull.Value; } command.Parameters.Add(parameter); } } return command; }