Nsryjdtyk

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...