title: how to call sql string from nhibernate
body: <p>i have the following method, at the moment it's return the whole sql string. How would i execute the following. </p>
  
  <pre><code>            using (ITransaction transaction = session.BeginTransaction())
              {
                  string sql =
                      string.Format(
                          @"DECLARE @Cost money
                          SET @Cost = -1
                          select @Cost = MAX(Cost) from item_costings
                           where Item_ID = {0}
                          and {1} &gt;= Qty1 and {1} &lt;= Qty2
                          RETURN (@Cost)",
                          itemId, quantity);
  
                 string mystring = session
                      .CreateSQLQuery(sql)
                      .ToString();
  
                  transaction.Commit();
                  return mystring;
              }
  </code></pre>
  
  <p>// EDIT</p>
  
  <p>here is the final version using criteria</p>
  
  <pre><code> using (ISession session = NHibernateHelper.OpenSession())
          {
             decimal cost = session
                  .CreateCriteria(typeof (ItemCosting))
                  .SetProjection(Projections.Max("Cost"))
                  .Add(Restrictions.Eq("ItemId", itemId))
                  .Add(Restrictions.Le("Qty1", quantity))
                  .Add(Restrictions.Ge("Qty2", quantity))
                  .UniqueResult&lt;decimal&gt;();
              return cost;
          }
  </code></pre>