Connection Time Out

  • Thread starter Thread starter Ashish
  • Start date Start date
A

Ashish

Hi,

I am getting following error in production for a background process:

Failed to fill Order Data Set :
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.
Object reference not set to an instance of an object.

I have tried my hard to find out the solution. I know it happens when
connections are not closed, but the connection seems to be getting
closed (please see the code snippet below).
I have tried to analyze using
1. sp_who or sp_who2
2. Perf Counters
but i am not getting any clue..
and the process is breaking in this method call only.

Its really frustating that I have to analyze this process and whenever
it breaks i have to rerun it manually. I have read somewhere that using
Data Adapter Fill or Update may result in Connection Leaks..

Please look into the code snippet below:

Thanks
Ashish


Code Snippet:


Entities.Orders.ShippingAddressDataSet resultsDS = new
Entities.Orders.ShippingAddressDataSet();
SqlConnection dbConnection = new SqlConnection(ConnectionString);
try
{
dbConnection.Open();
System.Data.SqlClient.SqlCommand cmdShippingAddressSelect = new
System.Data.SqlClient.SqlCommand();
//
// cmdShippingAddressSelect
//
cmdShippingAddressSelect.CommandType =
System.Data.CommandType.StoredProcedure;
cmdShippingAddressSelect.CommandText = "AddressReadByAddressID";
cmdShippingAddressSelect.Connection = dbConnection;
cmdShippingAddressSelect.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@AddressID",
System.Data.SqlDbType.Int, 4));
cmdShippingAddressSelect.Parameters["@AddressID"].Value = addressID;
System.Data.SqlClient.SqlDataAdapter shippingAddressDataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
shippingAddressDataAdapter.SelectCommand = cmdShippingAddressSelect;
shippingAddressDataAdapter.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[]
{new System.Data.Common.DataTableMapping("Table",
"ShippingAddressList",
new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("UserID", "UserID"),
new System.Data.Common.DataColumnMapping("addresstypeid",
"addresstypeid"),
new System.Data.Common.DataColumnMapping("inactive", "inactive"),
new System.Data.Common.DataColumnMapping("description",
"description"),
new System.Data.Common.DataColumnMapping("addressid", "addressid"),
new System.Data.Common.DataColumnMapping("address1", "address1"),
new System.Data.Common.DataColumnMapping("address2", "address2"),
new System.Data.Common.DataColumnMapping("address3", "address3"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping("country", "country"),
new System.Data.Common.DataColumnMapping("metroarea", "metroarea"),
new System.Data.Common.DataColumnMapping("datecreated",
"datecreated"),
new System.Data.Common.DataColumnMapping("usercreated",
"usercreated"),
new System.Data.Common.DataColumnMapping("dateupdated",
"dateupdated"),
new System.Data.Common.DataColumnMapping("userupdated",
"userupdated"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("fax", "fax"),
new System.Data.Common.DataColumnMapping("recipientname",
"recipientname"),
new System.Data.Common.DataColumnMapping("IsDavValid", "IsDavValid")
})
});
shippingAddressDataAdapter.Fill(resultsDS);
}
catch (Exception exp)
{
throw new DBErrorException("Problem with AddressReadByAddressID ID: '"
+ addressID.ToString() + "'", exp);
}
finally
{
if(dbConnection != null)
{
if(dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
}
dbConnection.Dispose();
}
}
return resultsDS;
 
Hi,

Try to increase number of connections in the pool using "min pool size" and
"max pool size" in your connection string.

My advise is to perform data related assigments before connection is opened
and then call SqlDataAdapter.Update():

// ..set all mapping, dataset values
....
MyTypedDataSet.Tables[0].MyColumn1 = value1
MyColumnMapping.....
......
......
try
{
// try to do
oConnection.Open();
oAdapter.Update(myDataset);
}
catch (Excpetion ex)
{
// rethrow the exception
....
}
finally
{
//close the connection
....
}

--
Milosz Skalecki
MCP, MCAD


Ashish said:
Hi,

I am getting following error in production for a background process:

Failed to fill Order Data Set :
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.
Object reference not set to an instance of an object.

I have tried my hard to find out the solution. I know it happens when
connections are not closed, but the connection seems to be getting
closed (please see the code snippet below).
I have tried to analyze using
1. sp_who or sp_who2
2. Perf Counters
but i am not getting any clue..
and the process is breaking in this method call only.

Its really frustating that I have to analyze this process and whenever
it breaks i have to rerun it manually. I have read somewhere that using
Data Adapter Fill or Update may result in Connection Leaks..

Please look into the code snippet below:

Thanks
Ashish


Code Snippet:


