Enabling CLR Integration
You must enable CLR integration by executing the following SQL Server command:
sp_configure 'clr enabled', 1
RECONFIGURE
Creating User-Defined Types with the .NET Framework
You can create a new user-defined type.
Then use it in exactly the same way as the built-in SQL types such as the Int, NVarChar, or Decimal types.
For example, you can create a new type and use the type to define a column in a database table.
To create a user-defined type with the .NET Framework
1. Create an assembly that contains the new type.
2. Register the assembly with SQL Server.
3. Create a type based on the assembly.
The class must be decorated with a SqlUserDefinedType attribute.
The class must be able to equal NULL.
The class must be serializable to/from a byte array.
The class must be serializable to/from a string.
SqlUserDefinedType supports the following properties:
Format specifies how a user-defined type is serialized in SQL Server.
Possible values are Native and UserDefined.
IsByteOrdered marks the user-defined type as ordered in the same way as its byte representation.
IsFixedLength specifies that all instances of this type have the same length.
MaxByteSize specifies the maximum size of the user-defined type in bytes.
Name specifies a name for the user-defined type.
File: DBProduct.cs
using System;
using System.Text;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Runtime.InteropServices;
using System.IO;
[SqlUserDefinedType(Format.UserDefined, MaxByteSize = 512, IsByteOrdered = true)]
public class DBProduct : INullable, IBinarySerialize
{
private bool _isNull;
private string _title;
private string _director;
private decimal totals;
public bool IsNull
{
get { return _isNull; }
}
public static DBProduct Null
{
get
{
DBProduct product = new DBProduct();
product._isNull = true;
return product;
}
}
public string Title
{
get { return _title; }
set { _title = value; }
}
public string Director
{
get { return _director; }
set { _director = value; }
}
[SqlFacet(Precision = 38, Scale = 2)]
public decimal Totals
{
get { return totals; }
set { totals = value; }
}
[SqlMethod(OnNullCall = false)]
public static DBProduct Parse(SqlString s)
{
if (s.IsNull)
return Null;
DBProduct product = new DBProduct();
string[] parts = s.Value.Split(new char[] { ',' });
product.Title = parts[0];
product.Director = parts[1];
product.Totals = decimal.Parse(parts[2]);
return product;
}
public override string ToString()
{
if (this.IsNull)
return "NULL";
StringBuilder builder = new StringBuilder();
builder.Append(_title);
builder.Append(",");
builder.Append(_director);
builder.Append(",");
builder.Append(totals.ToString());
return builder.ToString();
}
public void Write(BinaryWriter w)
{
w.Write(_title);
w.Write(_director);
w.Write(totals);
}
public void Read(BinaryReader r)
{
_title = r.ReadString();
_director = r.ReadString();
totals = r.ReadDecimal();
}
public DBProduct()
{
}
}
You need to compile the DBProduct class into a separate assembly (.dll file).
csc /t:library DBProduct.cs
Registering the User-Defined Type Assembly with SQL Server
Register the DBProduct assembly by executing the following command:
CREATE ASSEMBLY DBProduct
FROM 'C:\DBProduct.dll'
Check SQL Server by executing the following query:
SELECT * FROM sys.assemblies
You can drop any assembly by executing the DROP Assembly command.
DROP Assembly DBProduct
Creating the User-Defined Type
CREATE TYPE dbo.DBProduct EXTERNAL NAME DBProduct.DBProduct
If you need to delete the type, you can execute the following command:
DROP TYPE DBProduct
Create a new database table with the following command:
CREATE TABLE DBProducts(Id INT IDENTITY, Product DBProduct)
You can insert a new record into this table with the following command:
INSERT DBProducts (Product)
VALUES ('Star Wars,George Lucas,12.34')
Finally, you can perform queries against the table with queries like the following:
SELECT Id, Product FROM DBProducts WHERE Product.Totals > 13.23
SELECT MAX(Product.Totals) FROM DBProducts
SELECT Product FROM DBProducts WHERE Product.Director LIKE 'g%'