SQL DB Transaction fails when executing INSERT after DELETE, on duplicated PK

[royadams]

Hi,

Your help is very much appreciated.


When running a DELETE and then INSERT (BULK INSERT) sql commands , I am receiving an error
“Cannot insert duplicate key”.

It seems like the DELETE doesn’t actually delete the records, and therefore the INSERT fails.


[Framework 4.0, SQL Server 2008].

Heres' the code I'm using:

public static bool RunInTransInternal(List<SqlCommand> cmds, string conn)
        {
            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew,
                    new TransactionOptions()
                    {
                        IsolationLevel = System.Transactions.IsolationLevel.Serializable,
                        Timeout = TimeSpan.FromHours(2)
                    }))
                {
                    using (SqlConnection connection1 = new SqlConnection(conn))
                    {
                        connection1.Open();
                        for (i = 0; i < cmds.Count; i++)
                        {
                            cmds[i].Connection = connection1;
                            cmds[i].CommandTimeout = 36000;
                            cmds[i].ExecuteNonQuery();
                        }
                    }
                    scope.Complete();

                }
            }

            catch (Exception ex)
            {
                return false
            }

            return true;
        }

Cheers
Roy