How to map a VARCHAR(MAX) column in NHibernate hbm.xml mapping files

Question

We have a SQL Server table with varchar and nvarchar max columns like this:

CREATE TABLE [dbo].[MyTable](
  :
  [MyBigUnicodeColumn] [nvarchar](max) NULL,
  [MyBigAnsiColumn]    [varchar](max) NULL,
  :

When creating the mapping (hbm.xml) files, the documentation says to use StringClob as the type attribute for large objects with a database type of DbType.String, but it doesn't say what to do if the database type is DbType.AnsiString.

<class name="MyTable" table="MyTable" lazy="false">
  :
  <property name="MyBigUnicodeColumn" type="StringClob" />
  <property name="MyBigAnsiColumn" type="????" />
  :

This is for NHibernate 3.3.1.

Limbourg

Answer 1

You can map them just as string or AnsiString.

<property name="MyBigUnicodeColumn" type="string" length="1000000"/>
<property name="MyBigAnsiColumn" type="AnsiString" length="1000000" />

Whenever the length is larger then 4000 or 8000 respectively, NH creates an nvarchar(max) or varchar(max).

I may be that the length is used for sql parameters and that it is truncated to the specified length (it depends on the NH version you are using, there had been some changes). So better specify it large enough.

---

Edit: Unfortunately, it doesn't work with the AnsiString the same as with normal strings. I read some NH code and found the following:

varchar(max) is supported by the dialect from SQL Server 2005 on.

MsSql2000Dialect.cs, line 205

RegisterColumnType(DbType.AnsiString, SqlClientDriver.MaxSizeForLengthLimitedAnsiString, "VARCHAR($l)");

MsSql2005Dialect.cs, line 19:

RegisterColumnType(DbType.AnsiString, SqlClientDriver.MaxSizeForAnsiClob, "VARCHAR(MAX)");

It registers varchar(max) as the sql type to choose when an AnsiString is mapped larger then 8000.

In the SqlClientDriver.cs you can see that it implements "blobs" in the params for strings, but not for ansi strings (line 135):

case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
    dbParam.Size = MaxSizeForLengthLimitedAnsiString;
    break;
// later on
case DbType.String:
case DbType.StringFixedLength:
    dbParam.Size = IsText(dbParam, sqlType) ? MaxSizeForClob : MaxSizeForLengthLimitedString;
    break;

It always puts 8000 as the limit of the parameter of type AnsiString.

Because of the inconsistency between the driver and the dialect, I would call it a bug.

Because the bug happens on all AnsiStrings, it doesn't help to specify the sql-type in the mapping (NH is able to choose the correct sql type). You need to use the workaround proposed in the thread you started on the NH forum:

<property name="MyBigAnsiColumn" type="StringClob" sql-type="VARCHAR(max)" /> 

---

I reported it as a bug: https://nhibernate.jira.com/browse/NH-3252

Disused

AnsiString is by definition a non-unicode string, so this shouldn't be broken. I think this would only change if future SQL Servers would offer new ways to store a string or an ansi string in the specified length. – Disused

On the other side, this is all implemented in the Dialect, and it is quite easy to have your own dialect. – Disused

This depends on the NH version you are using. They specify the length when passing parameters, in order to make SQL Server reuse the queries. The SQL Server client used to cut off data, which was very bad, so the check the length. If you want to use the hole 2 GB max length, specify it like this ... – Disused

Hi Stefan, In some more testing of NH 3.3.1 I don't think the VARCHAR one works. If I specify the length as 1000000 the varchar column will still not receive any more than 8000 chars OOTB. A brief look at the source code (the SqlClientDriver.SetDefaultParameterSize method) seems to back this up. FYI I have posted to the nhusers group to see if I can get some clarification. – Limbourg

Note: I have marked this as the answer but have not tested it myself. In the end I changed the varchar(max) columns to nvarchar(max) to avoid the issue. – Limbourg

Answer 2

User Nexus on the NHibernate (nhusers) forum said:

<property name="MyBigAnsiColumn" type="StringClob" sql-type="VARCHAR(max)" />  
Should be the most correct answer

Note: I have not verified this as I decided to convert all the VARCHAR(MAX) columns to NVARCHAR(MAX) instead.

Limbourg

Answer 3

public class Role
{
    public Role() { }
    public virtual string RoleId { get; set; }
    public virtual string RoleName { get; set; }
    public virtual string RoleDescription { get; set; }
}

public class RoleMap : ClassMapping<Role>
{
    public RoleMap()
    {
        Table("nda_roles");
        Schema("dbo");
        Lazy(true);
        Id(x => x.RoleId, map => 
        { 
            map.Column("role_id");
            map.Length(12);
            map.Type((IIdentifierType)TypeFactory.GetAnsiStringType(12));
            map.Generator(Generators.Assigned); 
        });
        Property(x => x.RoleName, map =>
        {
            map.Column("role_name");
            map.NotNullable(true);
            map.Length(50);
            map.Type(TypeFactory.GetAnsiStringType(50));
        });
        Property(x => x.RoleDescription, map =>
            {
                map.Column("role_description");
                map.Length(NHibernateConfig.GetMaxLengthAnsiString());
                map.Type(TypeFactory.GetAnsiStringType(NHibernateConfig.GetMaxLengthAnsiString()));
            });
    }
}


public static class NHibernateConfig
{
    private static string driver_class;
    private static string dialect;

    public static ISessionFactory GetNHibernateSessionFactory()
    {
        var config = new Configuration().Configure(); // Read config from hibernate.cfg.xml
        var configPath = HttpContext.Current.Server.MapPath(@"~\hibernate.cfg.xml");
        config.Configure(configPath);
        driver_class = config.Properties["connection.driver_class"];
        dialect = config.Properties["dialect"];
        config.CurrentSessionContext<WebSessionContext>();

        var mapper = new ModelMapper();
        mapper.AddMappings(new Type[]
        {
            typeof(NDA.Models.RoleMap),
            typeof(NDA.Models.PermissionMap),
            typeof(NDA.Models.CompanyMap),
            typeof(NDA.Models.UserMap),
        });
        HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
        config.AddMapping(domainMapping);

        new SchemaExport(config).Execute(false, true, false);

        return config.BuildSessionFactory();
    }

    public static int GetMaxLengthString()
    {
        int maxlenght = 255;
        switch (driver_class)
        {
            case "NHibernate.Driver.SqlClientDriver":
                switch (dialect)
                {
                    case "NHibernate.Dialect.MsSql2008Dialect":
                        maxlenght = 4000;
                        break;
                }
                break;
        }
        return maxlenght;
    }

    public static int GetMaxLengthAnsiString()
    {
        int maxlenght = 255;
        switch (driver_class)
        {
            case "NHibernate.Driver.SqlClientDriver":
                switch (dialect)
                {
                    case "NHibernate.Dialect.MsSql2008Dialect":
                        maxlenght = 8000;
                        break;
                }
                break;
        }
        return maxlenght;
    }

}

And hibernate.cfg.xml file:

<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
<session-factory name="NHibernate.NDA">
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="connection.connection_string_name">nda_connectionstring</property>
    <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
<property name="hbm2ddl.auto">validate</property>
<mapping assembly="NDA"/>

Haemophilic