The last week I have been working on a kind of file transfer protocol that shall be used to implement file access for a file explorer in a web portal that doesn’t have direct to the database server where files are stored. In addition, the portal needs load balansing where the web serveres don’t have access to each other resources directly. As a result, I needed to implement this file transfer protocol for sending and receiving file data between the web- and application servers. Each of the application servers have access to the database servere where the documents are stored as BLOBs.
During the implement of the protocol, I realized that I needed to do each task step-by-step. First you need to create or update a file header with the essential information, such as id, name, mime type, file size, “is directory” bit, description and CRC. This file header must be inserted if the file doesn’t exist, otherwise updated with new information.
When the file header is inserted or updated, you can write file data to the varbinary column. This column Items.Content is defined as Varbinary(MAX). One important issue is that when a row is created, all varbinary columns needs to initialized. This is fixed by writing a “null” value (not null, but 0x0) when the row is created (inserted). This 0x0 value must be overwritten when the actual file data is written in chunks to the database by controlling the write offset. Set offset to 0 (zero) and you will overwrite the data in the content column.
Write chunked data
The method WriteChunkedFileData below is just from the test program and the Connection is a property that returns a SQLConnection instans to the connected database. In addition, the test program is creating and closing the connection for each operation. This is not optimal programming but useful during testing and debugging.
When handling binary data writes to my varbinary column Content, I need to use the [Content].Write(chunk, offset, size) SQL command. This will write the chunk byte array to the Content column in the Items table, starting at position offset and write size number of bytes from the chunk array.
The offset is calculated based on the chunk number given in the input parameter idx.
Read chunked data
When reading data from a varbinary column I gladly discovered that I could use the substring function in SQL to retrieve the actual chunk I needed. No special handling. Like the WriteChunkedFileData method, we need to calculate the offset by the chunk number – this time from the chunkNo input parameter. Sorry for my inconsis coding standard from this test program 🙂
I will come back with the final FileTranferWCF is completed and optimized. Hope this help someone in the mean time 😉