NHibernate ISQLQuery with byte[] parameter throws error

Question

So, I am trying to use nhibernate to call a stored procedure that saves an image. My problem is that the query fails with this error:

The length of the byte[] value exceeds the length configured in the mapping/parameter.

Here is where I attach my parameters to the ISQLQuery. Parameters is a Dictionary<string, object>.

public void Execute()
{

    string sql = BuildSprocDefinition();
    var query = Session.CreateSQLQuery(sql);

    foreach (var parameter in Parameters)
    {
        if (parameter.Value is int)
            query.SetParameter<int>(parameter.Key, (int)parameter.Value);
        else if (parameter.Value is long)
            query.SetParameter<long>(parameter.Key, (long)parameter.Value);
        else if (parameter.Value is bool)
            query.SetParameter<bool>(parameter.Key, (bool)parameter.Value);
        else if (parameter.Value is byte[])
            query.SetBinary(parameter.Key, (byte[])parameter.Value);
        else
            query.SetParameter(parameter.Key, parameter.Value);
    }

    using (var tx = Session.BeginTransaction())
    {
        query.ExecuteUpdate();
        Session.Flush();
        tx.Commit();
    }
}

protected string BuildSprocDefinition()
{
    StringBuilder sql = new StringBuilder(String.Format("exec {0} ", storedProcName));

    foreach (var parameter in Parameters)
    {
        sql.Append(String.Format(":{0}, ", parameter.Key));
    }

    if (sql.ToString().EndsWith(", "))
    {
        sql = sql.Remove(sql.Length - 2, 2);
    }

    return sql.ToString();
}

In the database, my parameter is type varbinary(max) and I am trying to send in a 'byte[]' as the data.

I tried using the specific SetBinary and yet this fails. All other datatypes seem to be working.

I have also tried query.SetParameter<byte[]>(parameter.Key, (byte[])parameter.Value); with the same error.

Answer 1

it is a bit old question, but i put my answer for new searchers.

for byte[] parameters you can specify the length this way

query.SetParameter(parameter.Key, parameter.Value, NHibernate.Type.TypeFactory.GetBinaryType(((byte[])parameter.Value).Length));

you need to make sure that the procedure parameter has sufficient length

Answer 2

NHibernate doesn't magically know that the datatype in the database is varbinary(max). You need to specify a large enough length in your mappings (I think the default is 8000 bytes).