Someone asked me if I had a simple sample of how to read/write documents into a database table using FILESTREAM in SQL Server 2008.

I set about it.

Firstly, I had to find out how to switch FILESTREAM on in the RTM of SQL Server. At times like this you need Bob Beauchemin to tell you how it’s done and then you can go and run code such as this once you’ve done the necessary bits in SQL Configuration manager;

exec sp_configure filestream_access_level, 2
reconfigure
go

CREATE DATABASE Documents 
ON
PRIMARY 
( 
    NAME = DocData,
    FILENAME = 'c:\temp\docs.mdf'
),
FILEGROUP FileGroup CONTAINS FILESTREAM
( 
    NAME = DocFiles,
    FILENAME = 'c:\temp\docstreams'
)
LOG ON  
( 
    NAME = DocLog,
    FILENAME = 'c:\temp\docs.ldf'
)
GO

and now I’ve got a database that can accept file stream data. Phew. Time for a simple table;

create table Docs
(
    id uniqueidentifier rowguidcol not null primary key,
    documentData varbinary(max) filestream not null,
    documentName nvarchar(1024) not null
)

Now I’ve got a table I can use something like Windows Presentation Foundation with VB to build a simple UI to allow me to add documents to the database and then edit them. Note that editing them here just means sucking out the file data, writing it to a temporary file, passing that to the Shell for editing and then saving the contents back to the database.

There ended up being too much code to paste in here as a blow-by-blow account but here’s my rather grey looking UI;

with a few items from my desktop that I’ve added to my database table.

The code’s pretty hacky with almost no error handling but I thought I’d share it here for download regardless.