调用分页存储过程的函数
CREATE PROCEDURE sp_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO
CREATE PROCEDURE sp_page
@tb varchar(50), --表名
@col varchar(50), --按该列来进行分页
@coltype int, --@col列的类型,0-数字类型,1-字符类型,2-日期时间类型
@orderby bit, --排序,0-顺序,1-倒序
@collist varchar(800),--要查询出的字段列表,*表示全部字段
@pagesize int, --每页记录数
@page int, --指定页
@condition varchar(800),--查询条件
@pages int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000),@where1 varchar(800),@where2 varchar(800)
IF @condition is null or rtrim(@condition)=''
BEGIN--没有查询条件
SET @where1=' WHERE '
SET @where2=' '
END
ELSE
BEGIN--有查询条件
SET @where1=' WHERE ('+@condition+') AND '--本来有条件再加上此条件
SET @where2=' WHERE ('+@condition+') '--原本没有条件而加上此条件
END
SET @sql='SELECT @pages=CEILING((COUNT(*)+0.0)/'+CAST(@pagesize AS varchar)+
') FROM '+@tb+@where2
EXEC sp_executesql @sql,N'@pages int OUTPUT',@pages OUTPUT--计算总页数
IF @orderby=0
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'>(SELECT MAX('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+') t) ORDER BY '+@col
ELSE
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+
' FROM '+@tb+@where1+@col+'<(SELECT MIN('+@col+') '+
' FROM (SELECT TOP '+CAST(@pagesize*(@page-1) AS varchar)+' '+
@col+' FROM '+@tb+@where2+'ORDER BY '+@col+' DESC) t) ORDER BY '+
@col+' DESC'
IF @page=1--第一页
SET @sql='SELECT TOP '+CAST(@pagesize AS varchar)+' '+@collist+' FROM '+@tb+
@where2+'ORDER BY '+@col+CASE @orderby WHEN 0 THEN '' ELSE ' DESC' END
EXEC(@sql)
GO
SqlCommand cmd = new SqlCommand("sp_page",con) ;
cmd.CommandType = CommandType.StoredProcedure ;
cmd.Parameters.Add("@tb",SqlDbType.varchar,50);
cmd.Parameters["@tb"].Values = ...
....
DataGrid1.DataSource = sult ;
DataGrid1.DataBinder();
{
string sql="sp_page";
System.Data.SqlClient.SqlParameter[] p=new SqlParameter[9];
p[0]=new SqlParameter();
p[0].ParameterName ="@tb";
p[0].Value ="tablename";
p[0].DbType=System.Data.DbType.String; p[1]=new SqlParameter();
p[1].ParameterName ="@col";
p[1].Value ="col";
p[1].DbType=System.Data.DbType.String; p[2]=new SqlParameter();
p[2].ParameterName ="@coltype";
p[2].Value =1;
p[2].DbType=System.Data.DbType.Int32; p[3]=new SqlParameter();
p[3].ParameterName ="@orderby";
p[3].Value ="1";
p[3].DbType=System.Data.DbType.String; p[4]=new SqlParameter();
p[4].ParameterName ="@collist";
p[4].Value ="1";
p[4].DbType=System.Data.DbType.String; p[5]=new SqlParameter();
p[5].ParameterName ="@pagesize";
p[5].Value =1;
p[5].DbType=System.Data.DbType.Int32; p[6]=new SqlParameter();
p[6].ParameterName ="@page";
p[6].Value =1;
p[6].DbType=System.Data.DbType.Int32; p[7]=new SqlParameter();
p[7].ParameterName ="@condition";
p[7].Value ="1";
p[7].DbType=System.Data.DbType.String; p[8]=new SqlParameter();
p[8].ParameterName ="@pages";
p[8].Direction=System.Data.ParameterDirection.Output;
p[8].DbType=System.Data.DbType.Int32;
DataSet ds=ExecSPDataSet(sql,p);
if (p[8].Value!=DBNull.Value && p[8].Value.ToString()!=string.Empty )
output=Convert.ToInt32(p[8].Value);
return ds;
}
public static DataSet ExecSPDataSet(string sql,System.Data.IDataParameter[] paramers)
{
SqlConnection conn=new SqlConnection(ConnectionString);
SqlCommand sqlcom=new SqlCommand(sql,conn);
sqlcom.CommandType= CommandType.StoredProcedure ; foreach(System.Data.IDataParameter paramer in paramers)
{
sqlcom.Parameters.Add(paramer);
}
conn.Open();
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=sqlcom;
DataSet ds=new DataSet();
da.Fill(ds); conn.Close();
return ds;
}