Logo

NHibernate

The object-relational mapper for .NET

How to

This page is converted from the old nhforge.org Wiki. First published by: shauns on 03-05-2009, Last revision by: Dario Quintana on 03-09-2009

Use PostgreSQL Arrays with NHibernate

Postgresql supports the use of Arrays as a datatype for a column (documentation) - if you have a model that can storea List<string> for example, then in some cases its preferable to take advantage of this datatype, rather than requiring a join to a seperate table.

This How To shows how you would go about implementing an IUserType that does this, and how you would extend the default Postgresql driver supplied with NHibernate to support the specific extensions required by Npgsql (the DB library used to communicate with Postgresql) to use arrays.

Extending the Driver

As you will be using specific features of Postgresql, you'll need to extend the supplied driver to one that can directly reference Npgsql. Create a new Class Library project, and reference NHibernate and an up to date version of Npgsql.

You should then create a class that extends NpgsqlDriver like so:

public class NpgsqlDriverExtended : NpgsqlDriver{
        protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType) {
            if (sqlType is NpgsqlExtendedSqlType && dbParam is NpgsqlParameter) {
                this.InitializeParameter(dbParam as NpgsqlParameter, name, sqlType as NpgsqlExtendedSqlType);
            } else {
                base.InitializeParameter(dbParam, name, sqlType);
            }
        }

        protected virtual void InitializeParameter(NpgsqlParameter dbParam, string name, NpgsqlExtendedSqlType sqlType) {
            if (sqlType == null) {
                throw new QueryException(String.Format("No type assigned to parameter '{0}'", name));
            }

            dbParam.ParameterName = FormatNameForParameter(name);
            dbParam.DbType = sqlType.DbType;
            dbParam.NpgsqlDbType = sqlType.NpgDbType;
           
        }
    }

Essentially, we just check if we've been given something specific to Postgresql, and if so, setup the parameter slightly differently. You'll notice that we have NpgsqlExtendedSqlType - SqlType is used by NHibernate to flag which type of column an IUserType uses. We create an extended version of this that also has an Npgsql.NpgDbType, and its this we use to create the parameter.

Here is the code:

public class NpgsqlExtendedSqlType : SqlType{
       
        public NpgsqlExtendedSqlType(DbType dbType, NpgsqlDbType npgDbType) : base(dbType) {
            this.npgDbType = npgDbType;
        }

        public NpgsqlExtendedSqlType(DbType dbType, NpgsqlDbType npgDbType, int length) : base(dbType, length) {
            this.npgDbType = npgDbType;
        }

        public NpgsqlExtendedSqlType(DbType dbType, NpgsqlDbType npgDbType, byte precision, byte scale) : base (dbType, precision, scale) {
            this.npgDbType = npgDbType;
        }

        private readonly NpgsqlDbType npgDbType;
        public NpgsqlDbType NpgDbType {
            get {
                return this.npgDbType;
            }
        }
    }

You can go ahead and use this in your project now (change the driver parameter in your NHibernate config - mine is like cfg.Properties["connection.driver_class"] = "Wordbank.NH.Postgres.NpgsqlDriverExtended, Wordbank.NH.Postgres";) with no ill affects.

Use Postgresql Arrays

With this alternative driver, and support for Postgresql specific SQL types, then we can create an IUserType that will take List<string> and store it as string array in the database. This part is more straightforward - just create a class that implements IUserType. The key methods are NullSafeGet - this should return a List<string>. Npgsql will return the field as a string[] so we just do a simple conversion.

public virtual object NullSafeGet(IDataReader resultSet, string[] names, object owner) {
            int index = resultSet.GetOrdinal(names[0]);
            if (resultSet.IsDBNull(index)) {
                return null;
            }
            string[] res = resultSet.GetValue(index) as string[];
            if (res != null) {
                return res.ToList();
            }
            throw new NotImplementedException();
        }

 

NullSafeSet should store a List<string> - thanks to the other classes we have in place, we just need to set the parameter value to a string[]

public virtual void NullSafeSet(IDbCommand cmd, object value, int index) {
            IDbDataParameter parameter = ((IDbDataParameter)cmd.Parameters[index]);
            if (value == null) {
                parameter.Value = DBNull.Value;
            } else {
                List<string> list = (List<string>)value;
                parameter.Value = list.ToArray();
            }
        }

ReturnedType tells NHibernate what this IUserType returns:

public virtual Type ReturnedType {
            get { return typeof(List<string>); }
        }

And then finally, we hook into our new Postgresql aware SqlType via:

public SqlType[] SqlTypes {
            get { return new SqlType[] { new NpgsqlExtendedSqlType(DbType.Object, NpgsqlTypes.NpgsqlDbType.Array | NpgsqlTypes.NpgsqlDbType.Text) }; }
        }

The syntax tells Npgsql to store it as an Array of Text.

The final step is to tell our mapping file to use this IUserType. I use NHibernate.Mapping.Attributes like so -

private List<string> values;

        /// <summary>
        /// Values
        /// </summary>
        [Property(NotNull = true, Column = "values", Type = "Wordbank.NH.Postgres.StringArrayType, Wordbank.NH.Postgres")]
        public virtual List<string> Values {
            get { return this.values; }
            set {
                if (value == this.values) {
                    return;
                }
                this.values = value;
            }
        }

 

I hope this will come in use - you can use the same idea to create arrays of ints, doubles etc.

 

© NHibernate Community 2024