Використання ієрархічних структур SQL Server в Silverlight через WCF, Інші СУБД, Бази даних, статті

 


Однією з нових фіч 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 який показує один з варіантів того, як можна донести ієрархічні дані від сервера до клієнта. Можливо є й більш цікаві підходи – якщо ви з такими знайомі, будь ласка, залиште коментар.


Завантажити исходники до статті


Спасибі і до нових зустрічей!

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*