Speedup with MS Access and C# Winforms

Category: c# general

Question

MarkM91 on Wed, 23 Jan 2019 11:50:00


Hi, I was wondering if someone could help me with a project I am doing. I am now close to completion however the program is just really sluggish. This is due to the constant updating/filing between MS Access and the C# program. Because multiple users will be updating the data I need to constantly update the data on the program. My question is, is there any solution to the speed?

I have included one of my .cs files below to show how I am doing this.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using MAB.PCAPredictCapturePlus;

namespace HRSystem
{
    public partial class Course_Attendee : DevComponents.DotNetBar.Metro.MetroForm
    {
        public int CurrentPosition = 0;
        public BindingSource formDataSource;
        public HR_Dataset.RoleDataTable RoleListTable;
        public HR_Dataset.Course_Attendee_BioRow LinkedBio;
        public int AttendeeID = 0;
        public int LastAttendeeID = 0;
        public bool NewRecord = false;

        public Course_Attendee(BindingSource dataSource)
        {
            formDataSource = dataSource;
            InitializeComponent();
            RoleListTable = new HR_Dataset.RoleDataTable();
            this.peopleTableAdapter.Fill(this.hR_Dataset.People);
            this.course_Attendee_StatusTableAdapter.Fill(hR_Dataset.Course_Attendee_Status);
            this.course_Attendee_BioTableAdapter1.Fill(hR_Dataset.Course_Attendee_Bio);
            this.course_AttendeeTableAdapter.Fill(hR_Dataset.Course_Attendee);
            date_booking.DataBindings.Add("ValueObject", formDataSource, "Booking Date");
            combo_status.DataBindings.Add("SelectedValue", formDataSource, "Status", true);
            txt_invname.DataBindings.Add("Text", formDataSource, "Invoice Name", true);
            txt_invaddr.DataBindings.Add("Text", formDataSource, "Invoice Address", true);
            textbox_ponumber.DataBindings.Add("Text", formDataSource, "PO Number", true);
            combo_assosrole.DataBindings.Add("SelectedValue", formDataSource, "Associated Role");
            txt_ID.DataBindings.Add("Text", formDataSource, "ID");
            txt_extorg.DataBindings.Add("Text", formDataSource, "Organisation");
            txt_extrole.DataBindings.Add("Text", formDataSource, "Role");
            combo_extrolecat.DataBindings.Add("SelectedValue", formDataSource, "Role Category", true);
            txt_extmanagername.DataBindings.Add("Text", formDataSource, "Managers Name");
            txt_managerno.DataBindings.Add("Text", formDataSource, "Managers Number");
            txt_manageremail.DataBindings.Add("Text", formDataSource, "Managers Email");
            txt_cost.DataBindings.Add("ValueObject", formDataSource, "Cost");
            check_paid.DataBindings.Add("Checked", formDataSource, "Paid");
            CAttendee_navigator.BindingSource = formDataSource;
            formDataSource.PositionChanged += FormDataSource_PositionChanged;
        }

        private void FormDataSource_PositionChanged(object sender, EventArgs e)
        {
            CurrentPosition = formDataSource.Position;
        }

        private void course_AttendeeBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.formDataSource.EndEdit();
            course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
        }
        private void SetExternalFields(bool Visible)
        {
            layout_extorg.Visible = Visible;
            layout_extrole.Visible = Visible;
            layout_extrolecat.Visible = Visible;
            layoutSpacerItem4.Visible = Visible;
            layout_extmanagername.Visible = Visible;
            layout_extmanagerno.Visible = Visible;
            layout_extmanageremail.Visible = Visible;
        }
        private void combo_assosrole_TextUpdate(object sender, EventArgs e)
        {
            if (formDataSource.Current != null)
            {
                AttendeeID = (int)((DataRowView)this.formDataSource.Current).Row["ID"];
                LinkedBio = (from h in hR_Dataset.Course_Attendee_Bio where h.ID == AttendeeID select h).FirstOrDefault();

                if (!LinkedBio.IsInternalPersonNull())
                {
                    RefreshRoleList(LinkedBio.InternalPerson.ToString());
                    if (combo_assosrole.Items.Count > 0)
                        layout_assosrole.Visible = true;
                    else
                        layout_assosrole.Visible = false;

                    SetExternalFields(false);
                }
                else
                {
                    layout_assosrole.Visible = false;
                    SetExternalFields(true);
                }
            }
        }

