Problem
You probably know that SQL Server allows us to create new user defined types. Since you are learning about SQL Server and CLR integration you wonder about the possibility to use .NET code to create them. Keep reading and you will find out if this can be done.
Solution
In this tip we will go through the process of making a CLR type to store IMEI (International Mobile Equipment Identity) numbers. For those of you who don’t know, this number is used to identify mobile phones. You can check the value of your cellphone IMEI number by entering *#06#. IMEI numbers are 15 digits long where the rightmost digit is the check digit. The value for the check digit is calculated using the Luhn algorithm.
The Luhn algorithm is composed of these steps:
- Take a string of numbers and starting from the right multiply each number by 2 if its position in the string is odd, or multiply by 1 otherwise.
- If the previous multiplication result is a two digit number (for example 8 x 2 = 16), then sum the two digits (in our example 1 + 6 = 7).
- Sum the results of all of the previous multiplications and call it M.
- The check digit is 10 – (M mod 10). If the result of this formula is 10 (i.e. M mod 10 = 0) then the check digit is 0.
Let’s take for example the IMEI number 334954835329660 that I made using an online tool that creates random IMEI numbers. On the next image you can see steps on how this is computed. The M value equals 80 which is all of the digits added together in the Intermediate Result.
Making an IMEI CLR Type
Let’s create our first SQL Server CLR type.
In order to proceed you will need to obtain a copy of Visual Studio which you can download from here. After downloading and installing your copy of Visual Studio, open the application and create a new “SQL Server Database Project” as shown on the image below.
After pressing the Next button, the New Project Wizard will prompt you for a project name and a location to save it. I named the project as CLR_Types, but you can use a name of your preference if you pleased.
Finally, with the project being created we must go to the Solution Explorer window and right click on any part of it. You will see a context menu pop up in which you must select the “Add” menu entry. Then another context menu will emerge and in this one you have to click on “New Item…”. A picture is worth a thousand words, so in the next screen capture you can see how this is done.
After pressing the “Add New Item…” entry of the context menu, you will see a new window open. This window allows you to choose amongst different classes of items to select the appropriate template that suits your needs. In this case we will select “SQL CLR C#” on the tree view at the leftmost side of the window; and then we must select “SQL CLR C# User Defined Type”. Of course, we must provide a name for the new object and we do it on the bottom of the window. In this case I decided to use IMEI as the name. Take a look at the next screen capture to see it more graphically.
Finally, a new file named IMEI.cs was created with some prebuilt code as can be seen in the following screen capture.
By analyzing the auto generated code we can see that it contains a public structure named IMEI that implements the INullable interface. This is because user defined types must implement nullability, to say this more colloquially we must tell the SQL Server engine how to deal with NULL values. To implement this interface the template includes two functions. The IsNull function must return a Boolean true or false value depending if the value of the IMEI type is NULL. The other function is named Null and it must return an IMEI structure. Also, we have a private Boolean property that is named _null and we must keep its value updated in order to the IsNull function to work. In the code section below, you can see how I implemented these two functions.
public bool IsNull
{
get
{
return _null;
}
}
public static IMEI Null
{
get
{
IMEI h = new IMEI();
h._null = true;
h._sIMEI = new SqlString();
h._sIMEI = SqlString.Null;
return h;
}
}
There are also two other functions in the code template which we have to implement: ToString and Parse. The ToString function is the one responsible to show a string representation of our type. In our case, it will show a string containing an IMEI number.
The Parse function in the other hand, takes a string representation of our type. In our case, this function will check the input string for a valid IMEI number by calling a function named CheckLuhn.
In the next code section, you can see the full code of this UDT so you can copy, paste and compile. Also, I will be referencing parts of this code further on in order to explain it.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;
using System.Text;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,MaxByteSize =16, IsFixedLength = true,
Name = "IMEI")]
public struct IMEI : INullable, IBinarySerialize
{
public override string ToString()
{
return (string) _sIMEI;
}
public bool IsNull
{
get
{
return _null;
}
}
public static IMEI Null
{
get
{
IMEI h = new IMEI();
h._null = true;
h._sIMEI = new SqlString();
h._sIMEI = SqlString.Null;
return h;
}
}
public static IMEI Parse(SqlString s)
{
bool bIsValid = false;
IMEI u = new IMEI();
u._sIMEI = s;
if (s.IsNull)
return Null;
bIsValid = u.CheckLuhn();
if (!bIsValid)
{
InvalidCastException invalidCastException = new InvalidCastException("The value is not a valid IMEI number.");
throw invalidCastException;
}
return u;
}
void IBinarySerialize.Read(BinaryReader r)
{
int maxStringSize = 16;
char[] chars;
int stringEnd;
string stringValue;
chars = r.ReadChars(maxStringSize);
stringEnd = Array.IndexOf(chars, '\0');
if (stringEnd == 0)
{
stringValue = null;
return;
}
stringValue = new String(chars, 0, stringEnd);
this._sIMEI = stringValue;
}
void IBinarySerialize.Write(BinaryWriter w)
{
int maxStringSize = 16;
string stringValue = (string)this._sIMEI;
string paddedString;
Encoding ascii = Encoding.ASCII;
// Pad the string from the right with null characters.
paddedString = stringValue.PadRight(maxStringSize, '\0');
byte[] b = ascii.GetBytes(paddedString);
w.Write(b);
}
public SqlString _sIMEI { get; set; }
// Private member
private bool _null;
public bool CheckLuhn()
{
string str = (string)_sIMEI;
int iDigit = 0;
int iSum = 0;
bool bIsOdd = false;
for(int i= str.Length -1; i >=0; i--)
{
iDigit = (int)Char.GetNumericValue(str[i]);
if (bIsOdd == true)
iDigit *= 2;
iSum += iDigit / 10;
iSum += iDigit % 10;
bIsOdd = !bIsOdd;
}
return (iSum % 10 == 0);
}
}
Structure Attributes
Something that may have drawn your attention is the code enclosed with brackets before the declaration of the IMEI structure. Those are the attributes that defines the behavior of our structure.
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,MaxByteSize =16, IsFixedLength = true,
Name = "IMEI")]
The first attribute is Serializable. For those of you who are new to this concept, serialization is the process of converting an object into a stream of bytes to store or transmit it.
The second attribute is Microsoft.SqlServer.Server.SqlUserDefinedType and as you may guess, it defines that we are making a SQL Server user defined type. This attribute requires that we specify at least one property which is the Format property.
The Format property is used to indicate the serialization format of this structure. You can set this property to Format.Native or Format.UserDefined, depending on your choice you may need to specify additional properties.
- Format.Native: If you use the native format then your UDT structure is only allowed to use basic types like bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney. Notice that the String data type is not allowed in the native format. This is because the String data type is a Class in the .NET library.
- Format.UserDefined: With this format you can use any complex type you want, like string, XML, or even raw binary format. But since this format gives you full control, you must implement the IBinarySerialize interface to provide a serialization method for the UDT. Additionally, you must specify a value for the MaxByteSize property when using this type of format. Also, you can set the IsFixedLength property attribute.
In this UDT, I also defined the MaxByteSize and IsFixedLength properties to specify that all the instances of this data type will have the same length of 16 bytes which is the length of IMEI numbers. Finally, the last property, Name is the SQL Server name of our user defined type.
Dealing With Serialization and String Conversion
All user defined CLR types must be serializable, either by native serialization or with a serialization method created by the programmer implementing the IBinarySerialize interface. Something I want to emphasize is that even though a SQL Server CLR user defined type has the ability to parse a string in order to get its value, it doesn’t require that the base underlying types be of the string data type. For example, you can have a data type that represents RGB colors and pass the value for Red Green and Blue in a comma separated string but instead of storing the string you may want to store each color in a separated integer private variable.
In our IMEI type example I decided to keep the IMEI number in a string and therefore I had to implement two functions: IBinarySerialize.Read and IBinarySerialize.Write. If you go up to the section where the code for this data type is, you will see that the implementation of those functions is very similar to writing/reading a file.
Building and Deploying
To be able to use our newly created CLR type we must build our Visual Studio project by going to the Build menu and selecting “Rebuild Solution” as can be seen in the image below.
After building our project, we can see that a DLL file has been generated in the.\Bin\Debug location inside the project folder. The first step we have to do before creating our recently created user defined type is to create the assembly in our SQL Server database as follows.
CREATE ASSEMBLY [CLR_Types] FROM 'C:\source\repos\CLR_Types\bin\Debug\CLR_Types.dll' WITH PERMISSION_SET=SAFE GO
Finally with the assembly being created, now we can create our IMEI data type by running this command.
CREATE TYPE [dbo].[IMEI] EXTERNAL NAME [CLR_Types].[IMEI]; GO
The next screen capture shows the execution of the two sets of code above.
Testing
Now we can do some tests with our IMEI data type. Let’s take for example the IMEI number 334954835329660 and try to guess what will be the output of the following code.
USE SampleDB; GO DECLARE @I IMEI = '334954835329660' SELECT @I SELECT CAST(@I AS VARCHAR(16))
At first, what you are about to see in the next image can surprise you. We create a variable of type IMEI and assign its value by passing the IMEI number in a string. Since this is a valid IMEI, we don’t get any error or warning message. But when we want to retrieve the value stored in our variable, we get a binary stream. That’s because even though the primitive type in our IMEI type is a string, the IMEI type itself is not a string. Now you see why we had to implement the ToString method. Additionally, you will see that we can call the ToString function directly because it is declared as public.
Now let’s see what happens when we try to assign an invalid value to our type.
USE SampleDB; GO DECLARE @I IMEI = '334954835329630';
As you can see in the next screen capture, the exception we raised in the Parse function sends the error back to SQL Server.
Next Steps
- If you are new to CLR integration, take a look at my previous tip SQL Server CLR Introduction where I cover the basics.
- In order to test this tip in your environment you will need to enable CLR integration. If you don’t know how to do it take a look at this other tip of mine: How to Enable SQL Server CLR Integration using T-SQL.
