一次删除数据表所有行的快速方法 [英] 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?

推荐答案

如果您正在针对 sqlserver 数据库运行代码，那么
使用这个命令

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.

其他 RDBMS 也支持 TRUNCATE 关键字.

The TRUNCATE keyword is supported also by other RDBMS.

5 年后:
回顾这个答案，我需要补充一些东西.仅当您绝对确定 yourTableName 变量中的值的来源时，上述答案才是正确的.这意味着你不应该从你的用户那里得到这个值，因为他可以输入任何东西，这会导致 Sql 注入问题，在 这部著名的漫画.始终使用不可编辑的 UI 向用户展示硬编码名称(表格或其他符号值)之间的选择.

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.

这篇关于一次删除数据表所有行的快速方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！