Chapter 14: CLR Database Objects Advanced Topics


In this chapter I will first cover the creation and use of some managed database objects that are more complex to develop, and which you will probably seldom create or use—CLR user-defined types and CLR user-defined aggregations (or aggregate functions). The focus will then change to some generic aspects of managed database object development—transaction management and architectural guidelines for justified uses of managed database objects.

CLR User-defined Types

Microsoft has included the ability to create user-defined types (UDT) in managed code in SQL Server 2005.1 am not sure how much you will use this feature of SQL Server. Everybody I have asked which UDT functions they would like to have in SQL Server answered the same—time and date—but nobody asked for much on top of that.

To differentiate them from old Transact-SQL user-defined data types, BOL and Management Studio refer to managed UDTs as user-defined types (see Figure 14-1), while those that were already available in Transact-SQL are referred to as user-defined data types. The distinction is too subtle for my taste.

image from book
Figure 14-1: User-defined types

Structure of Managed UDT

User-defined types have a more complex implementation than other CLR database objects that I have shown you so far. They are implemented as a class if they are designed as reference types, or as a structure (struct) if they are designed as value types. Structures inherit from System.ValueType, while classes inherit from System. Object. You can instantiate an object based on a class, while you can only set values of components of a structure.

Multiple methods must be defined in a class (or a structure) for a managed UDT to achieve basic behavior. For example:

  • Parse() to set UDT values (properties) based on a string

  • ToString() to convert a UDT instance to its string interpretation

  • Properties to expose data components of a UDT

  • A method and a couple of properties as an implementation of System.Data.SqlTypes.INullable interface for the UDT to support nullability

  • Read() and Write() methods and MaxByteSize property of the IBinarySerialize interface to allow a UDT to support storing an intermediate state

