c# - stored procedure returns many records but datareader sees only one -

i created sql server stored procedure in use cursor , created asp.net method execute procedure. i'm using sqldatareader , while(reader.read()) read values. problem cursor in stored procedure returns many rows, method reads first record only. can help?

stored procedure:

create procedure [dbo].[getmenususergroupcanview]  (   @usergroupid int  ,@languageid int ) begin      declare @menuid int      declare @title varchar(255)      declare db_cursor cursor     (         select menuid         triogate.dbo.sys_usergroupmenus         usergroupid=@usergroupid , viewflag='true'     )      open db_cursor        fetch next db_cursor @menuid        while @@fetch_status = 0 begin            select           sys_menus.menuid         ,sys_menus.parentmenuid         ,sys_menus.descriptionlabelid         ,sys_menus.titlelabelid         ,sys_menus.tooltiplabelid         ,sys_menus.[icon]         ,sys_menus.[menuname]         ,sys_menus.[menutypeid] menutype         ,[dbo].[get_parentmenu_name](sys_menus.parentmenuid) parentmenuname         ,[dbo].[get_label_description](sys_menus.titlelabelid,1) title         ,[dbo].[get_label_description](sys_menus.tooltiplabelid,1) tooltip         ,[dbo].[get_label_description](sys_menus.descriptionlabelid,1) [description]         ,sys_menus.[maintablename] [table]         ,sys_menus.[seq],sys_menus.[menupath],sys_menus.activatelog,sys_menus.menuid          sys_menus              left join sys_labeldetails             on sys_menus[titlelabelid] = sys_labeldetails[labelid]         sys_labeldetails.languageid = @languageid          , menuid = @menuid               fetch next db_cursor @menuid        end      close db_cursor       deallocate db_cursor   end 

method:

public list<menu> getmenususergroupcanview(int usergroupid, int languageid) {     list<menu> list = new list<menu>();     sqlcommand cmd = new sqlcommand("getmenususergroupcanview ", connection);     cmd.commandtype = commandtype.storedprocedure;     cmd.parameters.addwithvalue("@usergroupid", usergroupid);     cmd.parameters.addwithvalue("@languageid", languageid);     try     {          connection.open();         sqldatareader reader = cmd.executereader();         while (reader.read())         {             menu entry = new menu();             entry.menuid = (int)reader["menuid"];             entry.parentmenuid = (int)reader["parentmenuid"];             entry.activatelog = (bool)reader["activatelog"];             entry.description = reader["description"].tostring();             entry.descriptionlabelid = (int)reader["descriptionlabelid"];             entry.icon = (byte[])reader["icon"];             entry.maintablename = reader["table"].tostring();              entry.menuname = reader["menuname"].tostring();             entry.menupath = reader["menupath"].tostring();             entry.menutypeid = reader["menutype"].tostring();             entry.parentmenuname = reader["parentmenuname"].tostring();             entry.seq = (int)reader["seq"];              entry.title = reader["title"].tostring();             entry.titlelabelid = (int)reader["titlelabelid"];             entry.tooltip = reader["tooltip"].tostring();             entry.tooltiplabelid = (int)reader["tooltiplabelid"];             list.add(entry);         }      }     catch { }         {         connection.close();     }     return list; }      

each iteration of loop performs select. so, instead of 1 set of multiple records, you're getting multiple sets.

choose destiny:

a. (treat symptom): advance reader between resultsets idatareader.nextresult()

or b. (fix problem): there's no need cursor; use join instead:

select  * /* dump columns in here */ sys_usergroupmenus g     join sys_menus m         join sys_labeldetails d         on d.labelid = m.titlelabelid         , d.languageid = @languageid     on m.menuid = g.menuid g.usergroupid = @usergroupid , g.viewflag = 'true'