>>>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-19b4514f744fAnd then you can take that and serialize to XML:
http://stackoverflow.com/questions/4468478/c-sharp-object-to-xml