TLDR:
The following code is run in the different databases, Oracle:
select sysdate from dualSQLiteselect datetime('now')When doing
Session.CreateSQLQuery(cmd).UniqueResult<DateTime>()the result is a DateTime when working against Oracle but a string when working against SQLite.It feels like a bug in the SQLite driver and a hack to check the returned type and do a DateTime.Parse() if it is a string. I could do that but are there any ways to have NHibernate return the correct type?
I am trying to fetch current database time from the database. It works fine when using Oracle but when I try to do it against SQLite (in my unit tests) it breaks as the date returned is not a DateTime but a string.
I've seen solutions using custom IUserType but I cannot see how I should use that in this case. Any suggestions?
using System;
using System.Collections.Generic;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Dialect.Function;
namespace My.Common.Types {
public class MyNHibernateDialectException : Exception {
public MyNHibernateDialectException(string message) : base(message) { }
}
/// <summary>
/// Define all custom functions here by name. It is important that when adding a new custom sql function, that function will work
/// in all dialects supported.
/// </summary>
public static class MyDatabaseDialects {
public enum Query {
SysDate
}
/// <summary>
/// Dialect implementations will use this function to verify that they all implement the same functions.
/// </summary>
/// <param name="dialect"></param>
public static void VerifyRegistrations(this NHibernate.Dialect.Dialect dialect) {
// Verify that the required function are there
foreach (var func in Enum.GetValues(typeof(Function))) {
var enumName = func.ToString();
if (!dialect.Functions.ContainsKey(enumName)) {
throw new MyNHibernateDialectException(
string.Format("The custom function '{0}' is not defined. Did you forget it in factory '{1}'?", enumName, dialect));
}
}
}
}
/// <summary>
/// An interface to reveal more advanced functionality that is database specific
/// </summary>
public interface IDialectExtensions {
/// <summary>
/// Fetch a query specfic for the current database.
/// </summary>
ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query);
/// <summary>
/// Fetch a parameterized query specfic for the current database.
/// </summary>
ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams);
}
/// <summary>
/// Class to store database specific objects except functions (which are supported by NHibernate).
/// </summary>
class DialectExtension {
private readonly Dictionary<MyDatabaseDialects.Query, string> queryDictionary = new Dictionary<MyDatabaseDialects.Query, string>();
public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
return this.GetQuery(session, query, null);
}
public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
var cmd = (queryParams == null) ? queryDictionary[query] : string.Format(queryDictionary[query], queryParams);
return session.Session.CreateSQLQuery(cmd);
}
public void RegisterQuery(MyDatabaseDialects.Query query, string hqlString) {
queryDictionary.Add(query, hqlString);
}
public void VerifyQueryRegistrations() {
foreach (var query in Enum.GetValues(typeof(MyDatabaseDialects.Query))) {
if (!queryDictionary.ContainsKey((MyDatabaseDialects.Query)query)) {
throw new MyNHibernateDialectException(string.Format("The custom query '{0}' is not defined.", query.ToString()));
}
}
}
}
public class MyOracle10gDialect : NHibernate.Dialect.Oracle10gDialect, IDialectExtensions {
private readonly DialectExtension dialectExtension = new DialectExtension();
public MyOracle10gDialect() {
#region Dialect extensions
dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select sysdate from dual");
dialectExtension.VerifyQueryRegistrations();
#endregion Dialect extensions
}
public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
return dialectExtension.GetQuery(session, query);
}
public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
return dialectExtension.GetQuery(session, query, queryParams);
}
}
public class MySqliteDialect : NHibernate.Dialect.SQLiteDialect, IDialectExtensions {
private readonly DialectExtension dialectExtension = new DialectExtension();
public MySqliteDialect() {
#region Dialect extensions
dialectExtension.RegisterQuery(MyDatabaseDialects.Query.SysDate, @"select datetime('now')");
dialectExtension.VerifyQueryRegistrations();
#endregion Dialect extensions
}
public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query) {
return dialectExtension.GetQuery(session, query);
}
public ISQLQuery GetQuery(ISession session, MyDatabaseDialects.Query query, params object[] queryParams) {
return dialectExtension.GetQuery(session, query, queryParams);
}
}
}
And I use the code above like this:
/// <summary>
/// Fetches a DialectExtensions object allowing us to have more advanced functionality that is database specific
/// </summary>
public static IDialectExtensions GetDialectExtensions(this IOperationContext operationContext) {
return Session.GetSessionImplementation().Factory.Dialect as IDialectExtensions;
}
/// <summary>
/// Get the database time by executing a raw SQL statement.
/// </summary>
public static DateTime? GetDatabaseTime() {
DateTime? result = null;
try {
result = GetDialectExtensions()
.GetQuery(Session, MyDatabaseDialects.Query.SysDate)
.UniqueResult<DateTime>();
} catch {
// SQLite will throw exception here as the result is returned as a string instead of a DateTime
}
return result;
}
