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