Running sql queries from Powershell

January 21, 2015 by boro

Have you ever needed to test out the performance of sets of sql queries?

Recently we had some performance issues on a live infrastructure which needed diagnosing. Getting code released was slightly tricky so instead we concocted some scripts to allow us to run sql queries from Powershell. The advantage of these, we could run from different boxes in the farm to try and isolate where the issues arrived.

The key settings in this setup are:

# Specify SQL Connection String. 
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;" 
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;" 
# Specify SQL Connection String. 

$runs = 10

# Specify SQL Connection String.

$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"

$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"

# Specify SQL Connection String.

$runs = 10

The connection strings are much like your standard application connection strings.

To then run in bulk you can use the script shown below. Note, adjust the specific queries you want to execute:

function ExecNonQuery 
{ 
    param ($conStr, $cmdText) 
   
    # Determine if parameters were correctly populated. 
    if (!$conStr -or !$cmdText) 
    { 
        # One or more parameters didn't contain values. 
        write-Host "ExecNonQuery function called with no connection string and/or command text." 
    } 
    else 
    { 
        write-Host "Creating SQL Connection..." 
        # Instantiate new SqlConnection object. 
        $Connection = New-Object System.Data.SQLClient.SQLConnection 
         
        # Set the SqlConnection object's connection string to the passed value. 
        $Connection.ConnectionString = $conStr 
         
        # Perform database operations in try-catch-finally block since database operations often fail. 
        try 
        { 
            #write-Host "Opening SQL Connection..." 
            # Open the connection to the database. 
            $Connection.Open() 
             
            #write-Host "Creating SQL Command..." 
            # Instantiate a SqlCommand object. 
            $Command = New-Object System.Data.SQLClient.SQLCommand 
            # Set the SqlCommand's connection to the SqlConnection object above. 
            $Command.Connection = $Connection 
            # Set the SqlCommand's command text to the query value passed in. 
            $Command.CommandText = $cmdText 
             
            #write-Host "Executing SQL Command..."   
            # Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar
            write-host $Command.ExecuteScalar() 
        } 
        catch [System.Data.SqlClient.SqlException] 
        { 
            # A SqlException occurred. According to documentation, this happens when a command is executed against a locked row. 
            #write-Host "One or more of the rows being affected were locked. Please check your query and data then try again." 
        } 
        catch 
        { 
            # An generic error occurred somewhere in the try area. 
            write-Host "An error occurred while attempting to open the database connection and execute a command." 
        } 
        finally { 
            # Determine if the connection was opened. 
            if ($Connection.State -eq "Open") 
            { 
                write-Host "Closing Connection..." 
                # Close the currently open connection. 
                $Connection.Close() 
            } 
        } 
    } 
} 

 
# Specify SQL Connection String. 
$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;" 
$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;" 
# Specify SQL Connection String. 

$runs = 10
$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()

for ($i=0; $i -le $runs; $i++)
{
ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users" 

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles" 

ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'" 
 
ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [ParentID] = @itemId) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'" 

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]
                     FROM (
                        SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]
                        FROM [Items]

                        UNION ALL                          
                        SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]
                        FROM [Items] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [SharedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order], 0 AS [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL
                        FROM [UnversionedFields] 

                        UNION ALL 
                        SELECT [ItemId], 2 as [Order],      [Version],       [Language], NULL as [Name], [Value], [FieldId], NULL, NULL 
                        FROM [VersionedFields]
                     ) as temp  WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock)  WHERE [ParentID] = @itemId) 
                     ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'" 
 
write-Host "Query Complete!"
}

$stopWatch.Stop()

Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"
Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

function ExecNonQuery

