SQL Server integration with CLR aka SQL CLR (Part VI – Creating a SQL UDT user defined type in SQL CLR)

Vishvvas
Posted by in .NET Framework category on for Advance level | Points: 300 | Views : 6972 red flag
Rating: 5 out of 5  
 2 vote(s)

This is next write-up in the multipart series for SQL Server Integration with CLR aka SQL CLR and discusses the creation of SQL user defined type through SQL CLR.

Introduction


In the first article, Introduction we discussed what is SQLCLR, why SQLCLR and the steps for employing SQL CLR into our work. In the next articles Stored Procedures, UDF, Aggregate, Trigger we explored how to create a SQL CLR stored procedure, UDF, Aggregate and trigger and in this article, we would see the creation a SQL server user defined type in SQL CLR.

User defined type popularly known as UDT provides extensibility to SQL server data and provides the ability to store objects and custom data structures in SQL server. UDT’s were introduced in SQL server 2005. Such UDT can contain multiple elements or data types and have different behaviours than the SQL server alias data types(the alias data types consists the single SQL Server system data type).
 

Objective


To learn how to create a SQL server user defined type in SQL CLR and use it. 

Description


Note: The tool for development is VS 2010 and SQL server 2008 R2. The new database “Test” is referred.

The first step in learning the SQL CLR database items is to create a SQL CLR project and a UDT can be added to such project as following.

When a UDT is added, the Visual Studio adds a class in the file as shown below.

The construct “struct” is specified for defining UDT as it is for Aggregate. The UDT is quite complex compared to other SQL CLR items and it requires lot of considerations from writing (serialization),  reading (with “Parse” functions), validations etc.

The database Test SQL server 2008 R2 has UDT’s as shown following


We would go through the code for Currency UDT and would try on the database “Test”. The code for such UDT is as follows (which is provided by Microsoft). The comments are self explanaroty.

/*=====================================================================

 

  File:      Currency.cs for Adventure Works Cycles SQLCLR Layer Sample

  Summary:   Defines a class for handing particular amounts of money in a

                                               particular culture's monetary system.  This class is exposed as

                                               a SQL Server UDT.

  Date:           August 15, 2003

 

---------------------------------------------------------------------

  This file is part of the Microsoft SQL Server Code Samples.

  Copyright (C) Microsoft Corporation.  All rights reserved.

 

This source code is intended only as a supplement to Microsoft

Development Tools and/or on-line documentation.  See these other

materials for detailed information regarding Microsoft code samples.

 

THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY

KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

PARTICULAR PURPOSE.

======================================================= */

using System;

using System.Globalization;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

 

//[Serializable]

//[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]

[Serializable]

[SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 32)]

public struct Currency : INullableIComparableIBinarySerialize

{

    const string nullMarker = "\0\0\0\0\0\0\0\0\0\0";

    const int cultureNameMaxSize = 10;

 

    private string cultureName;             //Who issued the money (en-us, for example)

 

    private CultureInfo culture;   //The object which represents cultureName

 

    private decimal currencyValue; //The amount of money

 

    // Public properties for private fields

    public CultureInfo Culture

