Как в SQL Server отсортировать иерархический рекурсивный запрос?

В SQL Server 2005 появилось нововведение в виде CTE (common table expressions), которое позволяет выполнять иерархические рекурсивные запросы к базе данных.
При разработке сайта Калькулятор на автокредит у меня появилась задача: отобразить иерархическую структуру страниц сайта, причем отсортированную по полю pageorder у страниц с одинаковым уровнем иерархии.

Структура таблицы выглядит примерно так:

CREATE TABLE [dbo].[pages](
  [id] [int] NULL,
  [pid] [int] NULL,
  [title] [nvarchar](max) NULL,
  [pageorder] [int] NULL
) ON [PRIMARY]

Поле pid указывает на id родителя данной записи. Кроме того должна еще существовать одна root-запись, у которой pid = NULL.

Итак сам запрос выглядит таким образом:

WITH CTE(pid, id, title, level, pageorder, pathstr) AS
(
 SELECT pid, id, title, 0, 0, CAST ('/' AS VARCHAR(MAX))
 FROM pages
 WHERE pid IS NULL
 UNION ALL
 SELECT e.pid, e.id, e.title, level + 1, e.pageorder, d.pathstr + CAST(e.pageorder as  VARCHAR) + '/'
 FROM pages e
     INNER JOIN CTE d
     ON e.pid = d.id

)
SELECT pid, id, replicate('... ', level) + title as title, level, pageorder, pathstr
FROM CTE
ORDER BY pathstr

Вот что мы получим после выполнения этого запроса:

pid         id          level       pageorder   pathstr   title                                                                                                                                                                                                                                    title
----------- ----------- ----------- ----------- --------- ------------------------------
NULL        1           0           0           /            Главная
1           4           1           3           /3/          ... Товары
4           5           2           3           /3/3/        ... ... автомобили
4           8           2           6           /3/6/        ... ... мотоциклы
1           2           1           6           /6/          ... Услуги
2           6           2           3           /6/3/        ... ... Лизинг
2           3           2           6           /6/6/        ... ... Кредитование
1           7           1           9           /9/          ... О сайте

(8 row(s) affected)

Стоит также отметить, что в SQL Server 2008 появился новый тип данных hierarchyid, позволяющий работать с иерархическими данными намного проще и удобнее.

LINQ to SQL Как получить случайную запись из таблицы SQL Server

Мне нужно было получить случайную запись из определенной таблицы в базе данных SQL Server.
В моем случае у меня была таблицы цитат и мне нужно было случайным образом выбирать запись(или записи) для отображения на веб-сайте. Кроме того я использовал LINQ to SQL на этом сайте, таким образом решение должно использовать эту технологию и, кроме того, быть достаточно простым и универсальным.

Идея заключалась в сортировке записей по случайному параметру а затем получении TOP n количества записей. Функция NEWID() из SQL Server выглядит хорошим кандидатом для получения случайного параметра, так как она генерирует GUID.

В SQL Server, попробуйте следующий запрос:

SELECT * FROM Quote ORDER by NEWID();

SELECT TOP 1 * FROM Quote ORDER by NEWID();

Сейчас для для нашего кода мы должны сгенерировать подобное поведение на .NET. Мне нужно было получить эквивалент NEWID() в моем LINQ to SQL DBML. Отмечу, что мы не можем непосредственно использовать NEWID() в хранимой процедуре или функции, так как SQL Server выдаст ошибку: “Invalid use of side-effecting or time-dependent operator in 'newid' within a function”. В обход этого мы создадим view который использует NEWID() и функцию, которая будет выбирать из этого view.

CREATE VIEW [dbo].[RandomView]
AS
SELECT NEWID() As ID
GO
CREATE FUNCTION [dbo].[GetNewId]
(
)
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END
GO

В DBML, мы просто перетащим таблицу Quote, а затем функцию GetNewID в дизайнер. Следующий код выбирает случайную запись из таблицы Quotes.

DataContext db = new QuoteDataContext();
var quote = db.Quote.OrderBy(q => db.GetNewId()).First();

Фактически мы можем выбирать любое количество записей из любой таблицы.

Как динамически сменить запрос для ListView привязанного к LinqDataSource?

Для того чтобы динамически сменить запрос для ListView привязанному к LinqDataSource нужно обработать событие OnSelecting у LinqDataSource. 

Например вот так:

        protected void dsMates_Selecting(object sender, LinqDataSourceSelectEventArgs e)
        {
            if (Request.Params["classMateId"] != null)
            {
                int mateId;
                if (int.TryParse(Request.Params["classMateId"], out mateId))
                {
                    using (DataClassesPrivateMsgDataContext db = new DataClassesPrivateMsgDataContext())
                    {
                        e.Result = db.Classmates.Where(mate => mate.Id == mateId).FirstOrDefault();
                    }
                }
            }
        }

Посмотреть как это работает можно здесь: сайт а-класса