Processing SQL Server FILESTREAM Data, Part 4 - Readin' and Writin'

In the prior installments in this series, I covered some background, FILESTREAM setup, and the file and table creation for this project. In this final installment, we'll finally see some C# code that I used to read and write the FILESTREAM data.

The Three "R"s

I was always amused by the irony that only one of the legendary Three "R"'s actually starts with an "R". Yet another indictment of American education? But I digress.

Before we work on the code to read FILESTREAM data, let's write to it first. First, we'll need a couple of structures to store information returned from various database operations.

public class InsertResult
{
    public decimal Id { get; set; }
    public byte[] timestamp { get; set; }
}

public class FilestreamInsertResult
{
    public decimal Id { get; set; }
    public byte[] timestamp { get; set; }
    public string FilestreamPath { get; set; }
    public byte[] FilestreamContext { get; set; }
}

public class FilestreamSelectResult
{
    public string FilestreamPath { get; set; }
    public byte[] FilestreamContext { get; set; }
}

Then, we can create a routine that mimics an SMTP send, but instead stores the email information to the database tables we created in "Processing SQL Server FILESTREAM Data, Part 3 - Creating Tables". Pardon the formatting in order to make the overlong lines fit within the blog template.

public bool Send( string fromAddress, string fromAlias, string recipients, 
                  string ccRecipients, string bccRecipients, 
                  string subject, string body, string[] attachments )
{
    try {
        using( IDbConnection connection = new SqlConnection( connectionString ) ) {
            connection.Open();

            using( IDbTransaction trans = connection.BeginTransaction() ) {
                try {
                    InsertResult insertId = connection.Query<insertresult>( 
                        @"INSERT INTO Notification.EmailMessages( 
                          TransmitStatusId, SubmitDate, TransmitDate, 
                          AttemptCount, FromAddress, FromAlias, ToAddresses, 
                          CcAddresses, BccAddresses, Subject, Body ) 
                          OUTPUT Inserted.EmailMessageId AS Id, Inserted.timestamp
                          VALUES( @TransmitStatusId, @SubmitDate, @TransmitDate, 
                          @AttemptCount, @FromAddress, @FromAlias, 
                          @ToAddresses, @CcAddresses, @BccAddresses, 
                          @Subject, @Body )", 
                          new
                          {
                              TransmitStatusId = Model.TransmitStatus.Queued,
                              SubmitDate = DateTime.Now,
                              TransmitDate = (System.Nullable<datetime>)null,
                              AttemptCount = 0,
                              FromAddress = fromAddress,
                              FromAlias = fromAlias,
                              ToAddresses = recipients,
                              CcAddresses = ccRecipients,
                              BccAddresses = bccRecipients,
                              Subject = subject,
                              Body = body
                          }, trans,
                          commandType: CommandType.Text ).FirstOrDefault();


                    if( attachments != null && attachments.Length > 0 ) {
                        for( int attachmentIdx = 0; attachmentIdx < attachments.Length; attachmentIdx++ ) {
                            FilestreamInsertResult filestreamId =
                                connection.Query<filestreaminsertresult>( 
                                    @"INSERT INTO Notification.EmailAttachments( EmailMessageId, 
                                      AttachmentFileId, SequenceNum, Filename, FileData ) 
                                      OUTPUT Inserted.EmailAttachmentId AS Id, 
                                      Inserted.timestamp, 
                                      Inserted.FileData.PathName() AS FilestreamPath,
                                      GET_FILESTREAM_TRANSACTION_CONTEXT() AS FilestreamContext
                                      VALUES( @EmailMessageId, NEWID(), @SequenceNum, @Filename, 0x00 )",
                                      new
                                      {
                                          EmailMessageId = insertId.Id,
                                          SequenceNum = attachmentIdx + 1,
                                          Filename = Path.GetFileName( attachments[ attachmentIdx ] )
                                      }, trans,
                                      commandType: CommandType.Text ).FirstOrDefault();

                            const int BUFSIZ = 32768;
                            using( Stream sqlFilestream = new SqlFileStream( 
                                      filestreamId.FilestreamPath, filestreamId.FilestreamContext, 
                                      FileAccess.Write ) ) {
                                using( FileStream infileStream = 
                                            File.Open( attachments[ attachmentIdx ], 
                                                       FileMode.Open, FileAccess.Read, 
                                                       FileShare.None ) ) {
                                    infileStream.CopyTo( sqlFilestream, BUFSIZ );
                                    infileStream.Close();
                                }
                                sqlFilestream.Close();
                            }
                        }
                    }

                    trans.Commit();
                }
                catch {
                    trans.Rollback();
                    throw;
                }
            }

            connection.Close();

            return true;
        }
    }
    catch( Exception ex ) {
        logger.Error( "Error in Send() method", ex );
        throw;
    }
}

