>>>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.

Sorry, I misinterpreted your question. Allergy season. :-)

I don't know if this really applies in your case, but I do something somewhat similar in MVC when I populate my ViewModels. I have a rich domain model that returns my object from the DB, and then I take that object and use Linq to project it into my ViewModel, usually with customizations.

Do you have an established DAL? If not, I would try to create one using some sort of ORM. You don't have to use a heavyweight like NHibernate or Entity Framework, but you shouldn't be working with raw DataReaders at this point. Consider this post from Jimmy Bogard: http://lostechies.com/jimmybogard/2012/07/24/dont-write-your-own-orm/

After you grab your object you can project it to a customized DTO using Linq: http://social.msdn.microsoft.com/forums/en-US/linqprojectgeneral/thread/a697e65a-de98-4939-aa6a-19b4514f744f

And then you can take that and serialize to XML: http://stackoverflow.com/questions/4468478/c-sharp-object-to-xml