Timeout Sql Server on a Web Api request

Question

Timeout Sql Server on a Web Api request

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

When executing the query I get 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 Template:

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. Home This query is used by a web api and after a 3 calls is returned TimeOut of Sql Server: "Erro na query que seleciona produto categoria" .

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

sql-server c# asp.net-web-api

asked by anonymous 13.09.2018 / 08:38

2 answers

Error trying to update a post in WordPress using the WP REST API plugin Problem with accent when generating txt in php

score 1 · Answer 1

Looking at your code, I did not find where you close the connection after the execution, you can quickly resolve by putting a con.Close () on Finally, verify that 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 using I recommend using a sample code: ( 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();
    }
}

Retired from Microsoft Web site: When the lifetime of an IDisposable object is limited to a single method, you must declare it and instantiate it in the using statement. The using statement calls the Dispose method on the object in the correct way and (when you use it as shown earlier) it also causes the object itself to exit the scope as soon as Dispose is called. Within the using block, the object is read-only and can not be modified or reassigned. The using statement guarantees that Dispose is called, even though 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, this is how the using statement is converted by the compiler. The preceding code sample expands to the following code at compile time

If you'd like more information about using:

link

score 0 · Answer 2

0

As noted in the comments by Thiago Loureiro I changed SqlDataReader to DataTable and it worked .

18.09.2018 / 14:22