Использование иерархических структур 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` ( Прежде чем прыгать в код на 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`. В Скачать исходники к статьеСпасибо и до новых встреч! |