Parámetros SQL condicionales con nHibernate

26 de marzo de 2014 • 5 min de lectura

El problema es que el CreateSqlQuery de nHibernate necesita una cadena sql completa para ser creada, pero no puedes crear una cadena hasta que hayas evaluado los parámetros. La única solución es evaluar los parámetros condicionales para crear la cadena sql para crear la sesión de nHibernate y luego reevaluar los parámetros nuevamente para agregarlos al objeto de consulta de nHibernate. El problema con esto es que la misma lógica de evaluación se escribe dos veces. Lo que se necesita es una api fluida simple que haga todo por ti y devuelva el ISQLQuery cuando esté terminado.

Antes

public IList<AppointmentScheduleSummary> FillQuantityOld(DateTime? fromDate, DateTime? toDate, string CompanyID)
{
    string sql = "select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where";

    if (fromDate.HasValue)
    {
        sql += "  VA.Date >= '" + fromDate.Value.ToShortDateString() + "' and";

    }

    if (toDate.HasValue)
    {
        sql += "  VA.Date <= '" + toDate.Value.AddDays(1).ToShortDateString() + "' and";
    }

    sql += "  VA.date = A.date  and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum ";
    sql += " and A.appttypeid is not null";
    sql += " and O.CompanyID='" + CompanyID + "'";
    sql += " group by E.FirstName + ' ' + E.LastName ,VA.Userid  ";

    ISQLQuery query = _NHibernateSessionManager.GetSession().CreateSQLQuery(sql)
     .AddEntity("K", typeof(AppointmentScheduleSummary));
    return query.List<AppointmentScheduleSummary>();
}

Después

public IList<AppointmentScheduleSummary> FillQuantity(DateTime? fromDate, DateTime? toDate,string CompanyID)
{
   var query = _NHibernateSessionManager.GetSession()
        .SQLQuery("select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where")

        .If(fromDate.HasValue, "VA.Date >= :fromDate and", parameters =>
        {
            parameters.SetParameter("fromDate", fromDate.Value.ToShortDateString());
        })

        .If(toDate.HasValue, "VA.Date <=:toDate and ", parameters =>
        {
            parameters.SetParameter("toDate", toDate.Value.AddDays(1).ToShortDateString());
            parameters.SetParameterList("", new[] {2, 3, 4,});

        })

        .Sql(" VA.date = A.date and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum and A.appttypeid is not null and O.CompanyID = :companyId" +
             " group by E.FirstName + ' ' + E.LastName ,VA.Userid")
            .SetParameter("companyId", CompanyID)
        .ToQuery();

     query.AddEntity("K", typeof(AppointmentScheduleSummary));
     return query.List<AppointmentScheduleSummary>();
}

SqlStringBuilder

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using NHibernate;

namespace IT2.DataAccess.Extensions
{
    public class SqlStringBuilder
    {
        private readonly ISession _session;
        private readonly Action<string> _logging;
        readonly StringBuilder _builder = new StringBuilder();
        readonly ParameterBuilder _parameterBuilder;

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlStringBuilder" /> class.
        /// </summary>
        /// <param name="session">The session.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="logging"></param>
        public SqlStringBuilder(ISession session, string sql, Action<string> logging)
        {
            _session = session;
            _logging = logging;
            _builder.Append(sql);
            Parameters = new Dictionary<string, object>();
            ParameterList = new Dictionary<string, IEnumerable>();
            _parameterBuilder = new ParameterBuilder(this);
        }

        /// <summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, object> Parameters { get; set; }

        /// <summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, IEnumerable> ParameterList { get; set; }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public ISQLQuery ToSqlQuery() 
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateSQLQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public IQuery ToQuery()
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        /// <summary>
        /// Ifs the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return If(evaluation, sql, null);
        }

        /// <summary>
        /// Conditionals the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>SqlStringBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            if (evaluation)
            {
                _builder.Append(string.Format(" {0} ", sql));

                if (parameters != null)
                {
                    parameters(_parameterBuilder);
                }
            }

            return _parameterBuilder;
        }

        /// <summary>
        /// Sets the parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _parameterBuilder.SetParameter(key, value);
            return _parameterBuilder;
        }

        /// <summary>
        /// Sets the parameter list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _parameterBuilder.SetParameterList(key, value);
            return _parameterBuilder;
        }

        /// <summary>
        /// SQLs the specified SQL.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns>IT2.DataAccess.SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Append(string.Format(" {0} ", sql));
            return this;
        }
    }

    public class ParameterBuilder
    {
        private readonly SqlStringBuilder _builder;

        /// <summary>
        /// Initializes a new instance of the <see cref="ParameterBuilder" /> class.
        /// </summary>
        /// <param name="builder">The builder.</param>
        public ParameterBuilder(SqlStringBuilder builder)
        {
            _builder = builder;
        }

        /// <summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _builder.Parameters.Add(key, value);
            return this;
        }

        /// <summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _builder.ParameterList.Add(key, value);
            return this;
        }

        /// <summary>
        /// Ifs the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return _builder.If(evaluation, sql);
        }

        /// <summary>
        /// Conditions the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            return _builder.If(evaluation, sql, parameters);
        }

        /// <summary>
        /// SQLs the specified SQL.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns>SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Sql(sql);
            return _builder;
        }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public IQuery ToQuery()
        {
            return _builder.ToQuery();
        }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public ISQLQuery ToSqlQuery()
        {
            return _builder.ToSqlQuery();
        }
    }
}