SQL Server Error while pulling hundreds of records

Question

I am getting this error when I try to get all the username password from production copied local database, I guess it is because of not closing the connection properly, but I am not sure how . I am using the Microsoft Enterprise Library, ant thought or comment about it?

 Timeout expired.  The timeout period elapsed prior to 
 obtaining a connection    from the pool.  This may have occurred
 because all pooled    connections were in use and max pool size was reached.

this is the mothod that is getting the username and password and producing the error.

private Model.UsernameandPass GetUsernamePass(string AccountNumber)
    {
        Model.UsernameandPass model = null;

        string myConnection = System.Configuration.ConfigurationManager.ConnectionStrings[connectionName].ToString();
        SqlDatabase db = new SqlDatabase(myConnection);

        using (DbCommand command = db.GetStoredProcCommand("Get_TheUsernamePassWordFromProduction"))
        {
            db.AddInParameter(command, "AccountNumber", DbType.String, AccountNumber);
            var result = db.ExecuteReader(command);

            try
            {


                while (result.Read())
                {

                    model = new Model.UsernameandPass();

                    model.Username = result.GetString(1);
                    model.Password = result.GetString(2);
                }

            }

            catch (Exception ex)
            {


            }


        }
        db = null;
        return model;
    }

I am getting the error in this line after program runs for a while.

 var result = db.ExecuteReader(command);

Looks like you are spawning multiple connections and are not closing them and eventually exceeding your pool size. Put a Using statement around you connection code piece or have a finally to close the connections..try this http://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception — SoulTrain, Feb 25 '15 at 22:52

score 0 · Answer 1 · edited May 23 '17 at 12:28

You're getting that error because a connection cannot be established, not only because they aren't being closed properly. Check permissions for the user you're trying to authenticate against the database with. Also be sure to call .open()/.close() when/if you are programatically opening/closing connections.

Check this link. You may want to increase your pool size, or check for long-running queries.

SQL Server Error while pulling hundreds of records

1 Answers1