将数据导出到excel时出现异常

Question

将数据导出到excel工作表时出现以下错误

ERROR:
Message : 
Exception of type 'System.Web.HttpUnhandledException' was thrown.
Error Description : 
System.Web.HttpUnhandledException: Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

有没有人建议我该怎么做。

CODE:
gridData.dataSource = GetData()
gridData.DataBind()


Response.Clear()
Response.AddHeader("content-disposition",   "attachment;filename=CompletionDatesReport.xls")
Response.Charset = ""
Response.Cache.SetCacheability(HttpCacheability.NoCache)
Response.ContentType = "application/vnd.xls"
Dim stringWrite As StringWriter = New StringWriter()
Dim htmlWrite As HtmlTextWriter = New HtmlTextWriter(stringWrite)
gridData.RenderControl(htmlWrite)
Response.Write(stringWrite.ToString())
Response.End()

Answer 1

没有代码，我们只能猜测，但SqlException: Timeout expired中有一个很大的线索-这表明您的查询花费了太长的时间。您可以增加命令的超时时间(但要尽量保持正常...)通过SqlCommand.CommandTimeout，但编写更高效的查询，或者不在一个查询中提取那么多的数据，通常会更可取。

Answer 2

我同意马克的观点。查看一下您的查询并对其进行优化，删除DISTINCT语句等以使其更快。