65.9K
CodeProject 正在变化。 阅读更多。
Home

发送多行到数据库进行修改

Nizam Mohd
starIconstarIconstarIconemptyStarIconemptyStarIcon

3.00/5 (5投票s)

2009年8月20日

CPOL

11分钟阅读

viewsIcon

31446

downloadIcon

286

本文介绍如何将多行数据发送到 SQL Server 2005 数据库进行修改。

引言

在使用 Microsoft SQL Server 2005 设计应用程序时，我们都遇到过这样的问题：当我们要修改（插入、更新或删除）多行数据到数据库时。我们知道如何从数据库返回一组行到应用程序，但直到 SQL Server 2005，都没有提供任何功能来将多行发送到数据库。

在本文中，我将介绍以下两种有效的解决方法：

  1. 分隔符字符串方法
  2. XML 数据方法

分隔符字符串方法

在此方法中，我们从应用程序层将对应于表每一列数据的分隔符字符串发送到一个存储过程（SP）。然后，该 SP 应具备解析分隔符字符串以提取数据值并修改记录的功能。

每当解析出现时，我们只会想到逐个获取值并在循环中修改记录，这又会产生多次调用数据库引擎。正如我们所知，如果一次性向数据库请求这种处理，SP 的性能将有显著提升。

我在后续章节的示例中说明了分隔符字符串方法的两种实现。

XML 数据方法

在此方法中，我们在应用程序层创建一个 XML 字符串并将其发送到 SP。然后，该 SP 应具备解析 XML 以提取数据元素并修改记录的功能。其优势在于，我们无需显式编写解析 XML 的代码，因为 SQL Server 2005 可以为我们完成。

分隔符字符串方法实现

对于分隔符字符串方法，根据我的经验，我提出了以下两种实现：

  1. 基于编号列表的表值函数：此表值函数将创建一个编号列表，然后使用 `SUBSTRING` 和 `CHARINDEX` 内置函数将分隔符字符串转换为表。
  2. 基于递归 CTE（公共表表达式）的表值函数：此表值函数也使用 `SUBSTRING` 和 `CHARINDEX` 内置函数，以及一个递归 CTE。

基于编号列表的表值函数

首先，我将解释构成最终表值函数的代码块。我们需要使用 CTE 创建一个编号列表，如下所示：

;WITH
    L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
    Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
SELECT * FROM Numbers

此 CTE 从 1 开始创建数字列表，直到 **POWER(POWER(POWER(POWER(2, 2), 2), 2), 2)**，即直到 **65536**。

现在考虑下面的代码片段，其中 `@list` 和 `@delim` 变量已被赋值。

DECLARE
	@list NVARCHAR(MAX), @delim NCHAR(1)

SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','

;WITH
	L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
	L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
	L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
	L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
	L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
	Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4
)
SELECT
	@list List,
	SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) - _
							Number) AS Value,
	Number AS StartingFrom,
	CHARINDEX(@delim, @list + @delim, Number) AS DelimeterPosition
FROM Numbers
WHERE Number <= CONVERT(INT, LEN(@list))
  AND SUBSTRING(@delim + @list, Number, 1) = @delim

`SUBSTRING` 语句从 `@list` 中从字符位置（1、5、11 和 16）开始截取字符。

SUBSTRING(@list, Number, CHARINDEX(@delim, @list + @delim, Number) – Number)

截取的字符数由 `CHARINDEX` 决定，它会在每次找到分隔符字符的行中返回 4、10、15、20。

CHARINDEX(@delim, @list + @delim, Number) - Number

上述 `SELECT` 语句仅在 `@list` 变量中的字符数满足条件时才有效。

Number <= CONVERT(INT, LEN(@list))

通过使用 `SUBSTRING` 函数创建的“`WHERE`”条件过滤掉输出列表中的重复值，只有在找到分隔符时才返回一个值。

SUBSTRING(@delim + @list, Number, 1) = @delim

上面代码片段的输出将是：

List                    Value   Starting From   Delimiter Position 
------------------------------------------------------------------
aaa,bbbbb,cccc,dddd     aaa         1                   4
aaa,bbbbb,cccc,dddd     bbbbb       5                   10
aaa,bbbbb,cccc,dddd     cccc        11                  15
aaa,bbbbb,cccc,dddd     dddd        16                  20

