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

User · Answer

Why dont you just do it in SQL   DELETE FROM SomeTable

User · Answer

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

User · Answer

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

User · Answer

Is there a Clear   method on the DataTable class    I think there is   If there is  use that

User · Answer

As someone mentioned  just use   dt Rows Clear

User · Answer

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

User · Answer

Datatable clear   method from class DataTable

User · Answer

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

User · Answer

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  lt  this recordCapacity  i                          this rows i    null                    int count   this table columnCollection Count          for  int j   0  j  lt  count  j                          DataColumn column   this table columnCollection j               for  int k   0  k  lt  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  lt  this recordCapacity  m                          if   this rows m     null   amp  amp   this rows m  rowID    -1                                 int record   m                  this FreeRecord ref record

User · Answer

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

User · Answer

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

User · Answer

Just use dt Clear    Also you can set your TableAdapter DataAdapter to clear before it fills with data

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

Examples related to c#

Examples related to datatable