Will using a transaction eliminate the need to use secondary dataContexts to attempt sql insert/update/deletes?

Question

So I have a list of IT/developer tickets stored in a database via Linq-To-Sql. Save attempts are only done at the user request, or if they say save on close. My concern was that a user makes a change to 2 or more tickets without saving in-between.

For some reason if the database rejects the change to one, doesn't give me very much information about which item, or which field on which ticket has the problem so I can tie it back to giving an indicator to the user. Now I can't save the good data on the records they edited, because one change is stuck in the queue, and now SubmitChanges no longer functions.

I built a buffering system where every ticket gets wrapped in another class so that changes are saved to a buffer instead of directly to the linq to sql object, where for each ticket changed:

• I could create a new dataContext instance
• attempt to save the changes for an individual row
• then report back to the user each one that failed.

The code I'm guessing has quite a smell, or at least is ugly.

My co-worker just suggested I try transactions. I'd rather not do tear-down of what I've built to test a transactional approach.

• Will transactions correctly reset all the changes to an item, or all items that SaveChanges would attempt to save? afterwards I would expect hasChanges to be empty, and SaveChanges to do nothing.
• Is there a better way to submit individual row changes at a time in linq-to-sql?
• Am I missing something in SaveChanges exceptions that would really help me know which row, and which field on that row is having a problem?

Perhaps I shouldn't allow (because of linq-to-sql or the real world doesn't need the ability to make changes on multiple units without deciding to save or not) the user to leave the ticket until they have decide if they want to save changes to it or not?

Answer 1

I have rolled my own buffering class, that's a little messy but works for sure saving one record at a time.

This is the parent class for my business objects that wrap around a linq entity.

public class BufferedLinqChange
{
    LqGpsDataContext _dataContext;

    internal BufferedLinqChange(LqGpsDataContext dataContext)
    {
        _dataContext = dataContext;
    }

    protected void SetBufferedProperty<T>(string key,Action linqAction
        ,bool linqEqualsValue,Action bufferAction)
    {
        if (linqEqualsValue)
        {
            if (Changes.ContainsKey(key))
                Changes.Remove(key);
        }
        else
        Changes.InsertOrUpdate(key, linqAction); bufferAction();
    }

    protected Dictionary<String, Action> Changes = new Dictionary<string, Action>();

    public int ChangeCount { get { return Changes != null ? Changes.Count : 0; } }
    public bool hasChanges { get { return Changes != null ? Changes.Count > 0 : false; } }

    public void SubmitChanges()
    {
        _dataContext.SubmitChanges();
        if (ChangeCount > 0)
        {
            Changes.ForEach((item) => item.Value.Invoke());
            _dataContext.SubmitChanges();
        }
    }
    public void CancelChanges()
    {
        if (Changes != null)
            Changes.Clear();
    }
}

Here's a sample of one of the properties:

#region assetTag


    String _AssetTag;
    public const String STR_assetTag = "assetTag";
    public String assetTag
    {
        get { return (Changes.ContainsKey(STR_assetTag) ? _AssetTag : Asset.assetTag); }
        set
        {
            SetBufferedProperty<String>(STR_assetTag
                , () => Asset.assetTag = value, Asset.assetTag == value, () => _AssetTag = value);
        }
    }
    #endregion