Sql server FileStream type in nhibernate

I would like to use FileStream with Nhibernate.

The only post I found on stackoverflow is Sql 2008 Filestream with NHibernate

In the 开发者_开发百科meantime, Nhibernate 3.0 (and 3.1) was released.

Does someone know a solution ?

It is not planned to be supported by nhibernate. (WONT FIX)

http://groups.google.com/group/nhusers/browse_thread/thread/e4a8f038f9c40a0d/a63ac4a8ce4f1244?pli=1

I hope it will change in the future ... maybe an extension (the open source magic)

Nhibernate can be used with the FILESTREAM... sort of. Persist the object using Nhibernate as normal. You can then issue a couple of standard SQL Queries to take care of the filestream. I place the queries as named queries in my mapping file for the object.

public void Save(Photo photo){
            //Save photo data
            session.Save(photo);
            //get path
            String path;
            Byte[] context;
            IQuery qry1 = session.GetNamedQuery(QUERY_SET_BLANK);
            qry1.SetInt64("photoId", photo.RID);
            int cnt = qry1.ExecuteUpdate();
            IQuery qry2 = session.GetNamedQuery(QUERY_GET_PATH);
            qry2.SetInt64("photoId", photo.RID);
            System.Collections.IList results = qry2.List();
            object[] item = (object[]) results[0];
            path = (String) item[0];
            context = (Byte[])item[1];
            if (context == null) throw new QueryException("Possible null transaction");
            //save photo
            using (SqlFileStream sqlFile = new SqlFileStream(path, context, FileAccess.Write)) {
                photo.Image.Save(sqlFile, ImageFormat.Jpeg);
                sqlFile.Close();
            }
        }

public Photo Get(Int64 rid) {
            Photo result = session.Get<Photo>(rid);
            if (result != null) {
                IQuery qry = session.GetNamedQuery(QUERY_GET_PATH);
                qry.SetInt64("photoId", result.RID);
                System.Collections.IList results = qry.List();
                object[] item = (object[])results[0];
                var path = (String)item[0];
                var context = (Byte[])item[1];
                if (context == null) throw new QueryException("Possible null transaction");
                using (SqlFileStream sqlFile = new SqlFileStream(path, context, FileAccess.Read))
                    result.Image = new System.Drawing.Bitmap(System.Drawing.Bitmap.FromStream(sqlFile));
            }
            return result;
        }

Where Photo is an object mapped to a table. The actual column of the FILESTREAM type is not mapped to the object (excluded from mapping), the the named queries take care of the persistence for that column. The name queries look like:

<sql-query name="PhotoPathContext">
    select Photo.PathName() as path, GET_FILESTREAM_TRANSACTION_CONTEXT() as con 
      from Core.Photos where PhotoRID = :photoId
  </sql-query>
  <sql-query name="PhotoSetBlankFileStream">
    update Core.Photos set Photo = Cast('' as varbinary(max)) where PhotoRID = :photoId
  </sql-query>

The thing need to be wrapped in a transaction which is required by the GET_FILESTRAM_TRANSACTION_CONTEXT() method in the query.

Unit tests would look something like:

[Test]
public void TestSavePhoto() {
    IList<Model.Photo> photos = repo.GetPhotos();
    VegTabUtilityServices.Photo photo = new VegTabUtilityServices.Photo();
    VegTabUtil.Model.Photo ph = photos[0];
    photo.RowGuid = ph.GetGuid().Value;
    photo.Name = ph.Name ?? photo.RowGuid.ToString();
    photo.Image = ph.Image;
    ISession session = SessionManager.Instance.Session;
    PhotoService ps = new PhotoService(session);
    using (NHibernate.ITransaction tx = session.BeginTransaction()) {
        ps.Save(photo);
        tx.Commit();
    }
    Assert.Greater(photo.RID, 0);
}

[Test]
public void TestPhotoConnection() {
    ISession session = SessionManager.Instance.Session;
    PhotoService ps = new PhotoService(session);
    Photo p;
    using (NHibernate.ITransaction tx = session.BeginTransaction()) {
        p = ps.Get(6l);
        tx.Commit();
    }
    Assert.NotNull(p);
    Assert.NotNull(p.Image);
    logger.Debug(String.Format("{0} by {1} pixels", p.Image.Width, p.Image.Height));
}

I have a more complete article on codeproject