将记录插入SQL db的更快捷方法
将记录插入SQL db的更快捷方法
提问于 2015-03-23 10:38:38
我有一个工具，它每小时在数据库中插入/更新数千条记录。它将输入读入dataTable并逐行运行SQL命令：
for(int i = 0; i < dataTable.Rows.Count; i++)
{
string sqlConnectionString = "server, db, user, pass, etc.";
SqlConnection sqlDBConnection = new SqlConnection(sqlConnectionString);
string query = @"INSERT INTO table
(col1, col2, col3, etc.)
VALUES
(@col1, @col2, @col3, etc.)";
SqlCommand queryCommand = new SqlCommand(query, sqlDBConnection);
queryCommand.Parameters.Add("@col1", SqlDbType.Int);
queryCommand.Parameters["@col1"].Value = Convert.ToInt32(dataTable.Rows[i][0]);
queryCommand.Parameters.Add("@col2", SqlDbType.VarChar);
queryCommand.Parameters["@col2"].Value = dataTable.Rows[i][1].ToString();
queryCommand.Parameters.Add("@col3", SqlDbType.VarChar);
queryCommand.Parameters["@col3"].Value = dataTable.Rows[i][2].ToString();
sqlDBConnection.Open();
queryCommand.ExecuteNonQuery();
sqlDBConnection.Close();
}它工作得很好，但速度很慢。你有更快的解决办法吗？
回答 4
Stack Overflow用户
回答已采纳
发布于 2015-03-23 10:45:20
由于您的数据已经在DataTable中，所以我认为最好的方法是使用SQLBulkCopy。例如：
string sqlConnectionString = "server, db, user, pass, etc.";
using (var bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "table";
bulkCopy.ColumnMappings.Add("Col1", "Col1");
bulkCopy.ColumnMappings.Add("Col2", "Col2");
bulkCopy.ColumnMappings.Add("Col3", "Col3");
bulkCopy.WriteToServer(dataTable);
}要更新现有记录，如果您已经使用DataTable填充了SqlDataAdapter，则可以使用SqlDataAdapter.Update()方法。如果不是，那么我建议在SQL中处理插入项。由于您使用的是Server 2012，所以可以使用表值参数。第一步是创建表型
CREATE TYPE dbo.YourTableType TABLE
(
Col1 INT,
Col2 INT,
Col3 INT
);这应该具有与您的c# DataTable相同的定义。
然后，下一步将创建一个存储过程，该存储过程接受此类型作为参数，并根据是否匹配使用MERGE插入或更新记录：
CREATE PROCEDURE dbo.UpsertYourTable @Table dbo.YourTableType READONLY
AS
BEGIN
MERGE dbo.YourTable WITH (HOLDLOCK) AS t
USING @Table AS s
ON s.Col1 = t.Col1 -- OR HOWEVER YOU IDENTIFY EXISTING RECORDS
WHEN MATCHED THEN UPDATE
SET Col2 = Col2,
Col3 = Col4
WHEN NOT MATCHED THEN
INSERT (Col1, Col2, Col3, Col4)
VALUES (s.Col1, s.Col2, s.Col3, 'Test');
END;
GO最后，要从c#调用它，您可以使用：
string sqlConnectionString = "server, db, user, pass, etc.";
using (var connection = new SqlConnection(sqlConnectionString))
using (var command = new SqlCommand("dbo.UpsertYourTable", connection))
{
command.CommandType = CommandType.StoredProcedure;
var tvp = new SqlParameter("@Table", SqlDbType.Structured);
tvp.Value = dataTable;
tvp.TypeName = "dbo.YourTableType";
command.Parameters.Add(tvp);
command.ExecuteNonQuery();
}Stack Overflow用户
发布于 2015-03-23 10:41:10
在SqlBulkCopy类或C#中的表值参数中使用大容量插入。
那应该会让事情加速
Stack Overflow用户
发布于 2015-03-23 10:44:28
这很慢，因为每个插入都是单独处理的，这会导致事务时间上的开销。尝试使用不同的方法进行批量插入。无论是在框架上(如spring大容量)，还是只需将查询更改为一次性插入多个值。
insert into table (col1, col2, col3, ...)
values (val1, val2, cal3, ...),
(val1, val2, cal3, ...),
(val1, val2, cal3, ...), ...但是，不要将它们全部连接起来，以免使db连接的缓冲区大小过大。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/29208147
复制相关文章
相似问题
腾讯云开发者
