Delete row from datatable where first column does not contain (some string)

Question

I have DataTable with the following columns:

ClientID date numberOfTransactions price

ClientID is of type string and I need to ensure that its contents include "A-" and "N6" for every value in the table.

I need to delete all rows from the DataTable where this first column (ClientID) does not contain both "A-" and "N6" (some totals and other unnecessary data). How can I select and delete these rows specifically from the DataTable?

I know this:

foreach (DataRow row in table.Rows) // Loop over the rows.
    {

        //Here should come part "if first column contains mentioned values
    }

I also know this

If (string.Contains("A-") == true &&  string.Contains("N6") == true)

{
//Do something
}

I need help how to implement this for first column of each row.

What query have you tried ..? also what DataBase are you using SQL Server, MYSQL etc...? — MethodMan
– MethodMan, Commented Jan 25, 2012 at 13:58
I believe the OP just wants to remove certain DataRows from the DataTable.Rows collection (in memory), so knowing where the data is coming from might actually be irrelevant (hence his/her confusion). — Chris Barlow
– Chris Barlow, Commented Jan 25, 2012 at 14:51

Chris Barlow · Accepted Answer · 2012-01-25 15:09:26Z

6

Try this:

EDIT: Totally messed up that last line, so if you tried it, try it now that I made it not stupid. =)

List<int> IndicesToRemove = new List<int>();
DataTable table = new DataTable(); //Obviously, your table will already exist at this point
foreach (DataRow row in table.Rows)
{
   if (!(row["ClientID"].ToString().Contains("A-") && row["ClientID"].ToString().Contains("N6")))
      IndicesToRemove.Add(table.Rows.IndexOf(row));
}
IndicesToRemove.Sort();
for (int i = IndicesToRemove.Count - 1; i >= 0; i--) table.Rows.RemoveAt(IndicesToRemove[i]);

edited Jan 25, 2012 at 15:09

answered Jan 25, 2012 at 15:00

Chris Barlow

3,3124 gold badges34 silver badges52 bronze badges

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

Comments

Bravo · Accepted Answer · 2012-01-27 06:37:57Z

2

try using this,

assuming dt as your Datatabe object and ClientID as your first column (hence using ItemArray[0])

for(int i=0; i<dt.Rows.Count; i++)
{
  temp = dt.Rows[i].ItemArray[0].ToString();

if (System.Text.RegularExpressions.Regex.IsMatch(temp, "A-", System.Text.RegularExpressions.RegexOptions.IgnoreCase) || System.Text.RegularExpressions.Regex.IsMatch(temp, "N6", System.Text.RegularExpressions.RegexOptions.IgnoreCase))
   {
     dt.Rows.RemoveAt(i);
     i--;
   }
 }

Simple and straight forward solution... hope it helps

edited Jan 27, 2012 at 6:37

answered Jan 25, 2012 at 15:03

Bravo

3,4814 gold badges31 silver badges44 bronze badges

4 Comments

Chris Barlow Over a year ago

If you do it this way, don't you risk skipping over rows during your iteration? Let's say i = 2 and you find a row that needs to be removed. You remove row (2) which adjusts all other rows' indices - but now i = 3, so the new (2) didn't get evaluated, right?

Chris Over a year ago

Agreed, removing from a collection while iterating through it will cause problems.

Bravo Over a year ago

@C.Barlow, you are right. my mistake. forgot to add one more line that was : "i--;" That's it.

SKR Over a year ago

I was facing the same problem, found this answer, works like a charm :)

Mabdullah · Accepted Answer · 2012-03-26 13:09:56Z

2

this should be more efficient, both in lines of Code and Time, try this :)

for(int x=0; x<table.Rows.Count;)
{
   if (!table.Rows[x].ItemArray[0].contains("A-") && !table.Rows[x].ItemArray[0].contains("N6"))
      table.Rows.RemoveAt(x);
   else x++;
}

Happy Coding

edited Mar 26, 2012 at 13:09

answered Jan 25, 2012 at 15:31

Mabdullah

1,0136 silver badges26 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:21:57Z

Preface: C.Barlow's existing answer is awesome, this is just another route someone could take.

This is one way to do it where you never have to loop all the way through the original table (by taking advantage of the DataTable.Select() method):

DataTable table = new DataTable(); // This would be your existing DataTable
// Grab only the rows that meet your criteria using the .Select() method
DataRow[] newRows = table.Select("ClientID LIKE '%A-%' AND ClientID LIKE '%N6%'");
// Create a new table with the same schema as your existing one.
DataTable newTable = table.Clone();
foreach (DataRow r in newRows)
{
    // Dump the selected rows into the table.
    newTable.LoadDataRow(r.ItemArray, true);
}

And now you have a DataTable with only the rows you want. If necessary, at this point you could clear out the original table and replace it with the contents of the new one:

table.Clear();
table = newTable.Copy();

Edit: I thought of a memory optimization last night, you can just overwrite the existing table once you have the rows you need, which avoids the need for the temporary table.

DataTable table = new DataTable(); // This would be your existing DataTable
// Grab only the rows that meet your criteria using the .Select() method
DataRow[] newRows = table.Select("ClientID LIKE '%A-%' AND ClientID LIKE '%N6%'");
// Clear out the old table
table.Clear();
foreach (DataRow r in newRows)
{
    // Dump the selected rows into the table.
    table.LoadDataRow(r.ItemArray, true);
}

Collectives™ on Stack Overflow

Delete row from datatable where first column does not contain (some string)

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related