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. Which timeout does cause error in your code? — Iliar Turdushev
– Iliar Turdushev, Commented Mar 27, 2020 at 10:18

DaggeJ · Accepted Answer · 2020-03-27 10:05:07Z

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, 2020 at 10:05

DaggeJ

2,2091 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

greatWallace Over a year ago

Thanks a lot! This was the problem! :)

DaggeJ Over a year ago

Glad to help :)

David · Accepted Answer · 2020-03-27 10:04:59Z

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, 2020 at 10:04

David

4741 gold badge4 silver badges7 bronze badges

2 Comments

greatWallace Over a year ago

This is not the problem because I have been using this from years. But thank you David.

David Over a year ago

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.

Collectives™ on Stack Overflow

Connection Timeout SQL c#

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related