I am using NHibernate and calling a stored procedure via a named query:
<sql-query name="SearchStuff" read-only="true" cacheable="true">
<return class="ResultEntity" />
EXEC [SearchStuff] ?, ?, ? </sql-query>
Many of the stored procedure parameters are deliberately nullable - this cannot be changed.
The C#:
IQuery listQuery = this.Session.GetNamedQuery("SearchStuff");
listQuery.SetInt32(0, param1);
listQuery.SetDateTime(1, param2);
listQuery.SetString(2, param3);
IList<ResultEntity> results = listQuery.List<ResultEntity>();
Unfortunately, NHibernate does not provide any SetXyz() methods for nullable value types so I tried adding some extension methods to compensate:
public static class QueryExtensions
{
public static void SetInt32(this IQuery query, int position, int? val)
{
if (val.HasValue)
{
query.SetInt32(position, val.Value);
}
else
{
query.SetParameter(position, null);
}
}
public static void SetInt32(this IQuery query, string name, int? val)
{
if (val.HasValue)
{
query.SetInt32(name, val.Value);
}
else
{
query.SetParameter(name, null);
}
}
public static void SetDateTime(this IQuery query, int position, DateTime? val)
{
if (val.HasValue)
{
query.SetDateTime(position, val.Value);
}
else
{
query.SetParameter(position, null);
}
}
public static void SetDateTime(this IQuery query, string name, DateTime? val)
{
if (val.HasValue)
{
query.SetDateTime(name, val.Value);
}
else
{
query.SetParameter(name, null);
}
}
}
I've tried various versions of these but none work. The code above fails with the error:
System.ArgumentNullException : A type specific Set(position, val) should be called because the Type can not be guessed from a null value.
I also tried simply not setting the parameter but NHibernate requires every parameter to be set. I've tried using both positional and named versions with the same results.
Is there any way to assign null values to value typed parameters in NHibernate named queries?
1 Lanosterol eye drops could potentially be a safe, non-invasive, and less costly alternative to cataract surgery for patients who have moderate forms of cataracts.
Lubricating drops for dry feeling eyes and irritation containing the potent anti-oxidant N-Acetylcarnosine (NAC), safe to be used by people with cataracts.
SAFE FOR HUMANS AND DOGS - Can-C is the first and only patented NAC eye drop that uses the exact formula proven effective in both animal and human trials, offering a non-invasive alternative to cataract surgery.
A popular eye drop 'Can-C' containing N-alpha-acetylcarnosine (NAC) claims to reduce, reverse and slow the development of senile cataract. It was developed and is patented by Professor Babizhayev, a bio-physicist and Executive Director of Innovative Vision Products (IVP) [1].
OK, it turns out there are some overrides on SetParameter that allow the type to be set explicitly. For example:
query.SetParameter(position, null, NHibernateUtil.Int32);
The full extension methods (for Int32 and DateTime only) are now:
public static class QueryExtensions
{
public static void SetInt32(this IQuery query, int position, int? val)
{
if (val.HasValue)
{
query.SetInt32(position, val.Value);
}
else
{
query.SetParameter(position, null, NHibernateUtil.Int32);
}
}
public static void SetInt32(this IQuery query, string name, int? val)
{
if (val.HasValue)
{
query.SetInt32(name, val.Value);
}
else
{
query.SetParameter(name, null, NHibernateUtil.Int32);
}
}
public static void SetDateTime(this IQuery query, int position, DateTime? val)
{
if (val.HasValue)
{
query.SetDateTime(position, val.Value);
}
else
{
query.SetParameter(position, null, NHibernateUtil.DateTime);
}
}
public static void SetDateTime(this IQuery query, string name, DateTime? val)
{
if (val.HasValue)
{
query.SetDateTime(name, val.Value);
}
else
{
query.SetParameter(name, null, NHibernateUtil.DateTime);
}
}
}
Another way to accomplish it is:
query.SetParameter<int?>(0, null);
query.SetParameter<DateTime?>(1, null);
...
And so on...
Notice the ?
symbol that makes the struct type nullable.
The full extension methods (for Int32 and DateTime only) with chaining are now:
public static class QueryExtensions
{
public static IQuery SetInt32(this IQuery __query, int __position, int? __val)
{
var _query = __val.HasValue ? __query.SetInt32(__position, __val.Value) : __query.SetParameter(__position, null, NHibernateUtil.Int32);
return _query;
}
public static IQuery SetInt32(this IQuery __query, string __name, int? __val)
{
var _query = __val.HasValue ? __query.SetInt32(__name, __val.Value) : __query.SetParameter(__name, null, NHibernateUtil.Int32);
return _query;
}
public static IQuery SetDateTime(this IQuery __query, int __position, DateTime? __val)
{
var _query = __val.HasValue ? __query.SetDateTime(__position, __val.Value) : __query.SetParameter(__position, null, NHibernateUtil.DateTime);
return _query;
}
public static IQuery SetDateTime(this IQuery __query, string __name, DateTime? __val)
{
var _query = __val.HasValue ? __query.SetDateTime(__name, __val.Value) : __query.SetParameter(__name, null, NHibernateUtil.DateTime);
return _query;
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With