In the previous entry we covered how to upload a file to SQL Server using the FILESTREAM, new to SQL Server 2008. In this post we will look at retrieving a file from SQL Server using FILESTREAM. If you missed yesterday’s installment, a simple front end was created, the full project can be found at the Code Gallery site http://code.msdn.microsoft.com/FileStreamFTS .
The interface is very simple:
The grid is a Data View Grid that shows the ID and Document information from the table we previously created. (If you want to see the code to populate the grid see the project.) The user picks a row, then clicks on the Get File button.
private void btnGetFile_Click(object sender, EventArgs e)
{
// Reset in case it was used previously
lblStatus.Text = "";
if (dgvFiles.CurrentRow != null)
{
// Grab the ID (Primary Key) for the current row
int ID = (int)dgvFiles.CurrentRow.Cells[0].Value;
// Now Save the file to the folder passed in the second
// paramter.
FileTransfer.GetFile2(ID, @"D:\Docs\Output\");
// And let user know it’s OK
lblStatus.Text = "File Retrieved";
}
}
The code is very simple, the heart of it is the FileTransfer.GetFile static method. Two values are passed in, the integer ID, which is the primary key from the database, and the path to save the file to. Here I simply hard coded a path, in a real life application you will want to give the user the ability to enter a path. Let’s take a look at the GetFile routine.
public static void GetFile(int ID, string outputPath)
{
// Setup database connection
SqlConnection sqlConnection = new SqlConnection(
"Integrated Security=true;server=(local)");
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = sqlConnection;
try
{
sqlConnection.Open();
// Everything we do with FILESTREAM must always be in
// the context of a transaction, so we’ll start with
// creating one.
SqlTransaction transaction
= sqlConnection.BeginTransaction("mainTranaction");
sqlCommand.Transaction = transaction;
// The SQL gives us 3 values. First the PathName() method of
// the Document field is called, we’ll need it to use the API
// Second we call a special function that will tell us what
// the context is for the current transaction, in this case
// the "mainTransaction" we started above. Finally it gives
// the name of the document, which the app will use when it
// creates the document but is not strictly required as
// part of the FILESTREAM.
sqlCommand.CommandText
= "SELECT Document.PathName()"
+ ", GET_FILESTREAM_TRANSACTION_CONTEXT() "
+ ", DocumentName "
+ "FROM FileStreamFTS.dbo.DocumentRepository "
+ "WHERE ID=@theID ";
sqlCommand.Parameters.Add(
"@theID", SqlDbType.Int).Value = ID;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.Read() == false)
{
throw new Exception("Unable to get BLOB data");
}
// OK we have some data, pull it out of the reader into locals
string path = (string)reader[0];
byte[] context = (byte[])reader[1];
string outputFilename = (string)reader[2];
int length = context.Length;
reader.Close();
// Now we need to use the API we declared at the top of this class
// in order to get a handle.
SafeFileHandle handle = OpenSqlFilestream(
path
, DESIRED_ACCESS_READ
, SQL_FILESTREAM_OPEN_NO_FLAGS
, context
, (UInt32)length, 0);
// Using the handle we just got, we can open up a stream from
// the database.
FileStream databaseStream = new FileStream(
handle, FileAccess.Read);
// This file stream will be used to copy the data to disk
FileStream outputStream
= File.Create(outputPath + outputFilename);
// Setup a buffer to hold the streamed data
int blockSize = 1024 * 512;
byte[] buffer = new byte[blockSize];
// There are two ways we could get the data. The simplest way
// is to read the data, then immediately feed it to the output
// stream using it’s Write feature (shown below, commented out.
// The second way is to load the data into an array of bytes
// (here implemented using the generic LIST). This would let
// you manipulate the data in memory, then write it out (as
// shown here), reupload it to another data stream, or do
// something else entirely.
// If you want to go the simple way, just remove all the
// fileBytes lines and uncomment the outputStream line.
List<byte> fileBytes = new List<byte>();
int bytesRead = databaseStream.Read(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
bytesRead = databaseStream.Read(buffer, 0, buffer.Length);
//outputStream.Write(buffer, 0, buffer.Length);
foreach (byte b in buffer)
fileBytes.Add(b);
}
// Write out what is in the LIST to disk
foreach (byte b in fileBytes)
{
byte[] barr = new byte[1];
barr[0] = b;
outputStream.Write(barr, 0, 1);
}
// Close the stream from the databaseStream
databaseStream.Close();
// Write out the file
outputStream.Close();
// Finally we should commit the transaction.
sqlCommand.Transaction.Commit();
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlConnection.Close();
}
return;
}
The routine kicks off by opening a connection, then establishing a transaction. Remember from the previous lesson that every time you work with a FILESTREAM it has to be in a transaction. Next we basically duplicate the SQL used in the previous lesson, returning the path name, transaction context, and document name. The only difference is we pass in the ID as a parameter. With that, just like with the previous example we call the OpenSqlFilestream API. Note a difference, in this example the second parameter is “DESIRED_ACCESS_READ” as opposed to the write access we indicated previosly.
Once we have the “handle” we can create a FileStream for reading from the database. In this example I loop through the file stream, loading the data into a LIST of bytes. Once in memory we are free to work with it as we need to. In this example I simply loop back through the generic List and write the data to the file stream we opened on the disk for writing. If all you are doing is writing, it would be somewhat more efficient to write the code like so:
int bytesRead = databaseStream.Read(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
bytesRead = databaseStream.Read(buffer, 0, buffer.Length);
outputStream.Write(buffer, 0, buffer.Length);
}
// Close the stream from the databaseStream
databaseStream.Close();
I simply eliminate the local byte array and write the buffer directly to the disk. Either way, the remainder is simple, just closing all the streams, commiting the transaction and closing the database connection.
This concludes the series on how to use FILESTREAM, in future posts we look into how to do Full Text Search with FILESTREAM stored objects.
Arcane Code 
I tried out the sample code as I am interested in archiving files into sql database. I tested with doc and jpeg files. I did find that many of the doc files retrieved corrupted – though not all just to make it interesting. In the same microsoft word they were created in the retrieved file end up looking like:
QµI§RáÜfR ©ò TÛç{}kî]·añwÓlùe
jŸ±ÆJn±4Ê Áƒ2ÇèÆMåY<\_(‹ŸÔZüÈ ‘3ý2éxjr•»˜Â
Hi,
How to update a BLOB that is stored on the fileserver. When I try using almost the same code as add, i get an updated BLOB however, the old file is still there, accessible which I don’t want. Any tips?
If I recall correctly, the file will stay until the next backup occurs. After the backup the old version of the file should be removed by SQL Server. Try either doing or waiting for the next backup and it should be gone.