C# 超时异常导致 SqlDataReader 关闭？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1719305/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Timeout exception causes SqlDataReader to close?
提问by Paulie
I'm trying to pull some binary data from a database and write them to pdf files. For the most part, this is going along swimmingly, but the occasional row of data seems to throw a particular error -
我正在尝试从数据库中提取一些二进制数据并将它们写入 pdf 文件。在大多数情况下，这是顺利进行的，但偶尔的数据行似乎会引发特定错误 -
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
超时已过。操作完成前超时时间已过或服务器未响应。
Keep in mind, this only happens on a handful of rows, and is never random. The same rows always throw the exception. I'm not really sure why the exception is being thrown, but I'm ok with skipping the rows that do cause problems and move on. My problem, however, is that when I catch the exception and then try to move onto the next row, I run into another exception -
请记住，这只会发生在少数行上，而且永远不会随机。相同的行总是抛出异常。我不确定为什么会抛出异常，但我可以跳过确实会导致问题的行并继续。然而，我的问题是，当我捕获异常然后尝试移到下一行时，我遇到了另一个异常 -
InvalidOperationException - Invalid attempt to call Read when reader is closed.
InvalidOperationException - 读取器关闭时调用 Read 的尝试无效。
Does this mean the reader is automatically closing as soon as it runs into an exception? How would I go about proceeding to the next row without any dramas?
这是否意味着阅读器一旦遇到异常就会自动关闭？我将如何在没有任何戏剧性的情况下进入下一行？
while (sdrReader.Read()) // Second exception happens here
{
try
{
byte[] byteData = new Byte[(sdrReader.GetBytes(0, 0, null, 0, int.MaxValue))]; // first exception happens here
sdrReader.GetBytes(0, 0, byteData, 0, byteData.Length);
string strOutputFileName = sdrReader.GetInt32(1).ToString() + ".pdf";
msMemoryStreams = new MemoryStream();
msMemoryStreams.Write(byteData, 0, byteData.Length);
byte[] byteArray = msMemoryStreams.ToArray();
msMemoryStreams.Flush();
msMemoryStreams.Close();
writeByteArrayToFile(byteData, txtFilesPath.Text + "\" + strOutputFileName);
}
catch (Exception e)
{
Logger.Write("Document failed to convert: " + e.Message);
}
}
Stack trace, as requested -
堆栈跟踪，根据要求 -
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadNetworkPacket()
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByteArray(Byte[] buff, Int32 offset, Int32 len)
at System.Data.SqlClient.TdsParser.ReadSqlValue(SqlBuffer value, SqlMetaDataPriv md, Int32 length, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ReadColumnData()
at System.Data.SqlClient.SqlDataReader.ReadColumn(Int32 i, Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.GetSqlBinary(Int32 i)
at System.Data.SqlClient.SqlDataReader.GetBytesInternal(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length)
at System.Data.SqlClient.SqlDataReader.GetBytes(Int32 i, Int64 dataIndex, Byte[] buffer, Int32 bufferIndex, Int32 length)
at Pdf2Rtf.Form1.Read() in F:\Code\Pdf2Rtf\Pdf2Rtf\Pdf2Rtf\Form1.cs:line 77
at Pdf2Rtf.Form1.btnRead_Click(Object sender, EventArgs e) in F:\Code\Pdf2Rtf\Pdf2Rtf\Pdf2Rtf\Form1.cs:line 24
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at Pdf2Rtf.Program.Main() in F:\Code\Pdf2Rtf\Pdf2Rtf\Pdf2Rtf\Program.cs:line 18
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
采纳答案by Jeff Sternal
It looks like your SqlCommandis timing out - when you call ExecuteReader, the associated command remains open and will be vulnerable to timeouts until you finish reading. As it says in the SqlCommand.CommandTimeoutdocumentation:
看起来您SqlCommand正在超时 - 当您调用 时ExecuteReader，关联的命令保持打开状态，并且在您完成阅读之前很容易受到超时的影响。正如SqlCommand.CommandTimeout文档中所说：
This property is the cumulative time-out for all network reads during command execution or processing of the results. A time-out can still occur after the first row is returned, and does not include user processing time, only network read time.
此属性是命令执行或结果处理期间所有网络读取的累积超时。返回第一行后仍然会出现超时，不包括用户处理时间，只包括网络读取时间。
When the command times out, it closes the reader, from which you can't recover.
当命令超时时，它会关闭读取器，您无法从中恢复。
The first thing to try to solve this is to increase the CommandTimeoutdramatically, just to make sure you can proceed.
尝试解决此问题的第一件事是CommandTimeout大幅增加，以确保您可以继续。
Next, if you haven't done so already, it may help to use the ExecuteReaderoverload that allows you to specify a CommandBehavior, and pass CommandBehavior.SequentialAccess(per the recommendation in the MSDN topic "Retrieving Large Data (ADO.NET)").
接下来，如果您还没有这样做，那么使用ExecuteReader允许您指定CommandBehavior, 和 pass的重载可能会有所帮助CommandBehavior.SequentialAccess（根据 MSDN 主题“检索大数据 (ADO.NET)”中的建议）。
Finally, you might also try breaking the reads into chunks of records.
最后，您还可以尝试将读取拆分为记录块。
回答by GuyBehindtheGuy
If the SQL Error Severity is less than 17, you can set SqlConnection.FireInfoMessageEventOnUserErrors = trueto handle the exception as a warning. Anything greater than Severity 17 is going to close the connectionno matter what.
如果 SQL Error Severity 小于 17，您可以将SqlConnection.FireInfoMessageEventOnUserErrors = true异常设置为警告处理。无论如何，任何高于严重性 17 的情况都会关闭连接。
