使用OPENXML批量插入数据至SQL Server数据库

在日常的数据库操作中,经常需要将大量数据插入数据库。传统的逐条插入方式虽然简单,但效率低下,尤其是在处理大量数据时。为了提高效率,可以使用OPENXML技术来实现批量插入。OPENXML是SQL Server2000引入的一个函数,它允许将XML文档以行集的形式处理,从而实现高效的数据插入。本文将详细介绍OPENXML的基本概念、使用方法,并通过示例代码演示如何批量插入数据到SQL Server数据库。

什么是OPENXML

OPENXML函数为SQL Server提供了一种将XML文档作为行集处理的能力。这意味着可以像处理关系数据库中的行集一样处理XML文档。OPENXML函数允许将XML文档传递给T-SQL存储过程,以便更新数据。

OPENXML的主要特点包括:

  • 扩展SQL语言
  • 在T-SQL存储过程中使用
  • 将XML文档作为参数传递
  • 使用XPath进行行和列选择器
  • 支持属性和元素中心映射
  • 支持边缘表行集
  • 支持XML注释/溢出列
  • 支持层次结构

OPENXML与ADO.NET

下面的代码块展示了如何使用OPENXML批量将数据插入数据库。这个过程模拟了需要的实际过程(处理.eml文件),通过读取文件夹中的文件并将它们的名称、创建日期和字节大小保存到数据库中。

CREATE TABLE FileDetails( FileName VARCHAR(50) PRIMARY KEY, CreatedDate VARCHAR(50), Size DECIMAL(18,0) ); CREATE PROC sp_bulkinsert @xmldata VARCHAR(MAX) AS BEGIN DECLARE @hDoc INT; EXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldata; -- 插入数据 INSERT INTO FileDetails SELECT FileName, CreatedDate, Size FROM OPENXML(@hdoc, '/ds/fileDetails', 2) WITH (FileName VARCHAR(50), CreatedDate DATETIME, Size DECIMAL) AS XMLFileDetails WHERE XMLFileDetails.FileName NOT IN (SELECT FileName FROM FileDetails); EXEC sp_xml_removedocument @hDoc; END;

可以使用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); } }

性能对比

通过编写另一段代码,以传统的逐条插入方式插入相同的字段,可以明显看到性能和时间上的巨大差异。这种方法同样适用于批量更新和删除记录。

沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485