Blog: Bypass the content API for better performance - 6.aspx

<%@ Page Language="C#" AutoEventWireup="true" Debug="true" %>
<%@ Import Namespace="Sitecore.Data.Items" %>
<%@ Import Namespace="Sitecore.Diagnostics" %>
<%@ Import Namespace="Sitecore.Data.SqlServer" %>
<script runat="server">

    private SqlServerDataApi dataApi = new SqlServerDataApi(
            Sitecore.Configuration.Settings.GetConnectionString(Sitecore.Context.Database.ConnectionStringName)
        );

    /// <summary>
    /// A simple example that determines the number of descendants that an item has.
    /// Note that this method disregards any security settings!
    /// </summary>
    /// <param name="item">The item for which to count descendants.</param>
    /// <returns>The number of descendants.</returns>
    public int GetDescendantsCount(Item item)
    {
        Assert.IsNotNull(item, "A valid Sitecore item must be provided to this method");
        string query = @"with cteChildren as
                               (select ID
                                from [dbo].[Items]
                                where ID = CAST(@ItemId AS UNIQUEIDENTIFIER)
                              union all
                                select e.ID
                                from cteChildren cte
                                inner join [dbo].[Items] e
                                on cte.ID = e.ParentID)
                            select count(ID) - 1 from cteChildren";
        Sitecore.Data.DataProviders.Sql.DataProviderReader reader
            = dataApi.CreateReader(query, "ItemId", item.ID.ToString().Trim(new[] { '{', '}' }));
        return reader.Read() ? reader.InnerReader.GetInt32(0) : 0;
    }

/// <summary>
/// A complex example that returns the actual descendants that inherit from a specific template id.
/// </summary>
/// <param name="item">The item for which to determine the descendants.</param>
/// <param name="templateId">The ID of the template of which the result items have to inherit.</param>
/// <returns>The actual descendants that inherit from the template that was passed.</returns>
public IEnumerable<Item> GetDescendantsInheritingTemplate(Item item, Sitecore.Data.TemplateID templateId)
{
    Assert.IsNotNull(item, "A valid Sitecore item must be provided to this method");

    // query SQL server directly
    string query = @"with cteChildren as
                    (select ID, TemplateID
                    from [dbo].[Items]
                    where ID = CAST(@itemId AS UNIQUEIDENTIFIER)
                    union all
                    select e.ID, e.TemplateID
                    from cteChildren cte
                    inner join [dbo].[Items] e
                    on cte.ID = e.ParentID),
                cteSubItems as
                (
                select ItemId from SharedFields
                where FieldId = '12C33F3F-86C5-43A5-AEB4-5598CEC45116'
                    and Value like '%' + @templateId + '%'
                    union all
                select sf.ItemId from SharedFields sf inner join cteSubItems ic
                on sf.Value like '%' + CAST(ic.ItemId as varchar(36)) + '%'
                where sf.FieldId = '12C33F3F-86C5-43A5-AEB4-5598CEC45116'
                )
                select cteChild.ID
                from (select ItemId from cteSubItems union select @templateId) cteSit
                    inner join cteChildren cteChild on cteSit.ItemId = cteChild.TemplateID";

    Sitecore.Data.DataProviders.Sql.DataProviderReader reader = dataApi.CreateReader(query,
        "itemId", item.ID.ToString().Trim(new[] { '{', '}' }),
        "templateId", templateId.ID.ToString().Trim(new[] { '{', '}' }));

    // return to the content API to get the actual items (therefore also applies security)
    List<Item> result = new List<Item>();
    while (reader.Read())
    {
        Sitecore.Data.ID itemId = new Sitecore.Data.ID(reader.InnerReader.GetGuid(0));
        if (itemId != item.ID)
        {
            Item descItem = Sitecore.Context.Database.GetItem(itemId);
            if (descItem != null)
            {
                result.Add(descItem);
            }
        }
    }
    return result;
}

    public string BenchMark(Func<int> func, int times)
    {
        DateTime start = DateTime.Now;
        int lastResult = -1;
        for (int i = 0; i < times; i++)
        {
            lastResult = func();
        }
        return string.Format("{0}<br />(performed {1} times, and took an average of {2} milliseconds)",
            lastResult,
            times,
            Math.Round(((DateTime.Now - start).TotalMilliseconds / times)));
    }

    public void Page_Load(object sender, EventArgs e)
    {
        DataBind();
    }

    private Item MyItem
    {
        get
        {
            return Sitecore.Context.Database.GetRootItem();
        }
    }

    private Sitecore.Data.ID FileTemplateId
    {
        get
        {
            return new Sitecore.Data.ID("{962B53C4-F93B-4DF9-9821-415C867B8903}");
        }
    }

    private bool InheritsFromTemplate(TemplateItem templateItem, Sitecore.Data.ID templateId)
    {
        return templateItem.ID == templateId
            || (templateItem.BaseTemplates != null
                && templateItem.BaseTemplates
                    .Where(baseTempl => InheritsFromTemplate(baseTempl, templateId)).Count() > 0);
    }

</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Test direct SQL server database access</title>
</head>
<body>
    <ol>
        <li>Count all descendants (using sitecore content API) =
            <%# BenchMark(() => MyItem.Axes.GetDescendants().Count(), 50) %></li>
        <li>Count all descendants (directly with the database, disregards security) =
            <%# BenchMark(() => GetDescendantsCount(MyItem), 50)%></li>

        <li>Get all descendants that inherit from 'File' (using sitecore content API) =
            <%# BenchMark(() => MyItem.Axes.GetDescendants()
                    .Where(desc => InheritsFromTemplate(desc.Template, FileTemplateId)).Count(), 50) %></li>
        <li>Get all descendants that inherit from 'File' (directly with the database) =
            <%# BenchMark(() => GetDescendantsInheritingTemplate(MyItem,
                    new Sitecore.Data.TemplateID(FileTemplateId)).Count(), 50)%></li>
    </ol>
</body>
</html>