Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.


Managing Files with SQL Server 2008's FILESTREAM Feature : Page 3

SQL Server's new FILESTREAM feature simplifies the process of keeping file-based data, such as images, in sync with relational data.




Application Security Testing: An Integral Part of DevOps

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 property:

string connectionString = ConfigurationManager.ConnectionStrings ["fileStreamDB"].ConnectionString;

The connection string is stored in the app.config file as follows:

<?xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="fileStreamDB" connectionString="server=localhost\SqlServer2008; database=FILESTREAMExample;integrated security=SSPI; persist security info=False;"/> </connectionStrings> </configuration>

Next it opens a connection to the database, and assigns appropriate values to the SqlCommand object to retrieve the Products table row where ProductID is 1:

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 = connection.BeginTransaction(IsolationLevel.ReadCommitted); 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, (byte[])reader.GetValue(1), FileAccess.Write, FileOptions.SequentialScan, 0);

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.
Property Description
Name Returns the logical path of the SqlFileStream object that is supplied to the constructor.
TransactionContext Allows you to specify the transaction context associated with the SqlFileStream object.
Length Returns the length of the current stream in bytes.
Position Allows you to specify the position within the current stream.
ReadTimeout Specified in milliseconds, this property determines the timeout period for the stream at the time of reading.
WriteTimeout 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; stream.Write((System.Text.Encoding.ASCII.GetBytes(contents)), 0, contents.Length); stream.Close();

Finally Listing 1 commits the transaction by calling the SqlTransaction.Commit() method.


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.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date