A fast way to delete all rows of a datatable at once

Question

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?

Answer 1

If you are running your code against a sqlserver database then
use this command

string sqlTrunc = "TRUNCATE TABLE " + yourTableName
SqlCommand cmd = new SqlCommand(sqlTrunc, conn);
cmd.ExecuteNonQuery();

this will be the fastest method and will delete everything from your table and reset the identity counter to zero.

The TRUNCATE keyword is supported also by other RDBMS.

5 years later:
Looking back at this answer I need to add something. The answer above is good only if you are absolutely sure about the source of the value in the yourTableName variable. This means that you shouldn't get this value from your user because he can type anything and this leads to Sql Injection problems well described in this famous comic strip. Always present your user a choice between hard coded names (tables or other symbolic values) using a non editable UI.

Answer 2

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);
            }
        }
    }
}

Answer 3

As someone mentioned, just use:

dt.Rows.Clear()

Answer 4

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.

Answer 5

Why dont you just do it in SQL?

DELETE FROM SomeTable

Answer 6

Just use dt.Clear()

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

Answer 7

TableAdapter.Update(dt.Clone());
//or
dt=dt.Clone();
TableAdapter.Update(dt);
//or
dt.Rows.Clear();
dt.AcceptChanges();
TableAdapter.Update(dt);

Answer 8

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.

Answer 9

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

Answer 10

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 ");

Answer 11

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();
}

Answer 12

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

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

Answer 13

Datatable.clear() method from class DataTable