LINQ to SQL with bulk insert

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

Nov 17 '08 #1

Erland Sommarskog

bob laughland (pe*************@gmail.com) writes:

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?

You must perform the BULK INSERT on the same connection as you run
your LINQ queries. Now, how you do that, I don't know, and I think you
are better of asking in a forum devoted to ADO .Net and LINQ to get an
answer.

This forum is devoted to SQL Server as such, and the only answer you will
get here, is that you are better off not using LINQ at all. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 18 '08 #2

bob laughland

On Nov 19, 8:16*am, Erland Sommarskog <esq...@sommarskog.sewrote:

bob laughland (peter.mcclym...@gmail.com) writes:

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?

You must perform the BULK INSERT on the same connection as you run
your LINQ queries. Now, how you do that, I don't know, and I think you
are better of asking in a forum devoted to ADO .Net and LINQ to get an
answer.

This forum is devoted to SQL Server as such, and the only answer you will
get here, is that you are better off not using LINQ at all. :-)

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks.

Better off to not use LINQ at all? Why do you say that?

Is that because people in this group are a bit biased?

Anyway I must admit LINQ has not been everything it is cracked up to
be so far. I have had some issues with it, and wonder whether I should
completely remove LINQ.

Nov 20 '08 #3

Erland Sommarskog

bob laughland (pe*************@gmail.com) writes:

On Nov 19, 8:16*am, Erland Sommarskog <esq...@sommarskog.sewrote:

>This forum is devoted to SQL Server as such, and the only answer you will
get here, is that you are better off not using LINQ at all. :-)

Better off to not use LINQ at all? Why do you say that?

Is that because people in this group are a bit biased?

I did have a smiley in there. :-) But, yes, there may be some bias here.

Anyway I must admit LINQ has not been everything it is cracked up to
be so far. I have had some issues with it, and wonder whether I should
completely remove LINQ.

I guess LINQ has some advantage, as you don't run the risk of getting
SQL syntax errors at run-time. Then again, you can use stored procedures...

Ideally, using LINQ should not be an issue. SQL is a declarative language,
and if LINQ generates SQL, it's also declarative. So you declare your
intent, and the optimizer figures out how to do it. In practice, though,
you may need to tweak the query a little bit, and if nothing else add a
hint. Which I guess is somewhat difficult to do in LINQ. I also suspect
that LINQ does not support all you can do in SQL. This means if you need
to do something more complex, you are back to the object-relational
impedance mismatch.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 21 '08 #4