    {

        get

        {

            //A culture name is required.  If not present the entire object is considered null.

            if (cultureName == nullreturn null;

 

            //If we've got a cached copy of the culture return it.

            if (culture != nullreturn culture;

 

            //Otherwise, set the cache and return the culture for the culture name specified.

            culture = CultureInfo.CreateSpecificCulture(cultureName);

            return culture;

        }

    }

 

    // Public property for the private field.

    public decimal CurrencyValue

    {

        get

        {

            return currencyValue;

        }

    }

 

    // Constructors for when we have the culture or the name of the culture

 

    public Currency(CultureInfo culture, decimal currencyValue)

    {

        this.cultureName = culture.Name;

        this.culture = culture;

        this.currencyValue = currencyValue;

    }

 

    public Currency(string cultureName, decimal currencyValue)

    {

        this.cultureName = cultureName;

        this.culture = null;

        this.currencyValue = currencyValue;

    }

 

    //Return the string representation for the currency, including the currency symbol.

    [SqlMethod(IsDeterministic = true,

        IsPrecise = true, DataAccess = DataAccessKind.None,

        SystemDataAccess = SystemDataAccessKind.None)]

    public override string ToString()

    {

        if (this.Culture == nullreturn "null";

 

        return String.Format(this.Culture, "{0:c}", currencyValue);

    }

 

    //The entire value of the currency is considered null if the culture name is null

    public bool IsNull

    {

        get

        {

            return cultureName == null;

        }

    }

 

    //The no-argument constructor makes a null currency.

    public static Currency Null

    {

        get

        {

            Currency h = new Currency((String)null, 0);

 

            return h;

        }

    }

 

    //Be sure to set the current UI culture before using this method! Even better, provide the culture

    //specifically (for the method after this one).

    [SqlMethod(IsDeterministic = true, IsPrecise = true, DataAccess = DataAccessKind.None, SystemDataAccess = SystemDataAccessKind.None)]

    public static Currency Parse(SqlString sqlString)

    {

        return ParseWithCulture(sqlString, CultureInfo.CurrentUICulture);

    }

 

    public static Currency ParseWithCulture(SqlString sqlString, CultureInfo culture)

    {

        if (sqlString.IsNull || sqlString.Value.ToLower(

            CultureInfo.InvariantCulture) == "null")

            return Currency.Null;

 

        int digitPos = -1;

        string stringValue = sqlString.Value;

 

        while (digitPos < stringValue.Length

            && !Char.IsDigit(stringValue, ++digitPos))

        {

        }

 

        if (digitPos < stringValue.Length)

            return new Currency(culture, decimal.Parse(

                stringValue.Substring(digitPos), culture));

 

        return Currency.Null;

    }

 

    public override int GetHashCode()

    {

        if (this.IsNull)

            return 0;

 

        return this.ToString().GetHashCode();

    }

 

    //Note: This only affects the behavior of CLR, not SQL Server.  Comparions

    //for SQL Server will be determined by the Write method below.

    public int CompareTo(object obj)

    {

        if (obj == null)

            return 1; //by definition

 

        if (obj == null || !(obj is Currency))

            throw new ArgumentException(

                "the argument to compare is not a Currency");

 

        Currency c = (Currency)obj;

 

        if (this.IsNull)

        {

            if (c.IsNull)

                return 0;

 

            return -1;

        }

 

        if (c.IsNull)

            return 1;

 

        string thisCultureName = this.Culture.Name;

        string otherCultureName = c.Culture.Name;

        if (!thisCultureName.Equals(otherCultureName))

            return thisCultureName.CompareTo(otherCultureName);

        return this.CurrencyValue.CompareTo(c.CurrencyValue);

    }

    // IBinarySerialize methods

    // The binary layout is as follow:

    //    Bytes 0 - 19:   Culture name, padded to the right with null characters, UTF-16 encoded

    //    Bytes 20+:      Decimal value of money

    // If the culture name is empty, the currency is null.

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage""CA2201:DoNotRaiseReservedExceptionTypes")]

    public void Write(System.IO.BinaryWriter w)

    {

        if (this.IsNull)

        {

            w.Write(nullMarker);

            w.Write((decimal)0);

            return;

        }

 

        if (cultureName.Length > cultureNameMaxSize)

        {

            throw new ApplicationException(string.Format(

                CultureInfo.InvariantCulture,

                "{0} is an invalid culture name for currency as it is too long.",

                cultureNameMaxSize));

        }

 

        String paddedName = cultureName.PadRight(cultureNameMaxSize, '\0');

        for (int i = 0; i < cultureNameMaxSize; i++)

        {

            w.Write(paddedName[i]);

        }

 

        // Normalize decimal value to two places

        currencyValue = Decimal.Floor(currencyValue * 100) / 100;

        w.Write(currencyValue);

    }

    public void Read(System.IO.BinaryReader r)

    {

        char[] name = r.ReadChars(cultureNameMaxSize);

        int stringEnd = Array.IndexOf(name, '\0');

 

        if (stringEnd == 0)

        {

            cultureName = null;

            return;

        }

 

        cultureName = new String(name, 0, stringEnd);

        currencyValue = r.ReadDecimal();

    }

}

Once this source code is built, it can be deployed to SQL server.

The build and deployment (shortcut key Ctrl + F5) process is same in Visual Studio 2010 as it is for other database items like stored procedure and UDF etc.

The TSQL statements to deploy the assembly and UDT is as follows

CREATE ASSEMBLY SqlServerProject

FROM ‘……\SqlServerProject\bin\Debug\SqlServerProject.dll'

WITH PERMISSION_SET = SAFE;

 

 

USE [Test]

GO

CREATE TYPE Currency

EXTERNAL NAME SqlServerproject.Currency

GO

 

To perform operations on this UDT , we would create following table.

USE [Test]

GO

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

CREATE TABLE [dbo].[ProductSales](

      [SalesID] [int] NOT NULL,

      [ProductID] [int] NOT NULL,

      [Qty] [smallint] NOT NULL,

      [Price] [dbo].[Currency] NULL

) ON [PRIMARY]

 

GO

Let’s insert some data into this table 

INSERT INTO [Test].[dbo].[ProductSales]

           ([SalesID]

           ,[ProductID]

           ,[Qty]

           ,[Price])

     VALUES

           (1,10,100,'50'

          )

GO

Please look at the data passed for currency. In this case the default culture would be selected i.e. en-US and we would see how this data appears when selected.

SELECT TOP 1 [SalesID]

      ,[ProductID]

      ,[Qty]

      ,[Price]

      , PriceToPay = CAST([Price] AS nvarchar)

  FROM [Test].[dbo].[ProductSales]


The above screenshot shows the result of select operation. The select query on this column returns binary value and when casted to “nvarchar” returns the vale with “$” sign because of the culture of the system OS.

Such user-defined types cannot be changed/ modified once they are created, as such changes could invalidate data in tables or indexes. To modify a type, one must either drop the type and then re-create it, or execute an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause.

As UDTs are accessed by the system as a whole unit, their use for complex data types may affect performance negatively. Complex data is preferably better modelled using traditional rows and tables. UDTs in SQL Server are well suited to the following:

·         Date, time, currency, and extended numeric types e.g. hierarchyid

·         Geospatial applications- e.g. geometry, geography

·         Encoded or encrypted data

UDT’s can have behavior and such behaviour can be accessed through the TSQL.

Creating, building, deploying and running (testing) a SQLCLR UDT is also straightforward as for other database items and with VS 2010 support for build and deploy in addition to the provision for test output in one go.


Summary and Conclusion


We are through our first exercise of creating a UDT in SQL CLR along-with deployment and testing. UDT’s are complex and requires advanced knowledge. The modification and changes also not straightforward as the data might be existing for such UDT’s in database tables.

Hope this helps to help understand how to create SQL CLR UDT and start mastering the SQL CLR world.

HAPPY PROGRAMMING!!!


Reference


http://msdn.microsoft.com/en-us/library/ms131106.aspx

http://msdn.microsoft.com/en-us/library/ms131086.aspx

Page copy protected against web site content infringement by Copyscape

About the Author

Vishvvas
Full Name: Vishwas Sutar
Member Level: HonoraryPlatinum
Member Status: Member,MVP
Member Since: 5/30/2011 2:13:10 AM
Country: India

http://www.dotnetfunda.com
Extensive and rich experience across gamut of technologies and programming languages like PB,VB,C++,VB.NET, C#, Classic ASP,ASP.NET, ASP.NET MVC.

Login to vote for this post.

Comments or Responses

Posted by: Mmvaishnav on: 2/17/2012 | Points: 25
"Awesome". All your articles on sql clr are nice and helpful.

I had no idea about sql clr. This article helped me to get an overview of sql clr.

I was able to achieve my task with ease with the help of these articles.

Thanks!!

Login to post response

Comment using Facebook(Author doesn't get notification)