FluentNHibernate mapping settings - file storage in SQL Server database

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.

---

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

---

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!

---

I have the following table in PostgreSQL with bytea column:

CREATE TABLE "SystemFiles"
(
  id integer NOT NULL,
  name character varying(64),
  contenttype character varying(64),
  data bytea,
  CONSTRAINT pk_id PRIMARY KEY (id)
)

Here is my entity:

public class SystemFiles
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string ContentType { get; set; }
    public virtual byte[] Data { get; set; }
}

And that's how my mapping looks like:

public class SystemFilesMap : ClassMap<SystemFiles>
{
    public SystemFilesMap()
    {
        Id(x => x.Id, "id").GeneratedBy.Identity();
        Map(x => x.Name).Column("name");
        Map(x => x.ContentType).Column("contenttype");
        Map(x => x.Data).Column("data");
    }
}

With above configutration I can read, save, delete files from/into database...

CONTROLLER:

using (var session = RisDbHelper.OpenSession())
{
        var tempImage = (from c in session.Query<SystemFiles>() where c.Name == "Logo" select c).FirstOrDefault();
        model.LogoImage = Convert.ToBase64String(tempImage.Data);
}

VIEW:

@if (!String.IsNullOrEmpty(Model.LogoImage))
{
    <img src="@String.Format("data:image/png;base64,{0}", Model.LogoImage)" style="width: 200px"/>
}