So I've read a lot about SqlDataReaders not being disposed of properly in .Net - and I've been fighting the "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" error for a couple days now. Obviously I could jack the max pool size up to 30,000 - but that doesn't address the actual problem.
As I step through the code, I execute the following SQL Query:
select * from sys.dm_os_performance_counters
where counter_name ='User Connections'
After the
cmd.Connection.Open();
line, the User Connections are incremented by 1. However, it NEVER goes back down unless I recycle the app pool on the web server (at which point all active database connections from the website are killed).
Here is my code:
public static DataTable SPExecuteDataTable(string[] ConnectionData, params object[] args)
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader dr = null;
try
{
conn = new SqlConnection(ConnectionData[1]);
cmd = new SqlCommand(ConnectionData[0], new SqlConnection(ConnectionData[1]));
cmd.CommandType = CommandType.StoredProcedure;
for (int i = 0; i < args.Length; i++)
{
SqlParameter Param = new SqlParameter(ConnectionData[i + 2], DBNullIfNull(args[i]));
cmd.Parameters.Add(Param);
}
cmd.Connection.Open();
DataTable dt = new DataTable();
using (dr = cmd.ExecuteReader())
{
if (dr != null)
dt.Load(dr);
else
dt = null;
}
return dt;
}
catch (Exception e)
{
Exception x = new Exception(String.Format("DataAccess.SPExecuteDataTable() {0}", e.Message));
throw x;
}
finally
{
conn.Close();
cmd.Connection.Close();
dr.Close();
conn.Dispose();
cmd.Dispose();
dr.Dispose();
}
So far, I've tried explicitly closing the connections (like in my finally block), but that doesn't work. I've also tried using statements like so:
using (SqlDataReader dr = blah blah blah)
{
//code here
}
But that also doesn't work. What is wrong with my code, here?
Solution:
Use DataTables! To prevent having non-data-access layers of your application from having to communicate with the database, just do this in your data-access layer:
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr != null)
dt.Load(dr);
else
dt = null;
}
return dt;
Then, you can manipulate the dt however you'd like in the rest of your solution, and the connections HAVE ALREADY BEEN properly disposed. Works like a charm and, luckily, the code for a datatable and datareader are very similar, so modifying the application in this manner was relatively painless.
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句