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

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

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

Скачать исходный код (1.88Mb)

Играем в SQL

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

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

Новый UDT

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

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

create table HelpItem
(
  Id      hierarchyid primary key clustered,
  Lvl     as Id.GetLevel(),
  ItemId  int unique,
  Name    nvarchar(32) not null,
  Content ntext
)

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

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

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

insert into HelpItem
  (Id, ItemId, Name, Content)
values
  (hierarchyid::GetRoot(), 1, 'My Product', 'How to use my product')

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

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

SELECT [Id].ToString()
      ,[Lvl]
      ,[ItemId]
      ,[Name]
      ,[Content]
  FROM [Hierarchical].[dbo].[HelpItem]
  
// here is the output:
 
Id Lvl ItemId Name       Content
-- --- ------ ---------- ---------------------
/  0   1      My Product How to use my product

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

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

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

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

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

insert into HelpItem
  (Id, ItemId, Name, Content)
values
  ((select Id from HelpItem where ItemId = 1).GetDescendant(null,null),
   2,
   'Introduction',
   '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

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

[DataContract]
public class HelpItem
{
  internal SqlHierarchyId Id;
  [DataMember]
  public int ItemId;
  [DataMember]
  public string Name;
  [DataMember]
  public string Content;
  private List<HelpItem> children;
  [DataMember]
  public List<HelpItem> Children
  {
    get
    {
      return children ?? (children = new List<HelpItem>());
    }
  }
}

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

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

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

[OperationContract]
public HelpItem GetHelpItems()
{
  SqlConnection dbConn = new SqlConnection(
    "Data Source=(local);Initial Catalog=Hierarchical;Integrated Security=True");
  List<HelpItem> results = new List<HelpItem>();
  try
  {
    dbConn.Open();
    SqlCommand cmd = new SqlCommand(
      "select Id.ToString(), ItemId, Name, Content from HelpItem order by Id.GetLevel()",
      dbConn);
    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        HelpItem i = new HelpItem
        {
          Id = SqlHierarchyId.Parse(reader.GetSqlString(0)),
          ItemId = reader.GetInt32(1),
          Name = reader.GetString(2),
          Content = reader.GetString(3)
        };
        results.Add(i);
        // make sure its parent knows
        foreach (HelpItem parent in results.Where(r => r.Id.Equals(i.Id.GetAncestor(1))))
          parent.Children.Add(i);
      }
    }
  }
  finally
  {
    if (dbConn.State == System.Data.ConnectionState.Open)
      dbConn.Close();
  }
  return results.Count() > 0 ? results[0] : null;
}

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

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

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

private static void ProcessChildren(HelpItem orig, HelpService.HelpItem curr)
{
  foreach (HelpService.HelpItem hi in curr.Children)
  {
    HelpItem i = new HelpItem { Name = hi.Name, Content = hi.Content };
    orig.Children.Add(i);
    ProcessChildren(i, hi);
  }
}
void hsc_GetHelpItemsCompleted(object sender, GetHelpItemsCompletedEventArgs e)
{
  // convert and assign
  if (e.Result != null)
  {
    HelpItem root = new HelpItem
                      {
                        Name = e.Result.Name,
                        Content = e.Result.Content
                      };
    ProcessChildren(root, e.Result);
    items.Clear();
    items.Add(root);
  }
}

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

Заключение

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

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