Published
Tuesday, October 23, 2007 7:18 AM
by
mtaulty
I wrote a little demo around FILESTREAM in SQL 2008 so I thought I'd share - the code's a little shabby but I got to the point where it did what I wanted and so I stopped. Some days I miss not writing production code because it's very different from demo code and the differences are where I used to enjoy spending my time.
Anyway, regardless...you need this SQL script to set up the single table involved here (or at least something a bit like it);
use master
go
-- Switching the thing on in the first place.
EXEC sp_filestream_configure
@enable_level = 3;
-- Need a filegroup that supports it too.
USE master;
GO
IF EXISTS (
SELECT * FROM sys.databases
WHERE name = N'PhotoDB'
)
DROP DATABASE PhotoDB
GO
CREATE DATABASE PhotoDB ON PRIMARY
( NAME = PhotoDB_data,
FILENAME = N'D:\data\PhotoDb.mdf',
SIZE = 10MB,
MAXSIZE = 50MB,
FILEGROWTH = 15%),
FILEGROUP PhotoDBFS CONTAINS FILESTREAM
( NAME = PhotoDB_FS,
FILENAME = N'D:\data\Photos')
LOG ON
( NAME = 'PhotoDB_log',
FILENAME = N'D:\data\PhotoDB_log.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB);
GO
use photodb
go
create table Photos
(
rowid uniqueidentifier rowguidcol not null primary key default(NEWID()),
name nvarchar(256) not null,
photo varbinary(max) filestream
)
Note that it's got a couple of hard-coded paths in there. They need changing for your environment.
Then there's a code project which I've placed here. It's a Windows Forms application. You run it up;
You click the import button to import an image from the file system. The app goes away, inserts a row into that photos table then requests Write access to the FILESTREAM data using OpenSqlFilestream (via interop). It then writes the image that you selected into that filestream.
Finally, it goes and requeries the database pulling back all the rows and when you switch from one row to another it goes and grabs the FILESTREAM for Read access again using OpenSqlFilestream in order to paint the image on the screen as below;
You click the Replace... button in order to replace one image with another (yes, I know that's slightly weird functionality but there you go). The code once again requests Write access to the FILESTREAM and overwrites it with your new image. It also updates the DB to change the name of the image. After the replace you get a confirmation dialog...
And we either commit the changes or roll them back highlighting that the changes that we've made via the FILESTREAM data are linked in to the transaction that's in flight.
The OpenSqlFilestream API gives you back a file handle - I ended up just passing this into the .NET FileStream class and letting it take ownership of the handle which worked fine for me here.