(
@filter varchar(400),
@sort varchar(400),
@pageNum int,
@pageSize int
)
@filter 表示where限制，@sort 是orger by限制， 要求返回第pageNum页
我的代码如下：
AS
BEGIN
SET NOCOUNT ON
DECLARE @Where varchar(500)
DECLARE @OrderBy varchar(400)
DECLARE @SQL varchar(1000)
SET @Where = 'WHERE Conversation.ConversationTypeID = ConversationType.ConversationTypeID'
+ case when (@filter <> '') then (' AND ' + @filter) end
SET @OrderBy = case when (@sort = '') then '' else ('ORDER BY ' + @sort) end
SET @SQL = '
DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT,
@pagenumber INT,
@size INT
SET @pagenumber = ' + str(@pageNum) +
' SET @size = ' + str(@pageSize) +
' SELECT @rows = COUNT(*), @pages = COUNT(*) / @size FROM
Conversation, ConversationType ' + @Where +
'IF @rows % ' + str(@pageSize) + '!= 0 SET @pages = @pages + 1
IF ' + str(@pageNum) + ' < 1 SET @pagenumber = 1
IF ' + str(@pageNum) + ' > @pages SET @pagenumber = @pages
SET @ubound = ' + str(@pageSize) + ' * @pagenumber
SET @lbound = @ubound - (' + str(@pageSize) + ' - 1)
SELECT
CurrentPage = @pagenumber,
TotalPages = @pages,
TotalRows = @rows
SELECT
A.ConversationID,
A.ConversationStatusID,
A.ConversationTypeID,
A.InitiationDateTime,
B.Name
FROM
Conversation A,
ConversationType B '
+ @Where +
' GROUP BY
A.ConversationID,
A.ConversationStatusID,
A.ConversationTypeID,
A.InitiationDateTime,
B.Name
HAVING
COUNT(*) BETWEEN @lbound AND @ubound
' + @OrderBy + ' '
Exec(@SQL)
END
但现在问题问题是
// Create IDbCommand
SqlCommand command = new SqlCommand("SelectConversations", connection);
// set its type to CommandType.StoredProcedure
command.CommandType = CommandType.StoredProcedure;
// Add four parameters
.......
// Create new adapter with the IDbCommand
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.SelectCommand = command;
// Create DataSet that will hold results
DataSet dataset = new DataSet();
// Fill data set
adapter.Fill(dataset);
return dataset;
dataset中没有table。 如果在stored procedure中不用Exec（），直接用select是可返回table的，但是直接用select就我没法加上 where 和 order by 参数。
等待高手们提供方法。
