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 : INullable, IComparable, IBinarySerialize
{
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 == null) return null;
//If we've got a cached copy of the culture return it.
if (culture != null) return 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 == null) return "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