A couple of notes about the code shown above:

• The code uses Marc Gravell and Sam Saffron's superb Micro-ORM Dapper which I highly recommend. While religious wars rage over the use of Micro-ORMs vs heavy ORMs, I far prefer Dapper to other approaches.
• The INSERT statements use the SQL Server OUTPUT clause to return ID information about the inserted rows, which is a more efficient method than sending a subsequent SELECT query for the information.
• Once the streams have been opened, the .NET 4.0 CopyTo method will do a nice job of copying the bytes. If you're on an earlier version of the framework, this method can easily be created. See Jon Skeet's sample implementation here.

Once the email message has been inserted into the master table and we have its ID, we can then attempt to insert the attachments into their corresponding detail table. This is done in two steps:

1. Insert the metadata about the attachment to the EmailAttachments table. Once this is complete, you can retrieve a file name and context ID for streaming attachment data to the FILESTREAM.
2. Open the FILESTREAM using provided framework methods for doing so. Write the attachment data to the FILESTREAM.

Seems simple, but there is a subtlety. The INSERT statement to add the metadata must add at least one byte of data to the file using Transact-SQL. That is indicated by the null byte ("0x00") that is the last value of the statement. If you don't supply this, instead supplying NULL or, as I initially attempted, default, SQL Server will not create a file since you haven't given it any data. Consequently, the SQL Server PathName() function will return NULL and the call to open the SqlFileStream will fail unceremoniously.

There are two ways that I could have submitted the attachment data to SQL Server, as the last value of the INSERT statement to the EmailAttachments table, or using streaming as I did in the example. I chose the latter so that, in the case of very large attachment, I could stream the file in chunks rather than reading the entire file into memory to submit via INSERT statement. This is less resource intensive under the heavy load I expect for this utility.

I then created a separate Windows service to read the messages, attempt to send via SMTP, log successes and failures, and queue for retrying a certain number of times. The heart of the portion that reads the attachments looks quite similar to the write operation.

public void GetAttachment( int attachmentId, string outfileName )
{
    try {
        using( IDbConnection connection = new SqlConnection( connectionString ) ) {
            connection.Open();

            using( IDbTransaction trans = connection.BeginTransaction() ) {
                try {
                    FilestreamSelectResult fileInfo =
                        connection.Query<filestreamselectresult>( 
                                @"SELECT FileData.PathName() AS FilestreamPath, 
                                  GET_FILESTREAM_TRANSACTION_CONTEXT() AS FilestreamContext
                                  FROM Notification.EmailAttachments 
                                  WHERE EmailAttachmentId = @EmailAttachmentId",
                                  new
                                  {
                                      EmailAttachmentId = attachmentId
                                  },
                                  transaction: trans,
                                  commandType: CommandType.Text).FirstOrDefault();

                    const int BUFSIZ = 32767;
                    using(FileStream outfileStream = File.Open( 
                            outfileName, FileMode.Create, 
                            FileAccess.Write, FileShare.None)) {

                        using( Stream sqlFilestream = 
                                    new SqlFileStream( fileInfo.FilestreamPath, 
                                            fileInfo.FilestreamContext, FileAccess.Read ) ) {
                            sqlFilestream.CopyTo( outfileStream, BUFSIZ )
                            sqlFilestream.Close();
                        }
                        outfileStream.Close();
                    }

                    connection.Close();

                }
                catch {
                    // Log error here
                    throw;
                }
            }

            connection.Close();
        }
    }
    catch( Exception ex ) {
        logger.Error( "Error in GetAttachment method.", ex );
        throw;
    }
}
</filestreamselectresult>

Some notes about the code shown above:

• I created a result class, shown earlier in this post, for retaining the file path and transaction context returned from the query.
• Note that you must create a transaction for the SELECT in order for the GET_FILESTREAM_TRANSACTION_CONTEXT method to return a context that can be used in the SqlFileStream constructor.
• Once again, I have used the CopyTo method to move the bytes between the streams.

Summary

That finishes the heart of the SQL Server FILESTREAM operations for the utility I was constructing. The real trick of it was the initial configuration and understanding the process. Hopefully, this series of articles will help someone past the problems I encountered. Good luck and good coding!