Using FILESTREAM to Write Data
For this example, consider a simple scenario that takes some user input, converts that into a byte array, and stores the information in the Picture
column of the Product
table. To follow along, create a Visual C# Windows Application named FileStreamExample
. To the new project's default form, add a Button named btnWriteFile
, a TextBox named txtInput
and a ListBox named lstResults
. After that, double-click on the button to create a Click
event handler containing the code shown in Listing 1
, which begins by retrieving the connection string from app.config
using the ConfigurationManager.ConnectionStrings
string connectionString = ConfigurationManager.ConnectionStrings
The connection string is stored in the app.config
file as follows:
<?xml version="1.0" encoding="utf-8" ?>
persist security info=False;"/>
Next it opens a connection to the database, and assigns appropriate values to the SqlCommand object to retrieve the Products
table row where ProductID
command.Connection = connection;
//Get the PathName of the File from the database
command.CommandText = "SELECT Picture.PathName(), "
+ "GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Product "
+ "WHERE ProductID = 1";
The SQL statement specifies the new function GET_FILESTREAM_TRANSACTION_CONTEXT ()
that retrieves a reference to the transaction in which the session is currently running. You can bind the FILESTREAM
file system operations to that transaction; however, the transaction must have already been started, and must not yet be aborted or committed. The function returns NULL
when no explicitly-started transaction is available.
So, the code creates a new SqlTransaction object by calling the SqlConnection.BeginTransaction()
method and assigning it to the SqlCommand object:
SqlTransaction transaction =
command.Transaction = transaction;
At this point, Listing 1
invokes the ExecuteReader()
method, executing the SQL statement. After executing the query, it retrieves the path of the file stream and assigns it to a local variable:
string path = reader.GetString(0);
You need a stream to write to a file, so the next step is to create an instance of the SqlFileStream class, supplying the path, transaction context, file access enumeration, file options enumeration, and allocation size:
SqlFileStream stream = new SqlFileStream(path,
The SqlFileStream class is a new class (introduced with SQL Server 2008) that provides access to FILESTREAM
column data as a sequence of bytes. Table 1 provides a brief description of the properties exposed by the SqlFileStream class.
Table 1. SqlFileStream Class Properties: The table lists and describes the properties exposed by the SqlFileStream class.
||Returns the logical path of the SqlFileStream object that is supplied to the constructor.
||Allows you to specify the transaction context associated with the SqlFileStream object.
||Returns the length of the current stream in bytes.
||Allows you to specify the position within the current stream.
||Specified in milliseconds, this property determines the timeout period for the stream at the time of reading.
||Specified in milliseconds, this property determines the timeout period for the stream at the time of writing.
Next, Listing 1
retrieves the user input, converts that to a byte array, and writes that to the file stream.
string contents = txtInput.Text;
Finally Listing 1
commits the transaction by calling the SqlTransaction.Commit()
That's the basic process for writing to a database-managed file using the FILESTREAM
feature. Now that you know how to write to a FILESTREAM
column, reading from the column will be simpler.