基于编号列表的表值函数：实现

现在，我们将上述所有 SQL 片段结合起来，创建表值函数，它将解析字符串并返回一个包含 `ID` 和 `Data` 两列的表。

CREATE FUNCTION [dbo].[TableFormDelimetedString]
(
	@param		NVARCHAR(MAX),
	@delimeter	NCHAR(1)
)
RETURNS @tmp TABLE
(
	ID		INT 	IDENTITY (1, 1),
	Data		Varchar(MAX)
)
BEGIN
;WITH
    L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
    L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
    L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
    L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
    L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
    Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS Number FROM L4)
INSERT INTO @tmp (Data)
SELECT LTRIM(RTRIM(CONVERT(NVARCHAR(4000),
		SUBSTRING(@param, Number,
			CHARINDEX(@delimeter, @param + @delimeter, Number) - Number
				)
		))) AS Value
       FROM   Numbers
       WHERE  Number <= CONVERT(INT, LEN(@param))
         AND  SUBSTRING(@delimeter + @param, Number, 1) = @delimeter
RETURN
END

基于编号列表的表值函数：用法

现在，如果我们像这样调用上述函数：

SELECT * FROM [TableFormDelimetedString]('Andy:Roger:Thomas:Rob:Victor',':')

我们将得到以下结果集：

ID      Data
--------------------
1       Andy
2       Roger
3       Thomas
4       Rob
5       Victor

基于递归 CTE 的表值函数

在这里，我将首先解释构成最终表值函数的代码块。众所周知，在递归 CTE 中，我们有一个锚定部分和一个递归部分。但如果我们创建一个仅包含锚定部分的 CTE，它看起来会像这样：

DECLARE
	@list NVARCHAR(MAX), @delim NCHAR(1)

SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','

;WITH CTETable (start, stop)
AS
(
	SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list + @delim, 1)
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list, start,
				 CASE
				 WHEN stop > 0
				 THEN stop - start
				 ELSE 0
				 END
				))) AS Data
	start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable

上述 SQL 的输出将是：

List                    Value   Starting From   Delimiter Position 
------------------------------------------------------------------
aaa,bbbbb,cccc,dddd     aaa         1               4

现在，通过向上述 CTE 添加一个迭代 `stop` 变量的递归成员，SQL 如下所示：

DECLARE
	@list NVARCHAR(MAX), @delim NCHAR(1)

SELECT @list = 'aaa,bbbbb,cccc,dddd', @delim = ','

;WITH CTETable (start, stop)
AS
(
	SELECT start = CONVERT(bigint, 1), stop = CHARINDEX(@delim, @list + @delim, 1)
	UNION ALL		-- added for recursive part of CTE
	SELECT start = stop + 1, stop = CHARINDEX(@delim, @list + @delim, stop + 1) _
	     FROM CTETable WHERE stop > 0	-- added for recursive part of CTE
)
SELECT @list List, LTRIM(RTRIM(SUBSTRING(@list, start,
				 CASE
				 WHEN stop > 0
				 THEN stop - start
				 ELSE 0
				 END
				))) AS Data
	start AS StartingFrom, stop AS DelimiterPosition
FROM CTETable
WHERE stop > 0

并给出以下结果集：

List                    Value   Starting From   Delimiter Position 
------------------------------------------------------------------
aaa,bbbbb,cccc,dddd     aaa         1               4
aaa,bbbbb,cccc,dddd     bbbbb       5               10
aaa,bbbbb,cccc,dddd     cccc        11              15
aaa,bbbbb,cccc,dddd     dddd        16              20

基于递归 CTE 的表值函数：实现

最后，我们从上述代码块创建表值函数，如下所示：

CREATE FUNCTION [dbo].[TableFormDelimetedStringWithoutNumberList]
(
	@list	NVARCHAR(MAX),
	@delim	NCHAR(1) = ','
)
RETURNS @tmp TABLE
(
	ID	INT     IDENTITY (1, 1),
	Data	Varchar(MAX)
)
BEGIN
	;WITH CTETable (start, stop)
	AS
	(
		SELECT start = CONVERT(bigint, 1),
			stop = CHARINDEX(@delim, @list + @delim)
		UNION ALL	-- added for recursive part of CTE
		SELECT start = stop + 1,
		  stop = CHARINDEX(@delim, @list + @delim, stop + 1)-- added for 
							-- recursive part of CTE
		FROM   CTETable
		WHERE  stop > 0
	)
	INSERT INTO @tmp (Data)
	SELECT LTRIM(RTRIM(SUBSTRING(@list, start,
		CASE
			WHEN stop > 0
			THEN stop - start
			ELSE 0
		END))) AS Data
	FROM CTETable
	WHERE stop > 0
