Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

Question

I am fairly new in asp.net development, so you can say that I have developed a poorly coded application that is giving me this error.

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

I tried searching on google and found that it happens because of unclosed connections in the application pool so I carefully examined my application and inserted using keyword for all Sqlconnections but still I am having this problem, here is some sample code from my application. All Sqlconnections are enclosed in using statement like this.

    using (Connection = new SqlConnection(CIPConnection))
    {
        string ReturnValue = String.Empty;
        try
        {
            SqlDataReader Reader;
            Connection.Open();
            string CommandText = "SELECT * FROM Users WHERE NAME = @NAME AND PASSWORD = @PASSWORD";
            Command = new SqlCommand(CommandText, Connection);
            Command.Parameters.AddWithValue("@NAME", UserName);
            Command.Parameters.AddWithValue("@PASSWORD", Password);
            Reader = Command.ExecuteReader();
            Reader.Read();
            if (Reader.HasRows)
            {
                Session["ID"] = Reader["ID"];
                Session["NAME"] = Reader["NAME"];
                Session["DEPARTMENT"] = Reader["DEPARTMENT"];

                switch (Reader["DEPARTMENT"].ToString())
                {
                    case "Admin":
                        ReturnValue = "Admin";
                        break;
                    case "Editing":
                        ReturnValue = "Editing";
                        break;
                    case "Sales and Support":
                        ReturnValue = "Sales and Support";
                        break;
                    case "Writing":
                        ReturnValue = "Writing";
                        break;
                    default:
                        ReturnValue = "Sorry";
                        break;
                }
            }
        }
        catch (Exception exp)
        {
            Response.Write(exp.Message.ToString());
        }
        return ReturnValue;
    }

Now my question is do I need to close Connection even in using statement block ? what will be the best way to close connection ? (putting it in finally block ? with every try statement ?) should I also use using statement with SqlReader and SqlCommand ? Please tell me the best way to get rid of unused connections so I can solve this problem.

Thanks.

score 2 · Accepted Answer · answered Jul 27 '11 at 21:21

Perhaps the problem is related to the SqlDataReader object, which is not closed. Try a using block:

using (var Reader = Command.ExecuteReader())
{

As a sidenote, the Read function returns false if no rows were found. So you could shorten this:

Reader.Read();
if (Reader.HasRows)
{

to:

if (Reader.Read())
{

score 0 · Answer 2 · edited May 23 '17 at 12:19

0

No, when it exists the using block, it will Dispose, which will Close and return it to the pool. The problem lies elsewhere, or you missed one.

Does End Using close an open SQL Connection

edited May 23 '17 at 12:19

Community

1
1

answered Jul 27 '11 at 21:17

Nikki9696

6,260
1
28
23

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

2 Answers2