SqlCommandBuilder not disposing of parameter list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using SqlCommandBuilder to create an update command to update a table on
my DB of approx 20,000 rows and 10 columns. Unfortunately, I am detecting a
small memory leak using PerfMon to monitor Private Bytes and Working Set.
I am also using .NET Memory Profiler which is telling me that I have
thousands of Undisposed Instances under the NameSpace
System.Collections.Generic and the Name List<SqlParameter>.Enumerator.
There is one Undisposed Instance for each row in my DB on the first update
and approx 5,000-10,000 for each additional update. I am assuming these
undisposed items are my memory leak. Further I am assuming the undisposed
items are a list of Sql Parameters that sqlcommandbuilder is creating and not
disposing of when I dispose of the builder object.
Here is a code Snippet:

SqlCommand sqlSelect = new SqlCommand("select * from "+tableName,
this.getConnection());
SqlDataAdapter dsCmd = new SqlDataAdapter(sqlSelect);
DataTable dtable = dt; //declare a data table
openConnection();
//Sql Command Builder code
SqlCommandBuilder updateCmd = new SqlCommandBuilder(dsCmd);
dsCmd.UpdateCommand = updateCmd.GetUpdateCommand();
//Update DB with dataset
dsCmd.Update(dtable);
dtable.Dispose();
dsCmd.Dispose();
updateCmd.Dispose();
closeConnection();

Any thoughts on how I properly dispose of the apparent list of parameters
that SqlCommandBuilder is creating?

P.S. I have attempted to not use Command Builder and instead manually
declare my SQL Commands, but that took my update time to the DB from less
than a minute to almost 20 minutes. But that is for another thread.
 
Update. I switched over to using a Stored Procedure on the SQL DB and
setting up my own Parameters, and I still had the same leak of undisposed
objects. I commented out the sqlcommand update command, the parameters.add
and the dataadapter.update and the leak went away. I then commented out
just the dataadapter.updatecommand and the dataadapter.update and the leak
still did not present itself. I finally commented out only the
dataadapter.update and the leak still did not present itself. So, I'm
suspecting the update is what's causing my problems.
Has anyone seen anything like this?
 
The CommandBuilder sucks. Tooooooooo many issues. Better to bite the bullet
and build everything you need with the DACW and parameters.
 
Earl said:
The CommandBuilder sucks. Tooooooooo many issues. Better to bite the bullet
and build everything you need with the DACW and parameters.
Click to expand...

Unfortunately I'm using Visual C# Express Edition to connect to a SQL Server
2000, so I don't believe I can use the DACW. Correct me if I'm wrong.
I have actually manually built the Data Adapter and the SQL Commands, but
I'm still getting the same Undisposed Instances. Now, I could be
misinterpreting how Garbage Collection works and what is exactly meant by an
Undisposed Instance. I am thinking that these Undisposed Instances should
go away when either, one I dispose of the Data Adapter (which is supposed to
be unnecessary), two I close the DB Connection by calling conn.close(), three
I leave the scope of the method which is performing the DB update or four I
call System.GC.Collect(). I have tried all four methods and I'm still
seeing the Instances. So that tells me one of the following is happening.
1. This is normal behavior and I don't need to worry about it, the GC will
get around to cleaning them up when it feels like it.
2. The .NET Memory Profiler I'm using is lying to me.
3. There is a bug in the way .NET cleans up Data Adapters and DB
Connections which is causing a memory leak.
AFAIK, I'm not the only one in the world using a DB with my C# Windows form,
so I'm hesitant to think it's the last one, but I just don't know.
 
Back
Top