        private void txt_attendee_TextChanged(object sender, EventArgs e)
        {
            if (formDataSource.Current != null)
            {
                DataTable Test = formDataSource.Table();
                AttendeeID = (int)((DataRowView)this.formDataSource.Current).Row["Attendee_Record"];
                LinkedBio = (from h in hR_Dataset.Course_Attendee_Bio where h.ID == AttendeeID select h).FirstOrDefault();
                if (LinkedBio != null)
                {
                    if (!LinkedBio.IsInternalPersonNull())
                    {
                        RefreshRoleList(LinkedBio.InternalPerson.ToString());
                        if (combo_assosrole.Items.Count > 0)
                            layout_assosrole.Visible = true;
                        else
                            layout_assosrole.Visible = false;

                        SetExternalFields(false);
                    }
                    else
                    {
                        layout_assosrole.Visible = false;
                        SetExternalFields(true);
                    }
                }
            }
        }

        private void txt_attendee_ButtonCustomClick(object sender, EventArgs e)
        {
            AttendeeInfo Info = new AttendeeInfo(LinkedBio);
            Info.ShowDialog();
            txt_attendee.Text = Info.LinkedBio.FullName;
            course_AttendeeTableAdapter.Update(hR_Dataset.Course_Attendee);
            course_Attendee_BioTableAdapter1.Update(Info.LinkedBio);
        }

