Command.Timeout Property Not Working

[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

 

[Miha Markic [MVP C#]]

CommandTimeout doesn't apply to fetching data - is affects just command
execution.

 

[Guest]

Miha,

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

- Brad

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

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

 

[Kevin Yu [MSFT]]

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."

 

[Guest]

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

 

[Kevin Yu [MSFT]]

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."