배열 매개 변수 예제
데이터베이스에서 행 집합을 생성, 업데이트 또는 삭제해야 하는 경우가 있습니다. 이러한 작업을 수행하는 방법은 여러 가지가 있습니다. 그 중 한 가지는 CLR(공용 언어 런타임) 통합 사용자 정의 데이터 형식을 사용하여 클라이언트에서 서버의 CLR 통합 저장 프로시저로 정보 배열을 전달하는 것입니다. 서버에 제공되는 데이터의 크기는 이러한 사용자 정의 데이터 형식의 특성으로 인해 8000바이트로 제한됩니다. 따라서 이 방법은 크거나 복잡한 데이터의 경우에는 적합하지 않습니다. 조작 중인 데이터가 작고 단순한 경우에는 각 행에 대해 저장 프로시저를 호출하는 것보다 이 방법이 훨씬 더 효율적일 수 있습니다. 배열을 전달함으로써 순서가 중요한 응용 프로그램에 대해 데이터의 순서가 유지됩니다. 이 예제에는 다음이 포함되어 있습니다.
ContactTypeNames 사용자 정의 데이터 형식. 필요한 연락처 유형 이름의 목록이 포함되어 있습니다.
Microsoft Visual C# 또는 Microsoft Visual Basic 메서드로 구현된 usp_EnsureContactTypeNames 저장 프로시저. 이 저장 프로시저는 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', 1
GO
RECONFIGURE
GO
[!참고]
CLR을 설정하려면 ALTER SETTINGS 서버 수준 사용 권한이 있어야 합니다. 이 사용 권한은 sysadmin 및 serveradmin 고정 서버 역할의 멤버가 암시적으로 소유합니다.
사용하고 있는 SQL Server 인스턴스에 AdventureWorks 데이터베이스를 설치해야 합니다.
사용 중인 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.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의 코드입니다.
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