Creating C# client program for SQL Server 2012 FILETABLE

FILETABLE is a great new feature on SQL Server 2012, it automatically creates the table schema and maintain the relational and file data integrity for you. However, writing client application program code to access the FILETABLE is not as simple as creating a FILETABLE in sql server. The sample code below demonstrates how to write the client program in C#.

public static byte[] ReadAnalystReportPDF(string pathName)
{
  byte[] buffer = new byte[0];
  if (CommonUtil.IsEmpty(pathName)) return buffer;
  string connStr = ConfigurationManager.ConnectionStrings["archiveDbConnectionString"].ConnectionString;
  using (SqlConnection conn = new SqlConnection(connStr))
  {
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction();
    SqlCommand cmd = conn.CreateCommand();
    cmd.Transaction = transaction;
    cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
    Object obj = cmd.ExecuteScalar();
    byte[] txContext = (byte[])obj;
    SqlFileStream fileStream = new SqlFileStream(pathName, txContext, FileAccess.Read);
    buffer = new byte[(int)fileStream.Length];
    fileStream.Read(buffer, 0, buffer.Length);
    fileStream.Close();
    cmd.Transaction.Commit();
    }
  return buffer;
}

public static void UploadAnalystReportPDF(string fileName, Stream source)
{
  string fileNameNoExt = Path.GetFileNameWithoutExtension(fileName);
  string fileExt = Path.GetExtension(fileName);

  string filestreamPath;
  byte[] filestreamTxn;

  string connStr = ConfigurationManager.ConnectionStrings["archiveDbConnectionString"].ConnectionString;
  using (TransactionScope ts = new TransactionScope())
  {
    using (SqlConnection conn = new SqlConnection(connStr))
    {
      conn.Open();

      #region uspUploadAnalystReport
      SqlCommand cmd1 = conn.CreateCommand();
      cmd1.CommandType = CommandType.StoredProcedure;
      cmd1.CommandText = "uspUploadAnalystReport";
      SqlParameter cmd1Param1 = cmd1.Parameters.Add("@filename", SqlDbType.NVarChar);
      cmd1Param1.Value = fileNameNoExt;
      SqlParameter cmd1Param2 = cmd1.Parameters.Add("@ext", SqlDbType.NVarChar);
      cmd1Param2.Value = fileExt;
      using (SqlDataReader rdr = cmd1.ExecuteReader(CommandBehavior.SingleRow))
      {
        rdr.Read();
        filestreamPath = rdr.GetSqlString(0).Value;
        filestreamTxn = rdr.GetSqlBinary(1).Value;
        rdr.Close();
      }
      #endregion

      #region save file content
      const int BlockSize = 1024 * 512;
      try
      {
        using (SqlFileStream dest = new SqlFileStream(filestreamPath, filestreamTxn, FileAccess.Write))
        {
          byte[] buffer = new byte[BlockSize];
          int bytesRead;
          while ((bytesRead = source.Read(buffer, 0, buffer.Length)) > 0)
          {
            dest.Write(buffer, 0, bytesRead);
            dest.Flush();
          }
          dest.Close();
        }
      }
      finally
      {
        source.Close();
      }
      #endregion
    }
    ts.Complete();
  }
}

The pathName parameter of the first C# method is obtained by file_stream.PathName() in T-SQL.
Below is the stored procedure being called by the second C# method:

CREATE PROCEDURE uspUploadAnalystReport
@filename nvarchar(150),
@ext nvarchar(50)
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;

DECLARE @name nvarchar(255) = @filename + @ext

IF @@TRANCOUNT = 0
BEGIN
THROW 51000, 'Stored Proc must be called within a transaction.', 1;
END

DECLARE @inserted TABLE (path_name nvarchar(max), filestreamTxn varbinary(max), path_locator_str nvarchar(4000))

DECLARE @i int = 0
WHILE 1 = 1
BEGIN
IF EXISTS (SELECT 1 FROM AnalystReports WHERE name = @name)
BEGIN
SET @i += 1;
SET @name = @filename + '_' + CAST(@i AS varchar(50)) + @ext
END
ELSE
BEGIN
BREAK;
END
END

INSERT AnalystReports (name, file_stream)
OUTPUT
inserted.file_stream.PathName(),
GET_FILESTREAM_TRANSACTION_CONTEXT(),
CAST(inserted.path_locator AS nvarchar(4000)) INTO @inserted
VALUES (@name, (0x));

SELECT * FROM @inserted;
END