C# time out exception when passing data to http response

I"m trying to send the user a pretty decent about of data could be about 200,000 records or more. WHen i run the selections it always times out. If i run smaller ones it works. Heres the code to export: I'm i goign about this wrong, is there a better way to achieve what i want in terms of performance. I need to get the user a csv file of data from a stored procedure.

public void ExcelExportCSV(SqlDataReader dr )
    {
        HttpResponse response = HttpContext.Current.Response;
        HttpContext context = HttpContext.Current;
        context.Response.Clear();

        while (dr.Read())
        {
            context.Response.Write(dr.GetValue(0)+ ",");
            context.Response.Write(dr.GetValue(1) + ",");
            context.Response.Write(dr.GetValue(2) + ",");
            context.Response.Write(dr.GetValue(3) + ",");
            context.Response.Write(dr.GetValue(4) + ",");
            context.Response.Write(dr.GetValue(5) + ",");
        }
        context.Response.Write(Environment.NewLine);
        context.Response.ContentType = "text/csv";
        context.Response.AppendHeader("Content-Disposition", "attachment; filename=membSel.csv");
        context.Response.End();
    }

national_fulfillmentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

smulangeCommented:
I think you should use sql command and set the time out to it

1.

SqlCommand cmd = new SqlCommand("sql statement", connection);
cmd.CommandTimeout = 999999 ;
cmd.CommandType    = CommandType.Text;

// open connection;
SqlDataReader dr = cmd.ExecuteReader();

then add logic to read records

while (dr.Read())
{
  ....
  ......
}

2. apart from this you need to add timeout configuration in web.config inside <system.web>  to avoid HtttpRequest timeout exception

ex: <httpRuntime maxRequestLength="100000" executionTimeout="36000" />


hope this will resolve issue
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jagrut_patelCommented:
Check whether setting executionTimeout attribute of httpRuntime element in web.config is useful. See this for more details about httpRuntime element.
0
national_fulfillmentAuthor Commented:
i had a couple other issues but i think adding the timeout on the sql command did the trick. Thanks a bunch.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.