LINQ to SQL with bulk insert

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);
}
connection.Close();
}

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.
.

Relevant Pages

  • Bulk Insert with LINQ to SQL
    ... I am using a combination of LINQ to SQL and bulk insert. ... One problem I am having is trying to use a 'transaction' to wrap ...
    (comp.databases.ms-sqlserver)
  • Execution Plan Hinting
    ... BEGIN TRANSACTION ... The first is a deletion of the data and the second is the bulk insert ... Will this give sufficient hint to SQL about the order it processes it ... I've seen that some hints can be passed to SQL for optimizing, ...
    (comp.databases.ms-sqlserver)
  • Re: Execution Plan Hinting
    ... BULK INSERT INTO Whatever... ... PK violation unless the file contains duplicate data so it looks to me like ... I can repro this under SQL 2000 but no problem under SQL ... BEGIN TRANSACTION ...
    (comp.databases.ms-sqlserver)
  • Re: LINQ to SQL with bulk insert
    ... LINQ to SQL is of type DBConnection, and the transaction is of type ... The bulk insert code uses Connection and Transaction. ... How can I use the transaction I created across the bulk insert? ... This forum is devoted to SQL Server as such, and the only answer you will ...
    (comp.databases.ms-sqlserver)
  • Re: Insert Into Without Log
    ... Basically, you cannot turn off transaction logging in SQL Server 2000, you ... can only use the below methods (Bulk insert, SELECT INTO, BCP.exe) to ... SQL Full Text Search Blog ...
    (microsoft.public.sqlserver.fulltext)