Stored procedure returns two result sets

[Ken VdB]

Hi everyone,



I have a stored procedure called 'PagingTable' that I use for performing
searches and returning just the results for the current 'page'. That way I
can do my paging on the server and only the data that actually gets
displayed on the webpage is returned from the database server.



The sp returns two result sets. The first result set contains a single row
and columns telling me how many total results the query retuned, how many
'pages' (based on the number of items per page that I specify) and the
current page I am on. The second result set returns the rows of data for
the current page. In 'classic' ASP I handled it this way:



strSQL = "EXECUTE PagingTable " & _

"@ItemsPerPage = 10, " & _

"@CurrentPage = " & CStr(intCurrentPage) & ", " & _

"@TableName = 'Products', " & _

"@UniqueColumn = 'ItemNumber', " & _

"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _

"@WhereClause = '" & strSQLWhere & "'"



'Open the recordset

rsItems.Open strSQL, conn, 0, 1



'Get the values required for drawing the paging table

intCurrentPage = rsItems.Fields("CurrentPage").Value

intTotalPages = rsItems.Fields("TotalPages").Value

intTotalRows = rsItems.Fields("TotalRows").Value



'Advance to the next recordset

Set rsItems = rsItems.NextRecordset



Its that last line that was key. I could then go on loop over the results
and output them. How do I do this in ASP.NET 2.0? The DataSource control
only allows a single "DefaultView". So does the DataReader. How can I bind
a repeater control to the second result set (the actual data) and use the
info in the first result set to build a "pager" UI?



Thanks,



Ken VdB

 

[Rad [Visual C# MVP]]

Hi everyone,

I have a stored procedure called 'PagingTable' that I use for performing
searches and returning just the results for the current 'page'. That way I
can do my paging on the server and only the data that actually gets
displayed on the webpage is returned from the database server.

The sp returns two result sets. The first result set contains a single row
and columns telling me how many total results the query retuned, how many
'pages' (based on the number of items per page that I specify) and the
current page I am on. The second result set returns the rows of data for
the current page. In 'classic' ASP I handled it this way:

strSQL = "EXECUTE PagingTable " & _

"@ItemsPerPage = 10, " & _

"@CurrentPage = " & CStr(intCurrentPage) & ", " & _

"@TableName = 'Products', " & _

"@UniqueColumn = 'ItemNumber', " & _

"@Columns = 'ItemNumber, Description, ListPrice, QtyOnHand', " & _

"@WhereClause = '" & strSQLWhere & "'"

'Open the recordset

rsItems.Open strSQL, conn, 0, 1

'Get the values required for drawing the paging table

intCurrentPage = rsItems.Fields("CurrentPage").Value

intTotalPages = rsItems.Fields("TotalPages").Value

intTotalRows = rsItems.Fields("TotalRows").Value

'Advance to the next recordset

Set rsItems = rsItems.NextRecordset

Its that last line that was key. I could then go on loop over the results
and output them. How do I do this in ASP.NET 2.0? The DataSource control
only allows a single "DefaultView". So does the DataReader. How can I bind
a repeater control to the second result set (the actual data) and use the
info in the first result set to build a "pager" UI?

Thanks,

Ken VdB

If your stored procedure has two result sets, then you can make use of the
fact that if you populate a dataset with the stored procedure, the dataset
will have two data tables. You can then databind to each of the data tables