Import binary data to FILESTREAM column / Export binary data from FILESTREAM column

My previous post (How to use FILESTREAM) explained how to setup and use FILESTREAM. In this post, I will be little more specific about binary data in FILESTREAM.

The following code shows how to import binary file to FILESTREAM column by using ADO.NET and how to export binary data from the FILESTREAM column. Let's assume we're using the same sample table as in the previous post.

using System.IO;
using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        string connStr = "Data Source=.;Integrated Security=true;Initial Catalog=FSDB";        

        static void Main(string[] args)
        {
            Program p = new Program();
            p.SavePictureFileToDB(@"c:\test\input.jpg");
            p.ReadPictureDataAndCreaetPictureFile(@"c:\test\output.jpg");
        }

        void SavePictureFileToDB(string picFile)
        {
            byte[] pic = File.ReadAllBytes(picFile);            

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                //@data is FileStream column / id=100
                string sql = "INSERT FSTable VALUES (NEWID(), 100, @data)";  
                SqlParameter param1 = new SqlParameter("@data", System.Data.SqlDbType.VarBinary);
                param1.Size = -1;
                param1.Value = pic;

                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.Add(param1);
                cmd.ExecuteNonQuery();                
            }
        }

        void ReadPictureDataAndCreaetPictureFile(string outputFilename)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                string sql = "SELECT Data FROM FSTable WHERE Id=100";
                SqlCommand cmd = new SqlCommand(sql, conn);
                object objResult = cmd.ExecuteScalar();
                byte[] bytes = (byte[])objResult;

                File.WriteAllBytes(outputFilename, bytes);
            }
        }
    }
}

SavePictureFileToDB() reads picture file and put the binary data into FILESTREAM column by using INSERT statement. ReadPictureDataAndCreaetPictureFile() reads binary data from the FILESTREAM column and create a new picture file with those bytes data. As you can see, the code is pretty much the same as handling varbinary data type. This approach will be good if the binary data is not that big and there is no performance issue on it. However, if the binary data is big and so performance is slow, you can consider using SqlFileStream, which is faster access to the data.