Download varbinary data from sql server

I have a SQL Server table with a Varbinary(Max) column that is basically compressed data.

My page allows users to download this data (after usual user authentication).

It used to work ok, with smaller data size, but now with time, the data is also getting bigger. I am facing lot of problems basically a wait time, before the Save dialog appears.

Code:

 while (reader.Read())
            {
                Response.Buffer = false;
                Response.Charset = "";
                Response.Cache.SetCacheability(HttpCacheability.NoCache);
                Response.ContentType = "application/gzip";
                Response.AddHeader("content-disposition", "attachment;filename="
                + "vbet_1_1.sdf.gz");
                byte[] bytes = (Byte[])reader["backupdata"]; // STUCK HERE
                Response.BinaryWrite(bytes);
                Response.Flush();
                Response.End();
            }

In the debugger, I can see that

byte[] bytes = (Byte[])reader["backupdata"];

is where that lag is.

My platform is ASP.Net with .NET Framework 4.0, SQL Server 2008, C# codebehind

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You need to stream the response back. Reading the entire file in memory and then writing it out is not going to scale and you’ll start exhasting the server as the number of requests or the size of the files increase.

Have a look at Download and Upload images from SQL Server via ASP.Net MVC and FILESTREAM MVC: Download and Upload images from SQL Server to see an example of how to do this. As you do not use MVC but straight ASP.NEt you can do it simpler, but the ideas are the same:

• use a streaming API to read the database response (specifically SqlCommand.ExecuteReade with CommandBehavior.SequentialAccess) and read the response in chunks, using SqlDataReader.GetBytes() (note again the small snipped on MSDN about the importance of SequentialAccess flag…)
• use a streaming API to write the HTTP response. Response.BinaryWrite() will do.

Method 2

This is may be helpful:

Response.AppendHeader("Content-Type", "application/gzip ");
Response.OutputStream.Write((Byte[])reader["backupdata"],0,((Byte[])reader["backupdata"]).Length);

And you can use filestream tables from sql2012 to directly storage files

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0