我写了一个分页存储过程create PROC Turnpage
@qCols varchar(1000), --需要查询的列
@qTables varchar(1000), --需要查询的表 和条件
@iKey varchar (255), --标识字段
@oKey varchar(255), --排序字段
@pageSize int = 30, --每页的行数
@pageNumber int = 1, --要显示的页码
@sortType int = 0, --排序方式,0表示ASC,非0表示DESC
@IsReCount bit = 0 -- 非 0 值则返回记录总数,0返回记录集
AS
set nocount on
BEGIN
DECLARE @sqlText AS varchar(2000)
DECLARE @sqlTable AS varchar(2000)
DECLARE @sort AS varchar(20)
if @sortType =0
SET @sort=' asc'
else
SET @sort=' desc' SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber) * @pageSize AS varchar(30)) + ' ' + @qCols +' from '+ @qTables + ' order by '+@oKey+ @sort
SET @sqlText = 'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @iKey + ' NOT IN(SELECT TOP ' +
CAST((@pageNumber-1) * @pageSize AS varchar(30)) + ' ' + @iKey +
' FROM (' + @sqlTable + ') AS tableB)'
if @IsReCount != 0
set @sqlText = 'select count(*) as Total from ' + @qTables
EXEC (@sqlText)
--print(@sqltext)
END
GO-------------------------------------
我写了一个方法
public DataSet ar_Article(string a_Tables,string a_Cols,string a_iKey,string a_oKey,int a_pageSize,int a_pageNumber,int a_sortType,int a_IsReCount)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(_connstring);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("Turnpage",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Para_Tables = new SqlParameter("@qTables",SqlDbType.VarChar,100);
Para_Tables.Value = a_Tables;
cmd.Parameters.Add(Para_Tables); SqlParameter Para_Cols = new SqlParameter("@qCols",SqlDbType.VarChar,1000);
Para_Cols.Value = a_Cols;
cmd.Parameters.Add(Para_Cols); SqlParameter Para_iKey = new SqlParameter("@iKey",SqlDbType.VarChar,255);
Para_iKey.Value = a_iKey;
cmd.Parameters.Add(Para_iKey); SqlParameter Para_oKey = new SqlParameter("@oKey",SqlDbType.VarChar,255);
Para_oKey.Value = a_oKey;
cmd.Parameters.Add(Para_oKey);
SqlParameter Para_pageSize = new SqlParameter("@pageSize",SqlDbType.Int);
Para_pageSize.Value = a_pageSize ;
cmd.Parameters.Add(Para_pageSize); SqlParameter Para_pageNumber = new SqlParameter("@pageNumber",SqlDbType.Int);
Para_pageNumber.Value = a_pageNumber;
cmd.Parameters.Add(Para_pageNumber); SqlParameter Para_sortType = new SqlParameter("@sortType",SqlDbType.Int);
Para_sortType.Value = a_sortType;
cmd.Parameters.Add(Para_sortType); SqlParameter Para_IsReCount = new SqlParameter("@IsReCount",SqlDbType.Bit);
Para_IsReCount.Value = a_IsReCount;
cmd.Parameters.Add(Para_IsReCount);
conn.Open();
SqlParameter WorkParam = null; WorkParam = cmd.Parameters.Add("@pageNumber",SqlDbType.Int);
WorkParam.Direction = ParameterDirection.Output;
DataSet ds;
System.Data.SqlClient.SqlDataAdapter da;
da = new SqlDataAdapter();
da.SelectCommand = cmd;
ds = new DataSet();
da.Fill(ds,"DBDataSet");
return ds;
}但返回ds 却返回不了存储过程中返回的参数,该如何解决呢,或者不用ds 同样能返回数据集和存储过程的参数!
谢谢!
@qCols varchar(1000), --需要查询的列
@qTables varchar(1000), --需要查询的表 和条件
@iKey varchar (255), --标识字段
@oKey varchar(255), --排序字段
@pageSize int = 30, --每页的行数
@pageNumber int = 1, --要显示的页码
@sortType int = 0, --排序方式,0表示ASC,非0表示DESC
@IsReCount bit = 0 -- 非 0 值则返回记录总数,0返回记录集
AS
set nocount on
BEGIN
DECLARE @sqlText AS varchar(2000)
DECLARE @sqlTable AS varchar(2000)
DECLARE @sort AS varchar(20)
if @sortType =0
SET @sort=' asc'
else
SET @sort=' desc' SET @sqlTable = 'SELECT TOP ' + CAST((@pageNumber) * @pageSize AS varchar(30)) + ' ' + @qCols +' from '+ @qTables + ' order by '+@oKey+ @sort
SET @sqlText = 'SELECT TOP ' + CAST(@pageSize AS varchar(30)) + ' * ' +
'FROM (' + @sqlTable + ') AS tableA ' +
'WHERE ' + @iKey + ' NOT IN(SELECT TOP ' +
CAST((@pageNumber-1) * @pageSize AS varchar(30)) + ' ' + @iKey +
' FROM (' + @sqlTable + ') AS tableB)'
if @IsReCount != 0
set @sqlText = 'select count(*) as Total from ' + @qTables
EXEC (@sqlText)
--print(@sqltext)
END
GO-------------------------------------
我写了一个方法
public DataSet ar_Article(string a_Tables,string a_Cols,string a_iKey,string a_oKey,int a_pageSize,int a_pageNumber,int a_sortType,int a_IsReCount)
{
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(_connstring);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("Turnpage",conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter Para_Tables = new SqlParameter("@qTables",SqlDbType.VarChar,100);
Para_Tables.Value = a_Tables;
cmd.Parameters.Add(Para_Tables); SqlParameter Para_Cols = new SqlParameter("@qCols",SqlDbType.VarChar,1000);
Para_Cols.Value = a_Cols;
cmd.Parameters.Add(Para_Cols); SqlParameter Para_iKey = new SqlParameter("@iKey",SqlDbType.VarChar,255);
Para_iKey.Value = a_iKey;
cmd.Parameters.Add(Para_iKey); SqlParameter Para_oKey = new SqlParameter("@oKey",SqlDbType.VarChar,255);
Para_oKey.Value = a_oKey;
cmd.Parameters.Add(Para_oKey);
SqlParameter Para_pageSize = new SqlParameter("@pageSize",SqlDbType.Int);
Para_pageSize.Value = a_pageSize ;
cmd.Parameters.Add(Para_pageSize); SqlParameter Para_pageNumber = new SqlParameter("@pageNumber",SqlDbType.Int);
Para_pageNumber.Value = a_pageNumber;
cmd.Parameters.Add(Para_pageNumber); SqlParameter Para_sortType = new SqlParameter("@sortType",SqlDbType.Int);
Para_sortType.Value = a_sortType;
cmd.Parameters.Add(Para_sortType); SqlParameter Para_IsReCount = new SqlParameter("@IsReCount",SqlDbType.Bit);
Para_IsReCount.Value = a_IsReCount;
cmd.Parameters.Add(Para_IsReCount);
conn.Open();
SqlParameter WorkParam = null; WorkParam = cmd.Parameters.Add("@pageNumber",SqlDbType.Int);
WorkParam.Direction = ParameterDirection.Output;
DataSet ds;
System.Data.SqlClient.SqlDataAdapter da;
da = new SqlDataAdapter();
da.SelectCommand = cmd;
ds = new DataSet();
da.Fill(ds,"DBDataSet");
return ds;
}但返回ds 却返回不了存储过程中返回的参数,该如何解决呢,或者不用ds 同样能返回数据集和存储过程的参数!
谢谢!
解决方案 »
- 使用HttpModule重写URL后无法使用Session,已设置Page跟配置文件,还是无效,求解
- 小女子求助ASP的留言版
- 在Web service 中,怎么读取其程序文件的位置
- 重装VS2005后 DropDownList3.SelectedValue 不能用了
- 页面中有几个输入框和一个确认按钮,数据在EXCEL中.怎样实现自动输入???
- WebNoteEditor在线编辑器优惠活动
- 一个简单的方法
- Win2003 server IIS上部署ASP.net项目报错
- 关于Response.Write()的问题
- 看了半天也不明白,到底 ASP.NET 模拟 有什么用?
- 散分问一个水晶报表的问题,分不够可以加,关于对路径拒绝访问的问题。
- 如何取CheckBoxList中选定的值
@qCols varchar(1000), --需要查询的列
@qTables varchar(1000), --需要查询的表 和条件
@iKey varchar (255), --标识字段
@oKey varchar(255), --排序字段
@pageSize int = 30, --每页的行数
@pageNumber int = 1, --要显示的页码
@sortType int = 0, --排序方式,0表示ASC,非0表示DESC
@IsReCount bit = 0 -- 非 0 值则返回记录总数,0返回记录集
AS
你参数接口根本没返回参数
WorkParam.Direction = ParameterDirection.Output;
这个是取什么值
WorkParam.Direction = ParameterDirection.Output;
这2句位置换下
//返回DataSet 和共含多少行数据的int,组成的数组
public object[] MyDataBindFunction (string strSQLpro,SqlParameter[] arrParameter)
{
cmd=new SqlCommand(strSQLpro,conn);
cmd.CommandType=CommandType.StoredProcedure;
ada = new SqlDataAdapter();
Mydataset = new DataSet();
if (arrParameter != null)
{
for (int i=0;i< arrParameter.Length;i++)
{
cmd.Parameters.Add(arrParameter[i]);
}
}
cmd.Parameters.Add(new SqlParameter("@RetrunValues", SqlDbType.Int));
cmd.Parameters["@RetrunValues"].Direction = ParameterDirection.ReturnValue;
ada.SelectCommand=cmd;
ada.Fill(Mydataset);
object[] output = new object[2];
output[0] = Mydataset;
output[1] = ada.SelectCommand.Parameters["@RetrunValues"].Value;
return output;
} CREATE PROCEDURE Index_Article_Class
....select @Countrows=count(ID) from Article where deleted=0 and pass=1
return @Countrows
GO