The best way to work with documents in a database is via a .Net application. I created a simple Windows forms project to access the table I created in previous lessons. I named the application FileLoader, you can the entire project at the Code Gallery site http://code.msdn.microsoft.com/FileStreamFTS .
The interface is very simple:
As you can see there are two main functions, the upper half uploads a file to the SQL Server. The lower half displays the files already in the table, lets the user pick one and then click the GetFile button to save it locally. Today we’ll look at the Upload File functionality. Here is the code:
private void btnUploadFile_Click(object sender, EventArgs e)
{
// Reset in case it was used previously
lblStatus.Text = "";
// Make sure user entered something
if (txtFile.Text.Length == 0)
{
MessageBox.Show("Must supply a file name");
lblStatus.Text = "Must supply file name";
return;
}
// Make sure what user entered is valid
FileInfo fi = new FileInfo(txtFile.Text);
if (!fi.Exists)
{
MessageBox.Show("The file you entered does not exist.");
lblStatus.Text = "The file you entered does not exist.";
return;
}
// Upload the file to the database
FileTransfer.UploadFile(txtFile.Text);
// Refresh the datagrid to show the newly added file
LoadDataGridView();
// Let user know it was uploaded
lblStatus.Text = fi.Name + " Uploaded";
}
The real line of importance is the FileTransfer.UploadFile. This calls a static method in a class I named FileTransfer.cs. In order to use FILESTREAM there is an API call we have to make, so at the header area of the FileTransfer we have a lot of declarations. These are pretty much a straight copy from the MSDN help files.
//These contants are passed to the OpenSqlFilestream()
//API DesiredAccess parameter. They define the type
//of BLOB access that is needed by the application.
const UInt32 DESIRED_ACCESS_READ = 0x00000000;
const UInt32 DESIRED_ACCESS_WRITE = 0x00000001;
const UInt32 DESIRED_ACCESS_READWRITE = 0x00000002;
//These contants are passed to the OpenSqlFilestream()
//API OpenOptions parameter. They allow you to specify
//how the application will access the FILESTREAM BLOB
//data. If you do not want this ability, you can pass in
//the value 0. In this code sample, the value 0 has
//been defined as SQL_FILESTREAM_OPEN_NO_FLAGS.
const UInt32 SQL_FILESTREAM_OPEN_NO_FLAGS = 0x00000000;
const UInt32 SQL_FILESTREAM_OPEN_FLAG_ASYNC = 0x00000001;
const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING = 0x00000002;
const UInt32 SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH = 0x00000004;
const UInt32 SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN = 0x00000008;
const UInt32 SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS = 0x00000010;
//This structure defines the format of the final parameter to the
//OpenSqlFilestream() API.
//This statement imports the OpenSqlFilestream API so that it
//can be called in the Main() method below.
[DllImport("sqlncli10.dll", SetLastError = true, CharSet = CharSet.Unicode)]
static extern SafeFileHandle OpenSqlFilestream(
string Filestreamath,
uint DesiredAccess,
uint OpenOptions,
byte[] FilestreamTransactionContext,
uint FilestreamTransactionContextLength,
Int64 AllocationSize);
//This statement imports the Win32 API GetLastError().
//This is necessary to check whether OpenSqlFilestream
//succeeded in returning a valid / handle
[DllImport("kernel32.dll", SetLastError = true)]
static extern UInt32 GetLastError();
OK, with that out of the way, I’ve created a public, static method to upload the file. Here is the full routine:
public static void UploadFile(string fileName)
{
// Establish db connection
SqlConnection sqlConnection = new SqlConnection(
"Integrated Security=true;server=(local)");
SqlTransaction transaction = null;
// Create a File Info object so you can easily get the
// name and extenstion. As an alternative you could
// choose to pass them in, or use some other way
// to extract the extension and name.
FileInfo fi = new FileInfo(fileName);
try
{
// Open the file as a stream
FileStream sourceFile = new FileStream(fileName
, FileMode.OpenOrCreate, FileAccess.Read);
// Create the row in the database
sqlConnection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = sqlConnection;
cmd.CommandText = "INSERT INTO "
+ "FileStreamFTS.dbo.DocumentRepository"
+ "(DocumentExtension, DocumentName) VALUES (‘"
+ fi.Extension + "’, ‘"
+ fi.Name + "’)";
cmd.ExecuteNonQuery();
// Now upload the file. It must be done inside a transaction.
transaction = sqlConnection.BeginTransaction("mainTranaction");
cmd.Transaction = transaction;
cmd.CommandText = "SELECT Document.PathName(), "
+ "GET_FILESTREAM_TRANSACTION_CONTEXT() "
+ "FROM FileStreamFTS.dbo.DocumentRepository "
+ "WHERE ID=(select max(id) from FileStreamFTS.dbo.DocumentRepository)";
SqlDataReader rdr = cmd.ExecuteReader();
if (rdr.Read() == false)
{
throw new Exception("Could not get file stream context");
}
// Get the path
string path = (string)rdr[0];
// Get a file stream context
byte[] context = (byte[])rdr[1];
int length = context.Length;
rdr.Close();
// Now use the API to get a reference (handle) to the filestream
SafeFileHandle handle = OpenSqlFilestream(path
, DESIRED_ACCESS_WRITE
, SQL_FILESTREAM_OPEN_NO_FLAGS
, context, (UInt32)length, 0);
// Now create a true .Net filestream to the database
// using the handle we got in the step above
FileStream dbStream = new FileStream(handle, FileAccess.Write);
// Setup a buffer to hold the data we read from disk
int blocksize = 1024 * 512;
byte[] buffer = new byte[blocksize];
// Read from file and write to DB
int bytesRead = sourceFile.Read(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
dbStream.Write(buffer, 0, buffer.Length);
bytesRead = sourceFile.Read(buffer, 0, buffer.Length);
}
// Done reading, close all of our streams and commit the file
dbStream.Close();
sourceFile.Close();
transaction.Commit();
}
catch (Exception e)
{
if (transaction != null)
{
transaction.Rollback();
}
throw e;
}
finally
{
sqlConnection.Close();
}
}
First we open a connection to the SQL Server, then create a FileInfo object to make it simple to extract the file name and extension. Next a record is inserted into the database that will act as a place holder. It has the name of the file and the extension, but no file yet. I did go ahead and open a FileStream to the source file, located on the disk. We’ll need this later to upload the file.
Next you will see that I begin a transaction. Every time you work with a FILESTREAM it must always be in the context of a transaction. After that a SQL Data Reader is created that has three pieces of information. First, it calls the PathName() function for the Document field in our table. The PathName() will be needed later when we call the API. The second field is returned from the GET_FILESTREAM_TRANSACTION_CONTEXT function, and returns the transaction context for the transaction. Note this is not the name (in this example “mainTransaction”), but the context which is a special value. These two values are then copied into local variables which will be used in calling the OpenSqlFilestream API. In this example I also retrieve the DocumentName field, this is used by the code when it writes the file to the database, but is not strictly needed for the FILESTREAM.
Next you will see the call to the OpenSqlFilestream API, which returns a “handle”. This handle is then used to create a FileStream object. Using this newly created FileStream (here named dbStream) we can then upload the file. Now the main work begins. After setting up a buffer, we then simply read from the source file stream into the buffer, then write the exact same buffer to the database FileStream. The loop continues until there are no more bytes in the source.
At this point we are essentially done. We close the streams, commit the transaction, and in the finally block close the SQL database connection. The file should now be in the database. I do want to point out one thing. In the SQL to get the information to the row just uploaded, I use a subquery to get the max(id), essentially returning the last row just inserted. This is fine for this simple example, when the database has just one user. In your production systems where you are likely to have many users, however, you should use an alternate method to return the row you need. Otherwise two users could insert rows at the same time, and thus a conflict could occur with both of them getting back the same max(id). It will not happen often, but at some point it could happen and be very hard to debug.
This handled the uploading of files to the SQL Server via FILESTREAM, in the next installment we’ll look at how to retrieve the file we just uploaded.
Arcane Code 
Thanks arcanecode!
This is a great article. It was a big help to me.
Do you have any example that shows how to do the same with asp.net? Basically I need to upload multiple files to sql filestream table. But along with these files, I also have other information such as name, email, address, etc.
It should be done in one single transaction.
Thanks, Vikram
It is odd that I chose this particular web page to leave my comments, but boy! I must really thank the author from my heart for his wonderful, easy, elegant and pertinent approach to introduce new concepts of SQL Server 2008 (like FILESTREAM). I am particularly interested in using this new facet along with FTS to get the best of both worlds! I am sure, the author has more details with regard to FTS. Thanks and thanks again! May your tribe increase!