how to call sql string from nhibernate
- by frosty
i have the following method, at the moment it's return the whole sql string. How would i execute the following.
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} >= Qty1 and {1} <= Qty2
RETURN (@Cost)",
itemId, quantity);
string mystring = session
.CreateSQLQuery(sql)
.ToString();
transaction.Commit();
return mystring;
}
// EDIT
here is the final version using criteria
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<decimal>();
return cost;
}
