求教一个通用存储分页的调用办法!!!

[feiniao119]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO




ALTER PROCEDURE SP_Page

@TB VARCHAR(50),
@COL VARCHAR(50),
-- @COLTYPE INT,
@ORDERBY BIT,
@COLLIST VARCHAR(800),
@PAGESIZE INT,
@PAGE INT,
@CONDITION VARCHAR(800),
@RecPages INT,
@RecCount INT OUTPUT,
@PAGES INT OUTPUT,
@OUTSQL NVARCHAR(4000) OUTPUT

AS

DECLARE @SQL NVARCHAR(4000)
DECLARE @WHERE1 VARCHAR(800)
DECLARE @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 @RecCount=COUNT(*),@PAGES=CEILING((+0.0)/'+CAST(@PAGESIZE AS VARCHAR)+') FROM '+@TB+@WHERE2

IF @RecPages = 0
EXEC SP_EXECUTESQL @SQL,N'@PAGES INT OUTPUT,@RecCount INT OUTPUT',@PAGES OUTPUT,@RecCount OUTPUT
ELSE
SELECT @PAGES = @RecPages

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

SET @OUTSQL = @SQL

EXEC(@SQL)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

//-----------------------------------------------------

DataList怎么调用??

[feiniao119]

public IList GetnewsList(int pageSize, int pageNo, string conditionStr)
{
IList list = new ArrayList();
string tb = "news";
string col = "id";
string orderby = "true";
string collist = "id, title, addtime, content, type";
string condition = conditionStr;

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "Sp_Page";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = new DBcon().Cnn;
SqlParameter parm = cmd.Parameters.Add("@TB", System.Data.SqlDbType.NVarChar, 50);
parm.Value = tb;
parm = cmd.Parameters.Add("@COL", System.Data.SqlDbType.NVarChar, 50);
parm.Value = col;
parm = cmd.Parameters.Add("@ORDERBY", System.Data.SqlDbType.Bit, 5);
parm.Value = orderby;
parm = cmd.Parameters.Add("@COLLIST", System.Data.SqlDbType.NVarChar, 800);
parm.Value = collist;
parm = cmd.Parameters.Add("@PAGESIZE", System.Data.SqlDbType.Int, 4);
parm.Value = pageSize;
parm = cmd.Parameters.Add("@PAGE", System.Data.SqlDbType.Int, 8);
parm.Value = pageNo;
parm = cmd.Parameters.Add("@CONDITION", System.Data.SqlDbType.NVarChar, 800);
parm.Value = condition;
parm = cmd.Parameters.Add("@RecPages", System.Data.SqlDbType.Int,1);
parm.Value = 0;
cmd.Parameters.Add("@RecCount", System.Data.SqlDbType.Int, 8);
cmd.Parameters["@RecCount"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@PAGES", System.Data.SqlDbType.Int, 8);
cmd.Parameters["@PAGES"].Direction = ParameterDirection.Output;
cmd.Parameters.Add("@OUTSQL", System.Data.SqlDbType.NVarChar, 4000);
cmd.Parameters["@OUTSQL"].Direction = ParameterDirection.Output;

using(SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{

while(dr.Read())
{
try
{
newsInfo newsinfo = new newsInfo(dr.GetInt32(0), dr.GetString(1), dr.GetDateTime(2), dr.GetString(3), dr.GetString(4));
list.Add(newsinfo);
}
catch{}
}
dr.Close();
}
_recordcount = Int32.Parse(cmd.Parameters["@RecCount"].Value.ToString());
return list;
}

[feiniao119]

请问DataList怎么调用这个储存分页