Welcome to Vortx Community Forum, where you can ask questions and receive answers from the staff at Vortx and other members of the community.

If you had a user account on our previous forums website, you will need to register a new account here.

Learn more about...

AspDotNetStorefront
DotFeed

SQl Connections Close/dispose

So we have gone and exceeed the allowed SQL connections, resulting in timeouts.

 

Searching the code I see tons of places connections are opened but not disposed or closed.

 

Are they being closed somewhere else?

 

Mainly during checkout and login events.

 

My question how do i fix this ?

 

Under a high load there are no connections available, and users are awaiting a connection.

 

Is there a way to force close/dispose connections after (x amount of time) besides hunting down every bit of code that opens a connection and force closure ?

I see tons of new connections but no 

 

   conn.Close();
   conn.Dispose();
asked Oct 5 in MultiStore by aflorestal (225 points)

1 Answer

0 votes
We do not know of any current problems with disposing of database connections.

What version of AspDotNetStorefront are you using?
answered Oct 5 by Vortx Joe (510 points)

9.5.1

Searched the entire code base and cant find a single iinstance of disposal outside of those in 3rd party extensions.

At which point, which file/process does it dispose of connections.

From what i see it relies on the pool to close the connection. Which is the preffered method if the pool acts accodingly which it does not seem to do so.

Eventually leading to max connecitons exceeded, then timeouts expire awaiting said connection.

We host with appledi so we dont have iis access to control the pool and sql connections timeouts.

So we are left with the default allotments. Which are exceeded as connections are remaining open versus being disposed and reused.

So i guess what I am after is , are there any web.configs that can be set to handle this, or how should i go about adding close, and or dispose to the cpl hundred new conecction strings that are coded in the checkout process.

for example

checkoutshipping.aspx.cs

 

 

  private void SetDebugInformation()
        {
            if ((AppLogic.AppConfigBool("RTShipping.DumpXMLOnCheckoutShippingPage") || AppLogic.AppConfigBool("RTShipping.DumpXMLOnCartPage")) && cart.ShipCalcID == Shipping.ShippingCalculationEnum.UseRealTimeRates)
            {
                StringBuilder tmpS = new StringBuilder(4096);
                tmpS.Append("<div class=\"divider\"></div>");
 
                using (SqlConnection con = new SqlConnection(DB.GetDBConn()))
                {
                    con.Open();
                    using (IDataReader rs = DB.GetRS("Select RTShipRequest,RTShipResponse from customer  with (NOLOCK)  where CustomerID=" + ThisCustomer.CustomerID.ToString(), con))
                    {
                        if (rs.Read())
                        {
                            String s = DB.RSField(rs, "RTShipRequest");
                            s = s.Replace("<?xml version=\"1.0\"?>", "");
                            try
                            {
                                s = XmlCommon.PrettyPrintXml("<roottag_justaddedfordisplayonthispage>" + s + "</roottag_justaddedfordisplayonthispage>");
                            }
                            catch
                            {
                                s = DB.RSField(rs, "RTShipRequest");
                            }
                            tmpS.Append("<b>" + AppLogic.GetString("shoppingcart.aspx.5", SkinID, ThisCustomer.LocaleSetting) + "</b><textarea rows=60 style=\"width: 100%\">" + s + "</textarea>");
 
                            s = DB.RSField(rs, "RTShipResponse");
                            try
                            {
                                s = XmlCommon.PrettyPrintXml("<roottag_justaddedfordisplayonthispage>" + s + "</roottag_justaddedfordisplayonthispage>");
                            }
                            catch
                            {
                                s = DB.RSField(rs, "RTShipResponse");
                            }
                            tmpS.Append("<b>" + AppLogic.GetString("shoppingcart.aspx.6", SkinID, ThisCustomer.LocaleSetting) + "</b><textarea rows=60 style=\"width: 100%\">" + s + "</textarea>");
                        }
                    }
                }
 
                DebugInfo.Text = tmpS.ToString();
            }
        }
 
 
Shouldnt  this include  ?
conn.Close();
conn.Dispose();
 
 
 

A database connection inside a using statement needs neither a close or dispose as they are called when the block goes out of scope.

https://stackoverflow.com/questions/18588049/sqlconnection-close-inside-using-statement

In your example code above, the:

using (SqlConnection con = new SqlConnection(DB.GetDBConn()))

ensures that close and dispose are called.

I did not know of any database connection problems in 9.5.1, either.  Do you have any custom code added to your site?  Have you changed the default database connection string?

 

 

 

...