Database connection for "SqlBulkCopy"

[Curious]

I've used the following for bulk-insertion. However, it throws an
exception about the database connection being closed when executing
"WriteToServer".

void BulkInsertDataTable(DataTable sourceTable)
{
SqlConnection sqlConn = null;
try
{

string connString =
"Server=PANDEVL;Database=db_dynamic;Trusted_Connection=True";
sqlConn = new SqlConnection(connString);
sqlConn.Open(); // Open database connection

using (SqlBulkCopy sC = new SqlBulkCopy(new
SqlConnection(connString)))
{
sC.DestinationTableName = "dbo.algo_swings";
sC.ColumnMappings.Add("sec_id", "sec_id");
sC.ColumnMappings.Add("ticker", "ticker");

sC.WriteToServer(sourceTable); //Exception about
database connection not open

}

sqlConn.Close();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.ToString());
}
finally
{
// Make sure the database connection is closed
if (sqlConn != null)
{
sqlConn.Close();
}
}


I opened it in "sqlConn.Open();" Anyone can point out what the
problem
is?

 

[Gregory A. Beamer]

using (SqlBulkCopy sC = new SqlBulkCopy(new
SqlConnection(connString)))

You are not using the SQL Connection you created for the work, you are
using a new one. The line above is the offending line.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

******************************************
| Think outside the box! |
******************************************