FILESTREAM Data in SQL Server 2008 (ADO.NET)

Article
10/26/2011

SQL Server 2008 introduces the FILESTREAM storage attribute for binary (BLOB) data stored in a varbinary(max) column. SQL Server has always provided the capability to store binary data, but working with it has required special handling. Unstructured data, such as text documents, images and video, is often stored outside of the database, making it difficult to manage.

Note

You must install the .NET Framework 3.5 SP1 (or later) to work with FILESTREAM data using SqlClient.

Specifying the FILESTREAM attribute on a varbinary(max) column causes SQL Server to store the data on the local NTFS file system instead of in the database file. Although it is stored separately, you can use the same Transact-SQL statements that are supported for working with varbinary(max) data that is stored in the database.

SqlClient Support for FILESTREAM

The .NET Data Provider for SQL Server, System.Data.SqlClient, supports reading and writing to FILESTREAM data using the SqlFileStream class defined in the System.Data.SqlTypes namespace. SqlFileStream inherits from the System.IO.Stream class, which provides methods for reading and writing to streams of data. Reading from a stream transfers data from the stream into a data structure, such as an array of bytes. Writing transfers the data from the data structure into a stream.

Creating the SQL Server Table

The following Transact-SQL statements creates a table named employees and inserts a row of data. Once you have enabled FILESTREAM storage, you can use this table in conjunction with the code examples that follow. The links to resources in SQL Server 2008 Books Online are located at the end of this topic.

CREATE TABLE employees
(
  EmployeeId INT  NOT NULL  PRIMARY KEY,
  Photo VARBINARY(MAX) FILESTREAM  NULL,
  RowGuid UNIQUEIDENTIFIER  NOT NULL  ROWGUIDCOL
  UNIQUE DEFAULT NEWID()
)
GO
Insert into employees
Values(1, 0x00, default)
GO

Reading FILESTREAM Data Example

The following code fragment demonstrates how to read data from a FILESTREAM. The code gets the logical path to the file, setting the FileAccess to Read and the FileOptions to SequentialScan. The code then reads the bytes from the SqlFileStream into the buffer. The bytes are then written to the console window.

using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();
    SqlCommand command = new SqlCommand("", connection);

    SqlTransaction tran = connection.BeginTransaction(
       System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    command.CommandText = 
        "select Top(1) Photo.PathName(), " 
        + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for the file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

            // Create the SqlFileStream
            FileStream fileStream = new SqlFileStream(path,
            (byte[])reader.GetValue(1),
                FileAccess.Read,
                FileOptions.SequentialScan, 0);

            // Read the contents as bytes and write them to the console
            for (long index = 0; index < fileStream.Length; index++)
            {
                Console.Write(fileStream.ReadByte());
            }
            fileStream.Close();
        }
    }
    tran.Commit();
}

Overwriting FILESTREAM Data Example

The following code fragment demonstrates how to write data to a FILESTREAM in which all existing data is overwritten. The code gets the logical path to the file and creates the SqlFileStream, setting the FileAccess to Write and the FileOptions to SequentialScan. A single byte is written to the SqlFileStream, replacing any data in the file.

using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();

    SqlCommand command = new SqlCommand("", connection);
    command.CommandText = "select Top(1) Photo.PathName(), "
    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";

    SqlTransaction tran = connection.BeginTransaction(
        System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for file 
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

            // Create the SqlFileStream
            FileStream fileStream = new SqlFileStream(path,
                (byte[])reader.GetValue(1),
                FileAccess.Write,
                FileOptions.SequentialScan, 0);

            // Write a single byte to the file. This will
            // replace any data in the file.
            fileStream.WriteByte(0x01);

            fileStream.Close();
        }
    }
    tran.Commit();
}

Inserting FILESTREAM Data Example

The following example demonstrates how to write data to a FILESTREAM by using the Seek method to append data to the end of the file. The code gets the logical path to the file and creates the SqlFileStream, setting the FileAccess to ReadWrite and the FileOptions to SequentialScan. The code uses the Seek method to seek to the end of the file, appending a single byte to the existing file.

using (SqlConnection connection = new SqlConnection(
    connStringBuilder.ToString()))
{
    connection.Open();

    SqlCommand command = new SqlCommand("", connection);
    command.CommandText = "select Top(1) Photo.PathName(), "
    + "GET_FILESTREAM_TRANSACTION_CONTEXT () from employees";

    SqlTransaction tran = connection.BeginTransaction(
        System.Data.IsolationLevel.ReadCommitted);
    command.Transaction = tran;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Get the pointer for file
            string path = reader.GetString(0);
            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

            FileStream fileStream = new SqlFileStream(path,
                (byte[])reader.GetValue(1),
                FileAccess.ReadWrite,
                FileOptions.SequentialScan, 0);

            // Seek to the end of the file
            fs.Seek(0, SeekOrigin.End);

            // Append a single byte 
            fileStream.WriteByte(0x01);
            fileStream.Close();
        }
    }
    tran.Commit();
}

Resources in SQL Server Books Online

The complete documentation for FILESTREAM is located in the following sections in SQL Server 2008 Books Online.

Topic	Description
Designing and Implementing FILESTREAM Storage	Provides links to FILESTREAM documentation and related topics.
FILESTREAM Overview	Describes when to use FILESTREAM storage and how it integrates the SQL Server Database Engine with an NTFS file system.
Getting Started with FILESTREAM Storage	Describes how to enable FILESTREAM on an instance of SQL Server, how to create a database and a table to stored FILESTREAM data, and how to manipulate rows containing FILESTREAM data.
Using FILESTREAM Storage in Client Applications	Describes the Win32 API functions for working with FILESTREAM data.
FILESTREAM and Other SQL Server Features	Provides considerations, guidelines and limitations for using FILESTREAM data with other features of SQL Server.

FILESTREAM Data in SQL Server 2008 (ADO.NET)

SqlClient Support for FILESTREAM

Creating the SQL Server Table

Reading FILESTREAM Data Example

Overwriting FILESTREAM Data Example

Inserting FILESTREAM Data Example

Resources in SQL Server Books Online

See Also

Concepts

Other Resources

Additional resources