有时，在数据库中创建、更新或删除一组行非常有用。 可以使用多种方法来实现这一目标。 其中一个是使用 CLR 集成用户定义数据类型，将信息数组从客户端传递到服务器上的公共语言运行时（CLR）集成存储过程。 此类用户定义的数据类型的性质将提供给服务器的数据的大小限制为 8000 字节。 因此，此方法对大型或复杂数据不满意。 如果所作的数据很小且简单，则此方法比为每个行调用存储过程更有效。 通过传递数组，数据顺序将保留给那些具有重要顺序的应用程序。此示例包含以下内容：
ContactTypeNames用户定义的数据类型。 这包含所需联系人类型名称的列表。作为
usp_EnsureContactTypeNamesMicrosoft Visual C# 或 Microsoft Visual Basic 方法实现的存储过程。 这会接受用户定义的数据类型的实例ContactTypeNames，并在表中插入新行Person.ContactType，以获取用户定义数据类型实例中包含的任何联系人姓名（该实例中尚不存在）。TestArrayParameter控制台应用程序。 这会基于传入的ContactTypeNames命令行参数创建用户定义的数据类型的实例，然后通过将用户定义的数据类型实例作为参数传递来调用usp_EnsureContactTypeNames存储过程。
先决条件
若要创建并运行此项目，必须安装以下软件：
SQL Server 或 SQL Server Express。 可以从 SQL Server Express 文档和示例网站免费获取 SQL Server Express
SQL Server 开发人员网站提供的 AdventureWorks 数据库
.NET Framework SDK 2.0 或更高版本或Microsoft Visual Studio 2005 或更高版本。 可以免费获取 .NET Framework SDK。
此外，必须满足以下条件：
正在使用的 SQL Server 实例必须启用 CLR 集成。
若要启用 CLR 集成，请执行以下步骤：
启用 CLR 集成
- 执行以下 Transact-SQL 命令：
sp_configure 'clr enabled', 1GORECONFIGUREGO注释
若要启用 CLR，必须具有
ALTER SETTINGS由服务器角色成员sysadminserveradmin隐式持有的服务器级别权限。AdventureWorks 数据库必须安装在正在使用的 SQL Server 实例上。
如果你不是正在使用的 SQL Server 实例的管理员，则必须有管理员授予 CreateAssembly 权限才能完成安装。
生成示例
使用以下说明创建并运行示例：
打开 Visual Studio 或 .NET Framework 命令提示符。
如有必要，请为示例创建目录。 对于此示例，我们将使用 C：\MySample。
在 c：\MySample 中，创建
ContactTypeNames.vb（针对 Visual Basic 示例）或ContactTypeNames.cs（对于 C# 示例），并将相应的 Visual Basic 或 C# 示例代码（如下所示）复制到文件中。根据所选语言，从命令行提示符将示例代码编译到所需的程序集中。
Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library ContactTypeNames.vbCsc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library ContactTypeNames.cs
在 c：\MySample 中，创建
Program.vb（针对 Visual Basic 示例）或Program.cs（对于 C# 示例），并将相应的 Visual Basic 或 C# 示例代码（如下所示）复制到文件中。在文件程序（围绕第 24 行）中找到相应的行，并替换为
XXXSQL Server 实例的名称Dim connection As New SqlConnection("data source=XXX;initial catalog=AdventureWorks;Integrated Security=SSPI")using (SqlConnection connection = new SqlConnection("data source=XXX;initial catalog=AdventureWorks;Integrated Security=SSPI"))
根据所选语言，从命令行提示符将示例代码编译到所需的可执行文件中。
vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Deployment.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Drawing.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Windows.Forms.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll,C:\MySample\ContactTypeNames.dll /out:TestArrayParameter Program.vbCsc /reference:ContactTypeNames.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /out:TestArrayParameter.exe Program.cs
将 Transact-SQL 安装代码复制到文件中，并将其
Install.sql另存为示例目录中。如果示例安装在其他
C:\MySample\目录中，则按指示编辑该文件Install.sql以指向该位置。通过执行来部署程序集、存储过程和函数
sqlcmd -E -I -i install.sql
通过在命令提示符处执行以下行来测试应用程序：
TestArrayParameter "Executive Sales Representative" "Executive Sales Manager"
将 Transact-SQL 清理脚本复制到文件中，并将其
cleanup.sql另存为示例目录中。使用以下命令执行脚本
sqlcmd -E -I -i cleanup.sql
示例代码
下面是此示例的代码列表。
这是库的代码 ContactTypeNames.
C#（编程语言）
#region Using directives
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Globalization;
using Microsoft.SqlServer.Server;
#endregion
// This class is used to demonstrate passing an array of a fairly small number of reasonably small strings
// to a CLR integration based stored procedure on the server. Because a UDT is limited to 8000 bytes
// this approach will not work well for large numbers of strings or long strings. See the contact
// creation stored procedure in the AdventureWorks CLR integration sample for an alternative approach
// using XML which does not have these limitations.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Microsoft.SqlServer.Server.Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000)]
public class ContactTypeNames : INullable, Microsoft.SqlServer.Server.IBinarySerialize
{
#region Constructors
private const int maxByteSize = 8000;
public ContactTypeNames()
{
}
public ContactTypeNames(string[] names)
{
int numberOfCharacters = 0;
foreach (string name in names)
{
if (name.Length == 0)
throw new ArgumentException("Zero length names are not allowed");
numberOfCharacters += name.Length;
}
int dataByteSize = numberOfCharacters*2 //UTF-16 characters take 2 bytes
+ names.Length*4 //Four byte header for each string
+ 4 //Four byte header for null string at end
+ 1; //One byte boolean for null flag
if (dataByteSize >= maxByteSize)
throw new ArgumentException(string.Format(CultureInfo.InvariantCulture, "Data provided occupies {0} bytes but only {1} bytes "
+ "are available", dataByteSize, maxByteSize));
this._names = names;
}
#endregion
#region Accessors
public string[] GetTypeNameArray()
{
//Don't let caller modify our copy of the array
return (string[])_names.Clone();
}
//This has an odd API because we can only define Transact-SQL functions on static methods.
[SqlFunctionAttribute(FillRowMethodName = "FillNameRow")]
public static IEnumerable GetContactTypeNames(ContactTypeNames names)
{
if (names == null)
throw new ArgumentNullException("names");
return names.GetTypeNameArray();
}
public static void FillNameRow(object nameArrayElement, out string contactName)
{
contactName = (string)nameArrayElement;
}
#endregion
#region String Conversions
/// <summary>
/// The string format for contact type names is a sequence of names separated by commas
/// </summary>
/// <param name="s">a string containing contact type names separated by commas</param>
/// <returns>An instance of contact type name containing the specified names</returns>
[Microsoft.SqlServer.Server.SqlMethod(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.None, IsDeterministic = false, IsMutator = false, IsPrecise = false,
SystemDataAccess = Microsoft.SqlServer.Server.SystemDataAccessKind.None)]
public static ContactTypeNames Parse(SqlString s)
{
if (s.IsNull)
return Null;
return new ContactTypeNames(s.Value.Split(new char[] {','}));
}
/// <summary>
/// Convert the contact type names to a string
/// </summary>
/// <returns>The contact type names separated by commas</returns>
public override string ToString()
{
if (this.IsNull)
return null;
StringBuilder sb = new StringBuilder();
foreach (string name in _names)
{
if (sb.Length > 0) sb.Append(", ");
sb.Append(name);
}
return sb.ToString();
}
#endregion
#region INullable Members
public static ContactTypeNames Null
{
get
{
return new ContactTypeNames();
}
}
public bool IsNull
{
get
{
return _names == null;
}
}
#endregion
#region IBinarySerialize Members
//Format:
//Byte 1: Null flag (boolean) (true = null)
//Byte 2 - 7994: Strings with 4 byte length headers,
// last string is a zero length string.
//This format is in part dictated by how the BinaryWriter serializes strings. See
//the Microsoft .NET Framework documentation on System.IO.BinaryWriter for more details.
public void Read(System.IO.BinaryReader r)
{
if (r.ReadBoolean())
{
_names = null;
return;
}
List<String> nameList = new List<String>();
string name;
while ((name = r.ReadString()).Length != 0)
{
nameList.Add(name);
}
_names = new string[nameList.Count];
nameList.CopyTo(_names);
}
public void Write(System.IO.BinaryWriter w)
{
if (w == null)
throw new ArgumentNullException("w");
w.Write(this.IsNull);
foreach (string name in _names)
{
w.Write(name);
}
w.Write(string.Empty);
}
#endregion
#region Private Implementation
private string[] _names;
#endregion
}
Visual Basic
#Region "Using directives"
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Collections
Imports System.Collections.Generic
Imports System.Text
Imports System.Globalization
Imports Microsoft.SqlServer.Server
Imports System.Runtime.InteropServices
#End Region
' This class is used to demonstrate passing an array of a fairly small number of reasonably small strings
' to a CLR integration based stored procedure on the server. Because a UDT is limited to 8000 bytes
' this approach will not work well for large numbers of strings or long strings. See the contact
' creation stored procedure in the AdventureWorks CLR integration sample for an alternative approach
' using XML which does not have these limitations.
<Serializable()> _
<SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=True, maxByteSize:=8000), CLSCompliant(False)> _
Public Class ContactTypeNames
Implements INullable, IBinarySerialize
#Region "Constructors"
Private Const maxByteSize As Integer = 8000
Public Sub New()
End Sub
Public Sub New(ByVal names() As String)
Dim numberOfCharacters As Integer = 0
For Each name As String In names
If name.Length = 0 Then
Throw New ArgumentException("Zero length names are not allowed")
End If
numberOfCharacters += name.Length
Next
'UTF-16 characters take 2 bytes
'Four byte header for each string
'Four byte header for null string at end
'One byte boolean for null flag
Dim dataByteSize As Integer = numberOfCharacters * 2 _
+ names.Length * 4 _
+ 4 _
+ 1
If dataByteSize >= maxByteSize Then
Throw New ArgumentException(String.Format(CultureInfo.InvariantCulture, _
"Data provided occupies {0} bytes but only {1} bytes are available", _
dataByteSize, maxByteSize))
End If
Me._names = names
End Sub
#End Region
#Region "Accessors"
Public Function GetTypeNameArray() As String()
'Don't let caller modify our copy of the array
Return CType(Me._names.Clone(), String())
End Function
'This has an odd API because we can only define Transact-SQL functions on static methods.
<SqlFunction(FillRowMethodName:="FillNameRow", TableDefinition:="[Name] [Name]")> _
Public Shared Function GetContactTypeNames(ByVal names As ContactTypeNames) As IEnumerable
If names Is Nothing Then
Throw New ArgumentNullException("names")
End If
Return names.GetTypeNameArray()
End Function
Public Shared Sub FillNameRow(ByVal nameArrayElement As Object, <Out()> ByRef contactName As String)
contactName = CStr(nameArrayElement)
End Sub
#End Region
#Region "String Conversions"
''' <summary>
''' The string format for contact type names is a sequence of names separated by commas
''' </summary>
''' <param name="s">a string containing contact type names separated by commas</param>
''' <returns>An instance of contact type name containing the specified names</returns>
<Microsoft.SqlServer.Server.SqlMethod(DataAccess:=Microsoft.SqlServer.Server.DataAccessKind.None, IsDeterministic:=False, IsMutator:=False, IsPrecise:=False, SystemDataAccess:=Microsoft.SqlServer.Server.SystemDataAccessKind.None)> _
Public Shared Function Parse(ByVal s As SqlString) As ContactTypeNames
If s.IsNull Then
Return Nothing
End If
Return New ContactTypeNames(s.Value.Split(New Char() {","c}))
End Function
''' <summary>
''' Convert the contact type names to a string
''' </summary>
''' <returns>The contact type names separated by commas</returns>
Public Overrides Function ToString() As String
If Me.IsNull Then
Return Nothing
End If
Dim sb As New StringBuilder()
For Each name As String In Me._names
If sb.Length > 0 Then
sb.Append(", ")
End If
sb.Append(name)
Next name
Return sb.ToString()
End Function
#End Region
#Region "INullable Members"
Shared ReadOnly Property Null() As ContactTypeNames
Get
Return New ContactTypeNames()
End Get
End Property
Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull
Get
Return Me._names Is Nothing
End Get
End Property
#End Region
#Region "IBinarySerialize Members"
'Format:
'Byte 1: Null flag (boolean) (true = null)
'Byte 2 - 7994: Strings with 4 byte length headers,
' last string is a zero length string.
'This format is in part dictated by how the BinaryWriter serializes strings. See
'the Microsoft .NET Framework documentation on System.IO.BinaryWriter for more details.
Public Sub Read(ByVal r As System.IO.BinaryReader) Implements IBinarySerialize.Read
If r.ReadBoolean() Then
Me._names = Nothing
Return
End If
Dim nameList As New List(Of String)
Dim name As String = r.ReadString()
While name.Length <> 0
nameList.Add(name)
name = r.ReadString()
End While
Me._names = New String(nameList.Count - 1) {}
nameList.CopyTo(Me._names)
End Sub
Public Sub Write(ByVal w As System.IO.BinaryWriter) Implements IBinarySerialize.Write
If w Is Nothing Then
Throw New ArgumentNullException("w")
End If
w.Write(Me.IsNull)
For Each name As String In Me._names
w.Write(name)
Next
w.Write(String.Empty)
End Sub
#End Region
#Region "Private Implementation"
Private _names() As String
#End Region
End Class
这是测试可执行文件的代码。
C#（编程语言）
#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
#endregion
class Program
{
static void Main(string[] args)
{
if (args.Length == 0)
{
Console.WriteLine("Usage: TestArrayParameter contactTypeName1 "
+ "contactTypeName2 ... contactTypeNamen");
return;
}
using (SqlConnection connection = new SqlConnection("data source=XXX;initial catalog=AdventureWorks;Integrated Security=SSPI"))
{
connection.Open();
ShowTypeNames(connection, "before any inserts");
SqlCommand command = connection.CreateCommand();
command.CommandText = "usp_EnsureContactTypeNames";
command.CommandType = CommandType.StoredProcedure;
SqlParameter namesParameter = new SqlParameter("@names", SqlDbType.Udt);
namesParameter.UdtTypeName = "ContactTypeNames";
namesParameter.Value = new ContactTypeNames(args);
command.Parameters.Add(namesParameter);
command.ExecuteNonQuery();
ShowTypeNames(connection, "after any inserts");
}
}
private static void ShowTypeNames(SqlConnection connection, string whenRan)
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT Name FROM Person.ContactType ORDER BY Name";
using (SqlDataReader reader = command.ExecuteReader())
{
Console.BackgroundColor = ConsoleColor.Blue;
Console.Write("Contact type names {0}: ", whenRan);
Console.ResetColor();
bool first = true;
while (reader.Read())
{
if (!first) Console.Write(", ");
Console.Write(reader[0].ToString());
first = false;
}
Console.WriteLine("");
Console.WriteLine("");
}
}
}
Visual Basic
#Region "Using directives"
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports System.IO
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
#End Region
Class Program
Shared Sub Main(ByVal args() As String)
If args.Length = 0 Then
Console.WriteLine("Usage: TestArrayParameter contactTypeName1 " _
+ "contactTypeName2 ... contactTypeNamen")
Return
End If
Dim connection As New SqlConnection("data source=XXX;initial catalog=AdventureWorks;Integrated Security=SSPI")
Try
connection.Open()
ShowTypeNames(connection, "Before any inserts")
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = "usp_EnsureContactTypeNames"
command.CommandType = CommandType.StoredProcedure
Dim namesParameter As New SqlParameter("@names", SqlDbType.Udt)
namesParameter.UdtTypeName = "ContactTypeNames"
namesParameter.Value = New ContactTypeNames(args)
command.Parameters.Add(namesParameter)
command.ExecuteNonQuery()
ShowTypeNames(connection, "After any inserts")
Finally
connection.Dispose()
End Try
End Sub
Private Shared Sub ShowTypeNames(ByVal connection As SqlConnection, ByVal whenRan As String)
Dim command As SqlCommand = connection.CreateCommand()
command.CommandText = "SELECT [Name] FROM [Person].[ContactType] ORDER BY Name"
Dim reader As SqlDataReader = command.ExecuteReader()
Try
Console.BackgroundColor = ConsoleColor.Blue
Console.Write("Contact type names {0}: ", whenRan)
Console.ResetColor()
Dim first As Boolean = True
While reader.Read()
If Not first Then
Console.Write(", ")
End If
Console.Write(reader(0).ToString())
first = False
End While
Console.WriteLine("")
Console.WriteLine("")
Finally
reader.Dispose()
End Try
End Sub
End Class
这是 Transact-SQL 安装脚本（Install.sql），用于部署程序集并在数据库中创建存储过程和函数。
USE AdventureWorks
GO
-- Drop existing sprocs, type, and assemblies if any.
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_EnsureContactTypeNames')
DROP PROCEDURE usp_EnsureContactTypeNames;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'GetContactTypeNames' and (type = 'FS' or type = 'FT'))
DROP FUNCTION [GetContactTypeNames];
GO
IF EXISTS (SELECT * FROM sys.types WHERE [name] = 'ContactTypeNames')
DROP TYPE ContactTypeNames;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'ContactTypeNames')
DROP ASSEMBLY ContactTypeNames;
GO
-- Add assemblies, type, and sproc
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath= 'C:\MySample\'
CREATE ASSEMBLY ContactTypeNames
FROM @SamplesPath + 'ContactTypeNames.dll'
WITH permission_set = Safe;
CREATE TYPE ContactTypeNames
EXTERNAL NAME ContactTypeNames.ContactTypeNames;
GO
CREATE FUNCTION GetContactTypeNames
(
@names dbo.ContactTypeNames
)
RETURNS TABLE
(
[Name] [Name]
)
AS EXTERNAL NAME [ContactTypeNames].[ContactTypeNames].[GetContactTypeNames];
GO
CREATE PROCEDURE usp_EnsureContactTypeNames
(
@names dbo.ContactTypeNames
)
AS
SET NOCOUNT ON;
INSERT Person.ContactType ([Name])
SELECT [Name] FROM GetContactTypeNames(@names) AS PotentialNames
WHERE [Name] NOT IN (SELECT [Name] FROM Person.ContactType);
GO
以下 Transact-SQL 从数据库中删除程序集和存储过程。
USE AdventureWorks
GO
DELETE Person.ContactType WHERE ContactTypeID > 20;
GO
-- Drop existing sprocs, type, and assemblies if any.
IF EXISTS (SELECT * FROM sys.procedures WHERE [name] = 'usp_EnsureContactTypeNames')
DROP PROCEDURE usp_EnsureContactTypeNames;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'GetContactTypeNames' and (type = 'FS' or type = 'FT'))
DROP FUNCTION [GetContactTypeNames];
GO
IF EXISTS (SELECT * FROM sys.types WHERE [name] = 'ContactTypeNames')
DROP TYPE ContactTypeNames;
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE [name] = 'ContactTypeNames')
DROP ASSEMBLY ContactTypeNames;
GO