question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked cooldadtx answered

How to Insert and update table using LINQ

i want to update data table data. i found this code sample https://stackoverflow.com/questions/19855772/update-two-columns-in-a-datatable-using-linq

see my screen shot first.
85434-capture2.png

suppose my data table looks like as per screen shot. now i want to update data table data using or if data not found then add data to that data table by linq.

now see my picture that periods are column header name which putting me in problem

var rowsToUpdate =
oldSP.AsEnumerable().Where(r => r.Field<string>("Section") == "Consensus Model"
&& r.Field<string>("LineItem") == "Net Revenue"
&& r.Field<string>("2010 FYA"));

my problem start here r.Field<string>("2010 FYA") 2010 FYA is column header then how could i mention 2010 FYA in LINQ.

basically i need to get this value by 1608.6500 by LINQ query. if i can get this value by LINQ then i can update data like this way

 foreach(var row in rowsToUpdate)
 {
     row.SetField("2010 FYA", "100.22");
 }

please help me to update period value which is coming as data table column name. provide a sample code. thanks









dotnet-csharp
capture2.png (369.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

cooldadtx avatar image
0 Votes"
cooldadtx answered

To clarify LINQ is query only. You cannot use LINQ to update anything. You can use LINQ to find the data you want to update and then, using whatever update mechanism that type supports, update the data. In your case you can use LINQ to find the DataRow values you want to change, then using the standard ADO.NET infrastructure of a DataAdapter update the rows.

my problem start here r.Field<string>("2010 FYA") 2010 FYA is column header then how could i mention 2010 FYA in LINQ.

You are already doing that correctly via the r.Field<decimal>("2010 FYA") logic. That gives you back the value of the column. I assume it is a decimal value given that it looks like monetary values.

basically i need to get this value by 1608.6500 by LINQ query.

I don't know what you mean by that. The aforementioned code would return back the value so you can use it in whatever expression you need to filter down the rows.

&& r.Field<decimal>("2010 FYA") > 10_000


The only issue you might run into is that it appears some of the columns are null (or perhaps empty strings). In that case the call will fail so you'll want to account for that. For example the following query in your LINQ would return any rows that have a value and that value is greater than 10K.

&& !r.IsNull("2010 FYA") && r.Field<decimal>("2010 FYA") > 10_000


now i want to update data table data using or if data not found then add data to that data table by linq.

LINQ only returns data that matches the conditions you do, again it is a query library. So I'm not sure what you mean by "if data not found". If an entire row is not found then LINQ won't return it as it doesn't exist. If you want to add new rows to your data then you're just talking about standard DataTable calls. Use NewRow to create the row and then add it to the table.

If you want to set the value for rows that don't have a value in that column then don't filter them out as part of your LINQ query. Instead in the foreach check the value and adjust accordingly.

foreach (var row in rowsToUpdate)
{
   if (row.IsNull("2010 FYA"))
       row.SetField("2010 FAY", 123);
}













5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.