数组参数示例

项目
05/08/2013

有时需要创建、更新或删除数据库中的一组行。实现的方法有多种。其中一种是使用 CLR 集成用户定义数据类型将客户端上的数组信息传递到服务器上的公共语言运行时 (CLR) 集成存储过程。这类用户定义数据类型的特性将提供给服务器的数据大小限制在 8000 个字节。因此，此方法不适于大型数据或复杂数据。如果操作的数据量小且简单，则此方法比为每行调用存储过程有效得多。通过传递数组可以保留数据顺序，数据顺序对一些应用程序而言非常重要。此示例包含以下内容：

ContactTypeNames 用户定义数据类型，其中包含所需的联系类型名称列表。
usp_EnsureContactTypeNames 存储过程作为 Microsoft Visual C# 或 Microsoft Visual Basic 方法实现。它接受 ContactTypeNames 用户定义数据类型的实例，并在 Person.ContactType 表中为联系名称插入新行，这些联系名称包含在用户定义数据类型实例中但在表中还不存在。
TestArrayParameter 控制台应用程序。它基于传入的命令行参数创建 ContactTypeNames 用户定义数据类型的实例，然后通过将用户定义数据类型实例作为参数进行传递来调用 usp_EnsureContactTypeNames 存储过程。

先决条件

若要创建和运行此项目，必须安装下列软件：

SQL Server 或 SQL Server Express。您可以从 SQL Server Express Documentation and Samples （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', 1

GO

RECONFIGURE

GO

注意

若要启用 CLR，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。
必须在您使用的 SQL Server 实例上安装 AdventureWorks 数据库。
如果您不是要使用的 SQL Server 实例的管理员，则必须让管理员授予您 CreateAssembly 权限，才能完成安装。

注意
若要启用 CLR，您必须具有 ALTER SETTINGS 服务器级别权限，sysadmin 和 serveradmin 固定服务器角色的成员隐式拥有该权限。

生成示例

按照以下说明创建和运行该示例：

打开 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.vb
- Csc /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# 示例代码（如下所示）复制到该文件中。
找到文件 Program 中相应的行（在第 24 行附近），将 XXX 替换为您的 SQL 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.vb
- Csc /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.

#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

这是用于测试可执行文件的代码。

#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

请参阅

概念

公共语言运行时 (CLR) 集成的使用方案和示例

通过

数组参数示例

先决条件

启用 CLR 集成

生成示例

按照以下说明创建和运行该示例：

示例代码

请参阅

概念

其他资源