SqlFileStream: Returning stream vs byte array in HTTP response

Question

I'm a little confused around the issue of returning a byte array vs a stream in an HTTP Response using .net Web API.

I came across the following code:

        SqlConnection conn = new SqlConnection();
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "Select FileData.PathName() As FilePath, GET_FILESTREAM_TRANSACTION_CONTEXT() AS Context From FileStorage";
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        reader.Read();
        string filePath = (string)reader["FilePath"];

        byte[] fileBytes = (byte[])reader["Context"];
        SqlFileStream stream = new SqlFileStream(filePath, fileBytes, FileAccess.Read);

        result.Content = new StreamContent(stream);
        result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
        result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");

Question 1: Why would they return a Stream instead of a byte array in the HTTP Response?

Question 2: Why create a SqlFileStream to read the data if the byte array is already available by calling (byte[])reader["Context"]? Wouldn't this mean that the entire file contents are read into memory? So why the need for a Stream?

Answer 1

Question 1: Why would they return a Stream instead of a byte array in the HTTP Response?

Because the byte array may be huge, so if you read the entire array into the memory of the server and keep it in memory until it has all been transmitted to the client you are imposing a huge memory burden on the server. That's the stuff Denial-Of-Service attacks are made of. By using a stream you allow the server to load the data in small chunks, on an as-needed basis, and to keep only a small chunk in memory at any given time, while waiting for it to be transmitted.

Question 2: Why create a SqlFileStream to read the data if the byte array is already available by calling (byte[])reader["Context"]? Wouldn't this mean that the entire file contents are read into memory? So why the need for a Stream?

The byte array that you see there is not the actual file contents. If you look at the documentation of the constructor of SqlFileStream, and also at the documentation of the SqlFileStream class, this byte array is some "transaction context" which is (a terrible hack) necessary for the database server to read the actual the data from storage. The actual data is potentially huge, so the code that you posted does all this in order to avoid loading it all into memory.