Dizi parametre örnek
Bazen oluşturmak, güncelleştirmek veya bir veritabanında bir satır silmek yararlıdır. Sen-ebil kullanma bu hedefe ulaşmak için çeşitli yaklaşımlar vardır. Onlardan bilgi dizisi bir istemciden bir ortak dil çalışma zamanı (clr) tümleştirme saklı yordam sunucuda Tümleştirme clr kullanıcı tanımlı veri türü kullanarak geçmek için biri. Bu tür kullanıcı tanımlı veri türleri yapısı 8000 bayt sunucuya sağlanan verilerin boyutunu sınırlar. Bu nedenle, bu yaklaşım büyük veya karmaşık veriler için tatmin edici değil. Küçük ve basit verileri manipüle, bu yaklaşım her satır için bir saklı yordam çağırma daha çok daha etkili olabilir. Bir dizi geçirerek, verilerin sırasıyla sırası önemli olduğu bu uygulamalar için korunur.Bu örnek aşağıdakileri içerir:
ContactTypeNamesKullanıcı tanımlı veri türüne. Bu, istenen ilgili kişi türü adlarının bir listesini içerir.
usp_EnsureContactTypeNames Depolanmış yordam Microsoft Visual C# veya Microsoft Visual Basic yöntemi olarak hayata. Bu örneği kabul ContactTypeNameskullanıcı tanımlı veri yazın ve yeni satır ekler Person.ContactTypeiçin hangi tabloda bulunmayan kullanıcı tanımlı veri türü örneği bulunan herhangi bir kişi adları tablo.
TestArrayParameterKonsol uygulaması. Bu örneği oluşturur ContactTypeNameskullanıcı tanımlı veri türü geçirilen komut satırı parametreleri temel ve sonra çağırır usp_EnsureContactTypeNamesdepolanmış yordam kullanıcı tanımlı veri türü örneği parametre olarak geçirerek.
Ön Koşullar
Oluşturmak ve bu proje aşağıdaki çalıştırmak için aşağıdaki yazılım yüklenmiş olmalıdır:
SQL Serverveya SQL Serverhızlı. Elde edebilirsiniz SQL Serverhızlı şarj ücretsiz SQL ServerExpress belgeleri ve örnekleri Web sitesi
İs available at AdventureWorks veritabanını SQL Servergeliştiricisi Web sitesi
.net Framework sdk 2.0 veya sonraki sürümünü ya da Microsoft Visual Studio 2005 veya sonraki bir sürümü. Elde edebilirsiniz.net Framework sdk ücretsiz.
Ayrıca, aşağıdaki koşulların karşılanması gerekir:
SQL ServerKullanıyorsanız örneğinin clr tümleştirmesi etkinleştirilmiş olması gerekir.
clr tümleştirme etkinleştirmek için aşağıdaki adımları gerçekleştirin:
clr tümleştirme etkinleştirmek
- Aşağıdaki yürütme Transact-SQLkomutları:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
[!NOT]
clr etkinleştirmek için olmalıdır ALTER SETTINGSörtülü üyeleri tarafından düzenlenen sunucu düzeyi izni sysadminve serveradminsabit sunucu rolü.
AdventureWorks veritabanı yüklenmesi gerekir SQL Serverkullanıyorsanız örneği.
Yönetici değilseniz SQL Serverkullandığınız örnek verdiğiniz bir yönetici olması gerekir CreateAssembly yüklemeyi tamamlamak için izni.
Örnek oluşturma
Oluşturun ve aşağıdaki yönergeleri kullanarak örnek çalıştırın:
Visual Studio'nun açın veya.net Framework buyurmak-e sevketmek.
Gerekirse, örnek bir dizin oluşturun. Bu örnekte, C:\MySample kullanacağız.
C:\MySample içinde oluşturmak ContactTypeNames.vb(için Visual Basic örnek) veya ContactTypeNames.cs(için C# örnek) ve uygun Visual Basic veya C# örnek kod (aşağıda) dosyasına kopyalayın.
Komut satırı isteminden gerekli derleme dil seçiminize bağlı olarak, aşağıdakilerden biri yürüterek örnek kod derlemesi.
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 içinde oluşturmak Program.vb(için Visual Basic örnek) veya Program.cs(için C# örnek) ve uygun Visual Basic veya C# örnek kod (aşağıda) dosyasına kopyalayın.
Uygun satırı (yaklaşık satir 24) Program dosyasını bulun ve yerine XXXörneğinin adı ile 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"))
Gerekli yürütülebilir komut satırı isteminden dil seçiminize bağlı olarak, aşağıdakilerden biri yürüterek örnek kod derlemesi.
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
Kopya Transact-SQLyükleme kodu olarak kaydedin ve dosyayı içine Install.sqlörnek Rehberi.
Örnek bir dizindeki diğer sonra yüklü olup olmadığını C:\MySample\, dosya düzenleme Install.sqlBu konumu göstermesi için belirtildiği gibi.
Kurul, saklı yordam ve işlevlerini yürüterek dağıtma
- sqlcmd -E -I -i install.sql
Komut isteminde aşağıdaki satırı çalıştırarak uygulamayı sınayın:
- TestArrayParameter "Executive Sales Representative" "Executive Sales Manager"
Kopya Transact-SQLTemizleme komut dosyası olarak kaydedin ve dosyayı içine cleanup.sqlörnek Rehberi.
Aşağıdaki komutu komut dosyasını yürütün
- sqlcmd -E -I -i cleanup.sql
Örnek kod
Bu örnek kod listeleri şunlardır:
Bu kitaplık kodudurContactTypeNames.
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
}
#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"
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
Bu test çalıştırılabilir koddur.
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("");
}
}
}
#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
#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
Bu Transact-SQLyükleme komut dosyasını (Install.sql), hangi derleme dağıtır ve işlevleri ve saklı yordam veritabanında oluşturur.
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
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
Aşağıdaki Transact-SQLsaklı yordamı ve derleme veritabanından kaldırır.
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
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
Ayrıca bkz.
Kavramlar
Kullanım senaryoları ve ortak dil çalışma zamanı (clr) tümleştirme örnekleri