C# 和表值参数

• 下载源文件 - 1.82 MB

引言

在本文中，我们探讨 SQL Server 2008 的表值参数。关于此主题的文章有很多，有些描述了 SQL Server 端的内容，有些则讨论了如何从 .NET 访问表值函数。

我找不到一篇清晰的文章专门描述如何使用 C# 和 SQL Server 2008 来处理表值参数的简单示例，经过一些研究，我写了这篇文章，以便其他人可以从我的发现中受益。

表值参数是一种将大量数据从 ADO.NET 传递到 SQL Server 的简单机制。它们允许数据在 SQL Server 中作为临时表可用。

过去，这项任务是通过 XML 完成的，其中批量数据被建模成一种分层结构并传递给存储过程。然后，存储过程会将该结构转换回临时表，以便以关系方式处理数据。

目标读者

本文的目标读者应熟悉 ADO.NET、C# 和 SQL Server 2008。

目标

为了演示一个端到端的表值参数示例。

代码

我们将直接进入本文的代码。我们将讨论从 SQL Server 表到 C# 代码的每一层。

数据库表

在本节中，我们将描述示例问题的表架构。此代码中的表将是一个简单的表，其中包含一个标识列，以及一个 nvarchar 和一个 integer 列。nvarchar 列不允许 null，而整数列允许 null。相应的架构创建脚本如下：

--This is the database table
CREATE TABLE dbo.SampleTable
(
Id int NOT NULL IDENTITY (1, 1),
SampleString nvarchar(64) NOT NULL,
SampleInt int NULL
) ON [PRIMARY]

表值参数类型

要将数据作为表值参数传递，必须创建一个新的数据库类型。该类型定义本质上描述了一行是什么样的，我们将传递一堆这样的行。相应的脚本如下：

-- Create a table data type
CREATE TYPE [dbo].[SampleDataType] As Table
(
--This type has structure similar to the DB table 
SampleString Nvarchar(64) Not Null -- Having one String
, SampleInt Int -- and one int
)

人们可能会注意到此类型的结构与表相似，但是重要的是，我们需要创建最方便传递数据的类型定义，这可能意味着反规范化。

存储过程

存储过程是一段相当简单的代码，它将 SampleDataType 类型作为输入参数。在存储过程内部，该参数可作为一个临时表使用。但是，该表必须是只读的。

--This is the Stored Procedure
CREATE PROCEDURE [dbo].[SampleProcedure]
(
-- which accepts one table value parameter. 
-- It should be noted that the parameter is readonly
@Sample As [dbo].[SampleDataType] Readonly
)
AS

Begin
-- We simply insert values into the DB table from the parameter
-- The table value parameter can be used like a table with only read rights
Insert Into SampleTable(SampleString,SampleInt)
Select SampleString, SampleInt From @Sample
End

数据库测试脚本

此时，我们应该使用此脚本快速测试我们的数据库是否有任何错误，该脚本还描述了将表值参数从一个存储过程传递到另一个存储过程的机制。

-- This is the sample script to test the SP
-- An instance of the Table parameter type is created
Declare @SampelData As [dbo].[SampleDataType]
-- and then filled with the set of values
Insert Into @SampelData(SampleString, SampleInt) Values('1',1);
Insert Into @SampelData(SampleString, SampleInt) Values('2',null);
Insert Into @SampelData(SampleString, SampleInt) Values('3',3);
Select * From @SampelData
-- we then call the SP to store the values
Exec SampleProcedure @SampelData
Select * From SampleTable

C# 代码

在 C# 代码中，我们需要解决两个问题才能使用所需的存储过程。

1. 创建一个与表值参数等效的数据结构 - 就像 nvarchar 在 C# 端表示为 string 一样。
2. 将此数据传递给存储过程。

数据表示

有许多机制可用于表示数据，例如将数据表示为 DataTable、IEnumerable、Linq 对象、数据读取器等。在本文中，我们将重点介绍 DataTable。我们所做的就是创建一个 DataTable，定义与我们的表数据类型并行的列，然后填充它们。

//To represent the table parameter in C#, we need to either 
//have a set of entities which are IEnumreable 
//or a data reader or a Data table.
//In this example we create a data table with same name as the type we have in the DB 
DataTable dataTable = new DataTable("SampleDataType"); 
//we create column names as per the type in DB 
dataTable.Columns.Add("SampleString", typeof(string)); 
dataTable.Columns.Add("SampleInt", typeof(Int32)); 
//and fill in some values 
dataTable.Rows.Add("99", 99); 
dataTable.Rows.Add("98", null); 
dataTable.Rows.Add("97", 99); 

传递数据

要传递数据，必须将其表示为 SqlParameter。此参数的类型是 Structured。详细信息如下面的代码片段所示。调用 SP 的其他代码很简单，可以在提供的代码中看到。

SqlParameter parameter = new SqlParameter(); 
//The parameter for the SP must be of SqlDbType.Structured 
parameter.ParameterName="@Sample"; 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dataTable; 
command.Parameters.Add(parameter); 

请注意，附加的代码是在 Visual Studio 2010 + SQL Server 2008 中开发的，但是它也可以在 Visual Studio 2005 和 SQL Server 2008 中工作。

历史

• 2009 年 8 月 20 日：初始发布