RETURN
END

基于递归 CTE 的表值函数：用法

现在，如果我们像这样调用上述函数：

SELECT * FROM [TableFormDelimetedStringWithoutNumberList]_
			('Andy:Roger:Thomas:Rob:Victor', ':')

我们将得到以下结果集：

ID  Data 
-------------
1   Andy
2   Roger
3   Thomas
4   Rob
5   Victor

我喜欢这两种实现方式的原因是，循环由 SQL Server 数据库引擎本身处理，这肯定比开发人员编写的显式 SQL 循环代码更有效。

使用表值函数实现

众所周知，上述两种表值函数实现，即 `TableFormDelimetedString` 和 `TableFormDelimetedStringWithoutNumberList`，可以从分隔符字符串及其位置（作为 `ID`）返回一个表。现在我们将看到如何从一个示例 SP 调用它们。

为了说明目的，我使用了下表，其中我们需要插入应用程序层发送的多条记录。

--Creating a base table
CREATE TABLE Emp(
	ID		    INT		IDENTITY (1, 1),
	Name		VARCHAR(50),
	Salary	    INT
);

为了调用这两个函数，我们创建一个 SP，它接受多个分隔符字符串和一个分隔符作为输入。此 SP 将调用其中一个表值函数，该函数又会将分隔符字符串转换为表。

在我们示例 SP 中，我们有员工姓名和他们的薪资在两个不同的分隔符字符串中，需要进行解析。由于上述表值函数可以将分隔符字符串转换为带 ID 列的表，我们将使用此 ID 列来连接由两个不同分隔符字符串创建的两个不同表，即员工姓名和薪资。

使用表值函数实现：示例 SP 实现

接受多个参数并将记录插入基础表（`Emp`）的示例 SP 如下所示：

CREATE PROCEDURE InsertEmp1
(
	@sName	NVARCHAR(MAX),
	@sSalary	NVARCHAR(MAX),
	@delimeter	CHAR (1)
)
AS
BEGIN
	INSERT INTO Emp (Name, Salary)
	SELECT NameList.Data, SalaryList.Data
	FROM TableFormDelimetedString (@sName, @delimeter) NameList
		INNER JOIN TableFormDelimetedString (@sSalary, @delimeter) SalaryList
			ON NameList.ID = SalaryList.ID
RETURN 0
END

使用表值函数实现：示例 SP 用法

在这里，我传递两个由“`:`”分隔的字符串“`'Andy:Roger:Thomas:Rob:Victor'`”和“`'100:200:1000:500:50'`”给 SP，它将相应的记录插入目标表中。

EXEC InsertEmp1 'Andy:Roger:Thomas:Rob:Victor', '100:200:1000:500:50', ':'

我们可以通过从目标表进行选择来查看结果：

SELECT * FROM Emp
ID  Name        Salary
------------------------
1   Andy        100
2   Roger       200
3   Thomas      1000
4   Rob         500
5   Victor      50

将 XML 数据发送到 SP

当我们创建 XML 数据时，有两种不同的方法可以将数据放入 XML 中：

  • 基于属性
  • 基于元素

解析基于属性的 XML

在这种方法中，我们将目标数据放在属性中，属性被包含在特定的元素中。基本上，我们需要在 XML 格式中创建分层数据。

假设我们要发送多个员工的姓名和薪资，我们创建一个如下所示的 XML：

Declare @xml	XML
SET @xml = N'
<ROWS>
	<ROW Name="Richard" Salary="1100"/>
	<ROW Name="Cliff" Salary="1200"/>
	<ROW Name="Donna" Salary="13000"/>
	<ROW Name="Ann" Salary="1500"/>
</ROWS>'
SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
	T.Item.value('@Salary', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)

首先，我想解释不同的 `XQuery` 函数，我使用它们从上述 XML 中获取数据并在 SP 中使用。在上述 XML 变量中，我的顶层元素是 `ROWS`，它包含多个 `ROW` 标签，这些标签进一步包含实际数据标签，例如 `Name` 和 `Salary` 作为属性。

