What is the equivalent C# 3.0 Linq to SQL for this?

Question

Old VB6 Code that needs to be converted:

'DB Connection
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConn
Conn.Open sConn
Conn.BeginTrans

'Recordset
Set rsPrice = New ADODB.Recordset
rsPrice.CursorLocation = adUseClient
rsPrice.CursorType = adOpenKeyset
rsPrice.LockType = adLockBatchOptimistic
rsPrice.ActiveConnection = Conn

What would be the exact equivalent of this in C# .NET 3.5 Linq to SQL? I remember reading somewhere that using System.Transactions would require having MTS on the backend, but my memory is fuzzy this morning.

EDIT:

I could not find a direct match for CursorLocation, CursorType, and LockType up with the new APIs, which is the source of my confusion. I am familiar with TransactionScope, but I do not know if the defaults of TransactionScope are acceptable or if I'm making a bone-headed mistake by trusting in them. The database is SQL Server 2005.

Answer 1

Using System.Transactions requires MSDTC (Microsoft Distributed Transaction Controller) not MTS.

If you can swallow that requirement, then you can do something like this:

using (System.Transactions.TransactionScope scope = new
System.Transactions.TransactionScope())
{
   try
   {
      //todo: do your linq to sql here

      //commit it, everything is successfully done!
      scope.Complete();
   }
}

Answer 2

DataContext comes with a constructor that accepts a connection. I think you can manage your transaction as it has been managed before.

http://msdn.microsoft.com/en-us/library/bb292288.aspx

If you provide an open connection, the DataContext will not close it. Therefore, do not instantiate a DataContext with an open connection unless you have a good reason to do this.

Also, we've used System.Transactions without the Distributed Transaction Coordinator. You only need that if your transaction covers multiple database servers (multiple connections).

Answer 3

Ironically your VB6 is actually a bit suspect. When you set CursorLocation to Client-side then CursorType has no impact. It is only relevant for server-side cursors.

See this KB article to prove that I'm not talking out of my hat!

My understanding is that LinqToSQL requests bring all the data to client. They do not use server-side cursors at all. Therefore, the behaviour of LINQ should be the same as your VB6 code. The only thing you might want to look into is there is a way in ADO to control how the optimistic locking detects update conflicts. You can use timestamp, changed fields or all fields. I'm not sure how LINQ handles update conflicts.