List All Stored Procedures Within a SQL Database and Then Grant Execute on Them

It's been too long since I last blogged.

Thought I'd dust things off with this little snippet of T-SQL to list all stored procedures that exist within a specified SQL database:

USE YourDatabaseNameHere
GO

SELECT NAME
FROM SYSOBJECTS 
WHERE TYPE = 'P' 
AND LEFT(NAME,2) <> 'sp'
AND LEFT(NAME,2) <> 'dt'

This can be useful if you wish to grant execute permissions on these objects to your database login. For example, the following T-SQL will literally write out the commands you need to run in order to grant such execute permissions:

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO YourDatabaseLoginNameHere'
FROM SYSOBJECTS 
WHERE TYPE = 'P' 
AND LEFT(NAME,2) <> 'sp'
AND LEFT(NAME,2) <> 'dt'

You can take the output from the above, paste into a query window and run:

GRANT EXECUTE ON MyStoredProc TO YourDatabaseLoginNameHere

GRANT EXECUTE ON AnotherStoredProc TO YourDatabaseLoginNameHere

GRANT EXECUTE ON AndAnotherStoredProc TO YourDatabaseLoginNameHere

Using Sitecore with Firefox, Chrome and other browsers

Sitecore recommends the use of IE for its client. When trying to log in with Firefox or Chrome (and presumably others), you don't get the option of viewing the desktop. There is a way you can override this behaviour. Open Sitecore in IE and go to the User Manager. Select your account and click Edit to bring up the "Edit User" dialogue. Click on the Profile tab and set the start URL to "Desktop". Save your changes and then try logging in with Firefox, etc. You should now see the full desktop.

Sitecore link fields put /home in the target URL's href

When using sc:fld('fieldname', ., 'url') in an XSLT rendering to get a link field's target URL, you will find that the href includes '/home'. This is rarely desirable. Thankfully, it's easy to solve. Simply use sc:fld('fieldname', ., 'id') instead to get the target ID. You can then pass the ID to sc:path().

How to remove an apostrophe from a string in Sitecore XSLT

Given a variable named "string", you can remove apostrophes by declaring a variable to hold the apostrophe and then passing that to the sc:Replace method:

<xsl:variable name="apos">'</xsl:variable>
<xsl:variable name="string"
select="sc:Replace( $input, $apos, '' )" />

Get a list of all Sitecore Items tagged with specific multilist items

If you wish to return all items in the content tree that have been tagged with a specific multilist item, you might be tempted to use Sitecore.Context.Database.SelectSingleItem(), passing it an XPath query. The problem with this is that performance degrades as the content tree grows. A far more efficient way is to use the GetReferrers method of the LinkDatabase class as shown below:

Database db = Sitecore.Context.ContentDatabase;

LinkDatabase linkDb = Globals.LinkDatabase;

// lookupItem is the multilist item
ItemLink[] links = linkDb.GetReferrers( lookupItem );

foreach( ItemLink link in links )
{
  Item linkedItem = db.Items[link.SourceItemID];
  // Process item...
}

Write to Sitecore log file from XSLT

It is simple to write to the Sitecore log file from within an XSLT file without having to write any custom code. We can just hook up to the Sitecore.Diagnostics.Log class and call one of its methods.

In the web.config, define the extension under <xslextensions>:

<extension mode="on"
type="Sitecore.Diagnostics.Log, Sitecore.Kernel"
namespace="http://www.sitecore.net/log"
singleInstance="true" />

Within the XSLT file, add a prefix that matches the namespace you used in the above step:

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:sc="http://www.sitecore.net/scReal"
xmlns:dot="http://www.sitecore.net/dot"
xmlns:log="http://www.sitecore.net/log"
exclude-result-prefixes="dot sc log">

The Log class contains a number of overloads for Info, Warn, Error and so on. Some of these expect an exception as a parameter, so we cannot call these direct from XSLT. We can, however match the overloads that expect a string and an object (eg. Warn( String, Object )):

<xsl:value-of select="log:Warn('Warning message', 'Another string... perhaps the name of your rendering file')" />

That's it! Now you can log whatever you like from within your renderings.

Enable XSLT IntelliSense within Visual Studio

