StringBuilder and memory management

[Brent]

I'm constructing a long SQL string using StringBuilder. Every 100 or so
iterations, I write the SQL to the database, and use the
StringBuilder.Remove method to "zero out" the string: e.g.,
"oStringBuilder.Remove(0,oStringBuilder.Length )". I then reuse
oStringBuilder to build the next SQL statement.

Sometimes this coding works fine, but as often as not the system reports
an out-of-memory error. I'm guessing that the Remove method deletes the
text in a string, but does not remove its memory allocation, and when I
reuse it, the string grows ever larger.

Is there any mechanism to destroy memory used -- or shrink it to zero
(or something!) -- within the StringBuilder class? If not, any other ideas?

Many thanks.

-- Brent

//==================================================
//Code snippet builds a valid MySQL statement
sqlstart = "Insert into myTable (Field1, Field2) Values ";
string field;
int count = 1;
StringBuilder sql = new StringBuilder(sqlstart);
char[] fieldsplitter = {'|'};

//after getting an array of text rows
for(int i = 0; i < arrRows.Length; i++, count++)
{
string[] arrFields = arrRows[i].Split(fieldsplitter);
sql.Append("(");
for(int j=0; j < arrFields.Length; j++)
{
field = arrFields[j];
sql.Append("'"+field+"',");

}
if (count==100 | i == arrRows.Length - 2)
{
sql.Append(");");
try
{
//write SQL to DB using "run" class
run.exec(sql.ToString());
}
catch //error in sql
{
Response.Write("SQL error: " + sql);

}

count = 0;
sql.Remove(0,sql.Length);
sql.Append(sqlstart);

}
else{sql.Append("),");}
}

//===============================================

[theath]

why not just reinstantiate the object? i don't know how the
stringbuilder class keeps references to its data, so it might not be
killing all references to it (keeping gc from happening).
reinitializing the stringbuilder ought to fix it if it's causing the
problem.

if that doesn't fix it, i'd suggest running a profiler on your
application to see where memory is getting sucked out.

hth
terry

[Brent]

Thanks for the tip. Turns out that I was reading in very large strings
from a file, and the memory was eaten up by that portion of the code,
not the code I posted earlier.

Thanks for your help!

theath wrote:

why not just reinstantiate the object? i don't know how the
stringbuilder class keeps references to its data, so it might not be
killing all references to it (keeping gc from happening).
reinitializing the stringbuilder ought to fix it if it's causing the
problem.

if that doesn't fix it, i'd suggest running a profiler on your
application to see where memory is getting sucked out.

hth
terry