如何将多个记录传递给存储过程

• 下载源代码 - 7.03 KB

引言

本文档描述了在调用存储过程时如何使用表值参数。表值参数是在 SQL Server 2008 中引入的。使用这种参数类型，可以将包含多行数据的表传递给存储过程或函数。在某些情况下，如果对相同的过程多次调用，但参数值不同，则此技术可以消除客户端和数据库之间多次往返的需求。

本文档不应被视为在 C# 中使用 SQL Server 特定类的示例，更不是编码风格的参考。

类型定义

示例使用了两种类型：ArtistType 和 RecordType。这些类型定义了后续参数的结构。定义如下：

-- Create the type for artist
CREATE TYPE ArtistType AS TABLE (
   [Artist#] int,
   [Name]    nvarchar(100)
);
-- Create the type for record
CREATE TYPE RecordType AS TABLE (
   [Record#] int,
   [Artist#] int,
   [Name]    nvarchar(100),
   [Year]    int
);

表定义

有两个目标表，由过程填充。在本例中，表中的数据不会被修改，但有一个技巧：客户端为每个艺术家和记录定义主键，并从记录到艺术家的外键。这些信息在存储过程中使用，但数据库中的实际主键是由 SQL Server 自动生成的。这些表是：

-- Create artist table
CREATE TABLE Artist (
   [Artist#] int           NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [Name]    nvarchar(100) NOT NULL
);
-- Create record table
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
   -- variables to use
   DECLARE @artist         int;
   DECLARE @artistIdentity int;
   DECLARE @name           varchar(100);
   DECLARE @year           int;

   -- cursor for artists parameter
   DECLARE artistCursor CURSOR FOR 
        SELECT [Artist#], [Name]
        FROM @Artists;

   -- loop through artists
   OPEN artistCursor;
   FETCH NEXT FROM artistCursor INTO @artist, @name;
   WHILE @@FETCH_STATUS = 0
   BEGIN
      -- insert the artist
      INSERT INTO Artist ([Name]) VALUES (@name);
      SET @artistIdentity= @@IDENTITY;

      -- cursor for records parameter
      DECLARE recordsCursor CURSOR FOR 
         SELECT [Name], [Year]
         FROM @Records
         WHERE [Artist#] = @artist;

      -- fetch records and insert them
      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;

   -- clean-up
   DEALLOCATE artistCursor;
END;

C# 代码

该程序是一个简单的控制台应用程序。它

• 构建并填充 Artist 和 Record 的数据表
• 创建连接
• 创建数据库对象
• 开始事务
• 调用过程
• 提交工作

为了使用这段代码，你需要安装一个 SQL Server 2008 实例并在其中创建一个数据库。之后，SQL Server 实例名称和数据库名称通过 app.config 进行配置。它看起来像这样：

...
<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();

差不多就是这样了。其余逻辑都在代码示例中。尽情享受吧！

历史

• 2008 年 12 月 2 日：创建。