Working with User-Defined Types

 

Working with User-Defined Types

You can create user-defined types (UDTs) in SQL Server to extend the existing scalar type system in the database. In addition to using the existing scalar types, such as int, char, nvarchar, and uniqueidentifier, you can create your own atomic data types for creating tables and return values. The benefit is that you can store your data in a more natural way and access the properties and methods of the data type from within T-SQL or your .NET code.

Many people incorrectly conclude that SQL Server is now an object database server, but the UDT feature doesn't come close to fulfilling this fantasy. When you want to store data in a more natural format, however, this feature fulfills the need. For example, it might be advantageous to create a custom date type and time type to be used instead of the built in datetime type. Or you might want to store a distance given in feet and inches as a single value rather than two separate values. Remember that the UDT should be an atomic value and should be created only when it is appropriate to display your UDT value in a column.

A UDT must have a string representation for input and display of the data. It must also have a binary representation (for being persisted to disk) that cannot exceed 8000 bytes, and it must have a null representation because all scalars in SQL Server must be able to have a null value.

UDTs must be implemented on every database that uses them. For example, if you are planning to explicitly create temporary tables based on data that is in your current database and that data includes UDTs, the UDTs must also be registered in the tempdb database. As a side note, there is no need to register all of your types in the tempdb database even though SQL Server often implicitly creates temporary tables in tempdb that are used to help the SQL engine do its work.

