[c#] Deleting row from datatable in C#

I'm having a problem deleting rows from a datatable. In my program, I am reading info from a database into a datatable using an sql query. I use an oledb connection and the code dt.Load(command.ExecuteReader()); to do this. Later, I want to delete rows that match an id string. I have tried the following code buy cannot get it to work:

            DataRow[] drr = dt.Select("Student=' " + id + " ' "); 
            for (int i = 0; i < drr.Length; i++)
                dt.Rows.Remove(drr[i]);
            dt.AcceptChanges();

Can anyone please suggest another way with an example?

There several different ways to do it. But You can use the following approach:

List<DataRow> RowsToDelete = new List<DataRow>();

for (int i = 0; i < drr.Length; i++) 
{     
   if(condition to delete the row) 
   {  
       RowsToDelete.Add(drr[i]);     
   } 
}

foreach(var dr in RowsToDelete) 
{     
   drr.Rows.Remove(dr); 
} 

I see a number of answers using the Remove method and others using the Delete method.

Remove (according to the docs) will immediately remove the record from the (local) table, and on Update, will not remove a missing record.

Delete in comparison changes the RowState to Deleted, and will update the server table on Update. Likewise, calling the AcceptChanges method before the Update to the server table will reset all your RowState(s) to Unchanged and nothing will flow to the server. (Still nursing my thumb after hitting this a number of times).

If you want to remove the entire row from DataTable ,

try this

DataTable dt = new DataTable();  //User DataTable
DataRow[] rows;
rows = dt.Select("Student=' " + id + " ' ");
foreach (DataRow row in rows)
     dt.Rows.Remove(row);

This question will give you good insights on how to delete a record from a DataTable:

DataTable, How to conditionally delete rows

It would look like this:

DataRow[] drr = dt.Select("Student=' " + id + " ' "); 
foreach (var row in drr)
   row.Delete();

Don't forget that if you want to update your database, you are going to need to call the Update command. For more information on that, see this link:

http://www.codeguru.com/forum/showthread.php?t=471027

Advance for loop works better for this case

public void deleteRow(DataRow selectedRow)
        {
            foreach (DataRow  in StudentTable.Rows)
            {
                if (SR[TableColumn.StudentID.ToString()].ToString() == StudentIndex)
                    SR.Delete();
            }

            StudentTable.AcceptChanges();
        }

a simple example : http://www.dotnetspark.com/tutorial/13-42-delete-row-from-datatable.aspx

Does this work for you?

I think the reason the OPs code does not work is because once you call Remove you are changing the Length of drr. When you call Delete you are not actually deleting the row until AcceptChanges is called. This is why if you want to use Remove you need a separate loop.

Depending on the situation or preference...

string colName = "colName";
string comparisonValue = (whatever it is).ToString();
string strFilter = (dtbl.Columns[colName].DataType == typeof(string)) ? "[" + colName + "]='" + comparisonValue + "'" : "[" + colName + "]=" + comparisonValue;
string strSort = "";

DataRow[] drows = dtbl.Select(strFilter, strSort, DataViewRowState.CurrentRows);

Above used for next two examples

foreach(DataRow drow in drows)
{
   drow.Delete();//Mark a row for deletion.
}
dtbl.AcceptChanges();

OR

foreach(DataRow drow in drows)
{
   dtbl.Rows[dtbl.Rows.IndexOf(drow)].Delete();//Mark a row for deletion.
}
dtbl.AcceptChanges();

OR

List<DataRow> listRowsToDelete = new List<DataRow>();

foreach(DataRow drow in dtbl.Rows)
{
   if(condition to delete)
   {
      listRowsToDelete.Add(drow);
   }
}

foreach(DataRow drowToDelete in listRowsToDelete)
{
   dtbl.Rows.Remove(drowToDelete);// Calling Remove is the same as calling Delete and then calling AcceptChanges
}

Note that if you call Delete() then you should call AcceptChanges() but if you call Remove() then AcceptChanges() is not necessary.

Also, here is a good link on row filter syntax.