How to use SQL Server 2008 FILESTREAM datatype in ADO.Net?

FileStream allows you to store unstructured data (LOB) in  the file system instead of the database.

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();

}

 

You can read more on FileStream DataType here

For more examples on using FileStream Data in ADO.Net read here

 

Reference:

1.       New Data Types in SQL Server 2008

2.       SQL Server 2008 New DATETIME DataTypes

3.       SQL Server 2008: The New Data Types

4.       FILESTREAM Storage in SQL Server 2008