Command.Timeout Property Not Working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I set the Timeout property of a command I am executing it does not end
when it reaches the timeout value specified. Here is the code I am using. My
sample query is simply returning all rows from a table with approximately
170,000 rows. The dataadapter.Fill takes between 8 and 10 seconds.

Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim datStartTime As DateTime
Dim datEndTime As DateTime

Try
cnn = New SqlConnection
cmd = New SqlCommand
da = New SqlDataAdapter
ds = New DataSet

cnn.ConnectionString = " - Removed for security reasons - "
cnn.Open()

cmd.Connection = cnn
cmd.CommandTimeout = 2
cmd.CommandText = " - Removed for security reasons - "
cmd.CommandType = CommandType.Text

da.SelectCommand = cmd
datStartTime = Now
da.Fill(ds)
datEndTime = Now
Me.txtDuration.Text =
datEndTime.Subtract(datStartTime).TotalSeconds.ToString & " seconds"

Me.DataGrid1.DataSource() = ds.Tables(0)

Catch ex As Exception
MessageBox.Show(ex.ToString)

Finally
If Not cmd Is Nothing Then
cmd.Dispose()
End If
If Not cnn Is Nothing Then
cnn.Dispose()
End If
End Try
 
CommandTimeout doesn't apply to fetching data - is affects just command
execution.
 
Miha,

Can you explain your answer in more detail? I thought part of completing
the command was fetching the data.

- Brad

Miha Markic said:
CommandTimeout doesn't apply to fetching data - is affects just command
execution.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info

Brad said:
When I set the Timeout property of a command I am executing it does not
end
when it reaches the timeout value specified. Here is the code I am using.
My
sample query is simply returning all rows from a table with approximately
170,000 rows. The dataadapter.Fill takes between 8 and 10 seconds.

Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim datStartTime As DateTime
Dim datEndTime As DateTime

Try
cnn = New SqlConnection
cmd = New SqlCommand
da = New SqlDataAdapter
ds = New DataSet

cnn.ConnectionString = " - Removed for security reasons - "
cnn.Open()

cmd.Connection = cnn
cmd.CommandTimeout = 2
cmd.CommandText = " - Removed for security reasons - "
cmd.CommandType = CommandType.Text

da.SelectCommand = cmd
datStartTime = Now
da.Fill(ds)
datEndTime = Now
Me.txtDuration.Text =
datEndTime.Subtract(datStartTime).TotalSeconds.ToString & " seconds"

Me.DataGrid1.DataSource() = ds.Tables(0)

Catch ex As Exception
MessageBox.Show(ex.ToString)

Finally
If Not cmd Is Nothing Then
cmd.Dispose()
End If
If Not cnn Is Nothing Then
cnn.Dispose()
End If
End Try
Click to expand...
Click to expand...
 
Hi Brad,

When you're trying to execute a SELECT command and fill data to a DataSet,
the process is actually divided into 2 parts.

1. A cursor is opened on server. A DataReader object is opened.
2. Fill is going though every row in the rowset and fetching data to the
DataSet.

The Timeout property only applies to the opening of a DataReader. The
latter part is a big deal but not controlled by SqlCommand.Timeout property.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin,

Is it possible to use a Try...Catch block to capture the exception thrown
when a timeout occurs? Also when a timeout occurs are changes automatically
rolled back or would I need to handle the rollback in the application code?

- Brad
 
Hi Brad,

We cannot use a Try...Catch block to catch the timeout exception, since in
this senario, there is no exception thrown. The transaction will not be
rollbacked. If there is exception throw when opening a DataReader, it can
be caught. And the transaction is rollbacked. HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top