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

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.

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.

   transaction.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.

Previous Page

Next Page