查看上述 SQL 的 `FROM` 子句，它有一个 `nodes()` 函数，该函数接受从根元素开始的目标元素路径，数据将从该路径读取。`nodes()` 函数返回一个行集，其中包含 XML 数据的逻辑副本。使用 `value()` 函数，您可以从行集中检索多个值。`value()` 函数接受两个参数，第一个参数是前面带有“`@`”的属性名，第二个参数是用于将该值转换为 SQL Server 数据类型的 SQL Server 数据类型。我们需要数据类型转换，因为所有属性值都被视为 `string`。

为了说明目的，我使用了下表，其中我们需要插入应用程序层发送的多条记录。

SP 实现：解析基于属性的 XML

下面的 SP 读取基于属性的 XML 中的数据并将其插入我们的基础表（`Emp`）中：

CREATE PROCEDURE [dbo].[InsertEmp2]
(
	@xml		XML
)
AS
BEGIN
	INSERT INTO Emp (Name, Salary)
	SELECT T.Item.value('@Name', 'VARCHAR(50)') Name,
		T.Item.value('@Salary', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)
RETURN 0
END

注意：XQuery 函数是区分大小写的。

SP 实现：解析基于属性的 XML：用法

在这里，我创建了一个 XML 变量，并用具有元素和属性的有效 XML 填充 `xml` 变量，然后将其传递给 SP。如果您没有任何属性值，则可以省略整个属性，这将返回 `NULL`。请确保您的目标表在该列中接受 `NULL`。

Declare @x	XML
SET @x = N'
<ROWS>
    <ROW Name="Richard" Salary="1100"/>
    <ROW Name="Cliff" Salary="1200"/>
    <ROW Name="Donna" Salary="13000"/>
    <ROW Name="Ann" Salary="1500"/>
</ROWS>'

EXEC InsertEmp2 @x
SELECT * FROM Emp
ID  Name        Salary 
--------------------------
1   Richard     1100
2   Cliff       1200
3   Donna       13000
4   Ann         1500

解析基于元素的 XML

在这种方法中，我们将目标数据放在元素下方。基本上，我们需要在 XML 格式中创建分层数据。如果我们想发送多个员工的 `name` 和 `salary`，我们创建一个如下所示的 XML：

Declare @xml	XML
SET @xml = N'
<ROWS>
    <ROW>
        <Name>Tom</Name>
        <Salary>2100</Salary>
    </ROW>
    <ROW>
        <Name>Nuk</Name>
        <Salary>2200</Salary>
    </ROW>
    <ROW>
        <Name>Gilbert</Name>
        <Salary>2300</Salary>
    </ROW>
    <ROW>
        <Name>Mat</Name>
        <Salary>2600</Salary>
    </ROW>
</ROWS>'

在这里，每个 `ROW` 元素代表一个员工的记录，包含 `name` 和 `salary`。这些行被包含在称为 `ROWS` 的顶层元素中。每个 `ROW` 元素包含两个元素，分别是 `name` 和 `salary`。此外，这些元素（`name` 和 `salary`）在开始和结束元素 XML 标签之间包含数据。

SELECT  T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
		T.Item.query('./Salary').value('.', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)

前面我们讨论了 `nodes()` 和 `value()` XQuery 函数。现在，我想解释一下这里使用的 `query()` 函数。`query()` 函数接受一个 XQuery 表达式作为参数（在本例中是 './Name' 或 './Salary'），并以 XML 格式返回指定的 XML 元素，例如：

query('./Name')

将返回：

<Name>……</Name>

从行集中。

但如果你看看 `value()` 函数。

value('.', 'VARCHAR(50)')

它有一个点“`.`”作为第一个参数，表示读取当前元素的值，第二个参数会将读取的值转换为指定的 SQL Server 数据类型。

SP 实现：解析基于元素的 XML

下面的 SP 从基于元素的 XML 中读取数据并将其插入我们的基础表（`Emp`）中：

CREATE PROCEDURE InsertEmp3
(
	@xml		XML
)
AS
BEGIN
	INSERT INTO Emp (Name, Salary)
	SELECT  T.Item.query('./Name').value('.', 'VARCHAR(50)') Name,
		T.Item.query('./Salary').value('.', 'INT') Salary
	FROM   @xml.nodes('/ROWS/ROW') AS T(Item)
