FluentNHibernate mapping settings - file storage in SQL Server database

Question

Hello,

I've read various things but I didn't see something specific, so I'm reposting. Sorry if I missed one and duplicate posted.

I am storing files in a database; previously, with ADO.NET Entity Framework, I would use image type and it streams it as byte[] array.

Is that the approach to do it in NHibernate with FluentNHibernate mappings? I setup the column as image. I defined this as the mapping for the property (which the C# property is a byte[] array):

Map(i => i.FileContents).CustomSqlType("image");

Is that the correct way to set this up? I am getting an error and am not sure if its related to this?

Thanks.

Answer 1

You don't need a custom type. Here is a mapping that works for a SQL Server image column named Content:

    Map(x => x.Content);

Here is usage of that mapping:

byte[] content = nhSession.CreateCriteria<AttachmentContent>()
                .Add<AttachmentContent>(ac => ac.Id == 3)
                .SetProjection(Projections.Property("Content"))
                .UniqueResult<byte[]>();

...and here's a way to get it out without a mapping (AttachmentDTO is not a mapped NH class, just a normal class) :

nhSession.CreateSQLQuery("select a.Content from Attachments a where a.Id = 1")
    .SetResultTransformer(Transformers.AliasToBean(typeof(AttachmentDTO)))
    .UniqueResult<AttachmentDTO>();

Here's the DTO class:

public class AttachmentDTO {
    public int Id { get; set; }
    public string ContentType { get; set; }
    public byte[] Content { get; set; }
}

Good luck!

Answer 2

You can also map Custom<TType>s to NHibernate.Type types

For instance:

Map(i => i.FileContents).Custom<NHibernate.Type.BinaryBlobType>();

I've mapped files stored as binary, but they weren't the 'image' type.

I did a quick google search and found a post with an ImageUserType which you could try to specify instead. http://weblogs.asp.net/ricardoperes/archive/2009/09/17/nhibernate-image-user-type.aspx

edit. This user type looks a lot better: http://www.martinwilley.com/net/code/nhibernate/usertype.html