Wouldn't it be nice to have Visual Studio list the available template names when typing out a call-template statement in XSLT? Wouldn't it also be nice to have it list available parameter names when typing out a with-param statement?

To enable these autocompletions and more, you need to back up your registry and then run regedit. Within regedit, create a string value called "XsltIntellisense" under "HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\XmlEditor" key, giving it the value "True". That's it!

Ultimately, I'd like to see full IntelliSense for XSLT in Visual Studio, but this is a good start.

ASP.NET Accessibility Top Ten

Here are my top ten tips for designing accessible ASP.NET sites. These are based on my understanding and opinion. If I am radically wrong about any of these, please feel free to correct me by leaving a comment.

Always set the display of validation controls to ‘dynamic’
In this way, screen readers ignore the text of the error message when there is no error.

Do not use image buttons
They are invisible to screen readers. Use a button with a background image instead.

Do not use AutoPostBack
They are impossible to navigate using a keyboard.

Do not use separator characters between links
Characters in breadcrumbs, footers, etc. are read out by screen readers. Use a span instead with a style instead. Or better still, use a bulleted list with a background image.

Display sets of results or links in a bulleted list
In a landing page or search results page, having each result marked up as a bullet makes it easier to navigate between results.

Insert hidden header text at the start of every navigation list
Screen reader users can navigate by headers and choose to skip the navigation lists.

Surround the individual digits in telephone numbers with span tags
Otherwise, the whole number will be read out as a single value.

Use hidden images with pagination and other non-descriptive hyperlinks
If pagination links are in the format: 1 | 2 | 3, a screen reader will read the links as ‘link 1’, ‘link 2’ and so on. So, insert a hidden image that has the alt text set to something more descriptive.

Include file sizes and types in hyperlink text
Unwittingly clicking a link to a file attachment can cause confusion.

Don’t bother using access keys
They are device-dependent, they can conflict with other shortcut keys and there is no standardisation as to what each key combination should do. Better to have clear navigation.

Using a Clean Sitecore Client for Authoring

I had a situation this week where I could not get the Sitecore client to work on my local machine. Although I could browse the end web site, I got the dreaded vague null reference exception when trying to browse to /sitecore. As the web site was fairly complex, I was finding it very painful to try to re-contruct from new. In the end, it dawned on me that all I needed to do was install a clean Sitecore client that was completely separate from the web site. This gave me the ability to change content, while the end web site enabled me to view the effect of code changes. This set-up has other benefits too; the Sitecore client runs unimpeded by any custom code (unless you are using pipelines, etc.). This makes it easier to identify/eliminate custom code as a possible cause for any slowness. Similarly, you can readily identify if any errors in the log are due to your code.

Accessible print-friendly web pages

There are times when you might want to render a page specifically for printing (rather than using CSS). For example you might want to include or exclude specific information on the printed page. When users view the print-friendly page, you might want to include some instructions at the top of the page, but not have these themselves print out. You might want to let users know they are viewing a print-friendly version and provide them with a link back to the on-screen view.

You can do this by first defining a “noprint” class:

    <style type="text/css">
        @media print
        {
            .noprint
            {
                display: none;
            }
        }
    </style>

You can then include your instructions in a DIV having this class.

Typically, the next action a user will take on viewing the page is to open the print dialogue box. We could attach a “window.print()” JavaScript action to the body’s onload event, or within a <script> tag somewhere in the body. However, having a dialogue box appear automatically is not particularly accessible as it changes the focus. The alternative is to insert a “print this page” link if the browser supports JavaScript. For non-JavaScript browsers, simply inform the user to use his or her browser’s print menu. This is demonstrated by the following HTML:

    <div class="noprint" style="background-color: #eeeeee;
border: solid 1px black; padding: 10px 10px 10px 10px;">
        <p>
            You are viewing a print-friendly version
 of a page.</p>
        <p>

            <script type="text/javascript">
                var str = "Print this page";
                document.write(
str.link("javascript:window.print()"));
            </script>

        </p>
        <noscript>
            <p>
                Please select print from your browser’s
menu to print the page</p>
        </noscript>
        <p>
            or <a href="/path/file.htm">
