467,917 Members | 1,406 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,917 developers. It's quick & easy.

Bulk Insert with LINQ to SQL

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();
Nov 17 '08 #1
  • viewed: 4134
Share:

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by php newbie | last post: by
6 posts views Thread by pk | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
4 posts views Thread by =?Utf-8?B?RXJpYyBGYWxza2Vu?= | last post: by
3 posts views Thread by bob laughland | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.