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:

Data Model

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

enter image description here

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);
        }
    }
  • 5
    I'd question the design of the thing, a password manager should treat passwords as first-class citizens - if it's just another text field, you have a potential security issue. – Mathieu Guindon Apr 24 '14 at 17:33
up vote 4 down vote accepted

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.

  • 1
    I've updated the design here quite tremendously since this was posted, but your suggestions were still helpful. My updates actually include some of your suggestions. My biggest performance problem (a "duh" moment) was that the GetCredential() method - which joins 3 tables - was nested within two foreach loops. I restructured that by retrieving the credentials beforehand and iterating through a list of them instead. – Tyler Daniels Apr 25 '14 at 16:34

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.