Here are the relevant technologies that I'm working with:
- Devart's dot Connect for Oracle (to facilitate Linq-to-Sql for Oracle).
- Strongly Typed ADO.NET Datasets.
- An Oracle database.
Here's the challenge:
- My legacy code submits database updates with ADO.NET datasets and table adapters.
- I'd like to begin converting that code over to Linq-to-Sql, but I'd like to do it piecemeal to minimize code churn and risk.
Here's my proof of concept schema:
Parent Table
- Parent.Id
- Parent.Name
Child Table
- Child.Id
- Child.ParentId
- Child.Name
Here's my proof of concept code block:
using System;
using System.Data.Common;
using DevArtTry1.DataSet1TableAdapters;
namespace DevArtTry1
{
class Program
{
static void Main(string[] args)
{
using (DataContext1 dc = new DataContext1())
{
dc.Connection.Open();
using (DbTransaction transaction = dc.Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
dc.Transaction = transaction;
Parent parent = new Parent();
parent.Id = 1;
parent.Name = "Parent 1";
dc.Parents.InsertOnSubmit(parent);
dc.SubmitChanges(); // By virtue of the Parent.Id -> Child.ParentId (M:N) foreign key, this statement will impose a write lock on the child table.
DataSet1.CHILDDataTable dt = new DataSet1.CHILDDataTable();
DataSet1.CHILDRow row = dt.NewCHILDRow();
row.ID = 1;
row.PARENTID = 1;
row.NAME = "Child 1";
dt.AddCHILDRow(row);
CHILDTableAdapter cta = new CHILDTableAdapter();
// cta.Transaction = transaction; Not allowed because you can't convert source type 'System.Data.Common.DbTransaction to target type 'System.Data.OracleClient.OracleTransaction.
cta.Update(dt); // The thread will encounter a deadlock here, waiting for a write lock on the Child table.
transaction.Commit();
}
}
Console.WriteLine("Successfully inserted parent and child rows.");
Console.ReadLine();
}
}
}
- As the comments above indicate, the thread will halt indefinitely on the child data adapter's update call because it will wait indefinitely for a write lock on the Child table. [Note the foreign key relationship: Parent.Id -> Child.ParentId (M:N)]
Here's my question:
- I want to wrap the entire code block in a transaction.
- Can I do this? Considering that:
- I want to commit an update on the Parent table with Linq-to-Sql's SubmitChanges method...
- And I want to commit an update on the Child table with an ADO.NET dataset table adapter.
Here are two interesting footnotes:
- This whole things works in reverse. That is, if I wanted to submit changes to the parent table with a data adapter and changes to the child table with linq-to-sql... that would work.
I tried to explicitly attach the transaction to the dataadapter, but the compiler won't allow it because it is a different type of transaction.
CHILDTableAdapter cta = new CHILDTableAdapter(); cta.Transaction = transaction; // Not allowed because you can't convert source type 'System.Data.Common.DbTransaction' to target type 'System.Data.OracleClient.OracleTransaction'. cta.Update(dt); transaction.Commit();