connection timeout error in asp.net

Question

I'm having some issues, each time I click on a button it gives me this error:

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 minute next minute I'm getting an error. It throws an exception

public List<TransactionIssues> GetAllTransactions()
            {
                SqlConnection connection = new SqlConnection(connectionString);
                SqlCommand command = new SqlCommand();
                List<TransactionIssues> transIssues = new List<TransactionIssues>();
                command.CommandText = "issue_sp_getallunpostedissues";
                command.CommandType = CommandType.StoredProcedure;
                command.Connection = connection;
                SqlDataReader rdrObj;

                try
                {
                    connection.Open();
                    rdrObj = command.ExecuteReader();
                    while (rdrObj.Read())
                    {
                        TransactionIssues issues = new TransactionIssues();
                        issues.requisitionNumber = rdrObj.GetString(0);
                        issues.transactionDate = rdrObj.GetDateTime(1);
                        //issues.expense_acccount.account_desc = rdrObj.GetString(2);
                        //issues.expense_acccount.index = rdrObj.GetInt16(3);
                        issues.inventory_acccount.index = rdrObj.GetInt32(2);
                        issues.inventory_acccount.account_desc = rdrObj.GetString(3);
                        issues.docNumber = rdrObj.GetString(4);
                        issues.docType = rdrObj.GetString(5);
                        issues.items = getTransItemByRquisition(rdrObj.GetString(4));
                        transIssues.Add(issues);
                    }
                    rdrObj.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    connection.Close();
                }
                return transIssues;
            }

What happens if you dispose of your command after using it (by putting it in a using statement, for instance)? It seems your connections stay in use, and you run out of connections in your pool. If increasing the pool size delays this error, you have to figure out why you are keeping them busy... — oerkelens
– oerkelens, Commented Nov 7, 2017 at 17:16
Not exactly duplicate but related: How can I solve a connection pool problem between ASP.NET and SQL Server? — William-H-M
– William-H-M, Commented Nov 7, 2017 at 17:19
How to use the using statement; seems i'm doing it incorrectly — user8869521
– user8869521, Commented Nov 7, 2017 at 17:25

FailedUnitTest · Accepted Answer · 2017-11-07 21:32:06Z

2

You should dispose of SqlConnection to prevent memory leaks and unclosed connections. "using" keyword takes care of calling "finally" and thus closes your connections:

public List<TransactionIssues> GetAllTransactions()
        {
            using(var connection = new SqlConnection(connectionString))
            {
                using(var command = new SqlCommand())
                {
                    List<TransactionIssues> transIssues = new List<TransactionIssues>();
                    command.CommandText = "issue_sp_getallunpostedissues";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Connection = connection;

                        connection.Open();
                        using(SqlDataReader rdrObj = command.ExecuteReader())
                        {
                            while (rdrObj.Read())
                            {
                                TransactionIssues issues = new TransactionIssues();
                                issues.requisitionNumber = rdrObj.GetString(0);
                                issues.transactionDate = rdrObj.GetDateTime(1);
                                //issues.expense_acccount.account_desc = rdrObj.GetString(2);
                                //issues.expense_acccount.index = rdrObj.GetInt16(3);
                                issues.inventory_acccount.index = rdrObj.GetInt32(2);
                                issues.inventory_acccount.account_desc = rdrObj.GetString(3);
                                issues.docNumber = rdrObj.GetString(4);
                                issues.docType = rdrObj.GetString(5);
                                issues.items = getTransItemByRquisition(rdrObj.GetString(4));
                                transIssues.Add(issues);
                            }
                        }
                        rdrObj.Close();

                    return transIssues;
                }
            }
        }

edited Nov 7, 2017 at 21:32

answered Nov 7, 2017 at 19:00

FailedUnitTest

1,8214 gold badges28 silver badges49 bronze badges

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

2 Comments

mxmissile Over a year ago

Should also wrap rdrObj in a using block.

FailedUnitTest Over a year ago

Good catch. Updated :)

Collectives™ on Stack Overflow

connection timeout error in asp.net

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related