A UDT can be created using a structure (C# struct) or a class, and the Visual Studio .NET template creates a structure. If you decide to change this to a class, you must add the StructLayout attribute with the LayoutKind.Sequential setting to the class to ensure that the fields in the class remain in the defined order when the object is serialized.

When creating a UDT in Visual Studio .NET, you use the SqlUserDefinedType attribute to tell Visual Studio how to register the type. This attribute has a property called Format that can be set to Native or UserDefined. If you set the format to Native, the SQLCLR provides the code to read and write the binary data, but all of your fields in this structure must be blittable. (Blittable fields are those whose managed and unmanaged byte representations are the same, so no conversion is required to marshal between managed and unmanaged code.) Here are the blit table types and other types you can use with Format.Native. Notice that string is not in the list. If you want to use a string field, you must set the Format property to UserDefined and you must provide the code to read and write the byte representation to disk.

  • System.Byte

  • System.SByte

  • System.Int16

  • System.UInt16

  • System.Int32

  • System.UInt32

  • System.Int64

  • System.IntPtr

  • System.UIntPtr

  • One-dimensional arrays of blittable types, such as an array of integers

  • System.Float

  • System.Double

  • SqlSingle

  • SqlDouble

  • SqlByte

  • SqlInt16

  • SqlUInt16

  • SqlInt32

  • SqlUInt32

  • SqlInt64

  • SqlUInt64

  • SqlDateTime

Another requirement when you use Format.Native is that you must assign the Serializable attribute to the UDT.

User-defined types can be sorted, indexed, and used in magnitude comparisons if the IsByte Ordered property of the SqlUserDefinedType attribute is set to true. The comparisons are always done using the binary representation of the UDT, so it's important to order your fields in your UDT so that comparisons can be made. Doing comparisons across the binary representations can be a big limitation if you are attempting to store many fields in the UDT, but if the UDT is implemented properly that is, implemented for truly atomic values this should not be a limitation.

The following code sample shows a UDT for a distance data type. The distance is atomic in that it is a single value called totalInches (but it is represented as feet and inches when it is displayed).

image from book

Visual Basic

Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _ Public Structure DistanceVb    Implements INullable, IComparable    Public ReadOnly Property Feet() As Integer       Get          Return CInt(totalInches / 12)       End Get    End Property    Public ReadOnly Property Inches() As Integer       Get          Return totalInches Mod 12       End Get    End Property    Public Overrides Function ToString() As String       Return String.Format("{0} ft. {1} in.", Feet, Inches)    End Function    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull       Get          ' Put your code here          Return m_Null       End Get    End Property    Public Shared ReadOnly Property Null() As DistanceVb       Get          Dim h As DistanceVb = New DistanceVb          h.m_Null = True          Return h       End Get    End Property    Public Shared Function Parse(ByVal s As SqlString) As DistanceVb       If s.IsNull Then          Return Null       End If       Dim u As DistanceVb = New DistanceVb       Dim distance As String = s.Value       If distance = "null" Then Return Null       distance = distance.ToLower()       Dim feet As Integer = 0       Dim inches As Integer = 0       Dim parts() As String = distance.Split(" "c)       Dim feetLocation As Integer = Array.IndexOf(parts, "ft.")       If (feetLocation > 0) Then          feet = Integer.Parse(parts(feetLocation - 1))       End If       Dim inchesLocation As Integer = Array.IndexOf(parts, "in.")       If (inchesLocation > 0) Then          inches = Integer.Parse(parts(inchesLocation - 1))       End If       u.totalInches = (feet * 12) + inches       Return u    End Function    Public Function CompareTo(ByVal obj As Object) As Integer _           Implements IComparable.CompareTo       Dim other As DistanceVb = CType(obj, DistanceVb)       Return totalInches - other.totalInches    End Function    Public totalInches As Integer    Private m_Null As Boolean End Structure 
image from book

image from book

C#

using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct DistanceCs : INullable, IComparable {    public int Feet    {       get       {          return totalInches / 12;       }    }    public int Inches    {       get       {          return totalInches % 12;       }    }    public override string ToString()    {       return string.Format("{0} ft. {1} in.", Feet, Inches);    }    public bool IsNull    {       get       {          return m_Null;       }    }    public static DistanceCs Null    {       get       {          DistanceCs h = new DistanceCs();          h.m_Null = true;          return h;       }    }    public static DistanceCs Parse(SqlString s)    {       if (s.IsNull)          return Null;       DistanceCs u = new DistanceCs();       string distance = s.Value;       if (distance == "null") return Null;       distance = distance.ToLower();       int feet = 0;       int inches = 0;       string[] parts = distance.Split(' ');       int feetLocation = Array.IndexOf(parts, "ft.");       if (feetLocation > 0)       {          feet = int.Parse(parts[feetLocation-1]);       }       int inchesLocation = Array.IndexOf(parts,"in.");       if (inchesLocation > 0)       {          inches = int.Parse(parts[inchesLocation - 1]);       }       u.totalInches = (feet * 12) + inches;       return u;    }    public int CompareTo(object obj)    {       DistanceCs other = (DistanceCs)obj;       return totalInches - other.totalInches;    }    private int totalInches;    private bool m_Null; } 
image from book

Notice the use of the SqlUserDefinedType attribute, which tells Visual Studio .NET how to deploy this structure. If you need to manually register a UDT with SQL Server, you can use the following SQL syntax.

The IComparable interface was also implemented on this UDT, which was not a UDT requirement, but you will want the ability to display and sort this UDT in a DataGridView control. For this, the IComparable must be implemented.

image from book

SQL: UDT Registration

CREATE TYPE <UDT name> FROM <assembly name>.<UDT name> 
image from book

The only data being persisted is totalInches, followed by m_Null. This means it is easy to provide sorting and comparisons based on the byte representation. Read-only properties were created to access the feet and inches independently. These properties are available in your .NET code as well as your T-SQL statements.

This sample also implements the INullable interface methods, IsNull and Null. Anywhere that a null is required for this data type, the Null method is used to generate it.

To test this UDT, add the following SQL script to the Test.sql file.

image from book

Visual Basic

create table UdtTestVb (Id int not null, distance distanceVb not null) insert into UdtTestVb values(1, '2 ft. 5 in.') insert into UdtTestVb values(2, '15 in.') insert into UdtTestVb values(3, '10 ft.') insert into UdtTestVb values(4, '1 ft. 23 in.') select id, convert(nvarchar(25),distance) from UdtTestVb drop table UdtTestVb 
image from book

image from book

C#

create table UdtTestCs (Id int not null, distance distanceCs not null) insert into UdtTestCs values(1, '2 ft. 5 in.') insert into UdtTestCs values(2, '15 in.') insert into UdtTestCs values(3, '10 ft.') insert into UdtTestCs values(4, '1 ft. 23 in.') select id, convert(nvarchar(25),distance) from UdtTestCs drop table UdtTestCs 
image from book

image from book

Output Window: UDT

id          Column1 ----------- ------------------------- 1           2 ft. 5 in. 2           1 ft. 3 in. 3           10 ft. 0 in. 4           2 ft. 11 in. (8 row(s) affected) (4 row(s) returned) 
image from book

When Not to Use a UDT

You should never use a UDT to model business objects such as customers, employees, orders, or products. Remember that in a relational database, these objects are usually represented by rows in one or more tables, whereas the UDT is represented as a single value in a single column. The 8000-byte limit is much higher than you should ever need. Indexing, sorting, and magnitude comparisons are done only one way, across the entire binary representation. This is workable if the UDT is truly atomic, but if you need different indexes, the UDT is not for you.

Any operation on a UDT except comparisons causes the UDT value to be deserialized and a method to be invoked. You should therefore consider performance implications when evaluating the use of a UDT.

When to Use a UDT

If you have atomic data, typically represented as a single field, and you have many complex methods that need to be associated with the data, the UDT might be the answer.

Also, it's worth noting that if you simply want to create a library of functions for use in T-SQL, you can create a type that has no data and comprises static methods. This is equivalent to creating user-defined functions, but the benefit is that these methods are grouped inside your type. Beware that you cannot assign execute permissions to the methods on a UDT. The following code shows how you can package a PadLeft and PadRight method into a UDT.

image from book

Visual Basic

Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server <Serializable()> _ <Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)> _ Public Structure StringStuffVb    Implements INullable    Public Shared Function PadLeft( _          ByVal inputString As SqlString, _          ByVal totalWidth As SqlInt32) As SqlString       Return New SqlString(_          inputString.Value.PadLeft(totalWidth.Value))    End Function    Public Shared Function PadRight(_          ByVal inputString As SqlString, _          ByVal totalWidth As SqlInt32) As SqlString       Return New SqlString(_          inputString.Value.PadRight(totalWidth.Value))    End Function    Public Overrides Function ToString() As String       ' Put your code here       Return ""    End Function    Public ReadOnly Property IsNull() As Boolean Implements INullable.IsNull       Get          ' Put your code here          Return m_Null       End Get    End Property    Public Shared ReadOnly Property Null() As StringStuffVb       Get          Dim h As StringStuffVb = New StringStuffVb          h.m_Null = True          Return h       End Get    End Property    Public Shared Function Parse(ByVal s As SqlString) As StringStuffVb       If s.IsNull Then          Return Null       End If       Dim u As StringStuffVb = New StringStuffVb       ' Put your code here       Return u    End Function    ' Private member    Private m_Null As Boolean End Structure 
image from book