RETURN 0
END

SP 实现：解析基于元素的 XML：用法

在这里，我创建了一个 XML 变量，并用我在 SP 中引用的有效 XML（包含元素）来填充它。如果您没有任何元素值，则可以省略整个元素，例如：

<ROWS>
    <ROW>
        <Name>Tom</Name>
        <Salary>2100</Salary>
    </ROW>
    <ROW>
        <Name>Nuk</Name>
    </ROW>
    <ROW>
        <Name>Mat</Name>
        <Salary>2600</Salary>
    </ROW>
</ROWS>

在上面的 XML 中，我没有任何关于第二个员工“`Nuk`”的数据。所以我根本没有包含 `salary` 元素。

Declare @x	XML
SET @x = N
    '<ROWS>
        <ROW>
            <Name>Tom</Name>
            <Salary>2100</Salary>
        </ROW>
        <ROW>
            <Name>Nuk</Name>
            <Salary>2200</Salary>
        </ROW>
        <ROW>
            <Name>Gilbert</Name>
            <Salary>2300</Salary>
        </ROW>
        <ROW>
            <Name>Mat</Name>
            <Salary>2600</Salary>
        </ROW>
    </ROWS>'

EXEC InsertEmp3 @x
SELECT * FROM Emp    
ID  Name        Salary 
--------------------------
1   Richard     1100
2   Cliff       1200
3   Donna       13000
4   Ann         1500
5   Tom         2100
6   Nuk         2200
7   Gilbert     2300
8   Mat         2600

分隔符字符串和 XML 数据解析的比较

在本文中，我尝试介绍了以下两种有效的解决方法来解决将多行发送到数据库进行更新的问题：

  1. 分隔符字符串方法
  2. XML 数据方法

现在，我将对这两种方法进行比较，并重点介绍它们的优点和缺点。

分隔符字符串方法

优点

  • 在数据库端易于解析
  • 在应用程序层易于创建

缺点

  • 手动解析，需要编写解析逻辑（在本例中，我们编写了表值函数）。
  • 需要传递与参数数量相同的分隔符字符串。
  • 由于分隔符字符串将作为 `string` 数据类型传递，因此 SQL Server 2005 支持的字符串大小限制（包括分隔符字符在内最多 4000 / 8000 个字符）就显得很重要。
  • 选择一个分隔符字符，因为它永远不应出现在您的数据中。

XML 数据

优点

  • 支持大量数据（最多 2 GB）
  • 只需从应用程序层发送一个参数
  • 无需手动解析，由 `XQuery` 函数处理
  • 可以与 XSD 验证传递的 XML

缺点

  • 解析复杂（需要了解 `XQuery` 函数）
  • 在应用程序层创建 XML 数据困难

摘要

在本文中，我们讨论了如何在 SQL Server 2005 中解析分隔符字符串，使用两种表值函数实现，而无需编写显式的 `loop` 语句。我们还讨论了 CTE（公共表表达式），它是 SQL Server 2005 的一项非常好的（尽管有点复杂）功能，以及 `XQuery` 函数以及如何使用它们将 XML 转换为表。

希望这能帮助您在设计使用 SQL Server 2005 的应用程序时解决多行发送问题。

本文也适用于应用程序在断开连接的环境中工作的情况。用户可以使用这两种方法中的任何一种来保存断开连接环境中的数据。当用户在线或连接到数据库并想要更新已修改的记录时，所有这些数据都可以通过分隔符 `string` 或 XML 传递给数据库进行更新。

Using the Code

本文分为两部分：

  • T-SQL
  • 客户端应用程序

T-SQL

您需要下载附加的 ZIP 文件并在您的数据库实例上运行 `.SQL` 文件。一旦创建了表、表值函数和过程，您就可以按照上述方法执行 SP。

客户端应用程序

附加的 ZIP 文件包含一个使用 C# 开发的客户端应用程序，用于演示。

关注点

在这里，我们讨论了：

  • 表值函数
  • `CHARINDEX`、`SUBSTRING` 等内置 T-SQL 函数。
  • XML 解析
  • `XQuery` 函数
  • 如何在单个 `SELECT` 语句中实现循环逻辑

历史

  • 2009年8月18日：初始版本
© . All rights reserved.