{

param ($conStr, $cmdText)

# Determine if parameters were correctly populated.

if (!$conStr -or !$cmdText)

{

# One or more parameters didn't contain values.

write-Host "ExecNonQuery function called with no connection string and/or command text."

}

else

{

write-Host "Creating SQL Connection..."

# Instantiate new SqlConnection object.

$Connection = New-Object System.Data.SQLClient.SQLConnection

# Set the SqlConnection object's connection string to the passed value.

$Connection.ConnectionString = $conStr

# Perform database operations in try-catch-finally block since database operations often fail.

try

{

#write-Host "Opening SQL Connection..."

# Open the connection to the database.

$Connection.Open()

#write-Host "Creating SQL Command..."

# Instantiate a SqlCommand object.

$Command = New-Object System.Data.SQLClient.SQLCommand

# Set the SqlCommand's connection to the SqlConnection object above.

$Command.Connection = $Connection

# Set the SqlCommand's command text to the query value passed in.

$Command.CommandText = $cmdText

#write-Host "Executing SQL Command..."

# Note, to see a result back here, ExecuteNonQuery has been changed to ExecuteScalar

write-host $Command.ExecuteScalar()

}

catch [System.Data.SqlClient.SqlException]

{

# A SqlException occurred. According to documentation, this happens when a command is executed against a locked row.

#write-Host "One or more of the rows being affected were locked. Please check your query and data then try again."

}

catch

{

# An generic error occurred somewhere in the try area.

write-Host "An error occurred while attempting to open the database connection and execute a command."

}

finally {

# Determine if the connection was opened.

if ($Connection.State -eq "Open")

{

write-Host "Closing Connection..."

# Close the currently open connection.

$Connection.Close()

}

# Specify SQL Connection String.

$master = "Data Source=###;Initial Catalog=Master;Integrated Security=SSPI;"

$core = "Data Source=###;Initial Catalog=Core;Integrated Security=SSPI;"

# Specify SQL Connection String.

$runs = 10

$stopWatch = [System.Diagnostics.Stopwatch]::StartNew()

for ($i=0; $i -le $runs; $i++)

{

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_Users"

ExecNonQuery -conStr $core -cmdText "SELECT COUNT (*) FROM aspnet_UsersInRoles"

ExecNonQuery -conStr $core -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [TemplateID]=@p0 OR [TemplateID]=@p1 OR [TemplateID]=@p2 OR [TemplateID]=@p3 OR [TemplateID]=@p4 OR [TemplateID]=@p5 OR [TemplateID]=@p6 OR [ID]=@p7 OR [ParentID]=@p8 OR [TemplateID]=@p9 OR [TemplateID]=@p10 OR [ParentID]=@p11)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier,@p10 uniqueidentifier,@p11 uniqueidentifier',@p0='AB86861A-6030-46C5-B394-E8F99E8B87DB',@p1='E269FBB5-3750-427A-9149-7AA950B49301',@p2='455A3E98-A627-4B40-8035-E683A0331AC7',@p3='239F9CF4-E5A0-44E0-B342-0F32CD4C6D8B',@p4='A87A00B1-E6DB-45AB-8B54-636FEC3B5523',@p5='F68F13A6-3395-426A-B9A1-FA2DC60D94EB',@p6='B6F7EEB4-E8D7-476F-8936-5ACE6A76F20B',@p7='11111111-1111-1111-1111-111111111111',@p8='11111111-1111-1111-1111-111111111111',@p9='EB06CEC0-5E2D-4DC4-875B-01ADCC577D13',@p10='B2A1FF6C-6B1F-41EF-B2E1-AA598CF7335F',@p11='C74AC643-53C8-4F1E-9508-840CDC72AACA'"

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='734FB52F-33E3-4BCF-ADA9-27F1A4D8B4E1'"

ExecNonQuery -conStr $master -cmdText "exec sp_executesql N'SELECT [ItemId], [Order], [Version], [Language], [Name], [Value], [FieldId], [MasterID], [ParentID]

FROM (

SELECT [Id] as [ItemId], 0 as [Order], 0 as [Version], '''' as [Language], [Name], '''' as [Value], [TemplateID] as [FieldId], [MasterID], [ParentID]

FROM [Items]

UNION ALL

SELECT [ParentId] as [ItemId], 1 as [Order], 0 as [Version], '''' as [Language], NULL as [Name], '''', NULL, NULL, [Id]

FROM [Items]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], '''' as [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [SharedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], 0 AS [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [UnversionedFields]

UNION ALL

SELECT [ItemId], 2 as [Order], [Version], [Language], NULL as [Name], [Value], [FieldId], NULL, NULL

FROM [VersionedFields]

) as temp WHERE [ItemId] IN (SELECT [ID] FROM [Items] WITH (nolock) WHERE [ParentID] = @itemId)

ORDER BY [ItemId], [Order] ASC, [Language] DESC, [Version] DESC',N'@itemId uniqueidentifier',@itemId='11A81FBC-77FB-40D9-A3B3-0078106B07FC'"

write-Host "Query Complete!"

}

$stopWatch.Stop()

Write-Host "total " $stopWatch.ElapsedMilliseconds "ms"

Write-Host "average " ($stopWatch.ElapsedMilliseconds/$runs) "ms"

Sitecore application pool crashes

December 19, 2014 by boro

Quick gotcha that caught me out. In an MVC project, previewing one page would cause the application pool to crash. After digging around (basically removing every component from the page) it turns out one of the controller renderings had no controller action set.

Easy to fix once found but took a while to track down as the logs and system event logs didn’t have many clues.

Sitecore prefetch cache

December 18, 2014 by boro

One area of Sitecore that doesn’t seem to get much attention is the Sitecore prefetch cache. Recently we’ve been investigating how to get the content editor working faster, especially with large content dbs. Under the hood Sitecore has prefetch caches for each database it uses. If you are tuning your site be sure to pay them some attention. The config files it uses live in /app_config/prefetch.

A word of caution before you begin, updating using the following approach can increase the app startup time dramatically. I’d recommend only applying within live environments as you don’t want to add minutes to app startup during dev. Another thing to be wary of is the cache sizes, during testing it wasn’t uncommon to reach 2000MB of data in the pre-fetch cache. If you tune up the capacities make sure you pay attention to other caches eg master[items] to ensure balance throughout the caching layer.

What does the prefetch cache give you out the box?

The ability to tune:

the size of the prefetch cache per database
the number of child items per children call
which items to cache
which items to load the children of
which items of template type to load

Depending on the setup of your Sitecore tree a combination of the above will need tuning.

Some tips

Speed up the CMS (master.config) media library by caching all the media folders:

<template desc="media folder">{FE5DD826-48C6-436D-B87A-7C4210C7413B}</template>

1

<template desc="media folder">{FE5DD826-48C6-436D-B87A-7C4210C7413B}</template>
Get a clear view of what is in all your caches with the more detailed admin page:
https://marketplace.sitecore.net/en/Modules/Sitecore_Cache_Admin.aspx

What else can you do?
In most of our builds we setup a file which contains all the template ids we use in the app. An example would be:

using System;

namespace ###.Cms
{
	public static class TemplateKeys
	{
		public static readonly Guid Homepage = new Guid("{4AC5A50D-FC94-4421-909F-A1F7567CB4B8}");       

		public static class Settings
		{
			public static readonly Guid BreadcrumbNav = new Guid("{20022ACD-1D2F-4C19-A8E8-322713BB734D}");

using System;

namespace ###.Cms

{

public static class TemplateKeys

{

public static readonly Guid Homepage = new Guid("{4AC5A50D-FC94-4421-909F-A1F7567CB4B8}");

public static class Settings

{

public static readonly Guid BreadcrumbNav = new Guid("{20022ACD-1D2F-4C19-A8E8-322713BB734D}");

This allows quick access to all the templates when asserting on specific content types. The following code maps this kind of file to the notation required by the prefetch cache configuration. When you run the page it will generate all the <template name=’… entries for the master.config.

Front end:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PrefetchCacheGenerator.aspx.cs" Inherits="###.Presentation.Website.Testing.Prefetching.PrefetchCacheGenerator" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox runat="server" ID="DebugTextBox" TextMode="MultiLine" Width="1000" Height="1000"></asp:TextBox>
    </div>
    </form>
</body>
</html>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PrefetchCacheGenerator.aspx.cs" Inherits="###.Presentation.Website.Testing.Prefetching.PrefetchCacheGenerator" %>

<!DOCTYPE html>

</head>

<body>

<div>

<asp:TextBox runat="server" ID="DebugTextBox" TextMode="MultiLine" Width="1000" Height="1000"></asp:TextBox>

</div>

</form>

</body>

</html>

Back end:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using ###.Cms;

namespace ###.Presentation.Website.Testing.Prefetching
{
    public partial class PrefetchCacheGenerator : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DebugTextBox.Text = GenerateXml();
        }

        private static string GenerateXml()
        {
            //TemplateKeys is our file containing all the templateId's
            Type templateKeysType = typeof (TemplateKeys);

            IEnumerable<Type> templateTypes = GetSubclasses(templateKeysType);

            StringBuilder builder = new StringBuilder();

            foreach (Type templateType in templateTypes)
            {
                foreach (PrefetchCacheEntry cacheEntry in LoadEntries(templateType, templateType.FullName + "_"))
                {
                    builder.AppendFormat("<template name=\"{0}\">{1:B}</template>", cacheEntry.Key, cacheEntry.Value);
                    builder.AppendLine();
                }
            }

            return builder.ToString();
        }

        private static IEnumerable<Type> GetSubclasses(Type templateKeysType)
        {
            foreach (Type type in templateKeysType.Assembly.GetTypes())
            {
                if (type.FullName.Contains(templateKeysType.Name))
                {
                    yield return type;
                }
            }
        }

        private static IEnumerable<PrefetchCacheEntry> LoadEntries(Type type, string prefix)
        {
            foreach (FieldInfo fieldInfo in type.GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static))
            {
                if (fieldInfo.FieldType == typeof (Guid))
                {
                    yield return
                        new PrefetchCacheEntry {Key = prefix + fieldInfo.Name, Value = Guid.Parse(fieldInfo.GetValue(null).ToString())};
                }
            }
        }
    }

    public class PrefetchCacheEntry
    {
        public string Key { get; set; }
        public Guid Value { get; set; }
    }
}

using System;

using System.Collections.Generic;

using System.Linq;

using System.Reflection;

using System.Text;

using ###.Cms;

namespace ###.Presentation.Website.Testing.Prefetching

{

public partial class PrefetchCacheGenerator : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

DebugTextBox.Text = GenerateXml();

}

private static string GenerateXml()

{

//TemplateKeys is our file containing all the templateId's

Type templateKeysType = typeof (TemplateKeys);

IEnumerable<Type> templateTypes = GetSubclasses(templateKeysType);

StringBuilder builder = new StringBuilder();

foreach (Type templateType in templateTypes)

{

foreach (PrefetchCacheEntry cacheEntry in LoadEntries(templateType, templateType.FullName + "_"))

{

builder.AppendFormat("<template name=\"{0}\">{1:B}</template>", cacheEntry.Key, cacheEntry.Value);

builder.AppendLine();

}

return builder.ToString();

}

private static IEnumerable<Type> GetSubclasses(Type templateKeysType)

{

foreach (Type type in templateKeysType.Assembly.GetTypes())

{

if (type.FullName.Contains(templateKeysType.Name))

{

yield return type;

}

private static IEnumerable<PrefetchCacheEntry> LoadEntries(Type type, string prefix)

{

foreach (FieldInfo fieldInfo in type.GetFields(BindingFlags.Instance | BindingFlags.Public | BindingFlags.Static))

{

if (fieldInfo.FieldType == typeof (Guid))

{

yield return

new PrefetchCacheEntry {Key = prefix + fieldInfo.Name, Value = Guid.Parse(fieldInfo.GetValue(null).ToString())};

}

public class PrefetchCacheEntry

{

public string Key { get; set; }

public Guid Value { get; set; }

}

How to build on this?
How about generating the config file during app startup?

Other info.
There is a detailed video from Sitecore at http://mediacontent.sitecore.net/wmv/CacheRecordingWebinar.mp4 about the caches. Alternatively read the Sitecore docs

How long does it take to open a Sitecore node?

December 12, 2014 by boro

In Sitecore builds a lot of the focus during development and performance tuning is understandably around the customer facing experience. Recently we’ve needed to investigate performance issues within the CMS itself. If you’ve ever dug into this you’ll know its a tricky application to analyse as a lot of the interactions are client-side.

One metric we wanted to gather was ‘how long does it take to open up a sitecore node’? With the help of support they suggested digging into the js file which glues all this together: ‘\sitecore\shell\applications\content manager\content editor.js’.

If you add in the following log entries the chrome console should reveal all:

scContentEditor.prototype.onTreeClick = function(sender, evt) {
    console.time("GetTreeviewChildren");
    ...

scContentEditor.prototype.onTreeClick = function(sender, evt) {

console.time("GetTreeviewChildren");

...

and then to finish up:

scContentEditor.prototype.expandTreeNode = function(sender, result) {
  ...
  console.timeEnd("GetTreeviewChildren");
}

scContentEditor.prototype.expandTreeNode = function(sender, result) {

...

console.timeEnd("GetTreeviewChildren");

}

and

scContentEditor.prototype.collapseTreeNode = function(sender) {
  ...
  console.timeEnd("GetTreeviewChildren");
}

scContentEditor.prototype.collapseTreeNode = function(sender) {

...

console.timeEnd("GetTreeviewChildren");

}

Depending on your findings you have a few options for improving performance. Be sure to check you’ve set the pre-fetch cache sizes (/app_config/prefetch/master.config) and the master db cache sizes.

Customizing the Sitecore Rich Text Editor

December 2, 2014 by boro

You have several options when customizing the Sitecore Rich Text Editor. It’s simple to setup custom sets of buttons and assign to different templates. If you are unsure on doing this I’d suggest googling ‘sitecore custom rich text editor’. This post details how to use the ‘Html Editor Configuration Type’ items.

Within the application there are various global settings which configure things like default tags to use for line breaks, which profile to use as default and a few more:

<!--  HTML EDITOR DEFAULT CONFIGURATION TYPE
            Specifies the type responsible for setting up the rich text editor. Can be overriden at profile level. Must inherit from Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client.
            Default value: Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client
      -->
      <setting name="HtmlEditor.DefaultConfigurationType" value="Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client" />
      <!--  HTML EDITOR DEFAULT PROFILE
            Path to the default html editor profile.
            Default value: /sitecore/system/Settings/Html Editor Profiles/Rich Text Default
      -->
      <setting name="HtmlEditor.DefaultProfile" value="/sitecore/system/Settings/Html Editor Profiles/Rich Text Default" />
      <!--  HTML EDITOR LINE BREAK
            Specifies the tag that the HTML editor inserts on Enter. Values can be
            "br", "div" and "p".
      -->
      <setting name="HtmlEditor.LineBreak" value="p" />
      <!--  HTML EDITOR REMOVE SCRIPTS
            If true, the rich text editor removes script tags from RTE field values before saving. Setting the value to true reduces the potential for cross-site scripting and other script-related issues.
            Default value: true
      -->
      <setting name="HtmlEditor.RemoveScripts" value="true" />
      <!--  HTML EDITOR SUPPORT WEB CONTROLS
            Indicates if the Html Editor supports web controls. If true, Web Controls are shown as yellow boxes.
            Default value: true
      -->
      <setting name="HtmlEditor.SupportWebControls" value="true" />
      <!--  HTML EDITOR VALIDATOR SERVICE URL
            Specifies the url that validates XHtml.
      -->
      <setting name="HtmlEditor.ValidatorServiceUrl" value="http://validator.w3.org/check" />

<!-- HTML EDITOR DEFAULT CONFIGURATION TYPE

Specifies the type responsible for setting up the rich text editor. Can be overriden at profile level. Must inherit from Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client.

Default value: Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client

-->

<!-- HTML EDITOR DEFAULT PROFILE

Path to the default html editor profile.

Default value: /sitecore/system/Settings/Html Editor Profiles/Rich Text Default

-->

<!-- HTML EDITOR LINE BREAK

Specifies the tag that the HTML editor inserts on Enter. Values can be

"br", "div" and "p".

-->

<!-- HTML EDITOR REMOVE SCRIPTS

If true, the rich text editor removes script tags from RTE field values before saving. Setting the value to true reduces the potential for cross-site scripting and other script-related issues.

Default value: true

-->

<!-- HTML EDITOR SUPPORT WEB CONTROLS

Indicates if the Html Editor supports web controls. If true, Web Controls are shown as yellow boxes.

Default value: true

-->

<!-- HTML EDITOR VALIDATOR SERVICE URL

Specifies the url that validates XHtml.

-->

The problem we ran into was that for certain rich-text-editors we wanted the line breaks to be br tags, elsewhere p tags. This ruled out the setting above.

One hidden feature which is very useful is the ability to setup different configurations for each rte. If you look in the folder in core ‘/sitecore/system/Settings/Html Editor Profiles/Rich Text Default’ you will see an item of type ‘/sitecore/templates/System/Html Editor Profiles/Html Editor Configuration Type’. Here you can define a type, the default mapped to ‘Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration,Sitecore.Client’

If you need to setup specific RTE’s to use br tags instead of p tags:

Setup your custom RTE profile in core
Create an item of type ‘/sitecore/templates/System/Html Editor Profiles/Html Editor Configuration Type’ called ‘Configuration Type’
Create a new class which inherits from ‘Sitecore.Shell.Controls.RichTextEditor.EditorConfiguration’
Update the ‘Configuration Type’ item to reference your new class
Override the methods you need – I found simply overriding SetupEditor was enough (see below).

An example of the code:

using Sitecore.Data.Items;
using Sitecore.Shell.Controls.RichTextEditor;
using Telerik.Web.UI;

namespace ###.SitecoreCustom
{
    public class CustomRichTextEditor : EditorConfiguration
    {
        public CustomRichTextEditor(Item profile) : base(profile)
        {
        }

        protected override void SetupEditor()
        {
            base.SetupEditor();

            Editor.NewLineMode = EditorNewLineModes.Br;
        }
    }
}

using Sitecore.Data.Items;

using Sitecore.Shell.Controls.RichTextEditor;

using Telerik.Web.UI;

namespace ###.SitecoreCustom

{

public class CustomRichTextEditor : EditorConfiguration

{

public CustomRichTextEditor(Item profile) : base(profile)

{

}

protected override void SetupEditor()

{

base.SetupEditor();

Editor.NewLineMode = EditorNewLineModes.Br;

}

Sitecore parallel publishing

November 28, 2014 by boro

In version 7.2 Sitecore introduced the ability to really crank up the publishing performance of the application. There is a lot more information about how to enable Sitecore parallel publishing at http://www.sitecore.net/Learn/Blogs/Technical-Blogs/Reinnovations/Posts/2014/03/Enabling-Parallel-Publishing.aspx

During testing it was found the time it took to publish was noticeably shortened. See here for some stats.

One uncertainty was exactly what does parallel publishing do behind the scenes? What does ‘parallel’ actually mean?

The setting MaxDegreeOfParallelism configures the number of threads to use
Languages are published concurrently
You don’t get any more publish queues by increasing the number of threads. This means you can only run 1 publish at a time still.
Publishing targets are published to in sequence – e.g. if you have 2 targets: web1 & web2 and publish to both, items will appear in web1 first, once complete items will then be pushed to web2
Be careful cranking things up too much – you need to ensure the servers in use can cope with the load. The dbs will be under a lot more strain during the publishes
For any diagnostics check in the new log file generated specifically for publishing

How to prove the targets run in series?

I ran a simple experiment to prove this:

Create 2 publish targets: web & web2
Create folder structure:
1. Parallel item (folder)
2. + item for web (only allowed to publish to web)
3. + item for web2 (only allowed to publish to web2)
Added custom publish item processor with Thread.Sleep(###)
Run publish with MaxDegreeOfParallelism set as 4
Check web db and check web2 db via dbbrowser

The data arrived:

Web 2a
Web 2b
Web2 2a
Web2 2c

The publish logs and the UI also indicate its one target after another.

1048 08:44:27 INFO Settings.Publishing.MaxDegreeOfParallelism:4

…

8588 08:44:45 INFO [Publishing]: Starting to process 2 publishing options

8588 08:44:45 INFO [PublishOptions]: root:{8960D17C-38F1-4DB0-8EBD-FD6B128D9E00}, language:en, targets:Web, database:web, mode:SingleItem, smart:False children:True, related:False

8588 08:44:45 INFO [PublishOptions]: root:{8960D17C-38F1-4DB0-8EBD-FD6B128D9E00}, language:en, targets:Web2, database:web2, mode:SingleItem, smart:False children:True, related:False

…

7040 08:44:46 INFO Starting [ParallelPublishing] – ProcessQueue

7040 08:44:46 INFO Processing queue

7040 08:45:46 INFO Processing re-try list (count=0)

7040 08:45:46 INFO Finished [ParallelPublishing] – ProcessQueue in 60354 ms

7040 08:45:46 INFO Publish Mode : SingleItem

7040 08:45:46 INFO Created : 2

7040 08:45:46 INFO Updated : 1

7040 08:45:46 INFO Deleted : 0

7040 08:45:46 INFO Skipped : 1

…

10136 08:45:46 INFO Starting [ParallelPublishing] – ProcessQueue

10136 08:45:46 INFO Processing queue

10136 08:46:46 INFO Processing re-try list (count=0)

10136 08:46:46 INFO Finished [ParallelPublishing] – ProcessQueue in 60101 ms

10136 08:46:46 INFO Publish Mode : SingleItem

10136 08:46:46 INFO Created : 2

10136 08:46:46 INFO Updated : 1

10136 08:46:46 INFO Deleted : 0

10136 08:46:46 INFO Skipped : 1

Two submit buttons in Sitecore WFFM

November 19, 2014 by boro

We recently ran into an interesting challenge where the user wanted two submit buttons on their wffm form, the value of which then needed logging in the WFFM database. Note this approach does rely on javascript.

WFFM gives you the ability to setup custom fields (see Sitecore docs). I based this implementation on section 3.7.3

In your solution setup a custom ascx:

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="CustomFormItem.ascx.cs" Inherits="###.Website.Forms.CustomFormItem" %>
<asp:HiddenField runat="server" ID="ButtonHiddenField" />
<script type="text/javascript">
    $scw(document).ready(function () {
        var hiddenField = $scw('#<%= ButtonHiddenField.ClientID%>')
        var submit = hiddenField.parentsUntil('#MainPanel').find(':submit');
        var clone = submit.clone(true);
        clone.attr('value', '<%=Title%>');
        clone.click(function() {
            hiddenField.val('<%=Title%>');
        });
        submit.click(function() {
            hiddenField.val('<%=DefaultButtonText%>');
        });
        clone.appendTo(submit.parent());
    });
</script>

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="CustomFormItem.ascx.cs" Inherits="###.Website.Forms.CustomFormItem" %>

<asp:HiddenField runat="server" ID="ButtonHiddenField" />

$scw(document).ready(function () {

var hiddenField = $scw('#<%= ButtonHiddenField.ClientID%>')

var submit = hiddenField.parentsUntil('#MainPanel').find(':submit');

var clone = submit.clone(true);

clone.attr('value', '<%=Title%>');

clone.click(function() {

hiddenField.val('<%=Title%>');

});

submit.click(function() {

hiddenField.val('<%=DefaultButtonText%>');

});

clone.appendTo(submit.parent());

});

</script>

and then the code behind:

using System;
using System.Web.UI;
using Sitecore.Data.Items;
using Sitecore.Form.Core.Controls.Data;
using Sitecore.Form.Web.UI.Controls;

namespace ###.Website.Forms
{
    public partial class CustomFormItem : ValidateUserControl, IHasTitle
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        public string DefaultButtonText
        {
            get 
            { 
                Item item = Sitecore.Context.Database.GetItem(Form.FormID);

                if (item != null)
                {
                    return item.Fields["Name"].Value;
                }

                return "default button";
            }
        }

        public override ControlResult Result
        {
            get { return new ControlResult("Custom Button", ButtonHiddenField.Value, "parameters"); }
        }

        protected override Control ValidatorContainer
        {
            get { return new Control(); }
        }

        protected override Control InnerValidatorContainer
        {
            get { return new Control(); }
        }

        public string Title { get; set; }
    }
}

using System;

using System.Web.UI;

using Sitecore.Data.Items;

using Sitecore.Form.Core.Controls.Data;

using Sitecore.Form.Web.UI.Controls;

namespace ###.Website.Forms

{

public partial class CustomFormItem : ValidateUserControl, IHasTitle

{

protected void Page_Load(object sender, EventArgs e)

{

}

public string DefaultButtonText

{

get

{

Item item = Sitecore.Context.Database.GetItem(Form.FormID);

if (item != null)

{

return item.Fields["Name"].Value;

}

return "default button";

}

public override ControlResult Result

{

get { return new ControlResult("Custom Button", ButtonHiddenField.Value, "parameters"); }

}

protected override Control ValidatorContainer

{

get { return new Control(); }

}

protected override Control InnerValidatorContainer

{

get { return new Control(); }

}

public string Title { get; set; }

}

You then need to create the custom field within Sitecore. This wants to live in ‘/sitecore/system/Modules/Web Forms for Marketers/Settings/Field Types/Custom’. The only field you need to set is the UserControl field. In my example this was /forms/customformitem.ascx.

When you setup your form you can then select:

In the front end this looks like:

And finally, when you submit you get:

Sorry mr a@b.com!, you might receive a few test signups 🙂

Summarising the Sitecore log

November 4, 2014 by boro

The Sitecore log analyser is a great tool for viewing patterns in log files over time.

If getting access to log files is slow or you simply want a quick summary of certain log files then hopefully the following might help. You can select the date range, filter terms and then view all log entries which fulfil that pattern.

I’d consider it to live in the same realm as the ‘/sitecore/admin’ pages as it can reveal critical site information (hence the admin check).

The front end isn’t the most glamourous but serves its purpose:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="LogViewer.aspx.cs" Inherits="###.Custom.LogViewer" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div id="LogViewerPanel" runat="server">
		Filter (| separate terms): <asp:TextBox runat="server" ID="FilterTextBox" Width="250"></asp:TextBox>
		Start Date (yyyy/MM/dd): <asp:TextBox runat="server" ID="StartDateTextBox"></asp:TextBox>
		End Date (yyyy/MM/dd): <asp:TextBox runat="server" ID="EndDateTextBox"></asp:TextBox>
		<asp:Button runat="server" Text="Go" OnClick="SearchClick"/>
	    <hr />
		<asp:TextBox runat="server" ID="ResultTextBox" TextMode="MultiLine" Width="1200" Height="600"></asp:TextBox>
    </div>
    </form>
</body>
</html>

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="LogViewer.aspx.cs" Inherits="###.Custom.LogViewer" %>

<!DOCTYPE html>

</head>

<body>

Filter (| separate terms): <asp:TextBox runat="server" ID="FilterTextBox" Width="250"></asp:TextBox>

Start Date (yyyy/MM/dd): <asp:TextBox runat="server" ID="StartDateTextBox"></asp:TextBox>

End Date (yyyy/MM/dd): <asp:TextBox runat="server" ID="EndDateTextBox"></asp:TextBox>

<asp:Button runat="server" Text="Go" OnClick="SearchClick"/>

<hr />

<asp:TextBox runat="server" ID="ResultTextBox" TextMode="MultiLine" Width="1200" Height="600"></asp:TextBox>

</div>

</form>

</body>

</html>

and then the code behind:

using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using Sitecore.Configuration;
using Sitecore.IO;

namespace ###.Custom
{
	public partial class LogViewer : System.Web.UI.Page
	{
		const string DateFormat = "yyyy/MM/dd";

		protected void Page_Load(object sender, EventArgs e)
		{
			LogViewerPanel.Visible = Sitecore.Context.User.IsAdministrator;
			
			ResultTextBox.Text = "";

			if (!IsPostBack)
			{
				StartDateTextBox.Text = EndDateTextBox.Text = DateTime.Now.ToString(DateFormat);
			}
		}

		protected void SearchClick(object sender, EventArgs e)
		{
			DateTime startDate = DateTime.ParseExact(StartDateTextBox.Text, DateFormat, CultureInfo.InvariantCulture);

			DateTime endDate = DateTime.ParseExact(EndDateTextBox.Text, DateFormat, CultureInfo.InvariantCulture);

			if (endDate < startDate)
			{
				Dump("End date must be after start date");
				return;
			}

			if ((endDate - startDate).Days > 7)
			{
				Dump("You can only pick a range of 7 days");
				return;
			}

			FindLogFiles(startDate, endDate, FilterTextBox.Text);
		}

		private void FindLogFiles(DateTime startDate, DateTime endDate, string filter)
		{
			Dump("Reading logs for: ", startDate.ToShortDateString(), " to: ", endDate.ToShortDateString(), " with filter: ", filter);

			List<DateTime> dates = new List<DateTime>();

			dates.Add(startDate);

			while (startDate < endDate)
			{
				startDate = startDate.AddDays(1);

				dates.Add(startDate);
			}

			string logFolder = FileUtil.MapPath(Settings.LogFolder);

			DirectoryInfo di = new DirectoryInfo(logFolder);

			string logFilePrefix = "log";

			List<FileInfo> validLogFiles = new List<FileInfo>();

			foreach (FileInfo fi in di.GetFiles(logFilePrefix+"*"))
			{
				if (fi.Name.ToLower().StartsWith(logFilePrefix))
				{
					foreach (DateTime dateTime in dates)
					{
						if (fi.Name.StartsWith(logFilePrefix + "." + dateTime.ToString("yyyyMMdd")))
						{
							validLogFiles.Add(fi);
						}
					}
				}
			}

			ShowLogEntries(validLogFiles.Distinct().OrderBy(a=>a.Name), filter);
		}

		private void ShowLogEntries(IEnumerable<FileInfo> files, string filter)
		{
			foreach (FileInfo fileInfo in files)
			{
				Dump("");
				Dump("Reading: ", fileInfo.Name);

				string content = FileUtil.ReadFromFile(fileInfo.FullName);

				string[] filters = filter.Split(new [] {'|'}, StringSplitOptions.RemoveEmptyEntries);

				int lineNumber = 0;

				foreach (string line in content.Split(new [] {'\r', '\n'}))
				{
					foreach (string filterPart in filters)
					{
						if (line.Contains(filterPart))
						{
							Dump(lineNumber, "--", line);
						}
					}

					lineNumber++;
				}

				Dump("------------------------------------------------------");
			}
		}

		private void Dump(params object[] values)
		{
			ResultTextBox.Text += String.Concat(values) + "\r\n";
		}
	}
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

using System;

using System.Collections.Generic;

using System.Globalization;

using System.IO;

using System.Linq;

using Sitecore.Configuration;

using Sitecore.IO;

namespace ###.Custom

{

public partial class LogViewer : System.Web.UI.Page

{

const string DateFormat = "yyyy/MM/dd";

protected void Page_Load(object sender, EventArgs e)

{

LogViewerPanel.Visible = Sitecore.Context.User.IsAdministrator;

ResultTextBox.Text = "";

if (!IsPostBack)

{

StartDateTextBox.Text = EndDateTextBox.Text = DateTime.Now.ToString(DateFormat);

}

protected void SearchClick(object sender, EventArgs e)

{

DateTime startDate = DateTime.ParseExact(StartDateTextBox.Text, DateFormat, CultureInfo.InvariantCulture);

DateTime endDate = DateTime.ParseExact(EndDateTextBox.Text, DateFormat, CultureInfo.InvariantCulture);

if (endDate < startDate)

{

Dump("End date must be after start date");

return;

}

if ((endDate - startDate).Days > 7)

{

Dump("You can only pick a range of 7 days");

return;

}

FindLogFiles(startDate, endDate, FilterTextBox.Text);

}

private void FindLogFiles(DateTime startDate, DateTime endDate, string filter)

{

Dump("Reading logs for: ", startDate.ToShortDateString(), " to: ", endDate.ToShortDateString(), " with filter: ", filter);

List<DateTime> dates = new List<DateTime>();

dates.Add(startDate);

while (startDate < endDate)

{

startDate = startDate.AddDays(1);

dates.Add(startDate);

}

string logFolder = FileUtil.MapPath(Settings.LogFolder);

DirectoryInfo di = new DirectoryInfo(logFolder);

string logFilePrefix = "log";

List<FileInfo> validLogFiles = new List<FileInfo>();

foreach (FileInfo fi in di.GetFiles(logFilePrefix+"*"))

{

if (fi.Name.ToLower().StartsWith(logFilePrefix))

{

foreach (DateTime dateTime in dates)

{

if (fi.Name.StartsWith(logFilePrefix + "." + dateTime.ToString("yyyyMMdd")))

{

validLogFiles.Add(fi);

}

ShowLogEntries(validLogFiles.Distinct().OrderBy(a=>a.Name), filter);

}

private void ShowLogEntries(IEnumerable<FileInfo> files, string filter)

{

foreach (FileInfo fileInfo in files)

{

Dump("");

Dump("Reading: ", fileInfo.Name);

string content = FileUtil.ReadFromFile(fileInfo.FullName);

string[] filters = filter.Split(new [] {'|'}, StringSplitOptions.RemoveEmptyEntries);

int lineNumber = 0;

foreach (string line in content.Split(new [] {'\r', '\n'}))

{

foreach (string filterPart in filters)

{

if (line.Contains(filterPart))

{

Dump(lineNumber, "--", line);

}

lineNumber++;

}

Dump("------------------------------------------------------");

}

private void Dump(params object[] values)

{

ResultTextBox.Text += String.Concat(values) + "\r\n";

}

GetItem on unpublishable Sitecore items

October 22, 2014 by boro

Consider the following code:

Item rootItem = master.GetItem(new ID(id), Language.Parse(language));

1	Item rootItem = master.GetItem(new ID(id), Language.Parse(language));

All pretty straight-forwards. It knowingly uses the master db as its only needed within the cms realm.

How about if the item in question in unpublishable? What then happens? You get null!!?? So, how to actually get the item?

bool disableFiltering = Sitecore.Context.Site.DisableFiltering;
Sitecore.Context.Site.DisableFiltering = true;
Item rootItem = master.GetItem(new ID(id), Language.Parse(language));
Sitecore.Context.Site.DisableFiltering = disableFiltering;

bool disableFiltering = Sitecore.Context.Site.DisableFiltering;

Sitecore.Context.Site.DisableFiltering = true;

Item rootItem = master.GetItem(new ID(id), Language.Parse(language));

Sitecore.Context.Site.DisableFiltering = disableFiltering;

If you wanted to expand the idea and make it a bit more reusable you could build a disposable switcher to apply the same changes.

Hopefully this helps as it had me stumped for a while!

Sitecore event handlers

October 17, 2014 by boro

One very useful area of Sitecore is the ability to subscribe to system events. Examples are things like when items are saved (item:saved), items are published etc.

All the out the box events can be seen within the <events> section of the config.

You can subscribe to events via config:

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
	<sitecore>
		<events>
			<event name="cta:saved">
				<handler type="###.Shell.Events.CtaItemSaveHandler, ###" method="SaveCtaItem" />
			</event>
		</events>
	</sitecore>
</configuration>

</event>

</events>

</sitecore>

</configuration>

and raise your own custom events via:

Event.RaiseEvent("cta:saved", new object[]
					{
						context.Items[0]
					});

Event.RaiseEvent("cta:saved", new object[]

{

context.Items[0]

});

One thing to note, events such as item:saved will be run when sometimes you don’t expect it. Examples are package installations.

You can guard against custom event handlers running during package installations if you check for the existence of the current httpContext:

class MyEventHandler
{
    void ProcessEvent(...)
    {
        if (HttpContext.Current == null)
        {
            return;
        }

        //code to process event...
    }
}

class MyEventHandler

{

void ProcessEvent(...)

{

if (HttpContext.Current == null)

{

return;

}

//code to process event...

}

For more info on how to make use of Sitecore events, have a look at http://sdn.sitecore.net/Articles/API/Using%20Events.aspx

blog.boro2g .co.uk

Some ideas about coding, dev and all things online.

Running sql queries from Powershell

Sitecore application pool crashes

Sitecore prefetch cache

How long does it take to open a Sitecore node?

Customizing the Sitecore Rich Text Editor

Sitecore parallel publishing

Two submit buttons in Sitecore WFFM

Summarising the Sitecore log

GetItem on unpublishable Sitecore items

Sitecore event handlers