>>Hi everybody,
>>
>>In VFP I have code that first gets data from SQL Server into a cursor and then does extra select from that cursor into another cursor applying some extra formatting.
>>
>>I am wondering what is the best way to simulate this in C#? I understand I can get dataset (or reader) and loop through every row and generate a new dataset.
>>
>>Is there another way using LINQ, for example? If yes, can you show a sample, please?
>>
>>Thanks in advance.
>
>I don't want to be "that" guy, but... is there a reason you're using datasets? With custom classes you have much more flexibility.

I am not using datasets to do anything, I just meant I am using ADO.NET directly to get my data (either as a reader or a dataset). Once I get the data I transform it to XML and pass back.

I am converting VFP code to C# code. The procedures I've converted so far were simple (I started from simpler methods in that class). Usually it's just one call by either stored procedure or inline SQL.

So, this is VFP code I'm working on right now:

      text to lcSQL textmerge noshow
      select s.resource1, s.booking_id, s.start_time, s.end_time, s.flex_tspan,
        s.layer, s.backcolor, s.forecolor, s.clip_sp, s.clip_loc,
        s.department, s.category, s.item, s.lsndescrip,
        case when s.resource1=s.resrc_req1 then 'R' when len(s.resrc_req1)=0 then '' else 'r' end+
        case when s.masterbook=0 then '' else 'S' end+
        case when s.amt_paid>=s.price then '' when s.amt_paid=0.00 then 'U' else 'u' end as modifiers,
        cast(case when s.guest_no=0 then s.temp_name else rtrim(g.last_name)+' '+rtrim(g.first_name) end as char(36)) as gst_name,
        s.locked, len(cast(s.notes as varchar(10))) as notelength
      from dbo.b_sched s
       left join dbo.guests g WITH (index = guests_guest_no) on s.guest_no=g.guest_no
      where s.resource1=<<.VFP2SQL(padr(tcInstr,12))>>
       and s.start_time < <<.VFP2SQL(ttEnd)>>
       and s.end_time > <<.VFP2SQL(ttStart)>>
       and s.is_pod=0
      order by s.resource1, s.layer, s.start_time
      ENDTEXT
      lcSQLReturn=.mysqlexec(lcSQL, 'csrTemp', program())
      if !lcSQLReturn=='OK'
         lcRetVal='400-Line: '+transform(lineno())+' '+lcSQLReturn
         .write_log(lcRetVal, program(), 'E')
         .write_log('Returning: '+lcRetVal, program(), '2')
         return .RetValToSTR(lcRetVal)
      endif

&&now format it for display
      select resource1, booking_id, start_time, end_time, flex_tspan,;
         layer, backcolor, forecolor, clip_sp, clip_loc,;
         padr(space(iif(locked,2,0))+iif(empty(department+category+item),replicate('**'+alltrim(lsndescrip)+'** ',40),;
         alltrim(modifiers)+' '+proper(alltrim(gst_name))+' '+;
         alltrim(lsndescrip)+' '+alltrim(str(booking_id,16))),200) as display_msg, locked, ;
         iif(nvl(notelength,0)>0, .t., .f.) as isnotes ;
         from csrTemp;
         into cursor csrRetCursor

      do case
      case tcReturnType=='XML'
         lvRetVal='0-'+.xml_dbf2str('CSRRETCURSOR',-1)
         .write_log('Returning: '+lvRetVal, program(), '2')
      case tcReturnType=='XM2'
         lvRetVal='0-'+.xm2_dbf2str('CSRRETCURSOR',-1)
         .write_log('Returning: '+lvRetVal, program(), '2')
      otherwise                     &&default to ADO
         lvRetVal=.ado_dbf2rs('CSRRETCURSOR',-1)
         .write_log('Returning: {ADO RecordSet}', program(), '2')
      endcase

      return .RetValToSTR(lvRetVal)

I actually have a stored procedure that returns the first part (which is in the text to lcSQL commands - the procedure has 2 additional JOINs I don't need, but I think I'll go with the procedure anyway). So, I'll get the above into a datatable and then I would need to simulate the && now format to display part.