        private void p_savenclose_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.formDataSource.EndEdit();
            course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
            this.Close();
        }
        private void RefreshRoleList(string PersonID)
        {
            RoleListTable = roleTableAdapter.GetData_PersonID(int.Parse(PersonID));
            combo_assosrole.DataSource = RoleListTable;
            combo_assosrole.DisplayMember = "Role Title";
            combo_assosrole.ValueMember = "ID";
            combo_assosrole.DataBindings.RemoveAt(0);
            combo_assosrole.DataBindings.Add("SelectedValue", formDataSource, "Associated Role");
        }
        private void Course_Attendee_Load(object sender, EventArgs e)
        {
            AttendeeID = (int)((DataRowView)this.formDataSource.Current).Row["Attendee_Record"];
            LinkedBio = (from h in hR_Dataset.Course_Attendee_Bio where h.ID == AttendeeID select h).FirstOrDefault();
            if (LinkedBio != null)
            {
                if (!LinkedBio.IsInternalPersonNull())
                {
                    RefreshRoleList(LinkedBio.InternalPerson.ToString());
                    if (combo_assosrole.Items.Count > 0)
                        layout_assosrole.Visible = true;
                    else
                        layout_assosrole.Visible = false;

                    SetExternalFields(false);
                }
                else
                {
                    layout_assosrole.Visible = false;
                    SetExternalFields(true);
                }

                txt_attendee.Text = LinkedBio.FullName;
            }
        }
        private void LoadData()
        {
            course_Attendee_BioTableAdapter1.Fill(hR_Dataset.Course_Attendee_Bio);
            course_AttendeeTableAdapter.Fill(hR_Dataset.Course_Attendee);
        }
        private void txt_ID_TextChanged(object sender, EventArgs e)
        {
            bool NewBio = false;
            if (formDataSource.Current != null)
            {
                if (((DataRowView)this.formDataSource.Current).Row["Attendee_Record"] == DBNull.Value)
                {
                    AttendeeSearch Searching = new AttendeeSearch();
                    Searching.ShowDialog();
                    if (Searching.CourseAttendee.ID != 0)
                    {
                        ((DataRowView)this.formDataSource.Current).Row["Attendee_Record"] = Searching.CourseAttendee.ID;
                        course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                        int pos = formDataSource.Position;
                        course_AttendeeTableAdapter.Fill(hR_Dataset.Course_Attendee);
                        course_Attendee_BioTableAdapter1.Fill(hR_Dataset.Course_Attendee_Bio);

                    }
                    else
                    {
                        MessageBox.Show("You did not select or add an attendee, current record will now be removed", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        formDataSource.RemoveCurrent();
                    }
                    NewBio = true;
                }
                AttendeeID = (int)((DataRowView)this.formDataSource.Current).Row["Attendee_Record"];
                LinkedBio = (from h in hR_Dataset.Course_Attendee_Bio where h.ID == AttendeeID select h).FirstOrDefault();

                if (NewBio)
                {
                    AttendeeInfo Info = new AttendeeInfo(LinkedBio);
                    Info.ShowDialog();
                    txt_attendee.Text = Info.LinkedBio.FullName;
                    NewBio = false;
                    LinkedBio = (from h in hR_Dataset.Course_Attendee_Bio where h.ID == AttendeeID select h).FirstOrDefault();
                }

                if (LinkedBio != null)
                {
                    if (!LinkedBio.IsInternalPersonNull())
                    {
                        RefreshRoleList(LinkedBio.InternalPerson.ToString());
                        if (combo_assosrole.Items.Count > 0)
                            layout_assosrole.Visible = true;
                        else
                            layout_assosrole.Visible = false;

                        SetExternalFields(false);
                    }
                    else
                    {
                        layout_assosrole.Visible = false;
                        SetExternalFields(true);
                    }

                    txt_attendee.Text = LinkedBio.FullName;
                }
              
            }
        }

        private void bindingNavigator_Movement(object sender, MouseEventArgs e)
        {
            try
            {
                ToolStripButton nav = (ToolStripButton)sender;
                formDataSource.EndEdit();
                DataTable CAttendeeTable = formDataSource.Table();
                HR_Dataset.Course_AttendeeRow NextRole = hR_Dataset.Course_Attendee[formDataSource.Position];
                TextInfo textInfo = new CultureInfo("en-GB", false).TextInfo;
                switch (nav.Text)
                {
                    case "Move next":
                        NextRole = hR_Dataset.Course_Attendee[formDataSource.Position + 1];
                        break;
                    case "Move previous":
                        NextRole = hR_Dataset.Course_Attendee[formDataSource.Position - 1];
                        break;
                    case "Move last":
                        NextRole = hR_Dataset.Course_Attendee.LastOrDefault();
                        break;
                    case "Move first":
                        NextRole = hR_Dataset.Course_Attendee.FirstOrDefault();
                        break;
                }
                if (NextRole.Lockedby == "")
                {
                    if (Function.GetChangedColumns(hR_Dataset.Role, StringComparison.InvariantCultureIgnoreCase, true).Count > 0)
                    {
                        DialogResult R = MessageBox.Show("Do you want to save changes?", "Saving", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                        if (R == DialogResult.Yes)
                        {
                            course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                            switch (nav.Text)
                            {
                                case "Move next":
                                    formDataSource.MoveNext();
                                    break;
                                case "Move previous":
                                    formDataSource.MovePrevious();
                                    break;
                                case "Move last":
                                    formDataSource.MoveLast();
                                    break;
                                case "Move first":
                                    formDataSource.MoveFirst();
                                    break;
                            }
                        }
                        else if (R == DialogResult.No)
                        {
                            if (NewRecord)
                            {
                                formDataSource.RemoveCurrent();
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                                switch (nav.Text)
                                {
                                    case "Move next":
                                        formDataSource.MoveNext();
                                        break;
                                    case "Move previous":
                                        formDataSource.MovePrevious();
                                        break;
                                    case "Move last":
                                        formDataSource.MoveLast();
                                        break;
                                    case "Move first":
                                        formDataSource.MoveFirst();
                                        break;
                                }
                            }
                            else
                            {
                                hR_Dataset.Role.RejectChanges();
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                                switch (nav.Text)
                                {
                                    case "Move next":
                                        formDataSource.MoveNext();
                                        break;
                                    case "Move previous":
                                        formDataSource.MovePrevious();
                                        break;
                                    case "Move last":
                                        formDataSource.MoveLast();
                                        break;
                                    case "Move first":
                                        formDataSource.MoveFirst();
                                        break;
                                }
                            }
                        }
                        else if (R == DialogResult.Cancel)
                        {
                            return;
                        }
                    }
                    else
                    {
                        if (NewRecord)
                        {
                            DialogResult R = MessageBox.Show("Do you want to save changes?", "Saving", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                            if (R == DialogResult.Yes)
                            {
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                            }
                            else if (R == DialogResult.No)
                            {
                                formDataSource.RemoveCurrent();
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                                switch (nav.Text)
                                {
                                    case "Move next":
                                        formDataSource.MoveNext();
                                        break;
                                    case "Move previous":
                                        formDataSource.MovePrevious();
                                        break;
                                    case "Move last":
                                        formDataSource.MoveLast();
                                        break;
                                    case "Move first":
                                        formDataSource.MoveFirst();
                                        break;
                                }
                            }
                        }
                    }
                }
                else
                {
                    string[] Name = NextRole.Lockedby.Split('.');
                    MessageBox.Show("Record is currently locked by " + textInfo.ToTitleCase(Name[0]) + " " + textInfo.ToTitleCase(Name[1]), "Record is locked", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
                AttendeeID = int.Parse(txt_ID.Text);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        private void Course_Attendee_FormClosing(object sender, FormClosingEventArgs e)
        {
            formDataSource.EndEdit();
            AttendeeID = int.Parse(txt_ID.Text);
            switch (e.CloseReason)
            {
                case CloseReason.FormOwnerClosing:
                case CloseReason.TaskManagerClosing:
                case CloseReason.UserClosing:

                    if (Function.GetChangedColumns(formDataSource.Table(), StringComparison.InvariantCultureIgnoreCase, true).Count > 0)
                    {
                        DialogResult R = MessageBox.Show("Do you want to save changes?", "Saving", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                        if (R == DialogResult.Yes)
                        {
                            course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                        }
                        else if (R == DialogResult.No)
                        {
                            if (NewRecord)
                            {
                                formDataSource.RemoveCurrent();
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                            }
                            else
                            {
                                hR_Dataset.Course_Attendee.RejectChanges();
                            }
                        }
                        else if (R == DialogResult.Cancel)
                        {
                            e.Cancel = true;
                        }
                    }
                    else
                    {
                        if (NewRecord)
                        {
                            DialogResult R = MessageBox.Show("Do you want to save changes?", "Saving", MessageBoxButtons.YesNo, MessageBoxIcon.Information);
                            if (R == DialogResult.Yes)
                            {
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                            }
                            else if (R == DialogResult.No)
                            {
                                formDataSource.RemoveCurrent();
                                course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                            }
                            else if (R == DialogResult.Cancel)
                            {
                                e.Cancel = true;
                            }
                        }
                    }
                    break;
                case CloseReason.MdiFormClosing:
                case CloseReason.None:
                case CloseReason.ApplicationExitCall:
                case CloseReason.WindowsShutDown:
                    course_AttendeeTableAdapter.Update((HR_Dataset.Course_AttendeeDataTable)formDataSource.Table());
                    break;
            }
        }
    }
}



Replies

Andrey Belyakov on Wed, 23 Jan 2019 12:41:56


Hello,

Your code are written in the way when each change on the screen require to have an update from the server.

If you want to reduce amount of request - make request managible. For example - on the changes start/re-start timer and du update on his ticks. 

You also can do some kind of local cache and synchronize cache with a server when you need sync.

Kareninstructor on Wed, 23 Jan 2019 13:07:40


I agree with Andrey, 19 Updates for this is way too many. The only time this might be okay (and that is a slim to none possibility) is when you are having failures when doing less Updates which to me indicates a reason (if humanly possible) to migrate to SQL-Server or another database that is server based for multi-users and if not multi-users I can't imagine still updating that often. Also working with TableAdapters brings more baggage as the data designer which created the classes adds more than what would be needed when not using that method e.g. working with just OleDb (which is the core in your case for data designer/TableAdapter/BindingSource). Yes I realize this is the ending of your project but these are the cold hard facts.  

Any ways other things I noticed.

  • There should not be a need for refreshing data containers, instead what is there should be updated locally rather than reload/refresh.
  • As the above, same goes for repeating Fill operations.

MarkM91 on Fri, 25 Jan 2019 09:01:49


Many Thanks for the replies, I initially did start with a SQL Server however the project lead said they wanted it file based on a secure drive so was forced to either use flat files or MS Access.

I do realise that MS Access is not really multi user oriented and a SQL Server is the best option. I will look at the updating mechanics to try and speed it up so many thanks for your answers.

Andrey Belyakov on Fri, 25 Jan 2019 09:16:39


Access is not a flat file system. It have single file with multiple table & code inside.
You may have something very similar by using one of the versions of MS SQL (I was not able to get it installed/working) - the same single file and attachment to the service on demand.

I would not recomend use a flat files - in multiuser mode you will have to write a version of your "SQL Server" to handle all aspects of data manipulation.