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:
Dim strConn As String = "Data Source=SERVERNAME;Initial Catalog=DBNAME;" & _
Using connection As SqlConnection = New SqlConnection(strConn)
connection.Open()
Using command As SqlCommand = connection.CreateCommand()
command.CommandType = CommandType.StoredProcedure
command.CommandText = cmdText
If cmdParam IsNot Nothing Then
Dim intParam As Integer = cmdParam.Length / 2
For i As Integer = 0 To intParam - 1
command.Parameters.AddWithValue(
Next
End If
command.ExecuteNonQuery()
End Using
End Using
End Sub
0 Comments