Support SQLCE BinaryBlob and StringClob in NHibernate without depending on System.Data.SqlServerCe

Out of the box NHibernate 2 does not support the BinaryBlob or StringClob sql types for SQL Server compact 3.5. This results in NText and Image fields being truncated. One of the common workarounds you can find on the web is to override the existing SqlServerCeDriver and cast the parameter to an SqlCeParameter and manual set the parameter types to NText and Image.

using System.Data; 

using System.Data.SqlServerCe; 

using NHibernate.Driver; 

using NHibernate.SqlTypes;

public class MySqlServerCeDriver : SqlServerCeDriver 

{ 

    protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType) 

    { 

        base.InitializeParameter(dbParam, name, sqlType); 

        var parameter = (SqlCeParameter)dbParam;

        if (sqlType is StringClobSqlType) parameter.SqlDbType = SqlDbType.NText; 

        if (sqlType is BinaryBlobSqlType) parameter.SqlDbType = SqlDbType.Image; 

    } 

}

The problem with this is that it couples you directly to the System.Data.SqlServerCe library. For some people this won’t be a problem as they will only need to implement it in the project that is using SQLCE and already be coupled to the library. However in my case I wanted to put it in our core library where it could be reused but didn’t want users of the library to have a dependency on SQLCE.

One way of achieving what I wanted would have been to create a separate  sub library to our core library which dragged in the sql server ce data library but didn’t pollute  the core which would have been fine but you have to ask yourself how is it that the NHibernate library is not dependent on the sql server ce data library. The answer is that they use reflection, in fact the drivers themselves inherit from a the ReflectionBasedDriver and use reflection to create the parameters and set the data types. So assuming that the NHibernate library does it correctly we just need to look at one of the other implementations and replicate how they set the data types which gives rise to the following:

using System.Collections.Generic;

using System.Data;

using System.Reflection;

using NHibernate.Driver;

using NHibernate.SqlTypes;

public class SqlServerCe35Driver : SqlServerCeDriver

{

    private PropertyInfo dbParamSqlDbTypeProperty;

    public override void Configure(IDictionary<string, string> settings)

    {

        base.Configure(settings);

        using (IDbCommand cmd = CreateCommand())

        {

            IDbDataParameter dbParam = cmd.CreateParameter();

            dbParamSqlDbTypeProperty = dbParam.GetType().GetProperty("SqlDbType");

        }

    }

    protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)

    {

        base.InitializeParameter(dbParam, name, sqlType);

        if (sqlType is BinaryBlobSqlType)

        {

            dbParamSqlDbTypeProperty.SetValue(dbParam, SqlDbType.Image, null);

        }

        else if (sqlType is StringClobSqlType)

        {

            dbParamSqlDbTypeProperty.SetValue(dbParam, SqlDbType.NText, null);

        }

    }

}

As you can see there is slightly more work involved and we need to use reflection up front to get a hold of the sql db type property of the SqlCeParameter but result is that there is no dependency on the System.Data.SqlServerCe library and we have conformed to how the rest of the NHIbernate ReflectionBasedDriver implementations.