В проектах часто можно встретить использование хранимых процедур для вытяжки табличных данных. Однако, у хранимых процедур есть такие серьезные недостатки как отсутствие автоматической типизации выходных данных, отсутствие гибкости и вариантов абстрагирования.
В данной статье будут рассмотрены возможности представлений для устранения данных недостатков.
В первую очередь рассмотрим типизацию и гибкость выходных данных. В конце будет продемонстрирована возможность создания абстрактного движка для сортировки элементов через as.crud.
Строитель модели EntittyFramework способен автоматически сгенерировать сущности БД в виде прикладных объектов. Это правило справедливо как для таблиц, так и для их представлений. Эти объекты представляются как DbSet на прикладном уровне, которые поддерживают IQueryable, а значит, на прикладном уровне можно уточнить запрос к этим сущностям – фильтр, проекция полей, пагинация.
Рассмотрим произвольную Get-хранимку (детали опущены):
ALTER PROCEDURE [dbo].[GetCountMakeAndModel]
@modelPropertyId int,
@publishedStatusId int,
@parentCategoryId int
AS
BEGIN
WITH groupedMakeModel
AS
(
SELECT SUM(COUNT(*)) OVER (PARTITION BY ads.categoryID) AS MakeCount,
COUNT(*) as ModelCount,
ads.categoryID AS CategoryId,
category.name AS CategoryName,
(CASE WHEN avModel.value IS NULL THEN pvModel.value ELSE avModel.value END) AS Model,
image.path AS ImagePath
FROM cm_advertisements ads
INNER JOIN cm_categories category on category.id = ads.categoryID
LEFT JOIN cm_advertisementValues avModel ON avModel.advertisementID = ads.id AND avModel.propertyID = @modelPropertyId
LEFT JOIN cm_propertyValuesString pvModel ON pvModel.configurationID = ads.configurationID AND pvModel.propertyID = @modelPropertyId
LEFT JOIN cm_images image on category.imageID = image.id
WHERE ads.isReady = 1
AND statusID = @publishedStatusId
AND ads.isPart = 0
AND category.parentID = @parentCategoryId
GROUP BY ads.categoryID, category.name, CASE WHEN avModel.value IS NULL THEN pvModel.value ELSE avModel.value END, image.path
),
c
AS
(
SELECT *, DENSE_RANK() OVER(ORDER BY MakeCount DESC) AS CategoryNumber
FROM groupedMakeModel
)
SELECT MakeCount, CategoryId, CategoryName, ModelCount, Model, ImagePath
FROM c
WHERE CategoryNumber <= 15
ORDER BY MakeCount DESC, CategoryName, ModelCount DESC, Model;
ENDОбычная выборка с кучей JOIN и фильтром, структура которого задается жестко, лишь параметры гибкие.
Как эта хранимая процедура типизируется на прикладном уровне? Вручную пишется ViewModel, структура которой должна четко соответствовать структуре результата хранимой процедуры.
db.GetSQLDataTyped<MakeModelItem>("GetCountMakeAndModel", p, CommandType.StoredProcedure)
private class MakeModelItem
{
public int MakeCount { get; set; }
public int ModelCount { get; set; }
public int CategoryId { get; set; }
public string CategoryName { get; set; }
public string Model { get; set; }
public string ImagePath { get; set; }
}Вернемся к SQL коду и порассуждаем чисто в терминах БД. Почему для единичного SELECT-запроса делается хранимая процедура? Зачем ограничивать структуру фильтрации? Специально для подобного случая существуют представления SQL:
Представление выполняет функцию фильтра базовых таблиц, на которые оно ссылается. (c) msdn
Далее будет продемонстрировано аналогичное решение, но с помощью представлений.
IF OBJECT_ID('dbo.vw_psl_parcelServices', 'V') IS NOT NULL
DROP VIEW dbo.vw_psl_parcelServices;
GO
CREATE VIEW dbo.vw_psl_parcelServices
AS
SELECT
p.id as 'ID'
, p.name as 'ParcelName'
, 1 as 'IsIncoming'
, ost.code as 'OrderStatusCode'
, ost.name as 'OrderStatusName'
-- HACK
, p.outgoingParcelID as 'OutgoingParcelID'
, pt.code as 'ParcelType'
, p.parentID as 'ParentID'
, o.comment as 'Comment'
, '' as 'GiftComment'
, vp.MainSrc as 'MainSrc'
, s.*
FROM psl_incomingParcels p
LEFT JOIN vw_psl_incomingParcels vp
ON vp.ID = p.id
LEFT JOIN psl_service_incomingParcels ps
ON ps.incomingParcelID = p.id
LEFT JOIN vw_psl_services s
ON ps.serviceID = s.ServiceID
LEFT JOIN psl_parcelTypes pt
ON pt.id = p.parcelTypeID
LEFT JOIN psl_expectedParcelOrders epo
ON epo.id = p.orderID
LEFT JOIN psl_orders o
ON epo.orderID = o.id
LEFT JOIN psl_orderStatuses ost
ON ost.id = o.orderStatusID
UNION ALL
SELECT
p.id as 'ID'
, '' as 'ParcelName'
, 0 as 'IsIncoming'
, ost.code as 'OrderStatusCode'
, ost.name as 'OrderStatusName'
-- HACK
, NULL as 'OutgoingParcelID'
, 'OutgoingParcel' as 'ParcelType'
, NULL as 'ParentID'
, o.comment
as 'Comment'
, p.giftDesc as 'GiftComment'
, '' as 'MainSrc'
, s.*
FROM psl_outgoingParcels p
LEFT JOIN psl_outgoingParcels vp
ON vp.ID = p.id
LEFT JOIN psl_service_outgoingParcels ps
ON ps.outgoingParcelID = p.id
LEFT JOIN vw_psl_services s
ON ps.serviceID = s.ServiceID
LEFT JOIN psl_orders o
ON o.id = p.orderID
LEFT JOIN psl_orderStatuses ost
ON ost.id = o.orderStatusID
GO
-- test
select *
from vw_psl_parcelServices
order by ID DESC;В данном представлении, аналогично, лежит куча JOIN, плюс есть даже немного логики для определения отношения записи к определенному множеству – согласно бизнес-правилам. Более того, заданная структура автоматически импортируется в прикладной уровень в таком виде:
public partial class vw_psl_parcelServices
{
public int ID { get; set; }
public string ParcelName { get; set; }
public int IsIncoming { get; set; }
public string OrderStatusCode { get; set; }
public string OrderStatusName { get; set; }
public Nullable<int> OutgoingParcelID { get; set; }
public string ParcelType { get; set; }
public Nullable<int> ParentID { get; set; }
public string Comment { get; set; }
public string GiftComment { get; set; }
public string MainSrc { get; set; }
public Nullable<int> ServiceID { get; set; }
public string ServiceName { get; set; }
public string ServiceDesctiption { get; set; }
public Nullable<decimal> ServiceMinPrice { get; set; }
public Nullable<decimal> ServiceMaxPrice { get; set; }
public Nullable<int> ServiceHavePrecisePrice { get; set; }
public Nullable<decimal> ServicePrice { get; set; }
public Nullable<int> ServiceTypeID { get; set; }
public string ServiceTypeCode { get; set; }
public string ServiceTypeName { get; set; }
public Nullable<int> ParentServiceTypeID { get; set; }
public string ParentServiceTypeCode { get; set; }
public string ParentServiceTypeName { get; set; }
public Nullable<int> ServiceCategoryID { get; set; }
public string ServiceCategoryName { get; set; }
public string ServiceCategoryCode { get; set; }
public Nullable<int> ParentServiceCategoryID { get; set; }
public string ParentServiceCategoryName { get; set; }
public string ParentServiceCategoryCode { get; set; }
public string ServiceSrc { get; set; }
public string ServiceAlt { get; set; }
public string ServiceTitle { get; set; }
}И использование представления выглядит так:
// общая логика
public List<vw_psl_parcelServices> GetParcelServices(ParcelServiceFilter filter)
{
var res = repository.db.vw_psl_parcelServices.AsNoTracking().AsQueryable()
.Where(x => x.OrderStatusCode != "canceled")
.Where(x => x.OrderStatusCode != "cancelled")
.Where(x => x.OrderStatusCode != "deleted");
if (filter.Precise.IsFilled)
{
return res.Where(x => x.IsIncoming == (filter.Precise.IsIncoming.Value ? 1 : 0))
.Where(x => x.ID == filter.Precise.Id)
.ToList();
}
if (filter.OutgoingParcelID.HasValue)
{
res = res.Where(x => x.OutgoingParcelID == filter.OutgoingParcelID && x.IsIncoming == 1 && x.ParcelType == "product") ;
}
if (filter.IdList != null && filter.IdList.Length != 0)
{
res = res.Where(x => filter.IdList.Contains(x.ID));
}
return res.ToList();
}
// Использование
public List<vw_psl_parcelServices> GetParcelServices(ParcelServiceFilter filter)
{
var res = repository.db.vw_psl_parcelServices.AsNoTracking().AsQueryable()
.Where(x => x.OrderStatusCode != "canceled")
.Where(x => x.OrderStatusCode != "cancelled")
.Where(x => x.OrderStatusCode != "deleted");
if (filter.Precise.IsFilled)
{
return res.Where(x => x.IsIncoming == (filter.Precise.IsIncoming.Value ? 1 : 0))
.Where(x => x.ID == filter.Precise.Id)
.ToList();
}
if (filter.OutgoingParcelID.HasValue)
{
res = res.Where(x => x.OutgoingParcelID == filter.OutgoingParcelID && x.IsIncoming == 1 && x.ParcelType == "product") ;
}
if (filter.IdList != null && filter.IdList.Length != 0)
{
res = res.Where(x => filter.IdList.Contains(x.ID));
}
return res.ToList();
}
public List<vw_psl_parcelServices> GetIncomingParcelServices(params int[] id)
{
var extra = new ParcelServiceFilter();
extra.IdList = id;
return GetParcelServices(extra);
}
public List<vw_psl_parcelServices> GetIncomingParcelServices(int id, ParcelServiceFilter extra = null)
{
extra = extra ?? new ParcelServiceFilter();
extra.Precise.Id = id;
extra.Precise.IsIncoming = true;
return GetParcelServices(extra);
}
Данный код наглядно демонстрирует гибкость представлений – на основе одной SQL-структуры можно породить сколько угодно вариаций для каждого частного случая, и в то же время задать общие ограничения, которые справедливы для всех случаев или их подмножеств.
Абстрагирование
Помимо гибкости фильтрации, представления позволяют выполнить сортировку над любым из доступных полей. Ниже будет показан боевой пример. Реализация неуклюжая, однако была написана за три минуты с помощью блокнота и регулярных выражений по полям представлений:
switch (paging.Sort)
{
// powered by regexps =)
case "ID": orderedQuery = query.OrderBy(x => x.ID, paging.Direction); break;
case "Cost": orderedQuery = query.OrderBy(x => x.Cost, paging.Direction); break;
case "Size": orderedQuery = query.OrderBy(x => x.Size, paging.Direction); break;
case "Name": orderedQuery = query.OrderBy(x => x.Name, paging.Direction); break;
// и еще N кейсов
}
В качестве входных параметров – поле для сортировки и направление. Это берется напрямую из аякс-запроса который присылает as.crud. Изящное решение напрашивается само собой – с помощью отражения определить по какому полю требуется сортировка и выполнить эту сортировку. Однако, это уже тема для движка представлений.
В следующих статьях будут рассмотрены примененные здесь практики написания представлений и проработка решения для абстрактного движка выборки представлений
Периодически мы проводим обучение и даем возможность стажировки на базе нашей платформы с управлением на SQL. Если вам это интересно, то пожалуйста посмотрите информацию об обучении/стажировке по SQL.