Импортирование UDT в MS SQL SERVER 2005

Как вы уже, наверное, знаете, в состав MS SQL Server 2005 была интегрирована CLR, что сделало возможным разворачивать наши приложения в пределах процесса MS SQL Server.

Данная статья посвящена созданию UDT(user defined type) и интеграции его в MS SQL Server.

Итак, давайте начнем… Для начала необходимо рассмотреть требования, предъявляемые к каждому UDT:
- UDT реализуется в виде public структур или классов;

- Должны быть отмечены атрибутом SqlUserDefinedType с указанием обязательных параметров, касающихся способа сериализации, и атрибутом Serializable

- Должны реализовывать интерфейс INullable
public bool IsNull Проверка на NULL

- Должны реализовывать методы:
public static <Тип> Parse(SqlString s) Преобразование из строки
public override string ToString() Преобразование в строку
public static <Тип> Null NULL значение

- Если выбран способ сериализации UserDefined, то должен быть реализован интерфейс IBinarySerialize
public void Write(System.IO.BinaryWriter w) Сериализация
public void Read(System.IO.BinaryReader r) Десериализация

Теперь давайте рассмотрим небольшой пример, в котором я реализовал 3 UDP:
1) SQLPoint – точка, которая представлена в виде 2х значений, разделенными двоеточием – x и y координаты. Пример: «4:2″
2) SQLPointList – множество точек, разделенными запятыми. Пример: «2:1,5:3,22:12″
3) ListOfSQLPointList – набор множеств точек. Пример: «{2:4,4:5,2:1},{2:4,4:4},{2:2}»

Создадим сборку, в которой реализуем данные типы:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlTypes;

namespace NewType
{
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute(Microsoft.SqlServer.Server.Format.UserDefined, MaxByteSize = 500)]
public class ListOfSQLPointList : Microsoft.SqlServer.Server.IBinarySerialize, INullable
{
List _lists;

public List Lists
{
get { return _lists; }
set { _lists = value; }
}
public ListOfSQLPointList()
{
_lists = new List();
}

#region IBinarySerialize Members

public void Read(System.IO.BinaryReader r)
{
string strRead = r.ReadString();
ListOfSQLPointList listOfPointLists = ListOfSQLPointList.Parse(strRead);
foreach (SQLPointList pointList in listOfPointLists.Lists)
{
this._lists.Add(pointList);
}

}
public static ListOfSQLPointList Parse(SqlString s)
{
ListOfSQLPointList list = new ListOfSQLPointList();
string[] strArr = s.ToString().Split(new string[] { «},{» }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < strArr.Length; i++)
{
strArr[i] = strArr[i].TrimEnd(‘}’);
strArr[i] = strArr[i].TrimStart(‘{‘);
SQLPointList point = SQLPointList.Parse(strArr[i]);
list.Lists.Add(point);
}
return list;
}
public override string ToString()
{
string strResult = String.Empty;
if (_lists.Count > 0)
{
for (int i = 0; i < this._lists.Count – 1; i++)
{
strResult += «{» + _lists[i].ToString() + «},»;
}

strResult += «{» + _lists[_lists.Count - 1].ToString() + «}»;
}
return strResult;
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(this.ToString());
}

#endregion

#region INullable Members

public bool IsNull
{
get { return false; }
}

#endregion

public static ListOfSQLPointList Null
{
get { return null; }
}
}

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute(Microsoft.SqlServer.Server.Format.UserDefined, MaxByteSize = 150)]
public class SQLPointList : Microsoft.SqlServer.Server.IBinarySerialize, INullable
{
List _points;

public List Points
{
get { return _points; }
set { _points = value; }
}
public SQLPointList()
{
_points = new List();
}

#region IBinarySerialize Members

public void Read(System.IO.BinaryReader r)
{
string strRead = r.ReadString();
SQLPointList pointList = SQLPointList.Parse(strRead);
foreach (SQLPoint point in pointList.Points)
{
this._points.Add(point);
}

}
public static SQLPointList Parse(SqlString s)
{
SQLPointList list = new SQLPointList();
string[] strArr = s.ToString().Split(‘,’);
for (int i = 0; i < strArr.Length; i++)
{
if (strArr[i] != «»)
{
SQLPoint point = SQLPoint.Parse(strArr[i]);
list.Points.Add(point);
}
}
return list;
}
public override string ToString()
{
string strResult = String.Empty;
if (_points.Count > 0)
{
for (int i = 0; i < _points.Count – 1; i++)
{
strResult += _points[i].ToString() + ‘,’;
}
strResult += _points[_points.Count - 1].ToString();
}
return strResult;
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(this.ToString());
}

#endregion

#region INullable Members

public bool IsNull
{
get { return false; }
}

#endregion

public static SQLPointList Null
{
get { return null; }
}
}

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedTypeAttribute(Microsoft.SqlServer.Server.Format.UserDefined, MaxByteSize = 20)]
public class SQLPoint : Microsoft.SqlServer.Server.IBinarySerialize, INullable
{
private int _x;
private int _y;

public int X
{
get { return _x; }
set { _x = value; }
}
public int Y
{
get { return _y; }
set { _y = value; }
}

#region IBinarySerialize Members

public void Read(System.IO.BinaryReader r)
{
string strResult = r.ReadString();
SQLPoint point = SQLPoint.Parse(strResult);
this._x = point.X;
this._y = point.Y;
}

public void Write(System.IO.BinaryWriter w)
{
w.Write(this.ToString());
}

#endregion

#region INullable Members

public bool IsNull
{
get { return false; }
}

#endregion

public static SQLPoint Parse(SqlString s)
{
string[] strArr = s.ToString().Split(‘:’);
SQLPoint point = new SQLPoint();
point.X = int.Parse(strArr[0]);
point.Y = int.Parse(strArr[1]);
return point;
}
public override string ToString()
{
return _x.ToString() + «:» + _y.ToString();
}
public static SQLPoint Null
{
get { return null; }
}

}
}

Открываем Managment Studio, выбираем базу данных, для которой будем импортировать новые типы и создаем SQL запрос

CREATE ASSEMBLY NewTypes
FROM ‘ПУТЬ К DLL’ке’ ;
GO
CREATE TYPE SQLPointList
EXTERNAL NAME NewTypes.[NewType.SQLPointList] ;
GO
CREATE TYPE SQLPoint
EXTERNAL NAME NewTypes.[NewType.SQLPoint] ;
go
CREATE TYPE ListOfSQLPointList
EXTERNAL NAME NewTypes.[NewType.ListOfSQLPointList] ;

А также не забудьте активировать CLR в MS SQL SERVER

EXEC sp_configure ’show advanced options’ , ‘1′;
go
reconfigure;
go
EXEC sp_configure ‘clr enabled’ , ‘1′
go
reconfigure;
– Turn advanced options back off
EXEC sp_configure ’show advanced options’ , ‘1′;
go

При создании таблицы появится 3 новых типа:

И теперь вы сможете сохранять собственный тип в ячейку: