如何SQL事务工作？ [英] How Do SQL Transactions Work?

问题描述

我没有在SQL过长的工作，但我想我了解，通过包装一个事务中的SQL语句，所有语句完成，要么都不做。这里是我的问题。我有一个具有LINEITEM集合的命令对象。该生产线项上order.OrderId有关。我已验证的的所有ID设置和正确的但是当我尝试保存（插入）我得到冲突与国外订单的 INSERT语句KEY约束FK_OrderItemDetail_Order。冲突发生于数据库迈德特，表dbo.Order，列的OrderId'

I have not been working in SQL too long, but I thought I understood that by wrapping SQL statements inside a transaction, all the statements completed, or none of them did. Here is my problem. I have an order object that has a lineitem collection. The line items are related on order.OrderId. I have verified that all the Ids are set and are correct but when I try to save (insert) the order I am getting The INSERT statement conflicted with the FOREIGN KEY constraint "FK_OrderItemDetail_Order". The conflict occurred in database "MyData", table "dbo.Order", column 'OrderId'.

伪代码：

create a transaction
transaction.Begin()
Insert order
Insert order.LineItems <-- error occurs here
transaction.Commit

实际代码：

...
entity.Validate();
if (entity.IsValid)
{
    SetChangedProperties(entity);
    entity.Install.NagsInstallHours = entity.TotalNagsHours;
    foreach (OrderItemDetail orderItemDetail in entity.OrderItemDetailCollection)
    {
        SetChangedOrderItemDetailProperties(orderItemDetail);
    }
    ValidateRequiredProperties(entity);
    TransactionManager transactionManager = DataRepository.Provider.CreateTransaction();
    EntityState originalEntityState = entity.EntityState;
    try
    {
        entity.OrderVehicle.OrderId = entity.OrderId;
        entity.Install.OrderId = entity.OrderId;
        transactionManager.BeginTransaction();

        SaveInsuranceInformation(transactionManager, entity);
        DataRepository.OrderProvider.Save(transactionManager, entity);
        DataRepository.OrderItemDetailProvider.Save(transactionManager, entity.OrderItemDetailCollection);             if (!entity.OrderVehicle.IsEmpty)
        {
            DataRepository.OrderVehicleProvider.Save(transactionManager, entity.OrderVehicle);
        }
        transactionManager.Commit();
    }
    catch
    {
        if (transactionManager.IsOpen)
        {
            transactionManager.Rollback();
        }
        entity.EntityState = originalEntityState;
    }
}
...

有人建议我需要使用两个事务，一个用于秩序，一个是行项目，但我有理由相信这是错误的。不过，我一直在打这场超过了一天，我需要解决它，所以我能够继续前进，即使这意味着周围使用一个糟糕的工作。难道我也许只是在做一些愚蠢的事？

Someone suggested I need to use two transactions, one for the order, and one for the line items, but I am reasonably sure that is wrong. But I've been fighting this for over a day now and I need to resolve it so I can move on even if that means using a bad work around. Am I maybe just doing something stupid?

推荐答案

我注意到，你说你正在使用NetTiers你的代码生成。

I noticed that you said you were using NetTiers for your code generation.

我用NetTiers自己和发现，如果从表中删除外键约束，其重新添加到同一个表，然后运行构建脚本NetTiers在数据库中进行更改后再次可能有助于恢复数据访问层。我已经试过这与积极成果的时刻。

I've used NetTiers myself and have found that if you delete your foreign key constraint from your table, add it back to the same table and then run the build scripts for NetTiers again after making your changes in the database might help reset the data access layer. I've tried this on occasion with positive results.

祝您的问题。

这篇关于如何SQL事务工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！