Использование иерархических структур SQL Server в Silverlight через WCF

Источник: gotdotnet

Одной из новых фич SQL Server 2008 была поддержка иерархических структур данных. Эти структуры можно использовать для хранения информации об организационной структуре предприятия, структуре форума, топологии сети, и другой иерархической информации. В этой статье я хочу кассказать про то, как можно работать с этой новой структурой данных в SQL Server.

Для компиляции и использования исходного кода вам потребуются Visual Studio 2008 и SQL Server 2008.

Играем в SQL

Как вы уже наверное догадались, мы будем работать со структурами данных которые фактически позволят нам создавать древовидные структуры прямо в SQL Server (по сравнению со "строками" и "колонками" данных, к которым мы уже привыкли). Поскольку мы будем работать с деревьями, нам потребуется следующая информация:

Где находится корень дерева.
Кто является "предками" и "потомками" определенного элемента дерева.
Уровень (глубина) элемента в дереве.
Способы обхода дерева.
Способы индексирования элементов дерева.

Новый UDT

Для того чтобы работать с вышеописанными фичами, SQL Server 2008 внедрил новый UDT (user-defined type, тип определенный пользователем) под названием `hierarchyid` (MSDN). Этот тип содержит путь от корня до нашего элемента и, тем самым, мы можем использовать его вместо внешнего ключа для связывания элементов в иерархические структуры. Значение, которое SQL Server записывает в этот элемент нечитабельно (оно формируется с помощью хитрого алгоритма), но мы можем использовать метод `ToString()` для того чтобы получить его "читабельное" представление.

Прежде чем прыгать в код на C#, давайте поиграем со старым добрым SQL чтобы прочувствовать эту новую фичу и разобраться с тем, как с ней работать. Представим что мы используем SQL Server для хранения иерархической структуры документации (help topics как в дереве MSDN). Для начала, определим схему для нашей таблички:

1.create table HelpItem

2.(

3. Id hierarchyid primary key clustered,

4. Lvl as Id.GetLevel(),

5. ItemId int unique,

6. Name nvarchar(32) not null,

7. Content ntext

8.)

Наверное эта структура чуть-чуть отличается от того, к чему вы привыкли - особенно если вы привыкли к тому что первый ряд таблицы всегда `int identity`. Использование здесь `hierarchyid` в качестве primary key позволяет нам использовать различные подходы индексирования таблицы, которые мы обсудим позже. Вторая колонка, `Lvl`, является рассчетной колонкой (computed column) которая вызывает метод `GetLevel()` колонки `Id`. Думаю вы догадались что тут происходит - `hierarchyid` это CLR-тип, у которого есть метод который умеет определять "уровень" элемента в дереве.

Добавление элементов

Чтобы оценить полезность колонок `Id` и `Lvl`, давайте добавим данных в нашу таблицу. Это можно сделать с помощью следующего выражения:

1.insert into HelpItem

2. (Id, ItemId, Name, Content)

3.values

4. (hierarchyid::GetRoot(), 1, 'My Product', 'How to use my product')

Если вы никогда не встречались с использованием оператора `::` - ничего страшного - это просто оператор для вызова статического метода (class-level method) внутри SQL Server - инстанс-методы используют точку, так же как и в C#. В приведенном выше коде, мы заставили этот `hierarchyid` быть "корневым".

Чтобы просматривать данные, придется чуть-чуть постараться. Вызывать типичное `SELECT Id` тут бесполезно т.к. значение `hierarchyid` будет в формате `0x` в каждом ряду. И не спрашивайте меня почему. Чтобы получить читабельное значение, нужно использовать метод `ToString()` который я уже упоминал.

01.SELECT [Id].ToString()

02. ,[Lvl]

03. ,[ItemId]

04. ,[Name]

05. ,[Content]

06. FROM [Hierarchical].[dbo].[HelpItem]

07.

08.// here is the output:

09.

10.Id Lvl ItemId Name Content

11.-- --- ------ ---------- ---------------------

12./ 0 1 My Product How to use my product

Как видите, текствое представление корневого элемента `Id` это просто слэш (/), а уровень элемента - ноль (0).

Потомки и предки

Использовав немного шаманства, у нас получилось добавить корень дерева. Мы также предоставили каждому элементу дерева уникальный, автогенерируемый идентификатор `ItemId`. Теперь, для того чтобы добавить еще несколько элементов в иерархию, нам нужно познакомиться с методом `GetDescendant()`.

Вкратце, метод `GetDescendant()` генерирует `hierarchyid` нового элемента который а) является потомком элемента на котором он был вызван; и б) находится между двумя элементами которые вы предоставите `GetDescendant()` в качестве параметров. Конечно, любой из параметров может быть равен `null`, что означает отсутствие ограничений на местоположение нового элемента.

Ну вот, давайте теперь добавим потомок под названием "Introduction" нашему корневому элементу:

1.insert into HelpItem

2. (Id, ItemId, Name, Content)

3.values

4. ((select Id from HelpItem where ItemId = 1).GetDescendant(null,null),

5. 2,

6. 'Introduction',

7. 'Some general info about our product')

Еще раз повторюсь - в примере выше, для нашего нового элемента, мы выбрали "предком" корневой элемент. Передав два значения `null`, мы сказали SQL Server что нам все равно, где позиционно будет этот элемент - ведь у нас пока нет других элементов на этом уровне. Просмотр значения `hierarchyid` нашего нового элемента вернет нам значение `/1/`. Само по себе это значение нам не особо важно, т.к. нам нужно лишь бинарное значение.

