SQL equivalent to Last()

We all love Linq in all its glory, but from time to time you have to write raw SQL queries (c: and it is fun to, don’t get me wrong, but it doest have the same range of ready to use functions as Linq.

This time i needed to find the last element from a table with at least trazillion rows, this is how i did it.

SELECT * FROM  tableName
WHERE   id = (SELECT MAX(id) FROM tableName)

You can posibly wrap it in to a CREATE FUNCTION, but this worked for me as it is,.. (c:

if you table doesn’t have an id with which hold the usable number you can do somthing like this

SELECT MAX(rownr) FROM (
SELECT ROW_NUMBER() OVER(ORDER BY customerText DESC) AS rownr
  FROM bbc) AS Q

the customerText is a name for any kind of column in your table ..

Releace of my own Umbraco based webpage.

For little bit more then the past month time I’ve been working on designing and developing my own webpage. For this task I’ve chosen Umbraco CMS engine, beacouse of it’s similarities with Sitecore, but allso because it is free to use. I’ve was pleasantly surprised on the helpfulness of the Umbraco community, and i like to contribute my knowledge to further Umbraco development by posting articles about it on my blog.

To differ my self from all the other mass-produced templated webpages outhere, I’ve designed and implemented my own look and feed for my webpage.
my webpage design
The main idea behind this webpage is to make a content driven webpage not a blog. I’ve written an article about how this webpage came to being and i am going to write further articles in the future on how i did it. More about the nuts and bolts of it all. (c: So sign up for an RSS feed, so you can keep up.

here are some highlights of the implementation.

  • youtube integration
  • Custom developed tag based article system
  • Lucene index search results
  • Tag and Related articles lists.
  • Umbraco based contact form(creates nodes in umbraco, so i can see a feed of all new items when logged in)

If you have any comment’s pleas tell me i wood like to hear your opinion.

Get YouTube video JSON Data using JQuery and Ajax

Earlier I’ve written an article on how t get Youtube data using JSON and it was a blast, but i had a litlle cosmetic drawback. You had to put script tags in your markup.   It works but it’s not pretty (c: . One of the easiest ways to load JSON data onto our website is to use the $.ajax(), which is a very useful JQuery method.  Her’s my way of doing it.

      $.ajax({
            type: "GET",
            url: "http://gdata.youtube.com/feeds/users/[USERNAME]/uploads?alt=json-in-script&format=5",//generateJSonPlayListCallBackUrl(),
            cache: false,
            dataType:'jsonp',
            success: function(data){
                showMyVideos(data);
          }
        });

I’ve taken the showMyVideos() from Google’s Json documentation article it’ will display a list of video titles from a given Youtube  users Chanel.  To use this code snippet just replace [USERNAME] with a Youtube user name, and put inside a JS file (c: Take notice of dataType:’jsonp’, thats the important bit. This allows us to retrieve youtube data back to our domain (c:

well thats it for to day,.. stay frosty people,..

How To make Search for Umbraco site with Lucene.Net, Examine and XSLT

Being able to search content in a website is probably the most common and most important feature of any website. And there are a millions ways to do this,.. so why use Lucene indexing or Eximine for that matter..   If you have been looking inside the umbraco bin directury you will notice a wary nice Lucene.Net.dll file. This is one of the reasons for why i chose  Lucene indexing, because it is already there (c:
Umbraco uses Lucene indexing to search it self, if you’ve ever wondered where those files hidden, they’re in the “/data/_systemUmbracoIndexDontDelete” folder.  There you’ll find at least one .cfs file, a deletable and segments file.

In my setup i wanted to separate my indexes from the ones Umbraco generates,  so -i chose  Examine to do the heavy lifting, of generating indexes on so on.  Examine has loads of useful features, i will not recite them all but you can read about them here. So I downloaded the demo site from the FarmCode.com to take a closer look at the Examine set up, and because i was curious.. (c:

After a quick run-thought the demo-site and its macros and other code,  i copied all the related DLL.

  • Lucene.Net.dll(lates version)
  • Examine.dll
  • UmbracoExamine.dll
  • UmbracoExamine.Contrib.dll

And completed the setup, which can be found here on the FarmCode.com site.

I’ve noticed that the UmbracoExamine.Contrib.dll han an XSLT extension, so i added  it to my xsltExtensions.config

<?xml version="1.0" encoding="utf-8" ?>
<XsltExtensions>
        <ext assembly="/bin/UmbracoExamine.Contrib" type="UmbracoExamine.Contrib.XsltExtensions" alias="examine" />
</XsltExtensions>

and wrote a small XSLT macro to test it.

<xsl:stylesheet
        version="1.0"
        xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
        xmlns:msxml="urn:schemas-microsoft-com:xslt"
        xmlns:examine="urn:examine"
        xmlns:umbraco.library="urn:umbraco.library" xmlns:Exslt.ExsltCommon="urn:Exslt.ExsltCommon" xmlns:Exslt.ExsltDatesAndTimes="urn:Exslt.ExsltDatesAndTimes" xmlns:Exslt.ExsltMath="urn:Exslt.ExsltMath" xmlns:Exslt.ExsltRegularExpressions="urn:Exslt.ExsltRegularExpressions" xmlns:Exslt.ExsltStrings="urn:Exslt.ExsltStrings" xmlns:Exslt.ExsltSets="urn:Exslt.ExsltSets" xmlns:tagsLib="urn:tagsLib"
        exclude-result-prefixes="msxml examine umbraco.library Exslt.ExsltCommon Exslt.ExsltDatesAndTimes Exslt.ExsltMath Exslt.ExsltRegularExpressions Exslt.ExsltStrings Exslt.ExsltSets tagsLib ">
<xsl:output method="xml" omit-xml-declaration="yes"/>

<xsl:param name="currentPage"/>
<xsl:template match="/">
  [[<xsl:value-of select="examine:Search('something')"/>]]
</xsl:template>

But it resulted in an “Error parsing XSLT file: \xslt\SearchPosts.xslt” error.. grrr,.. Nothing i did could make it good again,.. the XSLT code was pretty simple and the xsltExtensions.confiq was carrect.  As it tuns out the “UmbracoExamine.Contrib.dll” is not released yest (c:  thanx Lee

So in stead of getting frustrated and angry i’ve decided to write my own SearchExtension Class ,..  (c: Her is the busines end

//remember to add these (c:
//using Examine;
//using UmbracoExamine.SearchCriteria;
//using UmbracoExamine;
//using Examine.SearchCriteria;

public static XPathNodeIterator Search(string searchTerm, string field)
{
      return Search(searchTerm, field, "MySearcher");
}

public static XPathNodeIterator Search(string searchTerm, string field, string searchProvider)
{
//get query string search param & error check
if (string.IsNullOrEmpty(searchTerm)) return null;
var criteria = ExamineManager.Instance.CreateSearchCriteria(IndexType.Content, Examine.SearchCriteria.BooleanOperation.Or);

ISearchCriteria filter = criteria
   .Field("bodyText", searchTerm.Fuzzy().Value)
   .Or()
   .Field("menuTitle", searchTerm.Fuzzy().Value)
   .Compile();
XDocument node = new XDocument();
XElement content = new XElement("nodes");
//get the search results from the mysearch provider
foreach (SearchResult result in ExamineManager.Instance.SearchProviderCollection[searchProvider].Search(filter).AsEnumerable().Where(res => (res.Score * 10) > 2).OrderBy(res => res.Score))
{
   XElement element2 = new XElement("node");
   XAttribute attribute = new XAttribute("id", result.Id);
   XAttribute attribute2 = new XAttribute("score", result.Score);
   element2.Add(new object[] { attribute, attribute2 });
   foreach (KeyValuePair<string, string> pair in result.Fields)
   {
      XElement element3 = new XElement("data");
      XAttribute attribute3 = new XAttribute("alias", pair.Key);
      XCData data = new XCData(pair.Value);
      element3.Add(new object[] { attribute3, data });
      element2.Add(element3);
   }
content.Add(element2);
}
node.Add(content);
return node.CreateNavigator().Select("/");
}

After you have copied your DLL to umbracos bin dir and added it to the  xsltExtensions.config like describe above,.. It’s time to write the XSLT macro,.. this is my version (c:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xsl:stylesheet [ <!ENTITY nbsp " "> ]>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxml="urn:schemas-microsoft-com:xslt"
xmlns:mySearch="urn:mySearch"
xmlns:umbraco.library="urn:umbraco.library" xmlns:Exslt.ExsltCommon="urn:Exslt.ExsltCommon" xmlns:Exslt.ExsltDatesAndTimes="urn:Exslt.ExsltDatesAndTimes" xmlns:Exslt.ExsltMath="urn:Exslt.ExsltMath" xmlns:Exslt.ExsltRegularExpressions="urn:Exslt.ExsltRegularExpressions" xmlns:Exslt.ExsltStrings="urn:Exslt.ExsltStrings" xmlns:Exslt.ExsltSets="urn:Exslt.ExsltSets" xmlns:tagsLib="urn:tagsLib"
exclude-result-prefixes="msxml mySearch umbraco.library Exslt.ExsltCommon Exslt.ExsltDatesAndTimes Exslt.ExsltMath Exslt.ExsltRegularExpressions Exslt.ExsltStrings Exslt.ExsltSets tagsLib ">
<xsl:output method="xml" omit-xml-declaration="yes"/>
<xsl:param name="currentPage"/>
<xsl:variable name="currentKeyWord" select="$currentPage/data[@alias='KeyWords']"></xsl:variable>

<xsl:template match="/">
   <xsl:variable name="searchResult" select="mySearch:Search(string($currentKeyWord),string('KeyWords'))" />
   <xsl:if test="count($searchResult/nodes/node) != 0">
      <ul>
      <xsl:for-each select="$searchResult/nodes/node">
         <xsl:sort order="descending" select="./data[@alias = 'updateDate']"/>
            <li>
            <a href="{umbraco.library:NiceUrl(@id)}">
            <xsl:value-of select="./data[@alias = 'menuTitle']"/>
            </a>
            </li>
      </xsl:for-each>
      </ul>
   </xsl:if>
</xsl:template>

Notice that I am using ./data[@alias = ‘updateDate’] to probe for the fields stored in my LuceIndex.  Thats sums it up (c:

Happy coding..

My Sitecore 6 twitter module

For a couple of weeks ago I had fun making a twitter module for Sitecore 6.   The main goal of this module was to show of some of my skills as a Sitecore developer, and i want to share this with you (c:. Her are some of the highlights.

  1. Sitecore scheduled twitter updater task, that manages import of twits from a given Twitter user
  2. All twits are saved as Sitecore Items.
  3. All twits are displayed with a XSLT rendering.
  4. Download the package and the source files(at the end of the article)

Short description of the Sitecore Setup.

The Sitecore Scheduled task is setup to call the twitter manager webform witch gets all new twits from a twitter account which is defined by name in Sitecore. Here you can see it running on my machine
The webform called by the Sitecore scheduled task stores all the new twits as Items in the master database and publishes them to web database. These items are purely meant as data items,  so they have no renderings or layout attached to their template.  All the twit items are placed under the “global” folder item so they can be separated from all the other nondata items like my Twitter page item(described below).

An example off twits items generated by my twitter manager. Every Item is named so it will allays have a unique name.

Twitter manager webform called by the Sitecore, gets it’s twitter username from the Twitter page item, which in my example can be found under the Home root item.

Here you can see the twitter item based on the TwitterManagerPage template. Items based on this template are meant to display all twits from a given user in this case me (c:

Items based on the TwitterManagerPage template has its own icon and device setup.  So they can be used to show a list of twits items stored within the sitecore in the front-end.

Here you can see the setup of device for the TwitterManagerPage template.

To display a list of twits, I’ve made a simple layout with a placeholder, that in my case  is used to display a rendering, which renders all the twit items in the global folder as a sorted list.

her you can see the layout and rendering associated with twitter manager.

I like to keep my things neat so i’ve added a TwitterManager folder to layouts rendering and templates folders (c:
If you want to try this twitter module you can download the package that i have made here.  Remember to read the readme file which explaines in further detail the content of the pakadge and the install procidure.

When the twitter module is installed and up  runing on your Sitecore solution, you should be able to se a list with twitter entries like this one (c:

Download

TwitterManager Package[33kb],   TwitterManager source code [3mb]

if you have any questions to the source code or the description above, fill free and ask (c:

How XSLT macro can be used inside an umbraco richtext editor

I’ve been trying to make a simple XSLT macro, that can be used from the macro dropdown in Umbraco rich text editor. And this has proved somewhat more interesting that i have anticipated.

Making macroes in Umbraco is pretty simple and strait forward, just go under Development in Seactions and right click “XSLT files” folder in the tree menu. Choose create from the menu and type in the name of the XSLT, press ok and you are in business. If you haven’t unchecked Create macro, you’ll find your macro in the “Macros” folder, under the same name as your XSLT. Easy as eating pancakes right.. (c:

So her’s where the fun begins ,.. to make this usable from the reachtext editor, you must check the “Use in editor” chekerbox under “macro properties” tab. This is the easy setup part.

You can use your XSLT macro as it is in your .Net code by calling it like this

<umbraco:Item field="macroNameHere" runat="server"></umbraco:Item>

but here is the dangerous bit,.. If you are reading it in through XSLT,  from your richtext field,  like this

<xsl:value-of select="$currentPage/data[@alias = 'bodyText']" />

the Macro will never be rendered and the only thing you will get is this bit of code rendered as common text, Bummer )c:

<?UMBRACO_MACRO  macroAlias="macroNameHere" />

Now befour you will start tearing hair from your head, this is what you could do,.. (c:

<xsl:variable name="content" select="$currentPage/data[@alias='bodyText']"/>
<xsl:value-of select="umbraco.library:RenderMacroContent($content, $currentPage/@id)" disable-output-escaping="yes"/>

By using the RenderMacroContent you are forcing umbraco to render your macro which is allready represented by its tag in the reachtext field.

another apprach is to use the folowing code snipped witch will enchure to render our XSLT Macro automatically

<xsl:value-of select="umbraco.library:Item($currentPage/@id,'bodytext')" />

It’s less code compared to the other method, and as another small bonus the Item-extension makes sure that you can edit the content i canvas-mode as well. That option is normally not possible when rendering the content through XSLT  (c:

happy days,..

XSLT bug, xslt removes html tags by it self

And this is a fact,.. I often use  HTML  when i make my renderings for Sitecore.  If yore not care-full,.. you can end up with a site which is not W3C compliant or even worse if it messes up your design,..

So her’s the problem,. if you type something like this in your XSLT
<div class=”separator”></div>

XSLT will only render the start tag, like so
<div class=”separator” >
Bummer,..

the only solution for this is to place some kind of content inside your empty tag
like a white space,.. But be careful to put &nbsp; inside your XSLT it will result in error. Use &#160 ; in stead (c:

happy coding

EDIT:
Another solution for this was proposed by Richard Willis, thanx body
change your <xsl:output method=”xml“>  to <xsl:output method=”html“>
that will instruct the render to format your output like HTML, like we want to..

Converting twitter <created_at> date to Sitecore date format

I wanted to pull down some twits from my twitter account and show them with Sitecore CMS. When i looked at the http://api.twitter.com/1/statuses/user_timeline/maanehunden.xml
feed, i realized that i had to convert this Wed Jun 16 19:59:33 +0000 2010 wired date-format in to something mere Sitecoreish in order to save this value in a date field,..

this is what i did:

protected string DateFormat(string twitterDate)
{
   DateTime itemDate = DateTime.ParseExact(twitterDate, "ddd MMM dd HH:mm:ss zzz yyyy", CultureInfo.InvariantCulture);

   //this is an example of sitecore date format 20100621T194455
   return itemDate.ToString("yyyyMMddTHHmmss", CultureInfo.InvariantCulture);
}

Now I can use this method to format twitter date the way i wanted, end of story (c:

Searching for a new employeer

I am currently emploed at Cabana.dk but the time has come for me to explore and search for new challenges to further my development. My servises as a creative web developer are now available for hire.  Although this blog is in English, I am a danish developer resident in the city of Copenhagen, so let me know if you have a good proposition.

If you know someone who would be interested, please forward this URL: https://maanehunden.wordpress.com/2010/06/10/searching-for-a-new-employeer/

Workaround for chekcbox list validation

If you have tried to add a validator like a RequiredFieldValidator to a checkbox list asp control, you will de surprised to discover that that is not possible, and will result in error if you try. To counter this you can do two things extend checkbox list control ore make a custom validator. I chose the second solution, to keep the code to a minimum. Her’s a quick setup. I am taking advantage of OnServerValidate attribute of the CustomValidator control.

<asp:CustomValidator id="CustomCheckboxlistValidator1" runat="server"
OnServerValidate="CheckboxlistValidate"
ControlToValidate="MjauCheckboxlist "
ErrorMessage="One or more checkboxes must be chosen">
</asp:CustomValidator>

CheckboxlistValidate is the name of a javascript function that will hold the validation logic.

<script language="javascript">
function CheckboxlistValidate (oSrc, args) {
var checkboxList= document.getElementById("<%=MjauCheckboxlist.ClientID%>");
var InputElements = checkboxList.getElementsByTagName("input");
for (var i = 0; i < InputElements.length; i++) {
var input = InputElements[i];
if (input.checked) {
args.IsValid = true;
return null;
}
args.IsValid = false;
}
}
</script>

The JavaScript code is pretty simple. checkboxList variable holds the Html element with all checkboxes. From that you can derive all Imput elements and check them if they have been chosen. args.IsValid is used to set validation status. That’s it. One thing to remember thou the javascritp function must have to parameters,… oSrc – objectSource and arg – arguments. That’s about it (c:

Happy coding