Password Manager UI: Populating DataGridView is Painfully Slow

Question

I'm designing a small account/password manager using C# and SQL Server CE. LINQ to SQL is my ORM of choice.

Here's the data model representing how the SQL Server CE database is set up:

And here's my GUI with a cheesy color-coding system to show how the data is mapped to the GUI:

Since the user is able to dynamically add whatever fields and columns they want, I can't simply bind the DataGridView to a table or collection. Instead, the DataGridView's columns, rows, and cells each have to be populated separately and manually.

Below is my implementation. It gets the job done, but it's TERRIBLY SLOW! We're talking 5-6 seconds to populate the DataGridView with only 10 accounts and 10 fields. The implementation just feels clunky. I've considered creating a DataTable and binding that to the DataGridView, but I would still have to manually populate the columns and rows, and it would be harder without the Tag property to identify columns, rows, and cells. Any suggestions to improve anything ranging from my database design to my GUI are welcome!

Business Logic Methods

    public static List<Account> GetAccountsByCategory(int categoryID)
    {
        using (PassShedEntities context = new PassShedEntities())
        {
            return context.Account.Where(a => a.Category_id == categoryID).OrderBy(a => a.Id).ToList();
        }
    }

    public static Credential GetCredential(int accountID, int fieldID)
    {
        using (PassShedEntities context = new PassShedEntities())
        {
            return (from c in context.Credential
                    join f in context.Field on c.Field_id equals f.Id
                    join a in context.Account on c.Account_id equals a.Id
                    where c.Account_id == accountID
                    where f.Id == fieldID
                    select c).SingleOrDefault();
        }
    }

    public static List<Field> GetFieldsByCategoryID(int categoryID)
    {
        using (PassShedEntities context = new PassShedEntities())
        {
            return context.Field.Where(f => f.Category_id == categoryID).OrderBy(f => f.Display_index).ToList();
        }
    }

Populate Fields (DataGridView Columns)

    private void PopulateFields(int categoryID)
    {
        dgvShed.Columns.Clear();

        foreach (Field field in FieldLogic.GetFieldsByCategoryID(categoryID))
        {
            DataGridViewColumn fieldColumn = new DataGridViewTextBoxColumn();
            fieldColumn.Name = field.Label;
            fieldColumn.HeaderText = field.Label;
            fieldColumn.Tag = field.Id;

            if (field.Display_index != null)
            {
                fieldColumn.DisplayIndex = (int)field.Display_index;
            }

            dgvShed.Columns.Add(fieldColumn);
        }
    }

Populate Accounts (DataGridView Rows)

    private void PopulateAccounts(int categoryID)
    {
        dgvShed.Rows.Clear();

        foreach (Account account in AccountLogic.GetAccountsByCategory(categoryID))
        {
            DataGridViewRow accountRow = new DataGridViewRow();
            accountRow.Tag = account.Id;

            foreach (DataGridViewColumn column in dgvShed.Columns)
            {
                Credential credential = CredentialLogic.GetCredential(account.Id, (int)column.Tag);

                DataGridViewTextBoxCell credCell = new DataGridViewTextBoxCell();
                credCell.Tag = credential.Id;
                credCell.Value = credential.Value;

                accountRow.Cells.Add(credCell);
            }

            dgvShed.Rows.Add(accountRow);
        }
    }

Answer 1

What you call business logic methods is actually your data access logic. And you've put that in a bunch of static methods on what I'm assuming is a static class, which means the GUI code / "populate fields/accounts" is tightly coupled with that specific data access logic implementation (AccountLogic); I'd make them instance methods and inject an instance of AccountLogic and CredentialLogic as a dependency, into the constructor of whoever needs it.

---

Your data model seems to have navigation properties. Your code isn't using them:

        return (from c in context.Credential
                join f in context.Field on c.Field_id equals f.Id
                join a in context.Account on c.Account_id equals a.Id
                where c.Account_id == accountID
                where f.Id == fieldID
                select c).SingleOrDefault();

Could be:

return context.Credential
              .Where(c => c.Account_id == accountID && c.Field.ID == fieldID)
              .SingleOrDefault();

Not sure whether/how it would affect performance though, but definitely clearer.

---

foreach (Account account in AccountLogic.GetAccountsByCategory(categoryID))
{
    ...
}

Might have zero performance impact, but I find this cleaner:

var accounts = AccountLogic.GetAccountsByCategory(categoryID)
foreach (var account in accounts)
{
    ...
}

---

As for performance, I'd be tempted to blame WinForms and constant redrawing taking place - try to disable the refreshing of the DGV's as you're manipulating them; I'm pretty sure WinForms is redrawing the grid every time a column is added, and that requires quite a lot of computing that's not needed until you're done adding all the columns.