class that uses sql server isnt releasing memory

I'm running into a problem where the class I'm running seems to eat up a lot of memory with sql server. When it's done running, the memory usage never goes down in taskmanager. I can't figure out where the memory leak might be. Here's the code that is being called. Does anyone see a reason why it would continue to eat memory as it runs and then not release it? Thanks.

using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace QueryLoadTester{ class JobSeeker { private string ConStr = @."Data Source=server;Initial Catalog=db;Integrated Security=True"; public JobSeeker() { } #region UpdateJobSeeker public void UpdateJobSeeker(string JobSeekerId) { string qry = "SELECT top 100 dbo.JobSeeker.JobSeekerID, dbo.JobSeeker.SiteId, dbo.JobSeeker.PositionTitle, dbo.JobSeeker.LocationID, dbo.JobSeeker.CurrentSalary, " + "dbo.JobSeeker.DesiredSalary, dbo.JobSeeker.MinSalary, dbo.JobSeeker.CurrentHourly, dbo.JobSeeker.MinHourly, dbo.JobSeeker.Comments, " + "dbo.JobSeeker.Resume, dbo.JobSeeker.WillRelocate, dbo.JobSeeker.DateAdded, dbo.JobSeeker.LastModified FROM dbo.JobSeeker " + "where dbo.JobSeeker.Active=1 and dbo.JobSeeker.samplejobseeker=0 "; if (JobSeekerId.Length > 0) qry += " and dbo.JobSeeker.JobSeekerID=" + JobSeekerId; qry += " and dbo.JobSeeker.JobSeekerID not in (Select JobSeekerId from JobSeekerFullTextSearch)"; SqlConnection cnInsert = new SqlConnection(ConStr); SqlDataAdapter adp = new SqlDataAdapter(qry, cnInsert); cnInsert.Open(); DataSet dsJobSeekers = new DataSet(); adp.Fill(dsJobSeekers); adp.Dispose(); adp = null; if (dsJobSeekers.Tables[0].Rows.Count > 0) { string jid = string.Empty; string degree, degreegroup; StringBuilder sb = new StringBuilder(); SqlCommand cmdInsert = new SqlCommand(); cmdInsert.Connection = cnInsert; foreach (DataRow dr in dsJobSeekers.Tables[0].Rows) { jid = dr["JobSeekerID"].ToString(); if (JobSeekerId.Length > 0) { cmdInsert.CommandText = "Delete from JobSeekerFullTextSearch where JobSeekerId=" + JobSeekerId; cmdInsert.ExecuteNonQuery(); } DataSet dsExtras = GetJobSeekerExtras(jid, cnInsert); SqlTransaction trans = cnInsert.BeginTransaction(); cmdInsert.Transaction = trans; degree = degreegroup = string.Empty; #region insert record into fulltextsearch try { sb.Remove(0, sb.Length); sb.Append("Insert into JobSeekerFullTextSearch (JobSeekerId, PositionTitle, LocationID, CurrentSalary, "); sb.Append("DesiredSalary, MinSalary, CurrentHourly, MinHourly, CommentsResume, SiteId, WillRelocate, DateAdded, LastModified) values ("); sb.Append(jid); sb.Append(",'"); sb.Append(dr["PositionTitle"].ToString().Replace("'", "''")); sb.Append("',"); sb.Append("'"); sb.Append(dr["LocationID"].ToString().Replace("'", "''")); sb.Append("',"); sb.Append(Nullify(dr["CurrentSalary"])); sb.Append(","); sb.Append(Nullify(dr["DesiredSalary"])); sb.Append(","); sb.Append(Nullify(dr["MinSalary"])); sb.Append(","); sb.Append(Nullify(dr["CurrentHourly"])); sb.Append(","); sb.Append(Nullify(dr["MinHourly"])); sb.Append(",'"); sb.Append(dr["Comments"].ToString().Replace("'", "''")); sb.Append(" "); sb.Append(dr["Resume"].ToString().Replace("'", "''")); sb.Append("',"); sb.Append(dr["SiteId"].ToString()); sb.Append(","); sb.Append(Convert.ToInt32(Convert.ToBoolean(dr["WillRelocate"].ToString()))); sb.Append(",'"); sb.Append(dr["DateAdded"].ToString()); sb.Append("','"); sb.Append(dr["LastModified"].ToString()); sb.Append("')"); cmdInsert.CommandText = sb.ToString(); cmdInsert.ExecuteNonQuery(); #region dsExtras insert //degree info if (dsExtras.Tables.Count > 0 && dsExtras.Tables[0].Rows.Count > 0) { degree = dsExtras.Tables[0].Rows[0][0].ToString(); degreegroup = dsExtras.Tables[0].Rows[0][1].ToString(); if (degree.Length > 0 || degreegroup.Length > 0) { sb.Remove(0, sb.Length); sb.Append("Update JobSeekerFullTextSearch set DegreeLevel='"); sb.Append(degree); sb.Append("', DegreeLevelGroup="); sb.Append(degreegroup); sb.Append(" where JobSeekerId="); sb.Append(jid); cmdInsert.CommandText = sb.ToString(); cmdInsert.ExecuteNonQuery(); } } //disciplines info if (dsExtras.Tables.Count > 1 && dsExtras.Tables[1].Rows.Count > 0) { sb.Remove(0, sb.Length); foreach (DataRow d in dsExtras.Tables[1].Rows) { sb.Append(d[0].ToString()); sb.Append(","); } if (sb.ToString().Length > 0) { cmdInsert.CommandText = "Update JobSeekerFullTextSearch set DisciplineIdList='" + sb.ToString().Substring(0, sb.ToString().Length - 1) + "' where JobSeekerId=" + jid; cmdInsert.ExecuteNonQuery(); } } //industries info if (dsExtras.Tables.Count > 2 && dsExtras.Tables[2].Rows.Count > 0) { sb.Remove(0, sb.Length); foreach (DataRow d in dsExtras.Tables[2].Rows) { sb.Append(d[0].ToString()); sb.Append(","); } if (sb.ToString().Length > 0) { cmdInsert.CommandText = "Update JobSeekerFullTextSearch set IndustryIdList='" + sb.ToString().Substring(0, sb.ToString().Length - 1) + "' where JobSeekerId=" + jid; cmdInsert.ExecuteNonQuery(); } } //jobtypes info if (dsExtras.Tables.Count > 3 && dsExtras.Tables[3].Rows.Count > 0) { sb.Remove(0, sb.Length); foreach (DataRow d in dsExtras.Tables[3].Rows) { sb.Append(d[0].ToString()); sb.Append(","); } if (sb.ToString().Length > 0) { cmdInsert.CommandText = "Update JobSeekerFullTextSearch set JobTypeIdList='" + sb.ToString().Substring(0, sb.ToString().Length - 1) + "' where JobSeekerId=" + jid; cmdInsert.ExecuteNonQuery(); } } #endregion trans.Commit(); Console.WriteLine("Insert for " + jid); } catch (Exception exc) { trans.Rollback(); Console.WriteLine(jid + " - " + exc.ToString()); } finally { trans.Dispose(); trans = null; } #endregion dsExtras.Clear(); dsExtras.Dispose(); dsExtras = null; }//end foreach cmdInsert.Dispose(); cmdInsert = null; } cnInsert.Close(); cnInsert.Dispose(); cnInsert = null; GC.Collect(); } #endregion #region GetJobSeekerExtras private static DataSet GetJobSeekerExtras(string JobSeekerId, SqlConnection cn) { string qry = "Select JobSeekerDegree.DegreeLevel, VDegreeLevels.DegreeGroup from JobSeekerDegree inner join " + " VDegreeLevels on JobSeekerDegree.DegreeLevel=VDegreeLevels.DegreeLevel where JobSeekerId=" + JobSeekerId + ";" + "Select DisciplineID from JobSeekerDiscipline where JobSeekerId=" + JobSeekerId + ";" + "Select IndustryID from JobSeekerIndustry where JobSeekerID=" + JobSeekerId + ";" + "Select JobTypeID from JobSeekerJobType where JobSeekerID=" + JobSeekerId; DataSet ds = new DataSet(); SqlDataAdapter adp = new SqlDataAdapter(qry, cn); adp.Fill(ds); adp.Dispose(); adp = null; return ds; } #endregion #region Nullify private static string Nullify(object p) { if (p != System.DBNull.Value) return p.ToString(); else return "null"; } #endregion }}

GC.Collect(); is killing your code, you DON'T call that method in C# the CLR(common langauge runtime) calls it, any code calling GC.Collect(), must be C++ in .NET. The correct way to do it is very easy in C# wrap all those code blocks with the second USING statement that calls dispose automatically. If you must do it manually then call Dispose Bool. Hope this helps.|||GC.Collect doesn't seem to make a difference when it all runs. I was just trying that to see if it would release all or some of the memory when the class terminated. I'll try the using statement, but since I'm already disposing of all the objects, isn't that basically doing the same thing?|||

No it is not to quote C# and Asp.net expert Jeff Prosise GC.Collect can do really nasty stuff to your application. The USING statement on the other hand calls Dispose automatically if you place it after the Preprocessor directives. You can call Dispose Bool or Call Close and then Dispose. Hope this helps.

|||I just updated the code to use the using statements, but the memory is still not being released. Any other ideas? Thanks.|||

The first thing to do is try click on show execution cost before running your SQL statements in either Management studio or Query Analyzer. If it did not show high cost then try the profiler under tools to run trace on the statements to see what is killing the code. Another option is to open the connection late and close it quickly. Hope this helps.