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, Nov 07 '17 at 17:16
Not exactly duplicate but related: [How can I solve a connection pool problem between ASP.NET and SQL Server?](https://stackoverflow.com/questions/670774/how-can-i-solve-a-connection-pool-problem-between-asp-net-and-sql-server) — William-H-M, Nov 07 '17 at 17:19
How to use the using statement; seems i'm doing it incorrectly — , Nov 07 '17 at 17:25

FailedUnitTest · Accepted Answer · 2017-11-07T21:32:06.783

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;
                }
            }
        }

Should also wrap `rdrObj` in a using block. – mxmissile Nov 07 '17 at 21:24 — mxmissile, Nov 07 '17 at 21:24
Good catch. Updated :) – FailedUnitTest Nov 07 '17 at 21:32 — FailedUnitTest, Nov 07 '17 at 21:32

connection timeout error in asp.net

1 Answers1