Using Filestream with SQL Server and Fluent NHibernate

Save
    Written by HarmonyPSA on 2013-07-29 Last updated 2018-06-27 - 2 minute read

Storing large images and files in a SQL database can make it very large very fast, it will fill up your server and make backing up unwieldy. SQL Server 2008+ has a storage option called Filestream, where large binary files are stored in a folder on your regular hard drive rather than directly in the database, so we thought we'd have a shot at implementing it. Since we use NHibernate with Fluent mapping, it would have to play nicely with that, and as there didn't seem to be a lot of information out there for that specific case, it seemed to warrant a blog post.

The first thing to do is add a Filegroupto your database, like so:

ALTER DATABASE [MyDatabase]
ADD FILEGROUP [MyDatabaseGroup]
CONTAINS FILESTREAM
GO

Next, you have to nominate a folder in the file system to actually store the data, and attach it to the Filegroup created above:

ALTER DATABASE [MyDatabase] ADD FILE
(NAME = 'MyDatabaseImages',
FILENAME = 'E:MyDatabaseImagesimages.ndf')
TO FILEGROUP [MyDatabaseFileGroup]
GO

The name of the file and Filegroup can be whatever you want.

After that, you'll still need columns in your database for the ID of the data in the Filestream, and to represent the data itself. In this example, we'll call them (imaginatively) DataID and Data respectively. If you've generated your database from the NHibernate schema, they won't be set up for Filestream use, so you'll have to do a drop first. If not, you can ignore the next two commands.

So drop:

ALTER TABLE [File] DROP COLUMN DataID
GO
ALTER TABLE [File] DROP COLUMN Data
GO

And create:

ALTER TABLE [File] ADD DataID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE
GO
ALTER TABLE [File] ADD Data VARBINARY(MAX) FILESTREAM
GO

It turns out there isn't much literature on mapping Fluent NHibernate for Filestream because you don't have to do anything different, your mapping file looks like it usually would (so in light of my first paragraph, is this blog post still necessary? If not, I hope it's at least useful):

Map(x => x.Data).Length(10000);
Map(x => x.DataID);

The only real addition is having a DataID Guid in your File entity.

Meanwhile, in the folder you specified for your file, you'll have something like this:

And diving into that Guid folder, you'll see all the files you've added to the table living happily within:

Accessing the files in your code doesn't change at all, so now you're away using Filestream.

If you want to know how Harmony can help your business, please contact us.
 
Request demo
 

 

About the Author: Harmony Business Systems Ltd (HBS) is the company behind HarmonyPSA, the most complete cloud PSA software on the market. Developed with functionality to cater for even the most complex needs of MSPs, VARs, ISVs and Professional Services organisations, HarmonyPSA truly is the next generation of PSA systems. HBS is an independent company based in the UK. Follow HarmonyPSA on


Tags: C#, filestream, fluent NHibernate, SQL server, Technology

Categories

Recent posts

Subscribe to our blog