I want to update multiple rows of a Datatable based on a condition on column. I do not want to use for each as number of records are very high. I tried using LINQ but getting error. Please help

Help Studio
, ,
1

I want to update Status as “Not required” for all the rows in DT where the company code is not S1,S4, SR1 or SRS

DT.Select(“[Company Code] <> ‘A’ AND [Company Code] <> ‘B’ AND [Company Code] <> ‘C’ AND [Company Code] <> ‘D’”).ToList().ForEach(r=> { r[ “Status” ] = “Not Required”}).CopyToDataTable

It is giving syntax error. Even if I remove from above syntax.

2

Any help by anyone would be appreciated. Stuck on this from long. Thanks in Advance.

@lakshman Would you be able to help here.

3

Hi

Hope the below expression would help you resolve this

You need to write expression in Invoke Code activity.

dt.AsEnumerable().Where(Function(a) NOT a.Field(of String)(“Company Code”).ToString.Equals(“A”) AND NOT a.Field(of String)(“Company Code”).ToString.Equals(“B”) AND NOT a.Field(of String)(“Company Code”).ToString.Equals(“C”) AND NOT a.Field(of String)(“Company Code”).ToString.Equals(“D”)).ToList().ForEach(Sub(row) row(“Status”) = “Not Required”).CopyToDataTable()

Note: Create one argument for Invoke Code activity and mention properties as below.

Name - dt
Direction - In
Type - DataTable
Value - dt

Cheers @sumit.tyagi

4

Hi,

Do you try with C#? If so, the following helps you.

img20211013-2
img20211013-2495×660 13.1 KB 

arrayValidCode = new string[] {"S1","S4","SR1","SRS"};

dt.AsEnumerable().Where( r => !arrayValidCode.Contains( r.Field<string>("Company Code"))).Select(r => r["Status"]="Not Required").ToList();

Sample20211013-2CS.zip (2.9 KB)

Regards,

5

Got a lot of compile code error using this. Can you please test it once at your side and let me know the final query?

6

@Yoichi
I worked with this sample and its fine as below:
DT.AsEnumerable().Where( r => !ArrCompanyCodes.Contains( r.Field(“Company Code”))).Select(r => r[“Status”]=“Not Required”).ToList() ;

What if we need to update multiple columns. I tried as below but showing error while passing multiple arg in Select :
DT.AsEnumerable().Where( r => !ArrCompanyCodes.Contains( r.Field(“Company Code”))).Select(r => r[“Status”]=“Not Required”; r[“Remarks”]=“Comapny code not correct”).ToList() ;

7

Hi,

If you want to update 2 or more columns, can you try the following in Invoke Code?

DT.AsEnumerable().Where( r => !ArrCompanyCodes.Contains( r.Field<string>("Company Code")))
 .ToList<DataRow>()
 .ForEach(r => { 
    r["Status"] = "Not Required";
    r["Remarks"] = "Company code not correct";
  });

Regards,

8

@Yoichi
Thanks a lot it worked.
Can you also please help me with last query. I want to filter my Datatable using DT.Select in Assign statement which is filtered based on array of string. I do not want to use OR statements as I want comparison values coming from config.

DT.Select(“[Company Code]='”+ArrCompanyCodes+“'”).CopyToDataTable

Above exp is giving error.

Thanks,
Sumit Tyagi

9

Hi,

In this case, IN operator will help you.

DT.Select("[Company Code] IN ('"+string.Join("','",arrayCompanyCode)+"')").CopyToDataTable()

However If there is no special reason to use DataTable.Select method, I recommend to use LINQ Where method, because we can write it more simple.

Regards,

10

Can you please help me with some reference where I can study about these syntax for DT.Select or DT.Where and the above queries you helped with?

11

Hi,

Hope the following helps you

DataTable.Select method

The following is linked from the above page.

https://www.csharp-examples.net/dataview-rowfilter/

LINQ

For now, the following helps you.

Regards,

1 Like
12

Hi @Yoichi ,

I have a similar scenario but I want to put multiple conditions for different columns in where condition.
Below it the scenario for your reference where threshold is of type Double :

DT.AsEnumerable().Where(r => r(“total amount”) <= Threshold AND r(“Column name”)IS NULL)
.ToList()
.ForEach(r => {
r[“Status”] = “Completed”;
r[“Remarks”] = “XYZ”;
});

13

Hi @Yoichi ,

I am stuck at this since two days. Request you to please help to resolve my query.

Regards,
Ritika Singh.

14

Hi,

Which language do you use, C# or VB? Your expression seems to be mixed both.

Anyway, as this topic will be closed soon, perhaps you should raise new topic for it.

Regards,

15

Hi @Yoichi ,
I am a beginner,

I need to use C# in invoke code activity.

16

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.