Imagine, there is a program, written in C#, in which there is an object (A) which references 2 sets of objects ( set of B, set of C).
These objects are stored in MS SQL as rows in TableA, TableB, TableC
where
TableA has a field ID_Of_B referencing TableB as foreign key in a M:M relationship
TableC has a field ID_Of_A referencing TableA as foreign key in a M:1 relationship
(therefore, a single object A = 1 row in TableA, and it can be linked to multiple rows in TableB and TableC)
the question I'm trying to ask is, if I want to use C# to select the current B objects and C objects linked to object A (of a particular ID in TableA), how do I use SqlTransaction to do it in such a way the select statement will retrieve the values that are accurate?
I tried to do it with 3 different select statements (select from A), then (select from B), then (select from C), but within these 3 select statements either of the 2 tables could have changed information and the result set may not be accurate.
TLDR: how to use c# SqlTransaction to guarantee a select which pulls multiple table data in 1:M and M:M relationships in an atomic and accurate manner (does not let other simultaneous update / delete statements in tables B and C affect the integrity of the result set)
You can set the SqlTransaction.IsolationLevel to guarantee consistency. Either Snapshot or Serializable should work, depending exactly on your use case.