Winforms SQL Database App - How to group database transactions

Question

I'm relatively new to progrmming and am building my first serious client app. On one form I have a button which executes quite a large number of transactions with the SQL database, as follows:

private void btnALSave_Click(object sender, EventArgs e)
    {
        try
        {
            //...consider any Special Actions...//

            bool check = Automation_ALHistory_Saved(cboALHistoryType.Text);
            if (check == true)
            {
                //...create History item...//

                cHistory h = new cHistory();
                h.CaseNo = txtCaseNo.Text;
                h.HistoryType = cboALHistoryType.Text;
                h.HistoryDesc = txtALHistoryNote.Text;
                h.Save();

                //...actioned Diary marked complete...//

                cDiary.Completed(txtDiaryID.Text);                                        

                //...create new Diary (with exceptions)...//

                string strNextDiaryID = "";
                if (cboALHistoryType.Text != "Diary Cancelled" && cboALHistoryType.Text != "Case Closed")
                {
                    aMyDate cM = new aMyDate(txtALNextDueDate.Text);
                    DateTime pDate = cM.GetDate();
                    cDiary d = new cDiary();
                    d.CaseNo = txtCaseNo.Text;
                    d.DiaryType = cboALNextType.Text;
                    d.DiaryUserFor = cboALNextFor.Text;
                    d.DiaryNote = txtALNextNote.Text;
                    d.DiaryDueDate = pDate.ToString();
                    d.Save();
                    strNextDiaryID = d.DiaryID;
                }

                //...invoke KPI Manager...//

                int intActionedDiaryID = Int32.Parse(txtALDiaryID.Text);
                int intHistoryID = Int32.Parse(h.HistoryID);
                int intNextDiaryID = Int32.Parse(strNextDiaryID);
                cKPIManager kpi = new cKPIManager(intActionedDiaryID, intHistoryID, intNextDiaryID);

                //...reset forms...//

                ClearActionLinks();
                PopulateForm();
                tabCase.SelectTab("tabDiary");
            }
        }
        catch (Exception eX)
        {
            string eM = "Error attempting to save Diary / History Actions";
            aError err = new aError(eX, eM);
            MessageBox.Show(eM + Environment.NewLine + eX.Message);
        }
    }

What all of this does isn't important for the purposes of my question. What I really need to know, is what is the best way, to prevent (or undo) all the changes made to the database during the running of this method, IF it should fail at ANY point. Basically I need the success (or otherwise) of the method to be an 'all or nothing' scenario. Should the method fail at the last line, I need to undo everything the method had done before that (in terms of the changes it has made to the data on the server).

Advise for this beginner would be much appreciated.

score 2 · Accepted Answer · answered Jun 26 '12 at 09:23

2

Use TransactionScope class

try
{
    // Encapsulate your data access code in this using
    using (TransactionScope scope = new TransactionScope())
    {


        .....

        scope.Completed();
    }
}

answered Jun 26 '12 at 09:23

Steve

213,761
22
232
286

I now get the erro message "msdtc on server is unavailable" each time this method trys to run – PJW Jun 26 '12 at 10:13
Hope this could help (http://stackoverflow.com/questions/1690892/transactionscope-automatically-escalating-to-msdtc-on-some-machines) – Steve Jun 26 '12 at 10:26
Thanks - I just had to enable MSDTC in the windows services. – PJW Jul 04 '12 at 08:51

Winforms SQL Database App - How to group database transactions

1 Answers1