[c#] A fast way to delete all rows of a datatable at once

I want to delete all rows in a datatable. I use something like this:

foreach (DataRow row in dt.Rows)
{
  row.Delete();
}
TableAdapter.Update(dt);

It works good but it takes lots of time if I have much rows. Is there any way to delete all rows at once?

That's the easiest way to delete all rows from the table in dbms via DataAdapter. But if you want to do it in one batch, you can set the DataAdapter's UpdateBatchSize to 0(unlimited).

Another way would be to use a simple SqlCommand with CommandText DELETE FROM Table:

using(var con = new SqlConnection(ConfigurationSettings.AppSettings["con"]))
using(var cmd = new SqlCommand())
{
    cmd.CommandText = "DELETE FROM Table";
    cmd.Connection = con;
    con.Open();
    int numberDeleted = cmd.ExecuteNonQuery();  // all rows deleted
}

But if you instead only want to remove the DataRows from the DataTable, you just have to call DataTable.Clear. That would prevent any rows from being deleted in dbms.

If you are really concerned about speed and not worried about the data you can do a Truncate. But this is assuming your DataTable is on a database and not just a memory object.

TRUNCATE TABLE tablename

The difference is this removes all rows without logging the row deletes making the transaction faster.

Just use dt.Clear()

Also you can set your TableAdapter/DataAdapter to clear before it fills with data.

This will allow you to clear all the rows and maintain the format of the DataTable.

dt.Rows.Clear();

There is also

dt.Clear();

However, calling Clear() on the DataTable (dt) will remove the Columns and formatting from the DataTable.

Per code found in an MSDN question, an internal method is called by both the DataRowsCollection, and DataTable with a different boolean parameter:

internal void Clear(bool clearAll)
{
    if (clearAll) // true is sent from the Data Table call
    {
        for (int i = 0; i < this.recordCapacity; i++)
        {
            this.rows[i] = null;
        }
        int count = this.table.columnCollection.Count;
        for (int j = 0; j < count; j++)
        {
            DataColumn column = this.table.columnCollection[j];
            for (int k = 0; k < this.recordCapacity; k++)
            {
                column.FreeRecord(k);
            }
        }
        this.lastFreeRecord = 0;
        this.freeRecordList.Clear();
    }
    else // False is sent from the DataRow Collection
    {
        this.freeRecordList.Capacity = this.freeRecordList.Count + this.table.Rows.Count;
        for (int m = 0; m < this.recordCapacity; m++)
        {
            if ((this.rows[m] != null) && (this.rows[m].rowID != -1))
            {
                int record = m;
                this.FreeRecord(ref record);
            }
        }
    }
}

As someone mentioned, just use:

dt.Rows.Clear()

Datatable.clear() method from class DataTable

Is there a Clear() method on the DataTable class??

I think there is. If there is, use that.

Why dont you just do it in SQL?

DELETE FROM SomeTable

Here is a clean and modern way to do it using Entity FW and without SQL Injection or TSQL..

using (Entities dbe = new Entities())
{
    dbe.myTable.RemoveRange(dbe.myTable.ToList());
    dbe.SaveChanges();
}

I using MDaf just use this code :

DataContext db = new DataContext(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
db.TruncateTable("TABLE_NAME_HERE");
//or
db.Execute("TRUNCATE TABLE TABLE_NAME_HERE ");

Here we have same question. You can use the following code:

SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings["yourconnectionstringnamehere"].ConnectionString;
con.Open();
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = "DELETE FROM [tablenamehere]";
SqlDataReader data = com.ExecuteReader();
con.Close();

But before you need to import following code to your project:

using System.Configuration;
using System.Data.SqlClient;

This is the specific part of the code that can delete all rows is a table:

DELETE FROM [tablenamehere]

This must be your table name:tablenamehere - This can delete all rows in table: DELETE FROM