[SQLDownUnder] .net TransactionScope and Deferred Constraints

[sql]

Hello,

 

I am wanting to create surveys with varying amount of questions by inserting into two relations (this is

a simplified version)

 

Survey (SurveyID INT, SurveyName VARCHAR)

SurveyQuestions (QuestionID INT, SurveyID INT, QuestionText VAHRCHAR)

 

I am trying to achieve this by using TransactionScope. My code is looking something like this (c#):

 

Using (TransactionScope scope = new TransactionScope)

{

            Using (SqlCommand insertSurvey ….)

            {

                        …..

                        insertSurvey.ExecuteNonQuery;

}

 

            Using (SqlCommand insertQuestion ….)

            {

                        ….

Foreach (SurveyQuestion q in questions)

                        {

….

insertSurveyQuestion.ExecuteNonQuery; ***

                        }                      

            }

            scope.Commit();

}

 

My problem is that when the code gets to *** I am violating foreign key constraints.

 

How can I set the transaction to defer the checking of the constrains either in my

ADO.net code or in my T-SQL

 

kind regards,

Jonathon Kresner

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net