Is there a guru in the house? ADO NET timeout

[sodakotahusker]

We are using the Microsoft Application blocks and for some mind blowing reason - they chose not to supply a way of changing the timeout parameter - so anything over 30 seconds is going to puke. Thank heavens they compensate for their oversight by giving us the source code so we can tweak to our hearts content. So I did just that. I set a public property and then execute my query. In tracing it through the debugger the value gets set just find and all appears to be copacetic. Except for the fact that the timeout never happens. It acts exactly like changing the timeout value to 0, which tells it not to care. I set if to 300 and called a stored procedure with an infinite loop in it. 5 minutes went by. 6. 7. Finally I killed the SQL task and then control came back to my application and it errored out. So I set it to 1 and then modified my loop so that it would finish in about 10 seconds. The job finished and my program went along happily thinking all was well. So it ignores the command.CommandTimeout = xxx. Anybody have a suggestion for me to get this to work correctly? Thanks!!

 

[sodakotahusker]

Let me amend this. It does not ignore my timeout parameter. If it did that, my job would time out at 30 seconds. It simply acts like the parameter was set to zero. I check the value after the successful run, which takes probably 30 seconds now - and it returns the correct amount.

 

[chrissie1]

there are several timeouts to consider finding out wich one is the hard part. Your connection has a timeout to so perhaps you should set that aswell.

Christiaan Baes
Belgium

If you want to get an answer read this FAQ faq796-2540
There's no such thing as a winnable war - Sting

 

[sodakotahusker]

#1 - the connection timeout only applies to the actual connection in Dot Net.

#2 - I am trying to make it timeout (instead of the usual problem where people are trying to not have it time out).

#3 - I am surprised that this situation did not capture the imagination of somebody and cause them to load the SQLhelper code just to check this out. Especially since it could expose a bug in Microsoft code.

 

[ThatRickGuy]

#1 - the connection timeout only applies to the actual connection in Dot Net.

There are many time outs. There is a server side time out that will pop if the client doesn't communicate. There is a connection time out which will pop if the connection stops hearing from the server. There is a command time out that will pop is a command doesn't return in a given period of time. And I think there or other time outs available server side (not a DBA).

#2 - I am trying to make it timeout (instead of the usual problem where people are trying to not have it time out).

Often a challanging thing to do. I myself have been in a similar position (for testing purposes) and have never managed to get a hard tacked down way to force timeouts.

#3 - I am surprised that this situation did not capture the imagination of somebody and cause them to load the SQLhelper code just to check this out. Especially since it could expose a bug in Microsoft code.

Not everyone uses SQLHelper. And this bug (if that's what it is) exists in the data objects and connection, not in the SQLHelper. I still think the issue is misconfiguration. I have never been able to reliably set a time out to 1 second and enforce it, but I blaim it on my own limited knowledge of the system, no MS code block.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NoCoolHandle]

There is a difference between connectiontimeout and commandtimeout.

You need to set the command objects commandtimeout to stop your program from puking every 30 seconds.

Otherwise they say pot helps with nausia. Of course it won't help your code pass a drug test...but...

 

[NoCoolHandle]

Oh yeah the setting i find works is

command.commandtimeout = 0

(no timeout)

 

[ThatRickGuy]

Are you sure about that NCH? I'll have to do some testing if I get a chance.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[NoCoolHandle]

Rick, after a very carefull reread- just a fast skim before..

NO.
I am not sure.

I will see if i can play with this today also.

Rob

 

[NoCoolHandle]

Well I really can't comment on MS Application Blocks.. Really have never used them.

However with straight ADO.net the command timeout works as expected.

Error kicks in about 8 seconds after the actual timeout..
Proc Code

Create   Proc  WaitAWhile
@MinSecondsWait int = 0,
@MaxSecondsWait int = 30
as
set nocount on
Declare @x int
set @x = 0
while @x < @minSecondsWait or @x > @maxSecondsWait
Begin
Set @x = cast(rand() * 30 as int)
end
waitfor delay @x
Select 'Process took :' + cast(@x as varchar(1000) ) +' seconds.'

VB COde

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
' Wait between 100 and 103 seconds..
        Dim dt As DataTable = UseWaitAWhile(100, 103)
        If Not dt Is Nothing Then
            MsgBox(dt.Rows.Count)
        MsgBox(dt.Rows(0)(0).ToString)
        End If
    End Sub
    Private Function UseWaitAWhile(ByVal MinSecondsWait As Int32, ByVal MaxSecondsWait As Int32) As DataTable
        Dim con As New SqlClient.SqlConnection("server=localhost;Timeout=2;database=Crap;trusted_connection=yes")
        Dim cmd As New SqlClient.SqlCommand("WaitAWhile", con)
        Dim da As New SqlClient.SqlDataAdapter(cmd)
        Dim dt As New DataTable
        Dim dtStart As Date = Now

        With cmd
            .CommandType = CommandType.StoredProcedure
            .CommandTimeout = 20
            With .Parameters
                .Add("@MinSecondsWait", SqlDbType.Int, 4).Value = MinSecondsWait
                .Add("@MaxSecondsWait", SqlDbType.Int, 4).Value = MaxSecondsWait
            End With
            Try
                con.Open()
                da.Fill(dt)
                
            Catch ex As Exception
                MsgBox(ex.Message & ", TOok " & DateDiff(DateInterval.Second, Now(), dtStart) & " seconds to be reaised")
                Return Nothing
                Exit Function
            Finally
                con.Close()
                cmd.Dispose()
            End Try
        End With
        If dt Is Nothing Then
            Return Nothing
        Else
            Return dt
        End If
    End Function

 

[sodakotahusker]

Here is the code from the Microsoft Application Blocks. I just added the CommandTimeout line below. In copying this section of code I noticed that all the parameters that are used are included in the XML Summary section below the method name. Perhaps I need to put a commandTimeout tag into that section to make it work properly. I would not have been surprised if this routine just kept using the 30 second default timeout. I somehow deflected it to use a value of zero in setting it to a new value.
---------------------------------------------------------

/// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command
/// </summary>
/// <param name="command">The SqlCommand to be prepared</param>
/// <param name="connection">A valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">A valid SqlTransaction, or 'null'</param>
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">The stored procedure name or T-SQL command</param>
/// <param name="commandParameters">An array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
/// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection )
{
if( command == null ) throw new ArgumentNullException( "command" );
if( commandText == null || commandText.Length == 0 ) throw new ArgumentNullException( "commandText" );

// If the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
mustCloseConnection = true;
connection.Open();
}
else
{
mustCloseConnection = false;
}

// Associate the connection with the command
command.Connection = connection;

// Set the command text (stored procedure name or SQL statement)
command.CommandText = commandText;



// If we were provided a transaction, assign it
if (transaction != null)
{
if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" );
command.Transaction = transaction;
}

// Set the command type
command.CommandType = commandType;

// Attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}

// Set the timeout parameter to the value of our custom static property.
command.CommandTimeout = TimeOutParameter;

return;
}

 

[ThatRickGuy]

In copying this section of code I noticed that all the parameters that are used are included in the XML Summary section below the method name.

You can do that in VB.Net with a tool called "VB Commentor" A handy product in itself for writing header documentation, and when coupled with nDoc, an awesome combination for producing built in help files on your own projects.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.