Using SqlFileStream and LINQ togather

LINQ to SQL provides consistent way of accessing SQL database. It is possible to insert BLOB data into SQL FILESTREAM column by using LINQ insertion. However, in order to take benefit of fast FILESTREAM insertion, we have to use SqlFileStream instead of regular LINQ insertion. This post shows an example of using SqlFileStream and LINQ togather in one transaction.

First, this is a sample table that includes FILESTREAM column. FileStream column requires a ROWGUIDCOL, so we have RowId in the table.

CREATE TABLE MyTable
(
[Name] NVARCHAR(50) PRIMARY KEY,
-- ROWGUID column is required
[RowId] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
-- This is FILESTREAM column
[Data] VARBINARY(MAX) FILESTREAM NULL
)

The following code (Save() method) shows how to use LINQ and SqlFileStream togather in a single transaction. Multiple table insertion by LINQ can be performed, even if the sample only shows one table insertion.

class Program
{
   static void Main(string[] args)
   {
      Program p = new Program();
      p.Name = "Tom";
      p.FileName = @"C:\temp\Book1.xls";
      p.Save();
   }

   public string Name { get; set; }
   public string FileName { get; set; }

   void Save()
   {
      DbTransaction trans = null;
      MyDataClassesDataContext db = new MyDataClassesDataContext();
      try
      {
         db.Connection.Open();
         trans = db.Connection.BeginTransaction();
         db.Transaction = trans;

         // Insert data into Table
         // Add empty data to FILESTREAM column
         Guid rowId = Guid.NewGuid();
         var tab1 = new MyTable
         {
            Name = this.Name,
            RowId = rowId,
            Data = new System.Data.Linq.Binary(new byte[] { })
         };
         db.MyTables.InsertOnSubmit(tab1);
         
         // Can have more table insertion here.
         // ...

         db.SubmitChanges();

         // Save data file to FILESTREAM column by using SqlFileStream
         SqlConnection conn = db.Connection as SqlConnection;
         SqlCommand cmd = new SqlCommand("SELECT Data.PathName() FROM MyTable WHERE [Name]=@name", conn);
         cmd.Transaction = db.Transaction as SqlTransaction;

         SqlParameter paramName = new SqlParameter("@name", System.Data.SqlDbType.NVarChar, 50);
         paramName.Value = this.Name;
         cmd.Parameters.Add(paramName);
         object path = cmd.ExecuteScalar();
         if (path == DBNull.Value)
         {
            throw new InvalidOperationException();
         }

         cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
         byte[] transCtx = cmd.ExecuteScalar() as byte[];

         // Actual Insert into FILESTREAM
         SqlFileStream sqlFS = new SqlFileStream(path.ToString(), transCtx, FileAccess.ReadWrite);
         byte[] bytes = File.ReadAllBytes(this.FileName);
         sqlFS.Write(bytes, 0, bytes.Length);
         sqlFS.Close();

         trans.Commit();
      }
      catch
      {
         if (trans != null)
         {
            trans.Rollback();
         }
         throw;
      }
      finally
      {
         if (db.Connection != null && db.Connection.State == System.Data.ConnectionState.Open)
         {
            db.Connection.Close();
         }
      }
   }
}

SqlFileStream needs to acquire PathName (which is logical UNC of FILESTREAM column location) and transaction context before using SqlFileStream. So some SELECT statement was sent to SQL Server first. And then, SqlFileStream actually wrote BLOB data by using SqlFileStream.Write() method.
Once all data are inserted, Commit() method is called and finished the transaction.