0
\$\begingroup\$

I am loading data from a excel file to sql, I added comments to the code to further clarify what each lines purpose is.

private void Events_Click(object sender, RoutedEventArgs e)
{
    //open dialog for file select of event file
    string filePath = OpenFileDialogParameters.FileDialog("", "", "", 2);
    //if file select is not null
    if (filePath != null)
    {
        //catch errors in this block
        try
        {
            //begin a transaction
            using (var transaction = Context.Database.BeginTransaction())
            {
                //parse file into dataset
                Events_DataSet = ReadExcel.OpenExcel(filePath);
                int Drivecode = 0;
                DBEVENT[] Events = //get all rows in table 0
                                    (from x in Events_DataSet.Tables[0].Select()
                                     //last row must be drivecode, test to ensure it is not empty string
                                     where x.ItemArray[x.ItemArray.Length - 1].ToString() != string.Empty
                                     let dc = Drivecode = int.Parse(x.ItemArray[x.ItemArray.Length - 1].ToString())
                                     //join Session Drive status on drivecode
                                     join sds in Context.SDS on dc.ToString() equals sds.DriveCode into hasSD
                                     //ensure there is a drive loaded for this file and that the file hasnt already been processed
                                     where hasSD.Count() == 1 & hasSD.First().Events == false
                                     //select all items that fit these parameters to a array
                                     select new DBEVENT(x)).ToArray();
                //if duplicates found fail with error message
                if (Context.DBEVENTS.Where(x =>  x.קוד_נסיעה == Drivecode).Count() != 0)
                {

                    StatusBox.Text += "קובץ אירועי נסיעה כבר נטען למסד הנתונים עבור " + Environment.NewLine + "נסיעה " + Events[0].קוד_נסיעה + Environment.NewLine;
                    button2s.Fill = Brushes.IndianRed;
                    return;
                }
                else
                {
                    //ensure there are events to add
                    if (Events.Count() > 0)
                    {
                        //add each event to the table
                        foreach (var Event in Events)
                        {
                            Context.DBEVENTS.Add(Event);
                        }
                        //start inner try catch to handle saving
                        try
                        {
                            //Save Data
                            Context.SaveChanges();
                            //After Save add Completion to SessionDriveTable
                            var sdcompletion = (from sda in Context.SDS
                                                where Drivecode.ToString() == sda.DriveCode
                                                where sda != null
                                                select sda).First().Events = true;
                            //save completion
                            Context.SaveChanges();
                            //commit 
                            transaction.Commit();
                            button2s.Fill = Brushes.LawnGreen;
                        }
                        catch (Exception ex)
                        {
                            //rollback changes
                            transaction.Rollback();
                            //throw this error to the outer exception handler
                            //to ensure the entities are removed from the change tracker
                            throw ex;
                        }
                    }
                    //no events created could be drive doesn't exist or events already added
                    else
                    {
                        button2s.Fill = Brushes.IndianRed;
                        DataRow dr = Events_DataSet.Tables[0].Rows[1];
                        var DriveCode = dr[dr.ItemArray.Count() - 1].ToString();
                        StatusBox.Text += "מספר הנסיעה " + DriveCode + " לא קיים במסד הנתונים." + Environment.NewLine + "אנא השלם פרטי נסיעה טרם טעינת קובץ." + Environment.NewLine;
                        return;
                    }
                }
            }
        }
        //main exception handler
        //rolls back any tracked changes in entity framework
        //unwraps exceptions to the most relevant inner exception 
        catch (Exception ex)
        {
            ExceptionRollback();
            while (ex.InnerException != null)
            {
                ex = ex.InnerException;
            }
            button2s.Fill = Brushes.IndianRed;
            StatusBox.Text += ex.Message + Environment.NewLine;
        }
    }
}

Exception roll back simply removes any items added modified deleted using entity framework change tracker.

public void ExceptionRollback()
{
    if (Context.ChangeTracker.HasChanges())
    {
        foreach (var entry in Context.ChangeTracker.Entries()
            .Where(entry => entry.State == EntityState.Added ||
            entry.State == EntityState.Modified ||
            entry.State == EntityState.Deleted))
        {
            entry.State = EntityState.Detached;
        }
    }
}

Really looking for ideas on how to abstract the functionality away from the UI code...

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.