Configuring and Using Filestream in SQL Server: Part 2

Introduction

In the first part of this article we looked at the following:

  • Enabling and Configuring FILESTREAM for SQL Server at the instance level.
  • Creating a FILESTREAM enabled database
  • Creating a table capable of storing FILESTREAM data

In this part, we’ll look at inserting objects into the FILESTREAM data, and we’ll also look at how applications interact with FILESTREAM in order to work with objects in the database.

So, just to quickly recap, we have a FILESTREAM enabled database called MyImages. In that database we have a table called Images which has two columns:

  • ID (A uniqueidentifier column with ROWGUIDCOL properties)
  • Doc (A varbinary column with the FILESTREAM property)

The ID column is a prerequisite to creating a FILESTREAM table. A non-null unique column with ROWGUID properties must exist or the table create statement will simply throw an error.

If I script out the table to a new query window it looks like this:

CREATE TABLE [dbo].[Images](
[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[Doc] [varbinary](max) FILESTREAM NULL,
UNIQUE NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]

Inserting an Object into the FILESTREAM Table

This bit is actually incredibly easy, because essentially all we need to do is insert a new GUID into ID and turn our object into a VARBINARY(MAX) datatype to insert into Doc. There are a couple of different ways you can do the latter, but I choose to use the OPENROWSET function in a sub-select, just because I find it easier to understand.

So let’s say we have an image called sqlserver.png at C:\images. Actually, here it is:

sqlserver

To put that image into our FILESTREAM table, we simply do the following:

USE MyImages

INSERT INTO Images (
Id
,Doc
)
VALUES (
NEWID()
,(
SELECT *
FROM OPENROWSET(BULK N'C:\images\sqlserver.png', SINGLE_BLOB) AS Document1
)
);

If we then explore the Images table, we’ll see the following results:

fsresults

Our image is now in SQL Server, as a record in our FILESTREAM table Images.

Retrieving FILESTREAM data from SQL Server

A lot of people ask me if I can provide them with a query or function in order to ‘retrieve’ FILESTREAM objects (images, documents, pdf’s etc) directly from SQL Server. I also get a few calls every now and again asking for access the FILESTREAM windows share, from users (usually developers) who want to browse the files which have been inserted into FILESTREAM.

It can be difficult to grasp at first, but the truth is it’s not really possible. It’s certainly not supported.

The follow up questions are usually these:

  1. Where are the actual files stored?
  2. How do I access them?

If I’m thinking as a DBA, the answer to question 1 should technically be “none of your business”, but I’m like to show people to emphasise the point that putting a file in FILESTREAM is not the same as putting the file in a shared folder.

To find the file, go to the location of your FILESTREAM filegroup. In my case it was C:\DATA\filestream1

Once there, you can mooch around and find your file. Good luck identifying it though, and even more so if you want to try and open it (please don’t try…)

Question 2 is a little harder to answer, but in as concise of a way as possible, the answer is “through a client application developed specifically to open and stream files from FILESTREAM in SQL Server”

Now, I’m not much of a developer, but I’m going to have a crack at writing a bit of C# in ASP.net to fetch our little image.

Ready? Ok… (dev’s please don’t murder me for my code)

Reading FILESTREAM objects with C# and ASP.net

First create a new C# ASP.net project in Visual Studio. I’m just going to place a button control on the default.aspx page and we’ll do all this code in the Button’s click event. The idea will be on click of the button we’ll make a connection to the FILESTREAM database, get the row storing our sqlserver.png image and open it up client-side.

Ok, in the button click event, we’ll start by opening a connection to our database. The connection string has been stored in web.config:

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Filestream"].ConnectionString);
con.Open();

Once we’ve got our connection we need to create the  SQL query and begin a transaction. The transaction is important as SQL Server expects FILESTREAM reads to take place within an open consistent transaction block. Notice how we use the PathName() method of the Doc column. The output of this is a sort of verbose virtual path to the file.

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = ("SELECT TOP 1 "
+ (" Doc.PathName() AS filePath, "
+ (" GET_FILESTREAM_TRANSACTION_CONTEXT() AS txContext "
+ ("FROM Images"))));

SqlTransaction trn = con.BeginTransaction("ItemTran");
cmd.Transaction = trn;

We’ll now create and execute a reader then use the SqlFileStream class to read the file, using the filePath value from the query above:

SqlDataReader reader;
reader = cmd.ExecuteReader();

if (reader.Read())
{
string filePath = reader.GetString(0);
object objContext = reader.GetValue(1);
byte[] txContext = (byte[])objContext;
SqlFileStream fs = new SqlFileStream(filePath, txContext, FileAccess.Read);

At this point we can do a few things, but what we’re going to do in this example is copy the file to our c:\temp\ folder so we can open it. You probably won’t do this in the real world, but it just proves the point that we can get the image from FILESTREAM and read it client-side.

//create or open the stream at c:\temp
FileStream fStream = new FileStream("C:\\temp\\sqlserver.png",
FileMode.OpenOrCreate);
//copy from FILESTREAM to file location using 4k buffer
fs.CopyTo(fStream, 4096);
//close the stream
fStream.Close();
}

Finally, we’ll open the file:

System.Diagnostics.Process.Start("C:\\temp\\sqlserver.png");

All being well, you should get a pop up of the image. Yes, the one we inserted into our FILESTREAM table all those sentences ago…

fsopen

Summary

I hope you’ve seen how easy it is to insert files into your FILESTREAM table. As for retrieving those same files later, I’m not saying it’s easy, but even for a non-developer type like myself I think it’s pretty straight-forward how to develop a client application to access and work with FILESTREAM objects in our database.

If you have any queries, comments or questions, please just ask.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s