多种实现批量插入的方法
4.76/5 (45投票s)
使用 ADO.NET 在单个数据库往返中，为 SQL Server 2000/2005 或 Oracle 数据库执行批量插入的各种方法。
目录
文章介绍与范围
我们经常会遇到这样的场景：需要一次性执行相同的插入或更新命令，但带有不同的值。例如，在一个可更新的网格/表格格式中显示多条记录，允许用户更新它们，然后更新数据库。有多种方法可以处理这个问题，最简单的方法是逐个执行 DML 命令。其中最耗费资源的部分是为每个 DML 命令建立连接。嗯，连接池会有所帮助，但仍然需要从连接池请求连接。有关连接池的详细信息，请参阅文章 ADO.NET 连接池一瞥。在这种情况下，最佳解决方案是只建立一次连接，并在其中执行多次插入/更新，而这正是本文的目标。让我们来看看可以实现这一目标的几种机制。
让我们开始设置环境。
设置环境
在示例中，我们将使用 SQL Server 2000/2005 或 Oracle。因此，让我们在所需的数据库中创建一个非常简单的表 Person。我们让它非常简单，只包含两个列：PersonId 和 PersonName。相应的语法是
CREATE TABLE Person ( PersonId INT PRIMARY KEY, PersonName VARCHAR(100) )
使用 ADO.NET 2.0 DataAdapter 对象的批量更新
适用于环境：.NET 2.0 (ADO.NET 2.0)，SQL Server 2000 或更高版本
在 ADO.NET 2.0 之前，这是一个非常繁琐的任务。但有了 ADO.NET 2.0，事情变得非常简单，因为 Adapter 现在支持用户定义的批量大小的多次插入/更新。我们将讨论限制在插入功能上。对于插入，使用通常的简单存储过程创建用于插入的 Command 对象，并将其指定为 DataAdapter 对象的 InsertCommand。除此之外，我们还需要指定 UpdateBatchSize，它决定了在一次网络往返中处理的插入次数。请遵循以下代码以获得完整的理解。
首先，在您的 SQL Server 实例中创建存储过程：
CREATE PROCEDURE sp_BatchInsert ( @PersonId INT, @PersonName VARCHAR(100) )
AS
BEGIN
INSERT INTO Person VALUES ( @PersonId, @PersonName);
END
现在请参考下面的 C# 代码。
private void btnBatchInsert_Click(object sender, EventArgs e)
{
// Get the DataTable with Rows State as RowState.Added
DataTable dtInsertRows = GetDataTable();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_BatchInsert", connection);
command.CommandType = CommandType.StoredProcedure;
command.UpdatedRowSource = UpdateRowSource.None;
// Set the Parameter with appropriate Source Column Name
command.Parameters.Add("@PersonId", SqlDbType.Int, 4, dtInsertRows.Columns[0].ColumnName);
command.Parameters.Add("@PersonName", SqlDbType.VarChar, 100, dtInsertRows.Columns[1].ColumnName);
SqlDataAdapter adpt = new SqlDataAdapter();
adpt.InsertCommand = command;
// Specify the number of records to be Inserted/Updated in one go. Default is 1.
adpt.UpdateBatchSize = 2;
connection.Open();
int recordsInserted = adpt.Update(dtInsertRows);
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
嗯，我们所有开发者做的第一件事就是使用 SQL Profiler 来检查。令我们惊讶的是，它显示发送到 SQL Server 的是 4 个不同的 RPC 请求。它在那里看起来是这样的：
不要惊慌。区别不在于您的语句在服务器上的执行方式，而在于您的请求是如何发送到服务器的。简单来说，当 UpdateBatchSize 为 2 时（在本例中），这意味着插入 2 行的请求将被分组，并在单个网络往返中发送到数据库服务器。您可以使用“Netmon”等其他工具进行更仔细的检查。因此，请适当地使用 UpdateBatchSize。
DataAdapter 有两个与更新相关的事件：RowUpdating 和 RowUpdated。每次批量操作只发生一个 RowUpdated 事件，而 RowUpdating 事件发生在处理的每一行上。您可能还想查看异常处理部分。请自行探索。使用 ADO.NET 2.0 的 SQLBulkCopy
适用于环境：.NET 2.0 或更高版本，SQL Server 2005 数据库或更高版本
使用 ADO.NET 2.0，我们获得了 Bulk Copy 的编程接口，它提供了一种非常简单直接的机制，可以将数据从一个 SQL Server 实例传输到另一个实例，从一个表传输到另一个表，从 DataTable 传输到 SQL Server 2005 数据库，从 DataReader 传输到 SQL Server 2005 数据库等等。
SqlBulkCopy 属于 System.Data.SqlClient 命名空间，在编程方面，它与 ADO.NET Command 对象一样简单。让我们来看看它的工作原理。
private void btnSQLBulkCopyInsert_Click(object sender, EventArgs e)
{
// Get the DataTable
DataTable dtInsertRows = GetDataTable();
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString))
{
sbc.DestinationTableName = "Person";
// Number of records to be processed in one go
sbc.BatchSize = 2;
// Map the Source Column from DataTabel to the Destination Columns in SQL Server 2005 Person Table
sbc.ColumnMappings.Add("PersonId", "PersonId");
sbc.ColumnMappings.Add("PersonName", "PersonName");
// Number of records after which client has to be notified about its status
sbc.NotifyAfter = dtInsertRows.Rows.Count;
// Event that gets fired when NotifyAfter number of records are processed.
sbc.SqlRowsCopied+=new SqlRowsCopiedEventHandler(sbc_SqlRowsCopied);
// Finally write to server
sbc.WriteToServer(dtInsertRows);
sbc.Close();
}
}
void sbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
MessageBox.Show("Number of records affected : " + e.RowsCopied.ToString());
}
上面的代码非常简单，并且具有很好的自解释性。
要点
- BatchSize 和 NotifyAfter 是两个不同的属性。前者指定一次处理的记录数，后者指定处理后需要通知客户端的记录数。
参考
- 没有比 MSDN 更好的地方了。有关 SqlBulkCopy 的详细信息，请参阅 http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx。
- 有关 SqlBulkCopy 所有属性的详细信息，请参阅 http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy_properties.aspx。
将数组作为参数传递给 ODP.NET Command 对象
适用于环境：ODP.NET 9.2.0.1 或更高版本，.NET 1.1 或更高版本，Oracle 9i 数据库或更高版本
Oracle 在 Oracle 数据库与 .NET 之间的连接方面投入了大量精力。Oracle 提供了 ODP.NET (Oracle Provider for .NET) 来实现 Oracle 数据库与 .NET 之间的连接。嗯，我没有深入研究其细节，但据信，从 .NET 环境连接到 Oracle 数据库时，使用 ODP.NET 会更好。也许因为它们都是 Oracle 产品，它们可能已经优化了 ODP.NET 以获得更好的性能，也许…
无论如何，让我们看看它在一次网络往返中为批量插入提供了什么。ODP.NET 提供了 OracleCommand 对象，它与 SQLCommand 对象非常相似。OracleCommand 对象支持将数组作为参数。唯一需要注意的是，在使用数组时，需要提供 ArrayBindCount，它会告知 ODP.NET 期望并处理数组中的记录数。简而言之，代码与通过提供两个简单参数调用存储过程时完全相同，只是，与提供简单值给参数不同，我们需要指定一个值数组。此外，我们指定 ArrayBindCount 与 Array Length 相同，以使 ODP.NET 能够进行批量插入。我敢肯定，下面的代码将帮助您更好地理解这一点。
在 Oracle 数据库中创建简单的存储过程 sp_InsertByODPNET，类似于上面代码中的 sp_BatchInsert，然后按照下面的 .NET 代码进行操作。
private void btnOracleODPNET_Click(object sender, System.EventArgs e)
{
int[] arrPersonId = {Convert.ToInt32(txtPersonId1.Text.Trim()),
Convert.ToInt32(txtPersonId2.Text.Trim()), Convert.ToInt32(txtPersonId3.Text.Trim()),
Convert.ToInt32(txtPersonId3.Text.Trim())};
string[] arrPersonName = {txtPersonName1.Text.Trim(), txtPersonName2.Text.Trim(),
txtPersonName3.Text.Trim(), txtPersonId4.Text.Trim()};
// You need to put the Namespace "using Oracle.DataAccess.Client;" to compile this code
OracleConnection connection = new OracleConnection(oracleConnectionString);
OracleCommand command = new OracleCommand("sp_InsertByODPNET", connection);
command.CommandType = CommandType.StoredProcedure;
// We need to tell the ODP.NET the number of rows to process
//and that we can do using "ArrayBindCount" property of OracleCommand Object
command.ArrayBindCount = arrPersonId.Length;
// For specifying Oracle Data Types for Parameters you need to use "Oracle.DataAccess.Types" namespace.
command.Parameters.Add("@PersonId", OracleDbType.Int16);
command.Parameters[0].Value = arrPersonId;
command.Parameters.Add("@PersonName", OracleDbType.Varchar2, 100);
command.Parameters[1].Value = arrPersonName;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
有几个人对这种方法提出了异议，因为他们在 ODP.NET 和 .NET 一起使用时遇到了内存泄漏。但我也听说，在 ODP.NET 和 .NET 的最新版本以及补丁中，这些问题已经得到解决。您可能希望在采用此方法之前进行自己的研究。
要点
- 使用最新版本的 ODP.NET，因为它通常包含以前版本的所有错误修复。
- 不要忘记设置 command.ArrayBindCount。
参考文献
- 有关 Oracle ODP.NET 的最新信息，请参阅 Oracle 网站上的详细信息，网址为： http://www.oracle.com/technology/tech/windows/odpnet/index.html。
将数据以 XML 格式传递给 SQL Server 存储过程
适用于环境：.NET 1.1、.NET 2.0，SQL Server 2000 或更高版本
SQL Server 2000 支持 XML。SELECT * FROM table FOR XML AUTO。语法听起来很熟悉，对吧？是的，SQL Server 2000 支持 XML。它不仅支持以 XML 格式返回数据，还支持读取 XML 字符串并解析它。在实现此方法的批量插入之前，为了更好地理解它，请复制下面的代码并在 SQL Query Analyzer SQL 窗口中执行。
DECLARE @intDocHandle int
DECLARE @xmlString varchar(4000)
SET @xmlString ='
<root>
<person PersonId="1" PersonName="AA"/>
<person PersonId="2" PersonName="BB"/>
<person PersonId="3" PersonName="CC"/>
<person PersonId="4" PersonName="DD"/>
</root>'
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlString
SELECT * FROM OPENXML(@intDocHandle,
'/root/person')
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
-- Remove the internal representation.
exec sp_xml_removedocument @intDocHandle
我将进一步的解释和理解部分留给您。很容易注意到，它围绕着两个重要的存储过程：sp_xml_preparedocument、sp_xml_removedocument 和一个关键字 OPENXML。现在让我们看看如何利用这一点来处理批量插入场景。
根据上述逻辑，所需存储过程的代码如下：
CREATE PROCEDURE sp_InsertByXML ( @strXML VARCHAR(4000) )
AS
BEGIN
DECLARE @intDocHandle int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @strXML
INSERT INTO Person ( PersonId, PersonName )
SELECT * FROM OPENXML(@intDocHandle,
'/PersonData/Person', 2)
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
-- Remove the internal representation.
EXEC sp_xml_removedocument @intDocHandle
END
现在我们需要在前台构建 XML，然后将其传递给此存储过程。我相信有多种方法可以做到这一点。您可以通过连接并构建 XML 来构建，或者使用 System.XML 命名空间中的 XMLDocument 对象并从中获取 XML 字符串。由于大多数时候我们都在处理 DataSet 和 DataTable，因此我选择从 DataSet 中获取 XML。首先，获取所需的 DataSet 对象中的数据。如果您在运行时构建 DataSet 时遇到问题，请参阅附加样本代码中的“private string GetXml()”方法，然后使用以下代码从中获取字符串：
System.IO.StringWriter sw = new System.IO.StringWriter ( );
dsPersonData.WriteXml (sw);
string strXml = sw.ToString();
至此，我得到了所需的 XML 字符串。现在只剩下从前端代码调用存储过程的工作，如下所示：
private void btnInsertByXMLInput_Click(object sender, System.EventArgs e)
{
string strXml = GetXml();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_InsertByXML", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@strXML",SqlDbType.VarChar, 4000);
command.Parameters[0].Value = strXml;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
它还返回消息“受影响的记录数：4”。已插入多条记录，任务再次完成。
要点
- XML 是区分大小写的。例如，在 OPENXML 语句中，“/root/person”不等于“/ROOT/person”。
- 您可以构建以属性为中心或以元素为中心的 XML。在上面的代码中，它是以元素为中心的 XML，因此我们在 OPEN XML 语法中有“2”，否则默认值为 1，用于以属性为中心的 XML。
- 在 ADO.NET 2.0 中，您可以直接从 DataTable 中获取 XML。在这种情况下，请相应地修改 OPENXML 中的 XPath。
- SQL Server 2000 支持 XML 处理，但在 SQL Server 2005 中，我们有了 xml 数据类型。它有更多的方法来支持 XML 的 DML 和 DDL。请根据您的需求进行选择。
参考文献
- 有关 sp_xml_preparedocument、sp_xml_removedocument、OPENXML 的详细信息，请参阅 Books Online。
形成单个 SQL 语句并直接从 ADO.NET 执行
这样做听起来可能有些粗鲁，甚至让当今的开发者和架构师感到厌恶，但是的，这也是一个选项。而且，我同意他们的看法。
如果您认为所有最新的机制（如上述机制）都不符合您的要求，那么您可以自己形成一个查询并执行它。但是，当您的项目设计允许您直接从 .NET 数据访问层执行查询时，可以使用 CommandType 为 Text 的 Command 对象。
为了执行多个插入查询，我们可以简单地将用分号“;”分隔的插入查询追加起来，并将其用作 Command 对象的 CommandText。使用 Command 对象的 ExecuteNonQuery() 方法并观察受影响的记录数。
嗯，我们可以通过几种方式来形成所需的 SQL 语句。让我们一一来看。
追加插入语句
在这种情况下，我们将把每个插入语句一个接一个地追加起来，并将其作为一个命令执行。我们试图实现的语法是：
INSERT INTO Person VALUES (‘1’, ‘AA’); INSERT INTO Person VALUES (‘2’, ‘BB’);
.NET 1.1 代码，用于创建包含 4 个同时插入的 SQL 查询并使用 command 对象执行它，看起来会像这样：
private void btnInsertByJoiningQueries_Click(object sender, System.EventArgs e)
{
string sqlText = "INSERT INTO Person VALUES ( '" + txtPersonId1.Text.Trim() + "',' " + txtPersonName1.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId2.Text.Trim() + "',' " + txtPersonName2.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId3.Text.Trim() + "',' " + txtPersonName3.Text + "'); ";
sqlText += "INSERT INTO Person VALUES ( '" + txtPersonId4.Text.Trim() + "',' " + txtPersonName4.Text + "'); ";
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand(sqlText,connection);
command.CommandType = CommandType.Text;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
在上面的示例中，它返回消息“受影响的记录数：4”。已插入多条记录，任务完成。
使用 Select 和 Union All 进行插入
这与追加插入语句非常相似，但我们不是追加每个完整的插入语句，而是首先选择值，然后将其传递给插入语句以将其插入表中。我们试图实现的语法是：
INSERT INTO TableName (Col1, Col2)
SELECT 1, ‘AA’
UNION ALL
SELECT 2, ‘BB’
UNION ALL
SELECT 3, ‘CC’
其余部分，该方法与我们在“追加插入语句”部分看到的方法非常相似。所以代码留给您。如果您仍然需要帮助，请随时发布。
将值以带列分隔符和/或行分隔符的字符串形式传递给 SQL Server 存储过程。在存储过程中拆分并插入
这里我们可以做的是，从前端创建一个带有预定义列分隔符和/或行分隔符分隔的值的字符串，然后让存储过程解析它，分离数据，将数据转换成有用的格式，然后将其插入相应的表中。嗯，我不会深入研究其细节，因为我也认为这种方法应该在所有其他选项都被排除时使用。但我可以给您一些提示。创建一个函数，该函数以所需的格式返回您拆分后的值。我在下面找到了一些：
SQL Server 2000 的拆分函数，网址为：
- http://www.eggheadcafe.com/community/aspnet/9/10012556/split-function-in-your-da.aspx
- http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
- Oracle 的拆分函数，网址为：
也许您可以获得作者的许可，修改并使用它。
要点
- 如果插入查询的数量非常多，请使用 StringBuilder 而不是简单的 string 对象以获得更好的性能。
- 只有当您别无选择时，才考虑合并 SQL 语句。这当然是一个选项，但不如前面提到的其他选项易于维护和安全。
其他有用参考
我偶然发现了两篇我觉得非常相关和有用的文章，我建议您阅读它们。
- 使用 DataAdapters 执行批量操作 (ADO.NET)： http://msdn2.microsoft.com/en-us/library/aadf8fk2.aspx
- 数据访问应用程序块（来自 microsoft.com）
- 如何：将数组绑定到 ODP.NET 数据库命令（来自 oracle.com/technology）
- 将数组或 DataTable 传递到存储过程： https://codeproject.org.cn/KB/database/PassingArraysIntoSPs.aspx
总结
呼，有这么多的方法来做同一件事。现在一个大问题——选择哪一个？相信我，没有硬性规定，但当您选择其中一个时，请确保您选择的机制能够提供最佳的运行时性能、易于开发、易于维护、安全可靠。如前所述，本文将讨论范围限制在确保在一个连接实例中完成批量插入以获得更好的性能和更少的资源消耗，但我敢肯定，有各种方法可以提高性能。
还有一件事我真的想让大家注意，那就是异常处理。即使在尝试插入单条记录时，您也可能遇到主键冲突、外键冲突或其他约束冲突等异常，而这里我们尝试插入多条记录，所以必须非常小心。由于范围有限，我在这里没有详细介绍异常处理。
请花些时间评价和提供关于本文的反馈。您的几分钟时间可以帮助提高本文的质量。
如果您有兴趣，请在此处单击查看我发布的所有文章。