В дополнение к возможности поиска потомков (что важно для произвольной вставки), мы также можем получить предка на N шагов выше по дереву путем вызова метода `GetAncestor(N)`. Результатом этого вызова будет `hierarchyid` соответствующего элемента, или `null` если в дереве нет предка такого уровня.

Еще один полезный метод - `IsDescendantOf()`. Он позволяет проверить, является ли конкретный `hierarchyid` потомком другого `hierachyid`, который передается в качестве параметра.

Ладно, уверен что вам уже надоела вся эта теоретика. Давайте попробуем применить иерархическую базу данных, привязав ее к приложению Silverlight.

Игры с C#

Мне всегда немного не по себе от фразы "современный ORM-фреймворк" - ведь если бы ORM-фреймворки шагали в ногу со временем, у нас была бы поддержка `hierachyid` и в Linq2Sql и в Entity Framework. Но конечно же это (пока) не так - этот тип не поддерживается.

После тщетных попыток заставить T4-шаблон Linq2Sql правильно взаимодействовать с типом `SqlHierarchyId` (вроде как все работало, но Linq2Sql не согласился с моей идеей использовать этот тип в качестве identity column), я решил работать без ORM, что для меня не является проблемой - ведь я планирую использовать небольшую табличку в Silverlight-приложении.

Строим DTO

Поскольку автогенерации сущности мы не дождемся, создадим ее сами.

01.[DataContract]

02.public class HelpItem

03.{

04. internal SqlHierarchyId Id;

05. [DataMember]

06. public int ItemId;

07. [DataMember]

08. public string Name;

09. [DataMember]

10. public string Content;

11. private List<HelpItem> children;

12. [DataMember]

13. public List<HelpItem> Children

14. {

15. get

16. {

17. return children ?? (children = new List<HelpItem>());

18. }

19. }

20.}

Следует пояснить что происходит в этой структуре. Некоторые из элементов не удасться передать через WCF, так что они объявлены как `private` - например, вы не сможете использовать `SqlHierarchyId` в Silverlight, поэтому это поле не получит атрибута `[DataMember]`. С другой стороны, для того чтобы передать иерархию по проводам нам придется ее "сжать" путем аггрегирования элементов в их же предках - для этого как раз нам пригодится коллекция `Children`.

Получение данных

Чтобы получить данные с сервера, мы используем старые добрые `SqlConnection` и `SqlCommand` - те конструкты которые использовались когда .Net только еще появлялся. Сейчас мы объявляем всего лишь один метод, который возвратит нам весь граф объектов:

01.[OperationContract]

02.public HelpItem GetHelpItems()

03.{

04. SqlConnection dbConn = new SqlConnection(

05. "Data Source=(local);Initial Catalog=Hierarchical;Integrated Security=True");

06. List<HelpItem> results = new List<HelpItem>();

07. try

08. {

09. dbConn.Open();

10. SqlCommand cmd = new SqlCommand(

11. "select Id.ToString(), ItemId, Name, Content from HelpItem order by Id.GetLevel()",

12. dbConn);

13. using (var reader = cmd.ExecuteReader())

14. {

15. while (reader.Read())

16. {

17. HelpItem i = new HelpItem

18. {

19. Id = SqlHierarchyId.Parse(reader.GetSqlString(0)),

20. ItemId = reader.GetInt32(1),

21. Name = reader.GetString(2),

22. Content = reader.GetString(3)

23. };

24. results.Add(i);

25. // make sure its parent knows

26. foreach (HelpItem parent in results.Where(r => r.Id.Equals(i.Id.GetAncestor(1))))

27. parent.Children.Add(i);

28. }

29. }

30. }

31. finally

32. {

33. if (dbConn.State == System.Data.ConnectionState.Open)

34. dbConn.Close();

35. }

36. return results.Count() > 0 ? results[0] : null;

37.}

В этом методе нет ничего умого помимо использования LINQ и метода `GetAncestor()` класса `SqlHierarchyId` для добавления каждого потомка к его "родителю". Заметьте также что несмотря на то, что мы возвращаем всего один элемент, этот элемент содержит в себе граф всех объектов.

Загрузка и использование

К сожалению, те POCO которые мы получаем от сервиса сами по себе не очень то полезны - у них слабая таких фич как например редактирования, поэтому мы вынуждены копировать их в свои структуры `HelpItem`:

01.private static void ProcessChildren(HelpItem orig, HelpService.HelpItem curr)

02.{

03. foreach (HelpService.HelpItem hi in curr.Children)

04. {

05. HelpItem i = new HelpItem { Name = hi.Name, Content = hi.Content };

06. orig.Children.Add(i);

07. ProcessChildren(i, hi);

08. }

09.}

10.void hsc_GetHelpItemsCompleted(object sender, GetHelpItemsCompletedEventArgs e)

11.{

12. // convert and assign

13. if (e.Result != null)

14. {

15. HelpItem root = new HelpItem

16. {

17. Name = e.Result.Name,

18. Content = e.Result.Content

19. };

20. ProcessChildren(root, e.Result);

21. items.Clear();

22. items.Add(root);

23. }

24.}

Эта процедура копирования позволяет нам использовать лучшие классы коллекций, и дополнительные фичи сущностей, которые нам пригодятся позже. Тем временем, мы можем использовать полученные элементы в нашей модели - и вот что мы получим:

Заключение

В этом посте я вкратце показал то как можно работать с `hierarchyid`. В исходниках - полноценный проект Silverlight/WCF который показывает один из вариантов того, как можно донести иерархические данные от сервера до клиента. Возможно есть и более интересные подходы - если вы с таковыми знакомы, пожалуйста, оставьте комментарий.

Скачать исходники к статье

Спасибо и до новых встреч!

Ссылки по теме

Файлы для загрузки

Исходники к статье