Bo,

Depends how complex your needs are in the past I have used linq2sql, ef but recently been using petapoco as the data access was not very complicated i.e not too many joins.  Right tool for the right job comes to mind!

Regards

Ismail

For all websites that have custom tables we use LLBLGen. Sometimes I also generate some Umbraco tables because it can be a lot faster to use a LINQ query than the API sometimes.

Jeroen

Hi Ismail and Jeroen,

Sorry for the late response - and thanks a lot for your thoughts on this.

I absolutely agree that we should use the right tool for the right job. It's just a matter of taking the right decision when chosing the right tool. I.e., today I found myself writing endles T-SQL methods because the site in question is running on MySQL. It's just a lot more time-consuming than doing a simple linq query, imo.

Jeroen: thanks for the link, i'll have to read up on that! Just out of curiousity, do you mind sharing an example of a situation where you're quering the Umbraco tables instead of using the API? :-)

Thanks again!

All the best,

Bo

I've got a situation where I needed to load an alternative template for a node, but I didn't know the name. I could use the Document API to get the documenttype and see which templates it has, but using that on the frontend isn't ideal. That's why I wrote a query with LLBLGen to do this:

/// <summary>
/// Return the template alias related to the nodeId. 
/// </summary>
/// <param name="nodeId"></param>
/// <returns></returns>
public string GetTemplateName(int nodeId)
{
    //Get the name of the crawlable redirect template.
    string crawlableRedirectTemplate = Configuration.CrawlableRedirectTemplate;

    //The node has a documenttype and the documenttype must only have 2 allowed templates.
    //The first allowed template is the crawlableRedirectTemplate which is used for redirecting to the default page (with the url after #!)
    //The second allowed template is the template which contains the actual html we need to display on the default page.
    //This query returns the second allowed template by excluding the crawlableRedirectTemplate.
    //This could also be done using the Umbraco API, but this custom query is a lot faster.
    return
        (
            from cc in MetaData.CmsContent
            join cct in MetaData.CmsContentType
                on cc.ContentType equals cct.NodeId
            join cdt in MetaData.CmsDocumentType
                on cct.NodeId equals cdt.ContentTypeNodeId
            join ct in MetaData.CmsTemplate
                on cdt.TemplateNodeId equals ct.NodeId
            where ((cc.NodeId == nodeId)
                && (ct.Alias != crawlableRedirectTemplate))
            select ct.Alias
        ).Single();
}

You can use custom queries to fetch data faster, but you should only do this to select data. Never try to use it to insert or update data. The Umbraco API can handle that much better (and it's safer).

Jeroen

is working on a reply...