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.
Example
The following example shows how to reuse the same connection between an ADO.NET command and the DataContext.
Dim conString = "Data Source=.\SQLEXPRESS;AttachDbFilename=c:\northwind.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True"
Dim northwindCon = New SqlConnection(conString)
northwindCon.Open()
Dim db = New Northwnd("...")
Dim northwindTransaction = northwindCon.BeginTransaction()
Try
Dim cmd = New SqlCommand( _
"UPDATE Products SET QuantityPerUnit = 'single item' " & _
"WHERE ProductID = 3")
cmd.Connection = northwindCon
cmd.Transaction = northwindTransaction
cmd.ExecuteNonQuery()
db.Transaction = northwindTransaction
Dim prod1 = (From prod In db.Products _
Where prod.ProductID = 4).First
Dim prod2 = (From prod In db.Products _
Where prod.ProductID = 5).First
prod1.UnitsInStock -= 3
prod2.UnitsInStock -= 5
db.SubmitChanges()
northwindTransaction.Commit()
Catch e As Exception
Console.WriteLine(e.Message)
Console.WriteLine("Error submitting changes... " & _
"all changes rolled back.")
End Try
northwindCon.Close()
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();