Remove datatable row if any column value is empty c#

Question

I need to remove the row from datatable even if any column in the datatable has empty values.

For Example :

My datatable has two columns

 Value     Input
  Dep     Phase 1
  Sec     
  Div     Phase 2

Result

 Value     Input
  Dep     Phase 1
  Div     Phase 2

I want to remove the second row as Input column has empty values. I'm stuck with the below code.

dtTable = dtTable.Rows.Cast<DataRow>()
          .Where(row => !row.ItemArray.All(field => field is DBNull || string.IsNullOrWhiteSpace(field as string))).
          .CopyToDataTable();

Can anyone suggest me how this can be achieved ?

@RahulAgarwal - I'm not able to delete a row even if any of its column has empty values. — vicky
– vicky, Commented Mar 6, 2018 at 12:33

Ehsan Sajjad · Accepted Answer · 2018-03-06 12:38:47Z

1

According to the requirements you are mentioning you would need to use Any which will check that if at least column has null value or empty string it would filter out that row. So, do like:

row => !row.ItemArray.Any(field => field is DBNull || 
                                string.IsNullOrWhiteSpace(field as string))

EDIT:

If your all columns are of type string then the above code would work fine, otherwise you will need to convert it to string explicitly by calling ToString():

row => !row.ItemArray.Any(field => field is DBNull || 
                                string.IsNullOrWhiteSpace(field.ToString()))

This will now only return those rows for which every column has value in it.

edited Mar 6, 2018 at 12:38

answered Mar 6, 2018 at 12:33

Ehsan Sajjad

62.6k16 gold badges112 silver badges171 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Oleksii Klipilin Over a year ago

what if column is not a string?

Ehsan Sajjad Over a year ago

then we would need to do some work to check for exact type or we can convert it to string via .ToString()

Waqas Shabbir Over a year ago

@Alexey, null should be converted to empty string and then after trim() function check length of that string either zero or not?

Tejas · Accepted Answer · 2018-03-06 12:58:16Z

1

If for loop can be used. We can check each row and column for nulls

    for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {

      if (dt.Rows[i]["col1"] == DBNull.Value || dt.Rows[i]["col2"] == DBNull.Value)
      {
           dt.Rows[i].Delete();
      }
    }

edited Mar 6, 2018 at 12:58

answered Mar 6, 2018 at 12:50

Tejas

10712 bronze badges

Comments

Ashwini GUPTA · Accepted Answer · 2021-06-11 14:05:04Z

0

Try this:

for (int i = dt.Rows.Count - 1; i >= 0; i--)
    {
    if (dt.Rows[i]["Column2"].ToString() == "")
        {
            dt.Rows[i].Delete();
            dt.AcceptChanges();
        }
    }

answered Jun 11, 2021 at 14:05

Ashwini GUPTA

276 bronze badges

1 Comment

4b0 Over a year ago

Code-only answers are not particularly helpful. Please add some descriptions of how this code solves the problem.

Chalaka Niroda · Accepted Answer · 2022-10-12 04:36:30Z

0

 for (int i = dt.Rows.Count - 1; i >= 0; i--)
                {
                    if (dt.Rows[i][1] == DBNull.Value)
                    {
                        dt.Rows[i].Delete();
                    }
                }
                dt.AcceptChanges();

it remove empty rows in th data table

answered Oct 12, 2022 at 4:36

Chalaka Niroda

1

Collectives™ on Stack Overflow

Remove datatable row if any column value is empty c#

4 Answers 4

EDIT:

3 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

EDIT:

3 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related