Timeout Sql Server in a request via Web Api

Question

Running the query gives a Timeout Sql Server error:

Connection Method:

public SqlDataReader GetDataReader(string comando)
        {
            SqlConnection conn = criaConexao();

            // Cria Comando
            SqlCommand cmd = new SqlCommand(comando, conn);
            cmd.CommandType = CommandType.Text;
            cmd.CommandTimeout = 0;
            foreach (SqlParameter oP in alParameters)
            {
                cmd.Parameters.AddWithValue(oP.ParameterName, oP.Value);
            }


            try
            {
                conn.Open();
                var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //conn.Close();
            }
        }

Method populating the model:

public List<MeuModelo> SelectProduto(MeuModelo meuModel)
        {
            try
            {
                StringBuilder sb = new StringBuilder();
                DataAcess dataAcces = new DataAcess();
                List<MeuModelo> lModel = new List<MeuModelo>();

                sb.Append(" SELECT ");
                sb.Append("  prd.ProductID, prd.ProductName, cat.Nome");
                sb.Append(" FROM");                
                sb.Append("     Products as prd (NOLOCK)");
                sb.Append("         INNER JOIN Categoria as cat (NOLOCK)");
                sb.Append("             ON prd.ProductID = cat.ProductID");
                sb.Append("         WHERE prd.ProductID = 1");

                SqlDataReader dr = dataAcces.GetDataReader(sb.ToString());

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        MeuModelo meuModelo = new MeuModelo();
                        meuModelo.ProductID = Convert.ToInt32(dr["ProductID"]);
                        meuModelo.Nome = dr["Nome"];
                        lModel.Add(meuModelo);
                    }
                }

                return lModel ;
            }
            catch(Exception ex)
            {
                throw new Exception("Erro na query que seleciona produto categoria: " + ex.Message.ToString());
            }
        }

The query when executed in sql server management studio is very fast and returns 1 record only.
This query is used by a web api and after a 3 calls is returned Sql Server Timeout: "Erro na query que seleciona produto categoria".

How to solve this Timeout problem considering that the execution of the query is fast and simple ?

Answer 1

Watching your code not found where you close the connection after running, quickly you can resolve by placing a con. Close() at Finally, see if this solves your problem.

I recommend changing your code to:

con = getConexaoBD();
try
{
    con.Open();

    string sql = "SELECT prd.ProductID, prd.ProductName, cat.Nome" +
                 " FROM Products as prd" +
                 " INNER JOIN Categoria as cat" +
                 " ON prd.ProductID = cat.ProductID" +
                 " WHERE prd.ProductID = 1";

    cmd = new SqlCommand(sql);
    cmd.Connection = con;
    cmd.CommandTimeout = 0;
    cmd.Parameters.Add(new SqlParameter("@ProductID", System.Data.SqlDbType.Int32, "ProductID"));
    cmd.Parameters["@ProductID"].Value = ProductID;

    rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        modelo.ProductID = rdr["ProductID"]
    }

    return .....
}
catch (Exception ex)
{
    return throw new Exception("Erro na query que seleciona produto categoria" + ex.Message);
}
finally
{
    con.Close();
    con.Dispose();
}

There is another way to write your code, using the using recommend using an example code in this way: (example taken from SOEN)

using (SqlConnection conn = new SqlConnection(connString))
{
    using (SqlCommand comm = new SqlCommand(selectStatement, conn))
    {
        try
        {
            conn.Open();
            using (SqlDataReader dr = comm.ExecuteReader())
            {
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        Console.WriteLine(dr["Person"].ToString());
                    }
                }
                else Console.WriteLine("No C-Level with Head Up Ass Found!? (Very Odd)");
            }
        }
        catch (Exception e) { Console.WriteLine("Error: " + e.Message); }
        if (conn.State == System.Data.ConnectionState.Open) conn.Close();
    }
}

Taken from the Microsoft website: When the lifetime of an Idisposable object is limited to a single method, you must declare it and instantiate it into the using instruction. The using statement calls the Dispose method on the object in the correct form and (when you use it as shown above) it also causes the object itself to drop out of scope as soon as Dispose is called. Inside the using block, the object is read-only and cannot be modified or reassigned. The using statement ensures that Dispose is called, even if an exception occurs within the using block. You can get the same result by placing the object inside a Try block and then calling Dispose in a Finally block. In fact, it is in this way that the using instruction is converted by the compiler. The previous code example expands to the following compile-time code

If you would like more information about using:

https://docs.microsoft.com/pt-br/dotnet/csharp/language-reference/keywords/using-statement

Answer 2

As noted in the comments by Thiago Loureiro I switched the SqlDataReader for DataTable and it worked.