Can't explicitly close the connection that SqlBulkCopy uses

[Rami]

Hi.

It seems that SqlBulkCopy uses its own connection string and I can not
figure out how to either disable connection pooling or explicitly close the
connection that it uses.

I am running SQL Server 2005 SP2 and SQL Profiler indicates "Audit Login"
when WriteToServer is called but does not indicate "Audit Logout" until my
application exists.

I also tried to create a SqlConnection, pass that into the SqlBulkCopy
constructor and explicitly open and close it, but that does not work either.

I also tried to call GC.Collect() but that still does not work.

Any ideas?

Thank you.

Here is a simple code sample:

try
{
using (SqlBulkCopy copy = new
SqlBulkCopy(this.SqlBulkCopyConnectionString,
SqlBulkCopyOptions.KeepIdentity))
{
//insert all rows
foreach (DataTable dt in ds.Tables)
{
copy.DestinationTableName = dt.TableName;
copy.WriteToServer(dt, DataRowState.Added);
copy.WriteToServer(dt, DataRowState.Modified);
copy.WriteToServer(dt, DataRowState.Unchanged);
}
}
}
catch (Exception ex)
{
Logger.Write(ex.ToString());

throw;
}

 

[William Vaughn \(MVP\)]

SqlBulkCopy does not use its own connection string. What you're seeing is
the Connection Pool holding the connection open after your application
closes it. It will remain open until the application ends or the 4-8 minute
(random interval) timeout occurs. Of course, you can (and perhaps should)
disable pooling for this operation.

hth

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________

 

[Rami]

Thank you.

I was able to use "Pooling=False;" on the connection string.