在SQL Server 2008中引入了表值参数,这是一种可以一次性传递多行数据给存储过程或函数的参数类型。在某些情况下,如果存储过程需要多次调用且每次调用的参数值不同,使用表值参数可以避免客户端和数据库之间的多次往返通信。
本文不是SQL Server特定类在C#中的使用示例,也不是编程风格参考。
示例中使用了两种类型:
这些类型定义了后续参数的结构。定义如下:
CREATE TYPE ArtistType AS TABLE (
[Artist#] int,
[Name] nvarchar(100)
);
CREATE TYPE RecordType AS TABLE (
[Record#] int,
[Artist#] int,
[Name] nvarchar(100),
[Year] int
);
有两个目标表由存储过程填充。在这个示例中,表中的数据不会被修改,但有一个技巧:客户端为每个艺术家和记录定义了一个主键,并且从记录到艺术家定义了一个外键。这些信息在存储过程中使用,但数据库中的实际主键由SQL Server自动生成。表定义如下:
CREATE TABLE Artist (
[Artist#] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Name] nvarchar(100) NOT NULL
);
CREATE TABLE Record (
[Record#] int NOT NULL IDENTITY(1,1) PRIMARY KEY,
[Artist#] int NOT NULL FOREIGN KEY REFERENCES Artist([Artist#]),
[Name] nvarchar(100) NOT NULL,
[Year] int NULL
);
存储过程由两个循环组成。外循环获取每个艺术家并将其插入数据库。之后,它获取新行分配的标识。然后,获取这个单个艺术家的所有记录,并设置外键为艺术家表中对应的主键。
CREATE PROCEDURE [dbo].[AddShoppings](
@Artists dbo.ArtistType READONLY,
@Records dbo.RecordType READONLY
) AS BEGIN
DECLARE @artist int;
DECLARE @artistIdentity int;
DECLARE @name varchar(100);
DECLARE @year int;
DECLARE artistCursor CURSOR FOR SELECT [Artist#], [Name] FROM @Artists;
OPEN artistCursor;
FETCH NEXT FROM artistCursor INTO @artist, @name;
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO Artist ([Name]) VALUES (@name);
SET @artistIdentity = @@IDENTITY;
DECLARE recordsCursor CURSOR FOR SELECT [Name], [Year] FROM @Records WHERE [Artist#] = @artist;
OPEN recordsCursor;
FETCH NEXT FROM recordsCursor INTO @name, @year;
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO Record ([Artist#], [Name], [Year]) VALUES (@artistIdentity, @name, @year);
FETCH NEXT FROM recordsCursor INTO @name, @year;
END;
CLOSE recordsCursor;
DEALLOCATE recordsCursor;
FETCH NEXT FROM artistCursor INTO @artist, @name;
END;
CLOSE artistCursor;
DEALLOCATE artistCursor;
END;
程序是一个简单的控制台应用程序。它:
要使用此代码,需要安装SQL Server2008实例并在其中创建数据库。之后,通过app.config配置SQL Server实例名称和数据库名称。配置可能如下:
<applicationSettings>
<TableValuedParameters.Properties.Settings>
<setting name="DataSource" serializeAs="String">
<value>MyMachine\SqlServerInstanceName</value>
</setting>
<setting name="DatabaseName" serializeAs="String">
<value>DatabaseNameToUse</value>
</setting>
</TableValuedParameters.Properties.Settings>
</applicationSettings>
实际调用数据库很简单。参数的关键字是System.Data.SqlDbType.Structured。这告诉SQL客户端数据是表格格式的,基于此,可以使用一个包含所有内容的DataTable对象作为参数:
command.CommandText = "AddShoppings";
command.CommandType = System.Data.CommandType.StoredProcedure;
parameter = command.Parameters.AddWithValue("@Artists", artist);
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter = command.Parameters.AddWithValue("@Records", record);
parameter.SqlDbType = System.Data.SqlDbType.Structured;
command.Transaction = transaction;
command.ExecuteNonQuery();