使用 DataSets 和 OpenXML 进行通用批量插入

poodull76

4.29/5 (12投票s)

2005 年 4 月 14 日

3分钟阅读

251718

创建 T-SQL 命令文本，以便快速且轻松地使用 OpenXML 更新表。

引言

我目前正在进行的项目需要将大量数据插入到 20 多个表中。我查找了批量插入解决方案，找到了一些 MSDN 上的文章和链接。

文章解释了如何使用 `DataSet` 的架构准备一个表，然后将其发送到一个存储过程来执行 `Update` 和 `Insert`。我发现对于我的需求，MSDN 的解决方案需要为每个表创建一个函数和一个存储过程。

因此，我花了一天时间实现了这段代码。它接受一个 `DataSet`、一个打开的 SQL 连接和一个表名，并编写执行 `OPENXML` 批量插入的命令文本。

代码

有一个调用函数和两个支持函数。第一个函数接受三个参数：

一个 `DataSet`，
一个打开的 SQL 连接，
以及一个表名。

然后，它首先处理表的 `ColumnMapping`，就像 MSDN 的教程一样。它还将 `DataSet` 流式传输到 `StringBuilder` 中，作为 XML。然后，它不会将 XML 发送到存储过程，而是将其发送到 `buildBulkUpdateSql`，后者会创建剩余的 T-SQL 脚本。

/// <summary>
/// Takes a dataset and creates a OPENXML script dynamically around it for 
/// bulk inserts 
/// </summary> 
/// <remarks>The DataSet must have at least one primary key, otherwise it'll wipe 
/// out the entire table, then insert the dataset. Multiple Primary Keys are okay. 
/// The dataset's columns must match the target table's columns EXACTLY. A 
/// dataset column "democd" does not work for the sql column
/// "DemoCD". Any missing or incorrect data is assumed NULL (default).
/// </remarks>
/// <param name="objDS">Dataset containing target DataTable.
/// <param name="objCon">Open Connection to the database.
/// <param name="tablename">Name of table to save.
public static void BulkTableInsert(DataSet objDS, 
                                 SqlConnection objCon, string tablename)
{
    //Change the column mapping first.
    System.Text.StringBuilder sb = new System.Text.StringBuilder( 1000);
    System.IO.StringWriter sw = new System.IO.StringWriter(sb); 
    foreach( DataColumn col in objDS.Tables[tablename].Columns)
    {
         col.ColumnMapping = System.Data.MappingType.Attribute;
    }
    objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);
    string sqlText = buildBulkUpdateSql(sb.ToString(), objDS.Tables[tablename]);
    execSql(objCon, sqlText);
}

这是生成通用 T-SQL 文本的地方。这里的唯一技巧是在将字符串发送到 `SqlCommand` 之前，从中删除 C# 转义字符。另一个需要注意的是，我如何使用数据库中的表作为架构，以便在 `WITH` 参数中进行工作，这样我就不必命名每个列和数据类型。

static string buildBulkUpdateSql( string dataXml, DataTable table)
{
    System.Text.StringBuilder sb = new System.Text.StringBuilder();
    dataXml = dataXml.Replace(Environment.NewLine, "");
    dataXml = dataXml.Replace("\"", "''");
    //init the xml doc
    sb.Append(" SET NOCOUNT ON");
    sb.Append(" DECLARE @hDoc INT");
    sb.AppendFormat(" EXEC sp_xml_preparedocument @hDoc OUTPUT, '{0}'", dataXml);
    //This code deletes old data based on PK.
    sb.AppendFormat(" DELETE {0} FROM {0} INNER JOIN ", table.TableName);
    sb.AppendFormat(" (SELECT * FROM OPENXML (@hdoc, '/NewDataSet/{0}', 1)", 
    table.TableName);
    sb.AppendFormat(" WITH {0}) xmltable ON 1 = 1", table.TableName);
    foreach( DataColumn col in table.PrimaryKey)
    {
        sb.AppendFormat(" AND {0}.{1} = xmltable.{1}", table.TableName, 
        col.ColumnName);
    }
    //This code inserts new data.
    sb.AppendFormat(" INSERT INTO {0} SELECT *", table.TableName);
    sb.AppendFormat(" FROM OPENXML (@hdoc, '/NewDataSet/{0}', 1) WITH {0}", 
    table.TableName);
    //clear the xml doc
    sb.Append(" EXEC sp_xml_removedocument @hDoc");
    return sb.ToString(); 
}

这里没有魔法。这只是一个简单的命令执行器。在我的实际应用程序中，我并不使用它，我也不期望你使用它，但为了代码补全，这里提供一下。

static void execSql(SqlConnection objCon, string sqlText)
{
    SqlCommand objCom = new SqlCommand();
    objCom.Connection = objCon;
    objCom.CommandType = CommandType.Text;
    objCom.CommandText = sqlText;
    objCom.ExecuteNonQuery();
}

缺点

正如函数头中的文档所述，此过程假定以下几点：

`DataTable` 必须至少有一个主键，以便它知道哪些是更新数据，哪些不是。
与主键匹配的现有数据将被删除，然后再重新插入。这一点可以争论是否是好主意。我认为它会加快整个事务的速度，但可能不是最优雅的解决方案。我对任何 DBA 的评论都感兴趣。更新部分很容易，然后您需要对 XML 表进行 `outer join` 来处理 `Insert` 数据；这可能会花费一些时间，具体取决于表的大小。
`DataTable` 的列名必须与数据库中的表名完全匹配。这有点令人头疼，但如果你在 IDE 中自动生成 `DataSet`，这对你来说应该不是问题。

待办事项

在创建 `Insert` 脚本之前，删除不必要的 XML。`DataSet` 可能包含多个表，所有这些表都由 `DataSet.WriteXml()` 函数写出。在大样本中，发送这些数据量太大了，而且完全没有用。

结论

我用大小表和 `DataSet` 对此进行了大量测试。还有很多改进和功能增强的空间，我知道这在许多专业环境中都行不通，但对我来说这是一个很好的开始，我希望在两天前就能拥有它。如我所提到的，我期待社区关于如何加速此代码片段的建议。