Connection Timeout SQL c#

Question

I have a project and when I try to run it and the data test is big I have always a connection timeout. I added "sqlCmd.CommandTimeout = 3600;" but still not working. What could am I doing wrong?

This is my code:

public void createCode(String ce, int ord, String beh, int wkd)
{
        String strSql = "";
        SqlCommand sqlCmd;

        SqlConnection conexion = new SqlConnection(getConexion());

        try
        {
            if (conexion.State != ConnectionState.Open)
                conexion.Open();

            //The insert works fine in sql server
            strSql = "Insert into x with values"; 

            sqlCmd = new SqlCommand(strSql, conexion);
            sqlCmd.CommandTimeout = 3600;
            sqlCmd.ExecuteScalar();
        }
        catch (Exception ex)
        {               
            throw new Exception("Error creating Code. " + ex.Message);
        }
        finally
        {
            if (conexion.State == ConnectionState.Open)
                conexion.Close();
        }

}

Please, provide an error message and connection string (obfuscating its secret parts). It is important to distinct two different timeout types: [ConnectionTimeout and CommandTimeout](https://stackoverflow.com/questions/847264/what-is-the-difference-between-sqlcommand-commandtimeout-and-sqlconnection-conne). Which timeout does cause error in your code? — Iliar Turdushev, Mar 27 '20 at 10:18

score 1 · Accepted Answer · answered Mar 27 '20 at 10:05

1

You might need to set transaction timeout in your config file, like so;

<system.transactions>
   <defaultSettings timeout="01:00:00" />
</system.transactions>

answered Mar 27 '20 at 10:05

DaggeJ

2,094
1
10
22

1

Thanks a lot! This was the problem! :) – greatWallace Mar 27 '20 at 10:30
Glad to help :) – DaggeJ Mar 27 '20 at 10:50

score 0 · Answer 2 · answered Mar 27 '20 at 10:04

0

sqlCmd.ExecuteScalar() is not correct for your script, try using sqlCmd.ExecuteNonQuery() instead and remove timeout.

        sqlCmd = new SqlCommand(strSql, conexion);
        sqlCmd.ExecuteNonQuery();

Check each function, ExecuteScalar tries to return first value from a select, while ExecuteNonQuery does not retrieve any value, just gets num of rows affected.

Hope it helps!

answered Mar 27 '20 at 10:04

David

464
1
4
7

This is not the problem because I have been using this from years. But thank you David. – greatWallace Mar 27 '20 at 10:14
So if you are sure your script and sql is ok, you may use sqlCmd.CommandTimeout = 0 and it won't fail due to sql timeout. – David Mar 27 '20 at 10:21

Connection Timeout SQL c#

2 Answers2