Remove Row from DataTable Depending on Condition

Question

I have a List that holds some IDs. I want to remove the rows from a DataTable where = ListLinkedIds

List<string> ListLinkedIds = new List<string>(); //This has values such as 6, 8, etc.
DataSet ds = new DataSet();
SqlDataAdapter da = null;
DataTable dt = new DataTable();



    da = new SqlDataAdapter("SELECT TicketID, DisplayNum, TranstypeDesc, SubQueueId, EstimatedTransTime,LinkedTicketId FROM vwQueueData WHERE (DATEADD(day, DATEDIFF(day, 0, Issued), 0) = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) AND QueueId = @QueueId AND SubQueueId = @SubQueueId   AND LinkedTicketId != @LinkedTicketId  AND Called IS NULL", cs);
   da.SelectCommand.Parameters.AddWithValue("@QueueId", Queue);
   da.SelectCommand.Parameters.AddWithValue("@SubQueueId", SubQueue);
   da.SelectCommand.Parameters.AddWithValue("@LinkedTicketId", ListLinkedIds[x]);
   da.Fill(ds);


//Removes from DataTable 
for (int x = 0; x < ListLinkedIds.Count(); x++)
{
   //Remove Row from DataTable Where ListLinkedIds[x]
}


gvMain.DataSource = ds;
gvMain.DataBind();

I tried dt.Rows.RemoveAt(remove) but that removes only the row number. I want to remove every row that is in the ListLinkedIds.

Possible duplicate: http://stackoverflow.com/a/5648389/2524304 — Maxim Zhukov, Jul 24 '13 at 17:43

score 10 · Accepted Answer · answered Jul 24 '13 at 17:46

10

Using LINQ you can create a new DataTable like:

DataTable newDataTable = dt.AsEnumerable()
                        .Where(r=> !ListLinkedIds.Contains(r.Field<string>("IDCOLUMN")))
                        .CopyToDataTable();

answered Jul 24 '13 at 17:46

Habib

219,104
29
407
436

http://stackoverflow.com/questions/3396669/how-to-remove-a-a-particular-row-value-from-a-datatable-based-on-a-condition a better option? – Ehsan Jul 24 '13 at 17:55
@EhsanUllah, not really sure, the 2nd approach is selecting everything that has to be removed and then removing it, if the `DataTable` size is not big enough then surly another copy of the `DataTable` can be created or iterate the datatable, find the row and then delete it – Habib Jul 24 '13 at 17:58
I have everything that has to be removed in the List. – Apollo Jul 24 '13 at 17:59
I uodated the code. Look at the SqlDataAdapter. The column is LinkedTicketId – Apollo Jul 24 '13 at 18:02
@Apollo, just replace that `IDCOLUMN` with your column name and give it a shot – Habib Jul 24 '13 at 18:15
I get this error: Unable to cast object of type 'System.Int32' to type 'System.String'. – Apollo Jul 24 '13 at 18:19
@Apollo, your list `ListLinkedIds` is of type `string` that is why I thought that the IDs in the table are of type `string`, change both of them to `int` – Habib Jul 24 '13 at 18:21
Did that. Nothing Happens, all the records still appear. – Apollo Jul 24 '13 at 18:23
@Apollo, are you changing the `DataSource` on your grid, now it should be pointing to your new DataTable – Habib Jul 24 '13 at 18:24
how can I display the results from that DataTable in my gridview – Apollo Jul 24 '13 at 18:25
1

@Apollo, `gvMain.DataSource = newDataTable;` – Habib Jul 24 '13 at 18:30
Warning: If all rows are removed, `CopyToDataTable()` will throw an exception. – Yehuda Shapira Jun 14 '16 at 12:19

score 3 · Answer 2 · answered Jul 24 '13 at 17:46

You can select the rows and then remove the returned result.

public void test() {
        List<string> ListLinkedIds = new List<string>(); //This has values such as 6, 8, etc.
        DataSet ds = new DataSet();
        SqlDataAdapter da = null;
        DataTable dt = new DataTable();

        //Removes from DataTable 
        for (int x = 0; x < ListLinkedIds.Count(); x++)
        {
            DataRow[] matches = dt.Select("ID='" + ListLinkedIds[x] + "'");
            foreach (DataRow row in matches) {
                dt.Rows.Remove(row);
            }
        }


    }

score 0 · Answer 3 · answered Jul 24 '13 at 17:50

0

Try Delete after datatable is populated.

for (int x = 0; x < ListLinkedIds.Count(); x++)
{
   foreach (DataRow dr in dt.rows)
        {
           if(dr["id"] == ListLinkedIds[x])
             dr.Delete();
        }
        dt.AcceptChanges();
}

answered Jul 24 '13 at 17:50

CodingSlayer

883
1
7
14

It only works for the first one, when it loops around it doesnt work for the next item on the list. – Apollo Jul 24 '13 at 18:05
I get Deleted row information cannot be accessed through the row. – Apollo Jul 24 '13 at 18:17

Remove Row from DataTable Depending on Condition

3 Answers3