How to: Reuse a Connection Between an ADO.NET Command and a DataContext (LINQ to SQL)
Because LINQ to SQL is a part of the ADO.NET family of technologies and is based on services provided by ADO.NET, you can reuse a connection between an ADO.NET command and a DataContext.
The following example shows how to reuse the same connection between an ADO.NET command and the DataContext.
string connString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\northwind.mdf; Integrated Security=True; Connect Timeout=30; User Instance=True"; SqlConnection nwindConn = new SqlConnection(connString); nwindConn.Open(); Northwnd interop_db = new Northwnd(nwindConn); SqlTransaction nwindTxn = nwindConn.BeginTransaction(); try { SqlCommand cmd = new SqlCommand( "UPDATE Products SET QuantityPerUnit = 'single item' WHERE ProductID = 3"); cmd.Connection = nwindConn; cmd.Transaction = nwindTxn; cmd.ExecuteNonQuery(); interop_db.Transaction = nwindTxn; Product prod1 = interop_db.Products .First(p => p.ProductID == 4); Product prod2 = interop_db.Products .First(p => p.ProductID == 5); prod1.UnitsInStock -= 3; prod2.UnitsInStock -= 5; interop_db.SubmitChanges(); nwindTxn.Commit(); } catch (Exception e) { Console.WriteLine(e.Message); Console.WriteLine("Error submitting changes... all changes rolled back."); } nwindConn.Close();
System.LinqSample Code Is Unclear (What is the "Northwnd" class?)
You need to understand that Northwnd inherits DataContext (see a similar DataContext definition at http://msdn.microsoft.com/en-us/library/bb399375.aspx) in order to understand this code sample.
- 12/9/2008
- Christopher Falter
- 10/9/2009
- ddrogahn
The VB.NET Sample Does Not Use the Shared Connection Correctly
The C# sample uses the shared conn and transaction correctly, per these steps:
1. Set the conn and transaction properties of a SqlCommand with shared conn and transaction, then run it.
2. Pass the shared conn into the constructor of the DataContext, then set the DataContext's .Transaction property with shared transaction.
3. Use the DataContext
4. Commit or Rollback, then close
The VB.NET sample unfortunately gets step 2 wrong. Instead of passing the SqlConnection instance to the DataContext constructor, it passes the string constant "..." (which is not a valid connection string and therefore should fail at run-time).
1. Set the conn and transaction properties of a SqlCommand with shared conn and transaction, then run it.
2. Pass the shared conn into the constructor of the DataContext, then set the DataContext's .Transaction property with shared transaction.
3. Use the DataContext
4. Commit or Rollback, then close
The VB.NET sample unfortunately gets step 2 wrong. Instead of passing the SqlConnection instance to the DataContext constructor, it passes the string constant "..." (which is not a valid connection string and therefore should fail at run-time).
- 12/9/2008
- Christopher Falter