Working with FileTable and ASP.NET C# (Open a PDF)

Question

I am trying to open a PDF in the browser (or download) PDF's when a user clicks on a link. I found a similar question on SO (Opening FileTable Files in c# / .net 4) and was trying to implement the answer in my code, but have not had any luck. I really just need to see a complete example of how to open a file from a FileTable in ASP.NET / C#.

Code:

public FileResult Download(int? id)
{
    //start of my test code
    Document document = db.Documents.Find(id);
    string fileName;
    byte[] fileData;
    System.Guid path = document.DocumentPath;

    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["FT_ConnectionString"].ConnectionString))
    {
        string sql = "SELECT TOP 1 file_stream, name, file_type, cached_file_size FROM FTAdvisoryOpinions WHERE stream_id = @SID";

        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.Add("@SID", SqlDbType.UniqueIdentifier).Value = path;
            connection.Open();
            using (var reader = command.ExecuteReader())
            {
                if (reader.Read())
                {
                    //file_stream = 0
                    //name = 1
                    //file_type = 2
                    //cached_file_size = 3

                    long fileSize = reader.GetInt64(3); //think this might be wrong
                    string contentType = reader.GetString(2);


                    fileData = reader.GetBytes(0, 2, fileData, 0, fileSize);  //says I have some invalid arguments
                    fileName = reader.GetString(1);
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

                    return File(fileData, System.Net.Mime.MediaTypeNames.Application.Octet, fileName);
                }
                else
                {
                    connection.Close();
                }
            }

            return null; // just returning null here til I figure it out.
        }
    }
}

Answer 1

Instead of reading the blob size through GetInt64 on a stored field, you can actually get this information from the call to GetBytes. Call the method once where you pass a 0 size. The result will tell you the size of data. Then call the method again with an array large enough to hold the data to actually read the data.

The main thing is that GetBytes doesn't return the bytes, it returns info on how many bytes are left to be read, and you pass in the data structure it should read bytes into as an argument.

PS: If your data is large you might wanna read the stream incrementally with a maximum buffer size. But you should be fine without, at least for starters.

PPS: As an aside, I want to mention that you don't really need to close your connection explicitly since the using block will dispose it and disposing it will close it.