The following struct is designed to store stock price information on the specified date:

 using System; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.10; using Systern.Runtime.InteropServices; namespace MyUDTs {    [Serializable]    [StructLayout(LayoutKind.Sequential)]    [SqlUserDefinedType(Format.UserDefined,                        IsByteOrdered = true,                        MaxByteSize = 4096,                        IsFixedLength = false)]    public struct cudt PriceDate : INullable, IBinarySerialize    {       #region Properties      private Double stockPrice;      private DateTime businessDay;      public Double StockPrice      {          get          {               return this.stockPrice; ;          }          set          {           stockPrice = value;          }       }      public DateTime BusinessDay      {          get          {               return this.businessDay;          }          set          {               businessDay = value;          }       }      #endregion      #region NULL      private bool isNull;      public bool IsNull      {          get          {             return (isNull);          }       }      public static cudt_PriceDate Null      {          get          {              cudt_PriceDate h = new cudt_PriceDate();              h.isNull = true;              return h;           }      }      #endregion      #region Default      public static cudt_PriceDate DefaultValue()      {           return cudt_PriceDate.Null;      }      #endregion      #region Cast      public override string ToString()      {          if (this.IsNull)               return "NULL";          else          {               return stockPrice + ";" + businessDay.Date;          }      }      public static cudt_PriceDate Parse(SqlString s)      {          if (s.IsNull || s.Value.ToLower() == "null")              return Null;          cudt_PriceDate st = new cudt_PriceDate();          string[] xy = s.Value.Split(";".ToCharArray());          st.stockPrice = Double.Parse(xy[0]);          st.businessDay = DateTime.Parse(xy[1]);          return st;          #endregion          #region Serialization          public void Read(BinaryReader r)          {          stockPrice = r.ReadDouble();              businessDay = DateTime.Parse(r.ReadString());          }          public void Write(BinaryWriter w)          {             w.Write(stockPrice) ;             w.Write(businessDay.ToString(}};          }          #endregion          #region Custom Methods          //The following calculations do not make much sense.          //They are simple demonstrations of methods in UDT.          [SqlMethod(              OnNullCall = false,              DataAccess = DataAccessKind.None,              IsDeterministic = false,              IsMutator = false,              IsPrecise = false)]          public static Sqllnt32 DiffDate(cudt_PriceDate from,                                          cudt_PriceDate to)          {              TimeSpan delta = to.BusinessDay.Subtract(from.BusinessDay);              return delta.Days;          }          [SqlMethod(OnNullCall = false)]          public static SqlDouble DiffPrice(cudt_PriceDate from,                                            cudt_PriceDate to)          {              return to.StockPrice - from.StockPrice;          }          #endregion      }  } 

Namespaces

A struct or class that will become a UDT must contain references to the System, System.Data.Sql, System.Data.SqlTypes, and Microsoft.SqlServer.Server namespaces. You can add additional references as needed.

 using System; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using System.10; using Systern.Runtime.InteropServices; 

Attributes

A struct that will be converted to a UDT must be preceded by several attributes.

The Serializable attribute is set to notify the compiler that the struct can be serialized and deserialized. Serialization is the process of converting the object into a series of bytes for storing (or transmitting to other system).

The StructLayout attribute is used to determine the way that the components of a UDT will be stored in memory.

The SqlUserDefinedType attribute notifies the compiler that the struct should be compiled as a UDT. The attribute contains a couple of properties. The Format property specifies the format of serialization. If Format.UserDefined is used, the type must implement its own serialization by implementing the IBinarySerialize.Read() and IBinarySerialize.Write() methods. IsByteOrdered specifies that SQL Server can perform binary comparisons of UDT instances, instead of comparing UDT instances in managed code. MaxByteSize defines the maximum size of a struct when it is serialized in bytes. In SQL Server 2005, the size of a serialized UDT is limited to 8000 bytes. IsFixedLength declares whether all instances of the UDT are of the same size. You can find more details about these attributes in Visual Studio 2005 documentation (but unfortunately not in BOL).

Interfaces

The declaration of a struct contains the names of the interfaces the struct is implementing:

      public struct cudt_PriceDate : INullable, IBinarySerialize 

These interfaces are defined in the .NET Framework and all classes and structs that inherit from them must contain implementations of specified methods and properties.

For example, for UDT to be nullable, the struct must implement the INullable interface, which means that it must contain the following methods:

 private bool isNull; public bool IsNull {    get    {       return (isNull);    } } public static cudt_PriceDate Null {    get    {       cudt_PriceDate h = new cudt_PriceDate();       h.isNull = true;       return h;    } } 

For a UDT to be serializable, it must contain an implementation of IBinarySerialize-the use of Read() and Write() methods:

      public void Read(BinaryReader r)      {      stockPrice = r.ReadDouble(};      businessDay = DateTime.Parse(r.ReadString());      }      public void Write(BinaryWriter w)      {      w.Write(stockPrice);      w.Write(businessDay.ToString()) ;      } 

Properties

The carriers of information in the struct are private fields:

 private Double stockPrice; private DateTime businessDay; 

The information in these fields is accessed through property methods (and the corresponding get and set methods):

 public Double StockPrice {     get     { return this.StockPrice;;     }     set     { StockPrice = value;     } } public DateTime BusinessDay {     get     { return this.businessDay;     }     set     { businessDay = value;     } } 

Conversion Methods

The ToString() and Parse() methods implement ways to convert a UDT to a string and a string to a UDT:

 public override string ToString() {     if (this.IsNull) return "NULL";      else      { return StockPrice + ";" + businessDay.Date;      } } public static cudt_PriceDate Parse(SqlString s) {     if (s.IsNull || s.Value.ToLower() == "null") return Null;     cudt_PriceDate st = new cudt_PriceDate();     string[] s = s.Value.Split(";".ToCharArray());     st.stockPrice = Double.Parse(s[0]);     st.businessDay = DateTime.Parse(s[1]);     return st; } 

The CLR engine is smart enough to use these methods whether they are referenced explicitly or implicitly through Transact-SQL Convert and Cast functions.

DefaultValue Method

To be able to set the default value of a UDT, its managed class must contain the DefaultValue() method, even if it returns only an instance set to Null:

 public static cudt_PriceDate DefaultValue() {     return cudt_PriceDate.Null; } 

Custom Methods and SqIMethodAttribute

It is also possible to define custom methods inside of the UDT that could be called as functions from Transact-SQL. The following two methods calculate difference in price and days between two instances of the UDT:

 [SqlMethod(      OnNullCall = false,      DataAccess = DataAccessKind.None,      IsDeterministic = false, IsMutator = false,      IsPrecise = false)] public static Sqllnt32 DiffDate(cudt_PriceDate from, cudt_PriceDate to) {      TimeSpan delta = to.BusinessDay.Subtract(from.BusinessDay);      return delta.Days; } [SqlMethod(OnNullCall = false)] public static SqlDouble DiffPrice(cudt_PriceDate from, cudt_PriceDate to) {      return to.StockPrice - from.StockPrice; } 

Such methods may be marked with the SqlMethod attribute, which may contain additional properties:

DataAccess

Specifies whether the function uses ADO.NET to access data on SQL Server, and whether the compiler should include components for database access to the assembly. Default is DataAccessKind.None.

IsDeterministic

Important if an index is created on the result of the function. It shows whether the function produces the same output values for the same input values. Default is false.

IsMutator

Signifies whether the method changes the values stored in an instance of the UDT. Default is false.

IsPrecise

Works like a hint that forces the compiler to include floating point operations in the assembly. Default is false.

Name

Used if you want to set the name of the Transact-SQL function to be different from the default (name of the method).

OnNullCall

Set to true if the method is called when null reference input arguments are specified. Default is true.

Deploying CLR UDT Without Visual Studio 2005

If you do not have Visual Studio 2005, you can create assembly and type using the following commands:

 CREATE ASSEMBLY MyUDTs FROM 'C:\Projects\CShrpPriceDate\bin\Debug\cudt_PriceDate.dll' WITH permission_set=Safe; CREATE TYPE cudt_PriceDate EXTERNAL NAME MyUDTs.MyUDTs.cudt_PriceDate; GO 

Using CLR User-defined Types

I will now demonstrate how to use a managed UDT. I will use a new UDT created in Visual Basic .NET. This is a structure that consists of fields for quantity and price:

 Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlClient Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Runtime.InteropServices Imports System.I0 <Serializable()> _ <StructLayout(LayoutKind.Sequential)> _ <SqlUserDefinedType(Format.UserDefined, IsByteOrdered:=True, _                     MaxByteSize:=4096, IsFixedLength:=False, _                     ValidationMethodName:="ValidateExtPrice")> _ Public Structure ExtPriceUDT     Implements INullable, IBinarySerialize #Region "Properties"     'field members     Private m_Qty As Decimal     Private m_Price As Decimal     Public Property Price() As Decimal         Get             Return (Me.m_Price)         End Get         Set(ByVal Value As Decimal)             m_Price = Value         End Set     End Property     Public Property Qty() As Decimal         Get             Return (Me.m_Qty)         End Get         Set(ByVal Value As Decimal)           m_Qty = Value         End Set     End Property #End Region #Region "Cast"     <SqlMethod(OnNullCall:=False)> _     Public Shared Function Parse(ByVal s As SqlString) As ExtPriceUDT         If s.IsNull Then             Return Null         End If         Dim u As ExtPriceUDT = New ExtPriceUDT         Dim x() As String = s.Value.Split("x".ToCharArray())         u.m_Qty = Decimal.Parse(x(0))         u.m_Price = Decimal.Parse(x(1))         If Not u.ValidateExtPrice() Then             Throw New ArgumentException("Invalid extended price values.")         End If         Return u     End Function    <SqlMethod(OnNullCall:=False)> _    Public Overrides Function ToString() As String        Return m_Qty.ToString + " x $" + m_Price.ToString    End Function #End Region #Region "Null"     ' Private member     Private m_Null As Boolean     Public Readonly Property IsNull() As Boolean _            Implements INullable.IsNull         Get             Return m_Null         End Get     End Property     Public Shared Readonly Property Null() As ExtPriceUDT        Get           Dim h As ExtPriceUDT = New ExtPriceUDT           h.m_Null = True           Return h        End Get    End Property #End Region #Region "Method"      <SqlMethod(OnNullCall:=False)> _      Private Function ValidateExtPrice() As Boolean         If (m_Qty >= 0) And (m_Price >= 0) Then             Return True         Else             Return False        End If    End Function    ' Return extended price    <SqlMethod(OnNullCall:=False)> _    Public Function ExtPrice() As Decimal        Return m_Qty * m_Price    End Function    <SqlMethod(OnNullCall:=False)> _    Public Function Diff(ByVal a As ExtPriceUDT) As ExtPriceUDT        'The following calculation does not make much mathematical sense.        'It is a simple demonstration of method in UDT.        Dim d As New ExtPriceUDT        If a.ExtPrice >= Me.ExtPrice Then            d.m_Price = a.m_Price - Me.m_Price            d.m_Qty = a.m_Qty - Me.m_Qty        Else            d.m_Price = Me.m_Price - a.m_Price            d.m_Qty = Me.m_Qty - a.m_Qty        End If        Return d    End Function    Public Shared Function DefaultValue() As ExtPriceUDT        Return ExtPriceUDT.Parse("0 x 0")    End Function #End Region #Region "Serialization"      <SqlMethod(OnNullCall:=False)> _      Public Sub Read(ByVal r As BinaryReader) _              Implements IBinarySerialize.Read         m_Qty = r.ReadDecimal()         m_Price = r.ReadDecimal()    End Sub      <SqlMethod(OnNullCall:=False)> _      Public Sub Write(ByVal w As BinaryWriter) _              Implements IBinarySerialize.Write          w.Write(m_Qty)          w.Write(m_Price)      End Sub #End Region End Structure 

To deploy the UDT, choose Build | Deploy from the menu. TIP

Tip 

A user must have Execute permission on a UDT to use it. I was not emphasizing this requirement on earlier programmatic objects, such as stored procedures and functions, because this requirement is the same for CLR objects and Transact-SQL objects. A user needs a permission to use a managed UDT, unlike a Transact-SQL UDT. The reason is that a managed UDT contains methods.

UDT in Tables and Variables

The use of a managed UDT in a table is very similar to the use of native SQL Server data types:

 create table OrderItem_wUDT (    OrderItemId int NOT NULL,    OrderId int NOT NULL,    PartIdd int NOT NULL,    ExtPrice ExtPriceUDT NULL ) GO 

It is simple to declare variables as instances of a CLR UDT:

 declare @ext_price ExtPriceUDT 

Invoking Static Methods

Static methods of a UDT are called using the :: operator. Note that this concerns static methods—not methods of an object instance.

 declare @a cudt_PriceDate set @a = CONVERT(cudt PriceDate, ' 911. 799;2005-11-02'} declare @b cudt_PriceDate set @b = Cast ('912.35;2005-11-03' as cudt_PriceDate) select cudt_PriceDate::DiffDate (@a, @b),        cudt_PriceDate::DiffPrice(@a, @b) 

The following statement creates a table with a column of the type ExtPriceUDT and sets a default value for it. Default values of UDT fields can be created by referencing static methods of UDTs.

 create table OrderItem_wUDT (    OrderItemId int NOT NULL,    OrderId int NOT NULL,    PartIdd int NOT NULL,    ExtPrice ExtPriceUDT NOT NULL         DEFAULT ExtPriceUDT::DefaultValue() ) GO 

Populating a Table

You can populate the OrderItem_wUDT table in almost the same way as if it were created with regular data types only. You can use a modification statement in the same way, but you have to use one of the methods that produces instances of ExtPriceUDT. In the following example, I used the Parse() method, which converts a string into an instance of ExtPriceUDT:

 INSERT   INTO    [dbo].[OrderItem_wUDT]    ([OrderItemId],[OrderId],[PartIdd],[ExtPrice]) values(   1,    1,    1,   ExtPriceUDT::Parse('35  x  450')) 

Accessing Values

Accessing properties or the complete UDT is very straightforward:

 select    ExtPrice.Qty qty,    ExtPrice.Price price,    ExtPrice from OrderItem_wUDT 

The query will return string representations of properties and binary representations of the UDT:

 Qty Price ExtPrice --- ----- -------------------------------------------------------- 35  45    0x23000000000000000000000000000000C201000000000000000000 

We can go a step further and access methods that are defined in the UDT:

 select     ExtPrice.ExtPrice(} [ExtPrice(}],     ExtPrice. ToString() [ToString () ] ,     ExtPrice.Diff(ExtPriceUDT::Parse('25 x 250')).ToString(} [Diff()] from OrderItem_wUDT 

The first and second columns display calculated values—decimal and string representations of extended price. The third column is a little more complicated. I wanted to display the difference between quantities and the difference between unit prices combined into a new instance of ExtPriceUDT. (I know that this calculation does not make much sense—I just wanted to demonstrate how to invoke a method.) Therefore, I created a new instance of ExtPrice using the Parse() method and used it as an argument of the Diff() method. It returns a result in the form of an ExtPriceUDT, so I applied the ToString() method to it (at the end) to convert it to a string. The result will be something like this:

 ExtPrice()     ToString()      Diff() -------------- --------------- ----------------- 15750          35 x $450       10 x $200 

Converting Values

All conversions that were performed in the previous section were based on the invocation of static methods and are relatively straightforward. But let's see some UDT magic:

 declare @ext_price ExtPriceUDT select @ext_price = Convert(ExtPriceUDT, '25 x 250') select @ext_price [Convert()] 

CLR will invoke the Parse() method although you have not implicitly invoked it. The result will be a binary representation of the UDT:

 Convert() ------------------------------------------------------------------ 0x19000000000000000000000000000000FA000000000000000000000000000000 

The same would happen if you used Cast() instead of Convert(). You can apply additional methods to it to get useful results:

 select Convert(ExtPriceUDT, '25 x 250'). ExtPrice() ExtPrice 

The result will be

 ExtPrice -------------- 6250 

You can perform conversion in the opposite direction as well. You can use the Convert() or Cast() function to convert data to a string instead of ToString():

 Select Cast(ExtPrice as varchar(30)) ExtPrice From OrderItem_wUDT 

The result will be

 ExtPrice ----------------- 35 x $450 

Comparing Values

When a UDT is created with the IsByteOrdered of SqlUserDefinedType attribute set to True, its instances can be compared:

 Select * From OrderItem_wUDT where ExtPrice > Convert(ExtPriceUDT, '25 x 250') 

Updating Values

It is possible to update the complete UDT field:

 Update OrderItem_wUDT Set ExtPrice = Convert(ExtPriceUDT, '25 x 250') where OrderItemId = 1 

It is also possible to update an individual property:

 Update OrderItem_wUDT Set ExtPrice.Qty = 25 where OrderItemId = 1 

However, it is not possible to update more than one property of the same column in a single Update statement:

 Update OrderItem_wUDT Set ExtPrice.Qty = 25,     ExtPrice.Price = 250 where OrderItemId = 1 

The engine will return the following error:

 Msg 264, Level 16, State 1, Line 1 Column name 'ExtPrice' appears more than once in the result column list. 

You can solve this problem using a method that returns an instance of the UDT, such as Parse() (see the first Update statement in this section). Alternatively (or if you need to set just a subset of properties), you can create a new method to set the values.

Cross-database CLR User-defined Types

Managed UDTs are database-level objects. To use a single UDT in multiple databases, it must be deployed in each of them. That means that you have to execute Create Assembly and Create Type statements in each database. After that, SQL Server 2005 will perform implicit conversions of values from one database to the other.

The same applies when referencing a managed UDT in a temporary table. Although temporary tables work in the context of the current database, since they are actually created in the tempdb database, they cannot reference CLR UDTs that are defined in the current database. For example, the following script will not be executed successfully:

 Use Asset5 go create table #tmp(pd cudt_PriceDate) 

SQL Server will return the following error:

 Msg 2715, Level 16, State 7, Line 1 Column, parameter, or variable #1: Cannot find data type cudt_PriceDate. 




Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL &  .NET
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET
ISBN: 0072262281
EAN: 2147483647
Year: 2006
Pages: 165

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