>>>>>>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.
>>>>
>>>>This is what I started to write
>>>>
>>>>
>>>>var results = from r in ds.Tables[0].AsEnumerable()
>>>> select new {resource1 = r.Field<String>("resource1") };
>>>>>>>>
>>>>My problem is that I will need to pass that result as datatable to another method.
>>>>
>>>>I am not sure how can I do that - transform this LINQ result as datatable?
>>>
>>>Try this (untested):
>>>
>>>var newTable = new DataTable();
>>>newTable.Columns.Add("resource1");
>>>var results = (from r in ds.Tables[0].AsEnumerable()
>>> select newTable.Rows.Add(r.Field<String>("resource1")))
>>> .ToList(); // Force immediate execution
>>>// newTable has the results as a DataTable, results has it as a List< DataRow>
>>>>>>
>>>or
>>>
>>>
>>>var newTable = new DataTable();
>>>newTable.Columns.Add("resource1");
>>>var results = ds.Tables[0].AsEnumerable()
>>> .Select(r =>
>>> {
>>> var newRow = newTable.NewRow();
>>> newRow["resource1"] = r["resource1"]; // Or loop through ds.Tables[0].Columns
>>> return newRow;
>>> })
>>> .CopyToDataTable();
>>>// results is the new DataTable, newTable is only used for the structure
>>>>>
>>This is not giving me the benefits of LINQ I was hoping to achieve here. I could have just created new table, loop the original manually and insert rows.
>>
>>Here is the code I am trying to convert
>>
>>
>> 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
>>>>
>>and I am already a bit stuck here
>>
>>
>>var results = from r in ds.Tables[0].AsEnumerable()
>> select new {resource1 = r.Field<String>("resource1"),
>> booking_id = r.Field<Int64>("booking_id"),
>> start_time = r.Field<DateTime>("start_time"),
>> end_time = r.Field<DateTime>("end_time"),
>> flex_tspan = r.Field<Boolean>("flex_tspan"),
>> layer = r.Field<Int16>("layer"),
>> backcolor = r.Field<Int32>("backcolor"),
>> forecolor = r.Field<Int32>("forecolor"),
>> clip_sp = r.Field<String>("clip_sp"),
>> clip_loc = r.Field<Int16>("clip_loc"),
>> display_msg = ("".PadRight((r.Field<Boolean>("locked"))?2:0))
>> };>>
>>So, I already have troubles figuring out the display_msg 1 line conversion.
>
>So is there a reason you didn't create a new table an manually loop through the rows? You are trying to force LINQ to do something it wasn't designed to do (create a new DataTable with a different structure). If you need the results as a DataTable, it needs to have a structure defined so that DataRows can be created for it. If you don't want to do that, you could write a method that would convert an IEnumberable< object> to a DataTable. Or you could create a class to hold the results of your query and use that instead of a DataTable to pass the results to the other function.
Agreed, not sure why it's coming back to a DataTable after being transformed. I thought it was going to be used as XML.
