CODEWITHSUNDEEP
c#sqllinq
KleberBH
KleberBH

Reputation: 462

Save data with foreign key or roll back if any fail

I have a Linq query that need to create or update one table, then gets the id and populate another table.

example:

public Batch SaveBatch(Batch batchModel, Audit auditModel)
{
    int batchID = 0;
    Batch batch;
    Audit audit = null;

    var exists = db.Batches.Where(t => t.BatchID == batchModel.BatchID);
    if (exists.Any())
    {
        batch = exists.First();
        batchID = batch.BatchID;

        if(batch.StatusID != 1) 
        {
           audit = CreateAudit(batchID, auditModel);
        }
        else
        {
           if (auditModel.EventTypeID == 16) 
           {
              audit = CreateAudit(batchID, auditModel);
           }
           else
           {
               var checkTrans = GetAllTransactions(batchModel.BatchID);
               if (checkTrans.Count() > 1)
               {
                   audit = CreateAudit(batchID, auditModel);
               }
           }
       }
    }
    else
    {
        batch = new Batch()
        {
            CreatedDate = batchModel.CreatedDate
        };

        db.Batches.InsertOnSubmit(batch);
        batchID = batch.BatchID;
        audit = CreateAudit(batchID, auditModel);
    }

    batch.Reason = batchModel.Reason;

    db.Audit.InsertOnSubmit(audit);
    db.SubmitChanges();
    return batch;
}

I am getting error saying the foreign key doesn't exist or doesn't match.

What I need is to insert or update the table Batches, then gets the ID from record inserted or updated on Batches. With the BatchesID I can populate table Audit.

But if anything goes wrong on the insert into table Audit it roll back the changes made to table Batches.

Table Audit contains a foreign key to Batches table.

SQL Tables:

Batches

BatchesID | Reason | CreatedDate | ... 

Audit

AuditID | BatchesID | ...

Upvotes: 0

Views: 64

Answers (1)

Wendy E
Wendy E

Reputation: 99

If you wrap your saves in a transaction scope, when you SaveChanges, your batch will get an Id and you can use it for the Audit. If anything fails inside the transaction, everything will be rolled back. See https://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(v=vs.110).aspx for good info on transactions, but try something like this:

using (TransactionScope scope = new TransactionScope())
{
if (exists.Any())
{
    batch = exists.First();
    batchID = batch.BatchID;

    if(batch.StatusID != 1) 
    {
       audit = CreateAudit(batchID, auditModel);
    }
    else
    {
       if (auditModel.EventTypeID == 16) 
       {
          audit = CreateAudit(batchID, auditModel);
       }
       else
       {
           var checkTrans = GetAllTransactions(batchModel.BatchID);
           if (checkTrans.Count() > 1)
           {
               audit = CreateAudit(batchID, auditModel);
           }
       }
   }
}
else
{
    batch = new Batch()
    {
        CreatedDate = batchModel.CreatedDate
    };

    db.Batches.InsertOnSubmit(batch);
    db.SaveChanges();
    batchID = batch.BatchID;
    audit = CreateAudit(batchID, auditModel);
}

batch.Reason = batchModel.Reason;

db.Audit.InsertOnSubmit(audit);
db.SaveChanges();
scope.Complete();
}
return batch;

Upvotes: 1

Related Questions