Building Database Objects with the .NET Framework : CLR Database Objects « ADO.net Database « ASP.NET Tutorial

1. ASP.NET Tutorial
2. ADO.net Database
3. CLR Database Objects

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%'