return to the normal on-screen view of the page
            </a>
        </p>
    </div>

Screen reader friendly phone numbers

When you display a telephone number on screen, screen readers read it out as a single number. The Fangs screen reader emulator (see http://sourceforge.net/projects/fangs) reads the number (01234) 567890 as “left paren six hundred sixty-eight right paren five hundred sixty-seven thousand eight hundred ninety”. The “six hundred sixty-eight” bit is particularly curious: the 01234 is being interpreted as an octal number.

By encasing each digit within a <span /> tag, the digits are read out individually. So, (01234) 567890 becomes “left paren zero one two three four right paren five six seven eight nine zero”.

I have written an XSLT template that takes a phone number and applies a <span /> to each digit:

  <xsl:template name="DisplayPhoneNumber">
    <xsl:param name="position" />
    <xsl:param name="phonenumber" />
    <xsl:variable name="digit"
select="substring($phonenumber, $position, 1)" />

    <xsl:choose>
      <xsl:when test="contains('012345678', $digit)">
        <span>
          <xsl:value-of select="$digit" />
        </span>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="$digit" />
      </xsl:otherwise>
    </xsl:choose>

    <xsl:if test="$position &lt;
string-length($phonenumber)">
      <xsl:call-template name="DisplayPhoneNumber">
        <xsl:with-param name="position">
          <xsl:value-of select="$position+1" />
        </xsl:with-param>
        <xsl:with-param name="phonenumber"
select="$phonenumber" />
      </xsl:call-template>
    </xsl:if>
  </xsl:template>

To call the template, pass in the telephone number and prime the starting position using a value of 1:

      <xsl:call-template name="DisplayPhoneNumber">
        <xsl:with-param name="position" select="'1'" />
        <xsl:with-param name="phonenumber"
select="'(01234) 567890'" />
      </xsl:call-template>

If you’re using this within Sitecore, you can simply pass in a field value in the usual way:

      <xsl:call-template name="DisplayPhoneNumber">
        <xsl:with-param name="position" select="'1'" />
        <xsl:with-param name="phonenumber"
select="sc:fld('phone', .)" />
      </xsl:call-template>

Visual Studio External Tools Menu

Further to my post about setting up Windows Explorer as an external tool within Visual Studio (http://www.neilpullinger.co.uk/2008/03/open-visual-studio-project-folder-in.html), I have set up a number of other tools set up in the same way. Here's my list now:

Title	Command	Arguments	Notes
Run test	(full path to nunit.exe)	$(ItemPath)
Reflector	(full path to reflector.exe)	$(ItemPath)
FxCop	(full path to fxcop.exe)	$(TargetPath)
Explorer	(full path to explorer.exe)	/e, /select, $(ItemPath)
Open in Notepad	(full path to notepad.exe)	$(ItemPath)
Open	(full path to a batch file containing just "%1")	$(ItemPath)
Error Lookup	(errlook.exe, in VS\Common\Tools folder)	(none)	Not sure where this one came from; installed by default?
Spy++	(spyxx.exe, in VS\Common\Tools folder)	(none)	Installed by default

I've excluded a couple of the others that are there by default (eg. Create Guid and Dotfuscator).

(Edited 19/12/2008)

Always set AutoPage to true in LinqDataSource

If you are binding a control to a LinqDataSource, always set the AutoPage property on the LinqDataSource to true (AutoPage=“true”) if you have enabled paging on the data-bound control. Without the AutoPage option set, the LinqDataSource will pull back all records in the data source every time a page is browsed. Not only is this inefficient and not scalable, but it allows the client to determine the level of resources required, which is not good practice.

<asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="MyDataContext" TableName="MyTable" AutoPage="true"></asp:LinqDataSource>

Sadly, the AutoPage option doesn’t seem to be available in other DataSource objects, so a manual implementation is necessary for these.

CustomValidator does not fire

Recently, I needed to ensure that a user fill in one of two TextBox controls on a form I was designing. I couldn't use two RequiredFieldValidator controls as that would mandate that both fields be completed. I therefore created a CustomValidator control and added it to the page. It did not work. There was a flaw in my thinking. The control did not ever fire when needed (ie. when both fields are blank), because the control does not, by default, validate blank fields. Thankfully, there is a way to change this. You can set the ValidateEmptyText to true:

<asp:CustomValidator ID="myValidator" runat="server" ErrorMessage="Error message" OnServerValidate="myValidator_ServerValidate ValidateEmptyText="true"></asp:CustomValidator>

Windows does not shut down

Slightly off-topic, but if your computer does not respond to an ordinary shutdown command, you can force a shutdown. To do this, select Start, Shutdown in the usual way and, as you click OK, hold down the control key. Use with care as this might terminate processes uncleanly. Always back up your data.

FindControl Causes Null Reference Exception with NMock

When using NMock on code that uses FindControl, a null reference exception is thrown:

PlaceHolder placeHolder =

(PlaceHolder) _view.MyPanel.FindControl("myplaceholder");

You could use the indexer instead:

PlaceHolder placeHolder =

(PlaceHolder) _view.MyPanel.Controls[0];

But if you insert another control in the panel, you break your code. So, the alternative is to loop through the controls and find a match by ID:

ControlCollection ctrls = _view.MyPanel.Controls;

foreach( Control ctrl in ctrls )
{
if( ctrl.ID == "myplaceholder" )
{
placeHolder = (PlaceHolder) ctrl;
break;
}
}

CruiseControl builds not working

Here's a quick checklist to assist in getting failed builds working again in CruiseControl.NET:

• Comment out any custom tasks, such as running unit tests, in the ccnet config file (defined within <exec> elements)
• Find error details quickly within the build log by searching for the following words/phrases:
• could not
  
• failed
  
• success="false"
  
• Check whether the code compiled without error by:
• Running the project's team build as used by CCNET
  
• Searching for "csc" (or "vbc") in the build log
  
• Clear out temporary files on the build server to remove any sticky references

TableHeaderScope enum does not generate XHTML-compliant output

When adding the scope attribute to a column for accessibility, the following does not generate XHTML-compliant output:

TableHeaderCell th = new TableHeaderCell();
th.Scope = TableHeaderScope.Column;

It generates the following HTML:

<th scope="column">

The value of the scope attribute should be "col". A work-around is to add the scope attribute manually using the Attributes collection:

th.Attributes.Add("scope", "col");

Using ASP.NET Log-in Controls with Sitecore Extranet

It is simple to use the log-in controls to sign in to a Sitecore extranet. Having dragged a Login control to the design surface of a layout or sub-layout (ie. web form or user control), go to the control’s properties and click the lightning symbol. Double-click the field to the right of the Authenticate item to attach an event handler to the Authenticate event. In the event handler, set the Authenticated property of the AuthenticateEventArgs parameter equal to the success flag of the Sitecore Login() method.

Your Login control definition will be similar to:

<asp:Login ID="Login1" runat="server"
OnAuthenticate="Login1_Authenticate"></asp:Login>

Your event handler will be similar to:

protected void Login1_Authenticate( object sender,
    AuthenticateEventArgs e )
{
    e.Authenticated = Sitecore.Context.Domain.Login(
        Login1.UserName, Login1.Password).Success;
}

Open Visual Studio Project Folder in Windows Explorer

Visual Studio 2008 has this feature already built in. If you’re still using VS2005, it’s easy to add:

Select Tools, External Tools to bring up the External Tools dialogue box. Enter a title in the title field. In the command field, enter the path to the Windows Explorer executable. In the arguments field, enter $(ProjectDir) or select Project Directory from the side menu. Your dialogue should look as follows. Click OK.



You could leave it there. You can now click on a project in Solution Explorer and select Tools, External Tools, and pick the item that you added. Windows Explorer will open at the project’s location. To make things even slicker, add the external tool as an item on the tool bar. To do this, right-click on the tool bar and select Customize. In the left-hand pane of the dialogue box, select Tools. In the right-hand pane, select the item you added and drag to the tool bar. This is tricky as the tools are just shown as External Command 1, External Command 2 and so on. You will need to work out the number assigned to yours.



Once you’ve dragged the right one to the tool bar, you can give it a name and icon as you like by right-clicking on it again (still in Customization mode) and tweaking the settings.