C# SQL 插入一行还是多行数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1024123/
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
SQL Insert one row or multiple rows data?
提问by David.Chu.ca
I am working on a console application to insert data to a MS SQL Server 2005 database. I have a list of objects to be inserted. Here I use Employee class as example:
我正在使用控制台应用程序将数据插入到 MS SQL Server 2005 数据库中。我有一个要插入的对象列表。这里我以 Employee 类为例：
List<Employee> employees;
What I can do is to insert one object at time like this:
我能做的是在这样的时间插入一个对象：
foreach (Employee item in employees)
{
string sql = @"INSERT INTO Mytable (id, name, salary)
values ('@id', '@name', '@salary')";
// replace @par with values
cmd.CommandText = sql; // cmd is IDbCommand
cmd.ExecuteNonQuery();
}
Or I can build a balk insert query like this:
或者我可以像这样构建一个阻止插入查询：
string sql = @"INSERT INTO MyTable (id, name, salary) ";
int count = employees.Count;
int index = 0;
foreach (Employee item in employees)
{
sql = sql + string.format(
"SELECT {0}, '{1}', {2} ",
item.ID, item.Name, item.Salary);
if ( index != (count-1) )
sql = sql + " UNION ALL ";
index++
}
cmd.CommandType = sql;
cmd.ExecuteNonQuery();
I guess the later case is going to insert rows of data at once. However, if I have several ks of data, is there any limit for SQL query string?
我猜后一种情况将一次插入数据行。但是，如果我有几 ks 数据，SQL 查询字符串是否有限制？
I am not sure if one insert with multiple rows is better than one insert with one row of data, in terms of performance?
在性能方面，我不确定一个多行插入是否比一个插入一行数据更好？
Any suggestions to do it in a better way?
有什么建议可以更好地做到这一点吗？
采纳答案by kemiller2002
Actually, the way you have it written, your first option will be faster.
实际上，按照您编写的方式，您的第一个选项会更快。
Your second example has a problem in it. You are doing sql = + sql + etc. This is going to cause a new string object to be created for each iteration of the loop. (Check out the StringBuilder class). Technically, you are going to be creating a new string object in the first instance too, but the difference is that it doesn't have to copy all the information from the previous string option over.
The way you have it set up, SQL Server is going to have to potentially evaluate a massive query when you finally send it which is definitely going to take some time to figure out what it is supposed to do. I should state, this is dependent on how large the number of inserts you need to do. If n is small, you are probably going to be ok, but as it grows your problem will only get worse.
你的第二个例子有问题。您正在执行 sql = + sql + 等。这将导致为循环的每次迭代创建一个新的字符串对象。（查看 StringBuilder 类）。从技术上讲，您也将在第一个实例中创建一个新的字符串对象，但不同之处在于它不必复制前一个字符串选项中的所有信息。
按照您的设置方式，SQL Server 将不得不在您最终发送大量查询时潜在地评估它，这肯定需要一些时间来弄清楚它应该做什么。我应该说，这取决于您需要执行的插入次数。如果 n 很小，你可能会没事，但随着它的增长，你的问题只会变得更糟。
Bulk inserts are faster than individual ones due to how SQL server handles batch transactions. If you are going to insert data from C# you should take the first approach and wrap say every 500 inserts into a transaction and commit it, then do the next 500 and so on. This also has the advantage that if a batch fails, you can trap those and figure out what went wrong and re-insert just those. There are other ways to do it, but that would definately be an improvement over the two examples provided.
由于 SQL 服务器处理批处理事务的方式，批量插入比单个插入更快。如果您要从 C# 插入数据，您应该采用第一种方法并将每 500 次插入到事务中并提交，然后执行下 500 次，依此类推。这也有一个好处，如果批处理失败，您可以捕获这些并找出问题所在，然后重新插入那些。还有其他方法可以做到这一点，但这绝对是对提供的两个示例的改进。
var iCounter = 0;
foreach (Employee item in employees)
{
if (iCounter == 0)
{
cmd.BeginTransaction;
}
string sql = @"INSERT INTO Mytable (id, name, salary)
values ('@id', '@name', '@salary')";
// replace @par with values
cmd.CommandText = sql; // cmd is IDbCommand
cmd.ExecuteNonQuery();
iCounter ++;
if(iCounter >= 500)
{
cmd.CommitTransaction;
iCounter = 0;
}
}
if(iCounter > 0)
cmd.CommitTransaction;
回答by Tamir
look at this thread, I've answered there about table valued parameter.
看看这个线程，我已经在那里回答了关于表值参数的问题。
回答by idstam
Bulk-copyis usually faster than doing inserts on your own.
批量复制通常比自己进行插入要快。
If you still want to do it in one of your suggested ways you should make it so that you can easily change the size of the queries you send to the server. That way you can optimize for speed in your production environment later on. Query times may v ary alot depending on the query size.
如果您仍然想以您建议的方式之一执行此操作，您应该这样做，以便您可以轻松更改发送到服务器的查询的大小。这样您就可以在以后的生产环境中优化速度。查询时间可能因查询大小而异。
回答by ICodeForCoffee
The batch size for a SQL Server query is listed at being 65,536 * the network packet size. The network packet size is by default 4kbs but can be changed. Check out the Maximum capacity article for SQL 2008 to get the scope. SQL 2005 also appears to have the same limit.
SQL Server 查询的批处理大小列为 65,536 * 网络数据包大小。网络数据包大小默认为 4kbs，但可以更改。查看SQL 2008的最大容量文章以了解范围。SQL 2005 似乎也有同样的限制。
回答by Alex_L
In MS SQL Server 2008 you can create .Net table-UDT that will contain your table
在 MS SQL Server 2008 中，您可以创建包含您的表的 .Net table-UDT
CREATE TYPE MyUdt AS TABLE (Id int, Name nvarchar(50), salary int)
then, you can use this UDT in your stored procedures and your с#-code to batch-inserts. SP:
然后，您可以在您的存储过程和您的 с# 代码中使用此 UDT 来批量插入。SP：
CREATE PROCEDURE uspInsert
(@MyTvp AS MyTable READONLY)
AS
INSERT INTO [MyTable]
SELECT * FROM @MyTvp
C# (imagine that records you need to insert already contained in Table "MyTable" of DataSet ds):
C#（假设您需要插入的记录已包含在 DataSet ds 的“MyTable”表中）：
using(conn)
{
SqlCommand cmd = new SqlCommand("uspInsert", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter myParam = cmd.Parameters.AddWithValue
("@MyTvp", ds.Tables["MyTable"]);
myParam.SqlDbType = SqlDbType.Structured;
myParam.TypeName = "dbo.MyUdt";
// Execute the stored procedure
cmd.ExecuteNonQuery();
}
So, this is the solution.
所以，这就是解决方案。
Finally I want to prevent you from using code like yours (building the strings and then execute this string), because this way of executing may be used for SQL-Injections.
最后，我想阻止您使用像您这样的代码（构建字符串然后执行此字符串），因为这种执行方式可能用于 SQL 注入。