Entities.Orders.ShippingAddressDataSet resultsDS = new
Entities.Orders.ShippingAddressDataSet();
SqlConnection dbConnection = new SqlConnection(ConnectionString);
try
{
dbConnection.Open();
System.Data.SqlClient.SqlCommand cmdShippingAddressSelect = new
System.Data.SqlClient.SqlCommand();
//
// cmdShippingAddressSelect
//
cmdShippingAddressSelect.CommandType =
System.Data.CommandType.StoredProcedure;
cmdShippingAddressSelect.CommandText = "AddressReadByAddressID";
cmdShippingAddressSelect.Connection = dbConnection;
cmdShippingAddressSelect.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@AddressID",
System.Data.SqlDbType.Int, 4));
cmdShippingAddressSelect.Parameters["@AddressID"].Value = addressID;
System.Data.SqlClient.SqlDataAdapter shippingAddressDataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
shippingAddressDataAdapter.SelectCommand = cmdShippingAddressSelect;
shippingAddressDataAdapter.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[]
{new System.Data.Common.DataTableMapping("Table",
"ShippingAddressList",
new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("UserID", "UserID"),
new System.Data.Common.DataColumnMapping("addresstypeid",
"addresstypeid"),
new System.Data.Common.DataColumnMapping("inactive", "inactive"),
new System.Data.Common.DataColumnMapping("description",
"description"),
new System.Data.Common.DataColumnMapping("addressid", "addressid"),
new System.Data.Common.DataColumnMapping("address1", "address1"),
new System.Data.Common.DataColumnMapping("address2", "address2"),
new System.Data.Common.DataColumnMapping("address3", "address3"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping("country", "country"),
new System.Data.Common.DataColumnMapping("metroarea", "metroarea"),
new System.Data.Common.DataColumnMapping("datecreated",
"datecreated"),
new System.Data.Common.DataColumnMapping("usercreated",
"usercreated"),
new System.Data.Common.DataColumnMapping("dateupdated",
"dateupdated"),
new System.Data.Common.DataColumnMapping("userupdated",
"userupdated"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("fax", "fax"),
new System.Data.Common.DataColumnMapping("recipientname",
"recipientname"),
new System.Data.Common.DataColumnMapping("IsDavValid", "IsDavValid")
})
});
shippingAddressDataAdapter.Fill(resultsDS);
}
catch (Exception exp)
{
throw new DBErrorException("Problem with AddressReadByAddressID ID: '"
+ addressID.ToString() + "'", exp);
}
finally
{
if(dbConnection != null)
{
if(dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
}
dbConnection.Dispose();
}
}
return resultsDS;
Click to expand...
 
The execution of

shippingAddressDataAdapter.Fill(resultsDS);

will automatically open and close the assigned connection.

There is no need to explicity open the connection and I think you will find
that this is the cause of your problem.


Ashish said:
Hi,

I am getting following error in production for a background process:

Failed to fill Order Data Set :
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.
Object reference not set to an instance of an object.

I have tried my hard to find out the solution. I know it happens when
connections are not closed, but the connection seems to be getting
closed (please see the code snippet below).
I have tried to analyze using
1. sp_who or sp_who2
2. Perf Counters
but i am not getting any clue..
and the process is breaking in this method call only.

Its really frustating that I have to analyze this process and whenever
it breaks i have to rerun it manually. I have read somewhere that using
Data Adapter Fill or Update may result in Connection Leaks..

Please look into the code snippet below:

Thanks
Ashish


Code Snippet:


Entities.Orders.ShippingAddressDataSet resultsDS = new
Entities.Orders.ShippingAddressDataSet();
SqlConnection dbConnection = new SqlConnection(ConnectionString);
try
{
dbConnection.Open();
System.Data.SqlClient.SqlCommand cmdShippingAddressSelect = new
System.Data.SqlClient.SqlCommand();
//
// cmdShippingAddressSelect
//
cmdShippingAddressSelect.CommandType =
System.Data.CommandType.StoredProcedure;
cmdShippingAddressSelect.CommandText = "AddressReadByAddressID";
cmdShippingAddressSelect.Connection = dbConnection;
cmdShippingAddressSelect.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@AddressID",
System.Data.SqlDbType.Int, 4));
cmdShippingAddressSelect.Parameters["@AddressID"].Value = addressID;
System.Data.SqlClient.SqlDataAdapter shippingAddressDataAdapter = new
System.Data.SqlClient.SqlDataAdapter();
shippingAddressDataAdapter.SelectCommand = cmdShippingAddressSelect;
shippingAddressDataAdapter.TableMappings.AddRange(new
System.Data.Common.DataTableMapping[]
{new System.Data.Common.DataTableMapping("Table",
"ShippingAddressList",
new System.Data.Common.DataColumnMapping[]
{
new System.Data.Common.DataColumnMapping("UserID", "UserID"),
new System.Data.Common.DataColumnMapping("addresstypeid",
"addresstypeid"),
new System.Data.Common.DataColumnMapping("inactive", "inactive"),
new System.Data.Common.DataColumnMapping("description",
"description"),
new System.Data.Common.DataColumnMapping("addressid", "addressid"),
new System.Data.Common.DataColumnMapping("address1", "address1"),
new System.Data.Common.DataColumnMapping("address2", "address2"),
new System.Data.Common.DataColumnMapping("address3", "address3"),
new System.Data.Common.DataColumnMapping("city", "city"),
new System.Data.Common.DataColumnMapping("state", "state"),
new System.Data.Common.DataColumnMapping("zip", "zip"),
new System.Data.Common.DataColumnMapping("country", "country"),
new System.Data.Common.DataColumnMapping("metroarea", "metroarea"),
new System.Data.Common.DataColumnMapping("datecreated",
"datecreated"),
new System.Data.Common.DataColumnMapping("usercreated",
"usercreated"),
new System.Data.Common.DataColumnMapping("dateupdated",
"dateupdated"),
new System.Data.Common.DataColumnMapping("userupdated",
"userupdated"),
new System.Data.Common.DataColumnMapping("phone", "phone"),
new System.Data.Common.DataColumnMapping("fax", "fax"),
new System.Data.Common.DataColumnMapping("recipientname",
"recipientname"),
new System.Data.Common.DataColumnMapping("IsDavValid", "IsDavValid")
})
});
shippingAddressDataAdapter.Fill(resultsDS);
}
catch (Exception exp)
{
throw new DBErrorException("Problem with AddressReadByAddressID ID: '"
+ addressID.ToString() + "'", exp);
}
finally
{
if(dbConnection != null)
{
if(dbConnection.State != System.Data.ConnectionState.Closed)
{
dbConnection.Close();
}
dbConnection.Dispose();
}
}
return resultsDS;
Click to expand...
 
Back
Top