C# 一次删除数据表所有行的快速方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11103181/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A fast way to delete all rows of a datatable at once
提问by oMatrix
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?
它工作得很好，但如果我有很多行，它会花费很多时间。有没有办法一次删除所有行？
采纳答案by Steve
If you are running your code against a sqlserver database then
use this command
如果您针对 sqlserver 数据库运行代码，请
使用此命令
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.
其他 RDBMS 也支持 TRUNCATE 关键字。
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 yourTableNamevariable. 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.
5 年后：
回顾这个答案，我需要补充一些东西。仅当您绝对确定yourTableName变量中的值的来源时，上述答案才是正确的。这意味着你不应该从你的用户那里得到这个值，因为他可以输入任何东西，这会导致 Sql Injection 问题在这个著名的漫画中得到了很好的描述。始终使用不可编辑的 UI 向用户展示硬编码名称（表或其他符号值）之间的选择。
回答by Kell
Why dont you just do it in SQL?
为什么不直接用 SQL 来做呢？
DELETE FROM SomeTable
回答by Robert Iver
Is there a Clear() method on the DataTable class??
DataTable 类上是否有 Clear() 方法？
I think there is. If there is, use that.
我认为有。如果有，请使用它。
回答by Sebastian
Datatable.clear()method from class DataTable
Datatable.clear()来自类 DataTable 的方法
回答by Kreg
As someone mentioned, just use:
正如有人提到的，只需使用：
dt.Rows.Clear()
回答by krillgar
This will allow you to clear all the rows and maintain the format of the DataTable.
这将允许您清除所有行并保持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.
但是，调用Clear()在DataTable（dt）将删除列，并从数据表格式。
Per code found in an MSDN question, an internal method is called by both the DataRowsCollection, and DataTablewith a different booleanparameter:
根据在 MSDN question 中找到的代码DataRowsCollection， 和DataTable使用不同的boolean参数调用内部方法：
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);
}
}
}
}
回答by Jake1164
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.
如果你真的关心速度而不担心数据，你可以做一个截断。但这假设您的 DataTable 位于数据库上，而不仅仅是内存对象。
TRUNCATE TABLE tablename
The difference is this removes all rows without logging the row deletes making the transaction faster.
不同之处在于这会删除所有行而不记录行删除，从而使事务更快。
回答by Tim Schmelter
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 UpdateBatchSizeto 0(unlimited).
这是通过 .dbms 从表中删除所有行的最简单方法DataAdapter。但是，如果您想批量进行，则可以将 DataAdapter 设置UpdateBatchSize为 0（无限制）。
Another way would be to use a simple SqlCommandwith CommandText DELETE FROM Table:
另一种方法是使用简单SqlCommand的 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 DataRowsfrom the DataTable, you just have to call DataTable.Clear. That would prevent any rows from being deleted in dbms.
但是，如果你不是仅仅要删除DataRows从DataTable，你只需要调用DataTable.Clear。这将防止在 dbms 中删除任何行。
回答by dupe
Just use dt.Clear()
只需使用 dt.Clear()
Also you can set your TableAdapter/DataAdapter to clear before it fills with data.
您也可以在填充数据之前将 TableAdapter/DataAdapter 设置为清除。
回答by Steve
Herewe 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
这必须是您的表名：tablenamehere- 这可以删除表中的所有行：DELETE FROM
