使用 ADO.NET 和 OPENXML 执行批量数据库操作

• 下载源代码 - 4.03 KB

引言

上周在我的工作中，我必须编写一个工具来处理数十万个电子邮件文件，并将它们的详细信息插入到 MS SQL Server 2005 数据库中。 以传统方式进行可能需要与数据库进行大量连接，并且会消耗大量资源。 我想知道是否可以将所有数据一次性插入到数据库中，这将更有效率。 我开始在 Google 上搜索，发现了 OPENXML。 这不是一个全新的技术，但我以前不知道这个（奇怪！！）。

经过几分钟的进一步研究，我发现如何使用 OPENXML 和存储过程对数据库执行批量操作。

什么是 OPENXML

OPENXML 是添加到 SQL Server 2000 的一个函数，它提供了一个 XML 文档的行集视图。 由于行集只是一个包含数据列的行集合，OPENXML 是允许以熟悉的关系数据库格式处理 XML 文档的函数。 它允许将 XML 文档传递给 T-SQL 存储过程以更新数据。

OPENXML - 摘要

• 它扩展了 SQL 语言
• 它在 T-SQL 存储过程中使用
• XML 文档作为参数传递
• 它使用利用 XPath 的行和列选择器

它支持以下内容

• 属性和元素为中心的映射。
• 边缘表行集。
• XML 注释/溢出列。
• 分层结构支持。

OPENXML 和 ADO.NET

此代码块说明了使用 OPENXML 将批量数据插入数据库的方法。 它通过读取文件夹中的文件并保存它们的文件名、创建日期和大小（以字节为单位）来模拟我想要的实际过程（处理 .eml 文件）。

1. 首先创建下表

CREATE TABLE FileDetails(
        FileName varchar(50) PRIMARY KEY,
        CreatedDate varchar(50) ,
        Size decimal(18, 0)

2. 现在创建如下所示的存储过程

CREATE PROC sp_bulkinsert @xmldata varchar(max)
    AS
    DECLARE @hDoc int
    exec sp_xml_preparedocument @hDoc OUTPUT,@xmldata
    --inserting data
    Insert Into FileDetails 
    SELECT FileName, CreatedDate, Size
    FROM OPENXML (@hdoc, '/ds/fileDetails',2)
    WITH (FileName varchar(50), CreatedDate Datetime, Size decimal) XMLFileDetails
    Where XMLFileDetails.FileName Not IN (Select FileName from FileDetails)
    EXEC sp_xml_removedocument @hDoc
    GO

3. 下面的示例代码段展示了如何调用此存储过程以一次性将批量数据插入到数据库中。

我们可以使用 FileInfo() 对象检索文件详细信息，并创建文件详细信息的 DataSet，然后我们可以使用 DataSet 的 WriteXml() 方法将此 DataSet 转换为 XML。

private static string preparexml(DataSet ds)
{
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    ds.WriteXml(sw, XmlWriteMode.WriteSchema);
    return sb.ToString();
}

此数据的 XML 表示形式可以作为参数传递给存储过程，如下所示

private static void bulkInsert(string xml) 
{
    try{
        SqlConnection con = creatCon();
        con.Open();
        SqlCommand cmd = new SqlCommand("sp_bulkinsert", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add(new SqlParameter("@xmldata",SqlDbType.VarChar));
        cmd.Parameters["@xmldata"].Value=xml;
        cmd.ExecuteNonQuery();
        Console.WriteLine("Completed inserting file informations to database");
        con.Close();
    }
     catch(Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

就这么简单，不是吗？ 您可以通过编写另一个代码来使用传统的逐个方式插入相同的字段来查看此方法的性能。 令人惊讶的是，这将显示性能和时间上的巨大差异。

您可以使用相同的方法来批量更新和删除记录。

最新的完整代码可以在这里下载和测试：完整源代码。