image from book

C#

using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)] public struct StringStuffCs : INullable {    public static SqlString PadLeft(       SqlString inputString, SqlInt32 totalWidth)    {       return new SqlString(          inputString.Value.PadLeft(totalWidth.Value));    }    public static SqlString PadRight(    SqlString inputString, SqlInt32 totalWidth)    {       return new SqlString(          inputString.Value.PadRight(totalWidth.Value));    }    public override string ToString()    {       // Replace the following code with your code       return "";    }    public bool IsNull    {       get       {          // Put your code here          return m_Null;       }    }    public static StringStuffCs Null    {       get       {          StringStuffCs h = new StringStuffCs();          h.m_Null = true;          return h;       }    }    public static StringStuffCs Parse(SqlString s)    {       if (s.IsNull)          return Null;       StringStuffCs u = new StringStuffCs();       // Put your code here       return u;    }    // Private member    private bool m_Null; } 
image from book

The UDT in this code sample has no fields for user data. The PadLeft and PadRight methods are added as static (Visual Basic shared) methods. The trick to using these methods in a T-SQL statement is to know the calling syntax. Here is the code you can add to your Test.sql file.

image from book

Visual Basic

select '<' + StringStuffVb::PadLeft('Hi',10) + '>' select '<' + StringStuffVb::PadRight('Hi',10) + '>' 
image from book

image from book

C#

select '<' + StringStuffCs::PadLeft('Hi',10) + '>' select '<' + StringStuffCs::PadRight('Hi',10) + '>' 
image from book

The output looks like this:

Column1 ----------------------- <        Hi> (8 row(s) affected) (1 row(s) returned) Column1 ----------------------- <Hi        > (8 row(s) affected) (1 row(s) returned) 

Note that the affected value of the row(s) may differ based on where you placed this T-SQL script command in your test file. In reality, no rows were affected by this T-SQL script, but the number was carried down from a previous T-SQL command.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net