Bagaimana Cara Setting Connection Time Out pada Koneksi Database SQL Server

Setahu Rani kita menentukan batas time out sebuah koneksi ke database SQL Server, terutama yaitu pada settingan di SQL Connection string nya. Seperti berikut misalnya, yang saya beri highlight kuning adalah pengaturan untuk 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;"

Tapi setting timeout itu tidak berfungsi sebagaimana seharusnya alias ga jalan. Time out error nya tetep muncul di bawah itu.

Ternyata pada saat eksekusi command juga perlu di setting timeout nya. Kira-kira seperti di bawah ini, yang perlu diperhatikan adalah yang diberi highlight kuning.

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