Archive

Posts Tagged ‘sql’

NHibernate SqlAliasCriterion and Random sorting

December 30th, 2011 No comments

Wrote this custom NHibernate Criterion to be able to add custom SQL snippets with alias support when using criteria.

    public class SqlAliasCriterion : SQLCriterion
    {
        private string sql;
 
        public SqlAliasCriterion(string sql)
            : base(new SqlString(sql), new string[0], new IType[0])
        {
            this.sql = sql;
        }
 
        public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
        {
            string replacedString = Regex.Replace(
                sql,
                @"{([a-zA-Z_]((\.)?[a-zA-Z0-9_])*)}",
                m =>
                {
                    ICriteria critter = criteria.GetCriteriaByAlias(m.Groups[1].Value);
                    if (critter != null)
                    {
                        return criteriaQuery.GetSQLAlias(critter);
                    }
 
                    return m.Groups[0].Value;
                }
            );
 
            return new SqlString(replacedString);
        }
    }

Usage example:

return Session.CreateCriteria<YourClass>("x")
        .Add(new SqlAliasCriterion("{x}.YourField = SomeNativeSQLFunc(foo)"))
        .List<YourClass>();

You can use the same trick to do ordering. (We use this for native MSSQL random ordering)

    public class OrderBySql : Order
    {
        private string sql;
 
        public OrderBySql(string sql, bool ascending)
            : base(String.Empty, ascending)
        {
            this.sql = sql;
        }
 
        public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery)
        {
            string replacedString = Regex.Replace(
                sql,
                @"{([a-zA-Z_]((\.)?[a-zA-Z0-9_])*)}",
                m =>
                {
                    ICriteria critter = criteria.GetCriteriaByAlias(m.Groups[1].Value);
                    if (critter != null)
                    {
                        return criteriaQuery.GetSQLAlias(critter);
                    }
 
                    return m.Groups[0].Value;
                }
            );
 
            return new SqlString(replacedString + (this.ascending ? " ASC" : " DESC"));
        }
    }

Example with random sorting (MSSQL):

criteria.addOrder(
    new OrderBySql("NEWID()")
);
Categories: C# Tags: , , ,

Update table from select.

November 18th, 2010 No comments

Here’s a small example how you can update a table from a select statement.

UPDATE Contact
SET Contact.Surname = x.Surname
FROM (
	SELECT Id, SUBSTRING(Surname, Len(Insertion) + 2, LEN(Surname) - Len(Insertion)) AS Surname
	FROM Contact
	WHERE Insertion IS NOT NULL
	AND Insertion <> ''
	AND CHARINDEX(Insertion + ' ', Surname) = 1) x
WHERE Contact.Id = x.Id;

Using a join is also possible.

UPDATE c
SET c.Body = x.Body, c.Summary = x.Summary
FROM Content c
JOIN PageContent x ON c.ID = x.ID;
Categories: General Tags: , ,