Introduction
Nhibernate does not include a type to map properties to SQL Server's FILESTREAM data type. The following code shows an approach I have used to allow the use of Nhibernate to manage basic table data and allow for properties mapped to the FILESTEAM data type.
Background
Nhibernate is a popular ORM used for developing .NET applications. It is a port of the Java Hibernate ORM. Therefore, the techniques used for Nhibernate will likely work just as well for Hibernate, If you need more information on either framework, you can search CodeProject with the keywords Nhibernate or hibernate.
The SQL Server FILESTREAM data type is a method of storage where SQL Server stores binary objects on the file system rather than directly in the database. If you need more information on the FILESTREAM data type, you can search CodeProject using the keywords 'sql server filestream'.
This tip assumes you have an understanding of both Nhibernate and the FILESTREAM data type.
Using the Code
The code provides a basic pattern on how to incorporate the FILESTREAM with Nhibernate. You can adapt the basic concepts in your own code.
The first thing you will require is an SQL Server instance (2008 or higher) with a file stream enabled database. Code Project has a number of good articles on how to get started with the FILESTEAM data type.
You will need a table that uses a FILESTREAM type:
CREATE TABLE [CORE].[Photos] (
[PhotoRID] bigint IDENTITY(1, 1) NOT FOR REPLICATION NOT NULL,
[RowGuid] uniqueidentifier ROWGUIDCOL NOT NULL,
[Version] timestamp NOT NULL,
[Photo] varbinary(MAX) FILESTREAM NULL,
[Name] nvarchar(50) NOT NULL,
[ParentTable] nvarchar(250) NULL,
[ParentRID] bigint NULL,
[DateCollected] date NULL,
[TimeCollected] time(7) NULL,
[TimeZoneRID] nvarchar(10) NULL,
[Description] nvarchar(250) NULL,
[GeographyLocationRID] bigint NULL,
[GeometryLocationRID] bigint NULL)
ON [PRIMARY]
FILESTREAM_ON [FileStreamGroup1]
WITH (DATA_COMPRESSION = NONE);
The columns of importance are [Photo] and [RowGuid]. Photo is the FILESTREAM type and tables with a file stream require a ROWGUIDCOL. The other columns are just examples of additional data elements that could be associated with the binary data (in this case, photos).
Since we are using an ORM, we have a class associated with the table data:
public class Photo : Entities.Base.BoMeta<Int64, Photo> {
private Bitmap image;
private IBo<Int64> parent;
private Int64? parentRID;
private String parentTable;
private String name;
private String description;
public Bitmap Image {
get { return this.image; }
set { this.image = value; }
}
public IBo<long> Parent {
get { return this.parent; }
set { this.parent = value; }
}
public Int64? ParentRID {
get { return this.parentRID; }
set { this.parentRID = value; }
}
public string ParentTable {
get { return this.parentTable; }
set { this.parentTable = value; }
}
public string Name {
get { return this.name; }
set { this.name = value; }
}
public string Description {
get { return this.description; }
set { this.description = value; }
}
}
Again, most of the columns are not important; however, public Bitmap Image is the property where we want to store the binary data (in this case, an image) in our class.
For the mapping file:
="1.0"="utf-8"
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Domain.Entities.Core"
assembly="Domain.Entities.Impl">
<class name="Photo"
schema="CORE"
table="Photos"
lazy="false">
<id name="RID"
column="PhotoRid"
type="System.Int64"
unsaved-value="0">
<generator class="identity" />
</id>
<property name="RowGuid"
column="RowGuid"
type="System.Guid"
insert="true"
update ="true" />
<property name="Name"
column="Name"
type="System.String"
length="50"
insert="true"
update="true"
not-null="true"
unique="false"/>
<property name="Description"
column="Description"
type="System.String"
length="250"
insert="true"
update="true"
not-null="false"
unique="false"/>
<property name="ParentTable"
column="ParentTable"
type="System.String"
length="250"
insert="true"
update="true"
not-null="false"
unique="false"/>
<property name="ParentRID"
column="ParentRID"
type="System.Int64"
not-null="false"
unique="false"
access="property"/>
<component name="Version"
class="Version"
access="property">
<property name="Bytes"
column="Version"
type="System.Byte[]"
generated="always"
access="property"/>
</component>
</class>
</hibernate-mapping>
In the mapping, we have no map for the Image property.
Now that the infrastructure is in place, we can use a service to manage the basic crud functions of the class:
public class PhotoService {
private ISession session;
private const String QUERY_GET_PATH =
"select Photo.PathName() as path, GET_FILESTREAM_TRANSACTION_CONTEXT() " +
"as con from Core.Photos where PhotoRID = :photoId";
private const String QUERY_SET_BLANK =
"update Core.Photos set Photo = Cast('' as varbinary(max)) where PhotoRID = :photoId";
public PhotoService(ISession session) {
session.GuardNull("session");
this.session = session;
}
public void Save(Photo photo){
session.Save(photo);
String path;
Byte[] context;
ISQLQuery qry1 = session.CreateSQLQuery(QUERY_SET_BLANK);
qry1.SetInt64("photoId", photo.RID);
int cnt = qry1.ExecuteUpdate();
ISQLQuery qry2 = session.CreateSQLQuery(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];
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) {
ISQLQuery qry = session.CreateSQLQuery(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];
using (SqlFileStream sqlFile = new SqlFileStream(path, context, FileAccess.Read))
result.Image = new System.Drawing.Bitmap(System.Drawing.Bitmap.FromStream(sqlFile));
}
return result;
}
public Photo GetInTransaction(Int64 rid) {
Photo result = null;
using (NHibernate.ITransaction tx = session.BeginTransaction()) {
result = Get(6l);
tx.Commit();
}
return result;
}
}
In the Save(Photo photo) method, we use Nhibernate to save the object to the database. After the new photo has been saved, we issue standard SQL queries to create a place holder for the file, then we get the FILESTREAM context to create a SqlFileStream object to do the actual saving of the image to the FILESTREAM.
For Photo Get(Int64 rid), first we use Nhibernate to get the Photo object. Same as with saving, we get the FILESTREAM context and use a SqlFileStream to retrieve the image.
It is important to note that the GET_FILESTREAM_TRANSACTION_CONTEXT() query statement needs to be run inside a transaction or else it will return null. This would be the same if you ran the query in TSQL.
Points of Interest
In my service, I created two methods, one called GetInTransaction(Int64 rid) will use the session to create a new transaction to get the FILESTREAM context. The other, Photo Get(Int64 rid), assumes that the caller will create a transaction to run the method in. I did this for when I wanted to get multiple photos in a loop and only wanted to create on transaction. In addition, the save method requires the calling code to run it inside a transaction. The following is an example of some unit tests:
[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));
}
The queries can also be stored in the mapping file as named queries:
="1.0"="utf-8"
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Domain.Entities.Core"
assembly="Domain.Entities.Impl">
<class name="Photo"
schema="CORE"
table="Photos"
lazy="false">
<id name="RID"
column="PhotoRid"
type="System.Int64"
unsaved-value="0">
<generator class="identity" />
</id>
<property name="RowGuid"
column="RowGuid"
type="System.Guid"
insert="true"
update ="true" />
<property name="Name"
column="Name"
type="System.String"
length="50"
insert="true"
update="true"
not-null="true"
unique="false"/>
<property name="Description"
column="Description"
type="System.String"
length="250"
insert="true"
update="true"
not-null="false"
unique="false"/>
<property name="ParentTable"
column="ParentTable"
type="System.String"
length="250"
insert="true"
update="true"
not-null="false"
unique="false"/>
<property name="ParentRID"
column="ParentRID"
type="System.Int64"
not-null="false"
unique="false"
access="property"/>
<component name="Version"
class="Version"
access="property">
<property name="Bytes"
column="Version"
type="System.Byte[]"
generated="always"
access="property"/>
</component>
</class>
<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>
</hibernate-mapping>
If so, the service would be as follows:
public class PhotoService {
private ISession session;
private const String QUERY_GET_PATH = "PhotoPathContext";
private const String QUERY_SET_BLANK = "PhotoSetBlankFileStream";
public PhotoService(ISession session) {
session.GuardNull("session");
this.session = session;
}
public void Save(Photo photo){
session.Save(photo);
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");
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;
}
public Photo GetInTransaction(Int64 rid) {
Photo result = null;
using (NHibernate.ITransaction tx = session.BeginTransaction()) {
result = Get(rid);
tx.Commit();
}
return result;
}
}
History
- 23rd October, 2015: Initial post
