none
invoke stored procedure from my c# application and get the results in chunks? RRS feed

  • Question

  • Hi There,

    I have a stored procedure that returns the results in chunks like this,

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[spPaging]
    @page_size INT,
    @page_nbr INT
    AS
    SELECT * FROM
    (
    SELECT distinct ROW_NUMBER() OVER (ORDER BY J.JobID ) rn ,J.JobID, CE.Name    as EmployerName,  CTitle.CleanTitle,VCJL.City +''+VCJL.State AS City ,VCJL.County,C.Date,OC.OccNameCode as ONETCODE,NB.Code as NAICSCODE,JF.MetaJobText,jinfo.url  from dw_Job J (NOLOCK)
    left outer join jobsft JF (NOLOCK) ON JF.JOBID = J.JOBID  
    left outer join jobinfo JINFO (NOLOCK) ON JINFO.JOBID=J.JOBID  
    inner join dw_OnetCode OC (NOLOCK) on OC.ID=J.OnetID
    inner join vw_NAICSBase01 NB (NOLOCK) on NB.ID=J.NAICSID   
    inner join vw_CanonJobLocation VCJL on VCJL.ID = J.CanonJobLocationID
    inner join CanonEmployer CE (NOLOCK) on CE.ID = J.EmployerID
    inner join dw_CleanTitle CTitle (NOLOCK) on CTitle.ID = J.CleanTitleID
    inner join Calendar C (NOLOCK) on C.ID = J.JobDateID  Where (  (C.year=2011)
     and   ( VCJL.CanonCountryID = 6) )
    ) AS Der WHERE rn > (@page_nbr - 1) * @page_size
       AND rn <= @page_nbr * @page_size;
    GO

    Now i want to invoke it from my c# application and get the results in chunks,somethings like this?

    But when i say nextresult() its not working,

    while (r.Read())   

                              {                                                                             
                                jobID = Convert.ToString(r.GetValue(0));
                                employer = Convert.ToString(r.GetValue(1));
                                title = Convert.ToString(r.GetValue(2));
                                city = Convert.ToString(r.GetValue(3));
                                county = Convert.ToString(r.GetValue(4));
                                jobdate = Convert.ToString(r.GetValue(5));
                                onet = Convert.ToString(r.GetValue(6));
                                naics = Convert.ToString(r.GetValue(7));
                                jobtext = Convert.ToString(r.GetValue(8));
                                joburl = Convert.ToString(r.GetValue(9));

                                jList.Add(new ExportJobList(jobID, title, employer, city, county, jobtext, jobdate, joburl, naics, onet));
                                recordCount++;
                            }
                            r.NextResult(); //something is wrong here.
                            while (r.Read())
                            {
                                jobID = Convert.ToString(r.GetValue(0));
                                employer = Convert.ToString(r.GetValue(1));
                                title = Convert.ToString(r.GetValue(2));
                                city = Convert.ToString(r.GetValue(3));
                                county = Convert.ToString(r.GetValue(4));
                                jobdate = Convert.ToString(r.GetValue(5));
                                onet = Convert.ToString(r.GetValue(6));
                                naics = Convert.ToString(r.GetValue(7));
                                jobtext = Convert.ToString(r.GetValue(8));
                                joburl = Convert.ToString(r.GetValue(9));

                                jList.Add(new ExportJobList(jobID, title, employer, city, county, jobtext, jobdate, joburl, naics, onet));
                                recordCount++;                       
                            }

    Please do help me out.

    Thanks,

    Aish

    Monday, April 9, 2012 6:14 AM

Answers

  • NextResult is used for multiple resultsets. Your stored procedure is only returning a single resultset.

    When you want to retrieve the next page of data another call to the stored procedure will be required with the next page number and size.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by IshNair Thursday, April 12, 2012 11:41 AM
    Monday, April 9, 2012 2:30 PM

All replies

  • NextResult is used for multiple resultsets. Your stored procedure is only returning a single resultset.

    When you want to retrieve the next page of data another call to the stored procedure will be required with the next page number and size.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by IshNair Thursday, April 12, 2012 11:41 AM
    Monday, April 9, 2012 2:30 PM
  • Paul you are correct, and i tried other way doing it and its possible.

    I tried to capture the chunks within the while (r.Read()) itself.

    Thanks for your time.

    Thursday, April 12, 2012 11:41 AM