Microsoft SQL Server 2005 Developer’s Guide- P6

Microsoft SQL Server 2005 Developer’s Guide- P6:This book is the successor to the SQL Server 2000 Developer’s Guide, whichwas extremely successful thanks to all of the supportive SQL Server developerswho bought that edition of the book. Our first thanks go to all of the peoplewho encouraged us to write another book about Microsoft’s incredible new relationaldatabase server: SQL Server 2005.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2005 Developer’s Guide- P6 Chapter 3: Developing CLR Database Objects 99User-Defined TypesAnother important new feature in SQL Server 2005 that is enabled by the integrationof the .NET CLR is the ability to create true user-defined types (UDTs). UsingUDTs, you can extend the raw types provided by SQL Server and add data types thatare specialized to your application or environment. In the following example you’ll see how to create a UDT that represents a gendercode: either M for male or F for female. While you could store this data in a standardone-byte character field, using a UDT ensures that the field will accept only thesetwo values with no additional need for triggers, constraints, or other data validationtechniques. To create a UDT using Visual Studio 2005, select the New | Project option, giveyour project a name, and click OK to create the project. For this project I used thename of Gender for the new UDT. After naming the project and clicking OK, I filledout the New Database Reference dialog using the required connection values todeploy the project to the appropriate SQL Server system and database. Next, I usedthe Project | Add User-Defined Type option to display the Add New Item dialog thatyou can see in Figure 3-11.Figure 3-11 Creating a .NET SQL Server UDT100 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Method Description IsNull This required method is used to indicate if the object is nullable. SQL Server 2005 requires all UDTs to implement nullability, so this method must always return true. Parse This required method accepts a string parameter and stores it as a UDT. ToString This required method converts the contents of the UDT to a string. Default constructor This required method creates a new instance of the UDT. Table 3-1 Required UDT Methods Select User-Defined Type from the list of SQL Server templates. Enter the name that you want to assign to the class and then click Open to have Visual Studio generate a starter project file for the UDT. The starter project file implements the four methods that SQL Server 2005 requires for all UDTs. These methods are needed to fulfill the SQL Server UDT contract requirements—it’s up to you to add the code to make the UDT perform meaningful actions. The four required UDT methods are listed in Table 3-1. You can see the completed Gender class that is used to implement a UDT for M (male) and F (female) codes in this listing: Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.IO _ _ Public Structure Gender Implements INullable, IBinarySerialize Public Sub Read(ByVal r As BinaryReader) _ Implements IBinarySerialize.Read m_value = r.ReadString.ToString() End Sub Chapter 3: Developing CLR Database Objects 101 Public Sub Write(ByVal w As BinaryWriter) _ Implements IBinarySerialize.Write w.Write(m_value.ToString()) End SubPublic Overrides Function ToString() As String If m_value.IsNull = False Then Return m_value.Value Else Return Nothing End If End Function Public ReadOnly Property IsNull() As Boolean _ Implements INullable.IsNull Get If m_value.IsNull = True Then Return True Else Return False End If End Get End Property Public Shared ReadOnly Property Null() As Gender Get Dim h As Gender = New Gender h.m_Null = True Return h End Get End Property Public Shared Function Parse(ByVal s As SqlString) As Gender If s.IsNull Then Return Null End If Dim u As Gender = New Gender u.Value = s Return u End Function102 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Create a Value Property Public Property Value() As SqlString Get Return m_value End Get Set(ByVal value As SqlString) If (value = M Or value = F) Then m_value = value Else Throw New ArgumentException _ (Gender data type must be M or F) End If End Set End Property Private members Private m_Null As Boolean Private m_value As SqlString End Structure To create a UDT, the code must adhere to certain conventions. The class’s attributes must be serializable, the class must implement the INullable interface, and the class name must be set to the name of the UDT. You can optionally add the IComparable interface. In this example, Gender is the class name. Near the bottom of the listing you can see where a private string variable named m_value is declared to hold the value of the data type. Like the other CLR database objects, the Attribute plays an important part in the construction of the CLR UDT. The SQL Server UDT Attribute accepts the property values shown in Table 3-2. The first thing to notic ...