Bulk Insert with LINQ to SQL

[bob laughland]

Hi All,

I am using a combination of LINQ to SQL and bulk insert. In the
process of performing 'one unit of work' I will be doing things like
reading, and deleting records using LINQ to SQL and then inserting new
records using bulk insert.

One problem I am having is trying to use a 'transaction' to wrap
around the whole thing.

In the LINQ to SQL code I am doing this (basically), and this sorts
out a transaction with all the LINQ to SQL code
,
DataContext dataContext = new DataContext("constring"); // this is not
exactly how I am doing it of course.
dataContext.Connection.Open();
dataContext.Transaction = dataContext.Connection.BeginTransaction();

Then in the Dispose method of my repository I am doing this,

dataContext.Transaction.Commit();
dataContext.Transaction.Dispose();
dataContext.Connection.Close();
dataContext.Dispose();

And in the bulk insert code I am doing something like this,

using (SqlConnection connection = new SqlConnection("constring"))
{
connection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
bulkCopy.DestinationTableName = databaseTableName;
bulkCopy.WriteToServer(dt);
}
}

But the types of things don't match up. The connection opened up by
LINQ to SQL is of type DBConnection, and the transaction is of type
DBTransaction.

The bulk insert code uses Connection and Transaction.

How can I use the transaction I created across the bulk insert?

I tried to use a TransactionScope, but that caused other problems, but
maybe this is an option I need to explore more?

TransactionScope was a problem because other SQL stataments are going
to be running at the same time (for logging) and I didn't want them to
be part of the transaction. I got some exceptions relating to that
too.
connection.Close();