Native SQL Queries

» Home » ActiveRecord » Documentation » ActiveRecord trunk Documentation » User's Guide » Native SQL Queries

There is a couple of scenarios in which HQL doesn't provide for all features you need. For example if you want to intersec three or more tables adding them to the "from" clause. Sometimes HQL complies with your requirements (most of them) but you want to get a fine grained sql statement control.

Teaching Native SQL Queries is out of the scope of this article. You should consult the NHibernate documentation on Native SQL Queries.

CreateSQLQuery

CreateSQLQuery is used in cases where the query would be a native SQL query.

Here are an example that involves two models, one to store words and another to store synonyms.


using Castle.ActiveRecord.Queries;
using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;
using System.Collections;
using Iesi.Collections;
using NHibernate.Expression;
using NHibernate;

[ActiveRecord]
public class Word : ActiveRecordBase
{
    private int _id;
    private string _key;

    public Word() {}

    [PrimaryKey]
    public int Id
    {
        get { return _id; }
        set { _id = value; }
    }

    [Property]
    public string Key
    {
        get { return _key; }
        set { _key = value; }
    }

    public IList FindSynonyms()
    {
        ISessionFactoryHolder sessionHolder = ActiveRecordMediator.GetSessionFactoryHolder();
        ISession session = sessionHolder.CreateSession(typeof(Synonym));

        try
        {
            string query = "select synonym.key from  word, synonym ";
            query += "where and synonym.word = word.id and word.key = '" + Key +"'";
            IQuery sqlQuery = session.CreateSQLQuery(query, "Synonym", typeof(Synonym));
            sqlQuery.SetMaxResults(10);
            return sqlQuery.List();
        }
        finally
        {
            sessionHolder.ReleaseSession(session);
        }
    }
}

Here you have a more complex query. There is a Menu model to store menu items, there is a MenuItemTranslation model to store items translations and the last model is the Language one, to store languages.


public string FindTranslation(string lang)
{
    if ((lang == null) || (lang.Length == 0)) return Description;
    
    ISessionFactoryHolder sessionHolder = ActiveRecordMediator.GetSessionFactoryHolder();
    ISession session = sessionHolder.CreateSession(typeof(MenuItemTranslation));
    
    try
    {
        string query = "select menuitemtranslation.translation from  menu, language, menuitemtranslation ";
        query += "where menuitemtranslation.menu = " + Id;
        query += " and menuitemtranslation.lang = language.id and language.englishname = '" + lang +"'";
        IQuery sqlQuery = session.CreateSQLQuery(query, "menuitemtranslation", typeof(MenuItemTranslation));
        sqlQuery.SetMaxResults(1);
        IList translations = sqlQuery.List();
        
        if ((translations != null) && (translations.Count > 0))
        {
            return ((MenuItemTranslation)translations[0]).Translation;
        }
        else
        {
            return Description; 
        }
    }
    finally
    {
        sessionHolder.ReleaseSession(session);
    }
}

Home

Castle Stronghold

Native SQL Queries

CreateSQLQuery