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 Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
SLODUG - Slovene Developer Users Group www.codezone-si.info

"Brad" <(E-Mail Removed)> wrote in message
news:FA7CD885-B05B-45E7-9B6D-(E-Mail Removed)...
> 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,

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

- Brad

"Miha Markic [MVP C#]" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:FA7CD885-B05B-45E7-9B6D-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

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

"Kevin Yu [MSFT]" wrote:

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

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