Link to home
Create AccountLog in
Avatar of shelbyinfotech
shelbyinfotech

asked on

Need a good article or reference on using multiple recordsets returned from a stored procedure to VB.NET

I have written a generic stored procedure that creates statements given parameters, it returns two recordsets, one containing paging information and another that contains the actual data meant to be returned.

I am used to working with datareader, but can see an advantage in using dataset, but am not quite sure on how to reference each one individually.

You can copy and paste the code to see what i am talking about. Usage by command line is like this:


EXEC      [dbo].[GenericDataPager]
            @datasrc = N'Tablename',
            @orderBy = N'FieldToSortBy',
        @fieldlist =N'Comma,Delimited,List,Of,Fields',
            @filter = N'FIELD = VALUE',
            @pageNum = 3, <-PAGE TO START ON
            @pageSize = 10 <- ITEMS PER PAGE


You will notice it returns two recordsets
CREATE PROCEDURE [dbo].[GenericDataPager]
-- 
-- 
-- @datasrc      - the table (or stored procedure, etc.) name 
-- @orderBy - the ORDER BY clause 
-- @fieldlis - the fields to return (including calculated expressions) 
-- @filter - the WHERE clause 
-- @pageNum - the page to return (must be greater than or equal to one) 
-- @pageSize - the number of records per page 

  @datasrc nvarchar(200)
 ,@orderBy nvarchar(200)
 ,@fieldlist nvarchar(200) = '*'
 ,@filter nvarchar(200) = ''
 ,@pageNum int = 1
 ,@pageSize int = NULL
AS
  SET NOCOUNT ON
  DECLARE
     @STMT nvarchar(max)         -- SQL to execute
    ,@recct int                  -- total # of records (for GridView paging interface)

  IF LTRIM(RTRIM(@filter)) = '' SET @filter = '1 = 1'
  IF @pageSize IS NULL BEGIN
    SET @STMT =  'SELECT   ' + @fieldlist + 
                 'FROM     ' + @datasrc +
                 'WHERE    ' + @filter + 
                 'ORDER BY ' + @orderBy
    EXEC (@STMT)                 -- return requested records 
  END ELSE BEGIN
    SET @STMT =  'SELECT   @recct = COUNT(*)
                  FROM     ' + @datasrc + '
                  WHERE    ' + @filter
    EXEC sp_executeSQL @STMT, @params = N'@recct INT OUTPUT', @recct = @recct OUTPUT
   
                    
    DECLARE
      @lbound int,
      @ubound int

    SET @pageNum = ABS(@pageNum)
    SET @pageSize = ABS(@pageSize)
    IF @pageNum < 1 SET @pageNum = 1
    IF @pageSize < 1 SET @pageSize = 1
    SET @lbound = ((@pageNum - 1) * @pageSize)
    SET @ubound = @lbound + @pageSize + 1
    IF @lbound >= @recct BEGIN
      SET @ubound = @recct + 1
      SET @lbound = @ubound - (@pageSize + 1) -- return the last page of records if                                               -- no records would be on the
                                              -- specified page

    END

   SELECT CAST(Round(CAST(@recct AS DEC(6, 2)) / CAST(@pageSize AS DEC(6, 2)), 0)AS INT) AS NumberOfPages,
       (( ( @pageNum - 1 ) * @pageSize )) + 1 AS RecordFrom,
       @lbound + @pageSize                    AS RecordTo,
       @recct                                 AS NumberOfRows -- return the total # of Pages

    SET @STMT =  'SELECT  ' + @fieldlist + '
                  FROM    (
                            SELECT  ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS row, *
                            FROM    ' + @datasrc + '
                            WHERE   ' + @filter + '
                          ) AS tbl
                  WHERE
                          row > ' + CONVERT(varchar(9), @lbound) + ' AND
                          row < ' + CONVERT(varchar(9), @ubound)
    EXEC (@STMT)                 -- return requested records 
  END

GO

Open in new window

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

With DataReader, you can use the NextResult() method to advance to the next resultset.

Or you can use a DataSet and if there are two resultsets, you will get two tables back which you can reference like

dSet.Tables(0)
dSet.Tables(1)
etc
Avatar of shelbyinfotech
shelbyinfotech

ASKER

And to reference a field in dSet.Table(0) ?
dSet.Tables(0).Rows(rowindex).Item(columnindex)
You can loop using

For i As Integer = 0 to dSet.Tables(0).Rows.Count - 1
      msgbox dSet.Tables(0).Rows(i).Item(0)
Next
Ok , I got fields KEY,LAST_NAME,FIRST_NAME , I want to assign the variable RETRIEVED_NAME to FIRST_NAME
In the case above, FIRST_NAME is item(2) ...can it be referenced by FIRST_NAME ?
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you Very , Very Much
Glad to help :-)