How to Set / Change SQLConnection Timeout

We can change/set timeout for an SQLConnection on its connections string as below. The highlighted code is for set the timeout. 

C#:

string strConn = "Data Source =SERVERNAME;Initial Catalog=DBNAME;" +
                 "User ID=USERNAME;Password=DBPSWD;Connection Timeout=60;";

VB.NET:

Dim strConn As String = "Data Source =SERVERNAME;Initial Catalog=DBNAME;" & _
                 "User ID=USERNAME;Password=DBPSWD;Connection Timeout=60;"

However when I try to use it while executing stored procedure command, it doesn't work. Timeout error still prompts under 60 seconds. Then I found that If you want to provide a timeout for a particular query, then you need to set the CommandTimeout.

The yellow highlighted is the setting needed.

C#:

public static void RunCommand(string cmdText, string[,] cmdParam)
{
    string strConn = "Data Source =SERVERNAME;Initial Catalog=DBNAME;" +
                     "User ID=USERNAME;Password=DBPSWD;" +
                     "Connection Timeout=60;";
    using (SqlConnection connection = new SqlConnection(strConn))
    {
        connection.Open();
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = cmdText;
            command.CommandTimeout = 60;
            if (cmdParam != null)
            {
               int intParam = cmdParam.Length / 2;
               for (int i = 0; i < intParam; i++)
                {
                    command.Parameters.AddWithValue(
                                        cmdParam[i, 0].ToString(),
                                        cmdParam[i, 1].ToString()
                                                   );
                }
            }
            command.ExecuteNonQuery();
        }
    }
}


VB.NET:

Public Shared Sub RunCommand(ByVal cmdText As String, _
                             ByVal cmdParam As String(,))

  Dim strConn As String = "Data Source=SERVERNAME;Initial Catalog=DBNAME;" & _
                          "User ID=USERNAME;Password=DBPSWD;" & _
                          "Connection Timeout=60;"
  Using connection As SqlConnection = New SqlConnection(strConn)
      connection.Open()
      Using command As SqlCommand = connection.CreateCommand()
          command.CommandType = CommandType.StoredProcedure
          command.CommandText = cmdText
          command.CommandTimeout = 60
          If cmdParam IsNot Nothing Then
              Dim intParam As Integer = cmdParam.Length / 2
              For i As Integer = 0 To intParam - 1
                  command.Parameters.AddWithValue(
                          cmdParam(i, 0).ToString(), 
                          cmdParam(i, 1).ToString()
                  )
              Next
          End If
          command.ExecuteNonQuery()
      End Using
  End Using
End Sub


Post a Comment

0 Comments