I have created a simple data fetch method which fetches data from a SQL Server database.
Each time I fetch data by calling this method the memory grows (application pool memory). When I explicitly call GC.Collect() after fetching the data, the memory is half of what it was without GC.Collect().
I know it is bad practice to call GC.Collect(), but without calling I am struggling to reduce memory.
private void DataPopulate(int ID)
{
using (SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
{
sqlCon.Open();
if (sqlCon.State == ConnectionState.Open)
{
using (SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("engine_connection_sendername_get", sqlCon) { CommandType = CommandType.StoredProcedure })
{
sqlCmd.Parameters.Add(new SqlParameter("id", ID));
using (SqlDataAdapter da = new SqlDataAdapter())
{
using (DataTable dt = new DataTable())
{
da.SelectCommand = sqlCmd;
sqlCmd.CommandTimeout = 300;
da.Fill(dt);
}
}
}
}
GC.Collect();
}
}
I am using the above test method to just check the memory issue. I created a sample .aspx page which only has a button in it . When I click the button this method is called. The Datatable is filled with data from the SQL Server database - that's all.
And I check the memory in application pool. Below is the summary of each time I click the button and the corresponding memory with and without using GC.Collect()
[
private void DataPopulate(int ID)called in a loop? if so, please dont open and close theSqlConnectionin a single execution, keep it around until your operation completed.sqlCmd.Parameters.Add(new SqlParameter("id", ID));Tricky way to do .AddWithValue but don't. See dbdelta.com/addwithvalue-is-evil and blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… and another one: dba.stackexchange.com/questions/195937/… Here is another andrevdm.blogspot.com/2010/12/…