How to delete all rows from datatable
I want to delete all rows from datatable with rowstate property value Deleted.
开发者_开发百科DataTable dt;
dt.Clear(); // this will not set rowstate property to delete.
Currently I am iterating through all rows and deleting each row.
Is there any efficient way? I don't want to delete in SQL Server I want to use DataTable method.
We are using this way:
for(int i = table.Rows.Count - 1; i >= 0; i--) {
DataRow row = table.Rows[i];
if ( row.RowState == DataRowState.Deleted ) { table.Rows.RemoveAt(i); }
}
This will satisfy any FK cascade relationships, like 'delete' (that DataTable.Clear() will not):
DataTable dt = ...;
// Remove all
while(dt.Count > 0)
{
dt.Rows[0].Delete();
}
dt.Rows.Clear();
dt.Columns.Clear(); //warning: All Columns delete
dt.Dispose();
I typically execute the following SQL command:
DELETE FROM TABLE WHERE ID>0
Since you're using an SQL Server database, I would advocate simply executing the SQL command "DELETE FROM " + dt.TableName
.
I would drop the table, fastest way to delete everything. Then recreate the table.
You could create a stored procedure on the SQL Server db that deletes all the rows in the table, execute it from your C# code, then requery the datatable.
Here is the solution that I settled on in my own code after searching for this question, taking inspiration from Jorge's answer.
DataTable RemoveRowsTable = ...;
int i=0;
//Remove All
while (i < RemoveRowsTable.Rows.Count)
{
DataRow currentRow = RemoveRowsTable.Rows[i];
if (currentRow.RowState != DataRowState.Deleted)
{
currentRow.Delete();
}
else
{
i++;
}
}
This way, you ensure all rows either get deleted, or have their DataRowState set to Deleted.
Also, you won't get the InvalidOperationException due to modifying a collection while enumerating, because foreach
isn't used. However, the infinite loop bug that Jorge's solution is vulnerable to isn't a problem here because the code will increment past a DataRow whose DataRowState has already been set to Deleted.
精彩评论