2
\$\begingroup\$

I had an idea of checking if Entry ID already exists in database. If it doesn't there is no point of removing non existing entry.

So when user types then each time new character is added I am opening or closing connection to my database file. This is my concern.

But what if there might be hundredths of entries? Could that cause performance problems? If so, how can I avoid this?

Here is my code:

namespace Password_Manager
{
    public partial class DeleteEntryWindow : Form
    {
        string user, pass, filePath;

        // Initializing MainWindow form.
        MainWindow mainWindow;

        public DeleteEntryWindow()
        {
            InitializeComponent();

            // Creating an EventHandler for textbox when text is chaned.
            txtEntryID.TextChanged += new EventHandler(ValidateInput);
        }

        public DeleteEntryWindow(MainWindow viaParameter, 
            string user, string pass, string filePath)
            : this()
        {
            mainWindow = viaParameter;
            this.user = user;
            this.pass = pass;
            this.filePath = filePath;
        }

        private void ValidateInput(object sender, EventArgs e)
        {
            int intNumber = 0;

            // Checking if input is empty, not a number, or is less than 1.
            if (!string.IsNullOrEmpty(txtEntryID.Text) &&
                int.TryParse(txtEntryID.Text, out intNumber) &&
                intNumber > 0)
            {
                lblMessage.Text = "Entry ID is valid.";
                lblMessage.ForeColor = Color.Green;
                btnDeleteEntry.Enabled = true;
            }
            else
            {
                lblMessage.Text = "You must enter Entry ID number!";
                lblMessage.ForeColor = Color.IndianRed;
                btnDeleteEntry.Enabled = false;
            }

            if (!DoesIDExist(intNumber, filePath))
            {
                lblMessage.Text = "Entry ID doesn't exist!";
                lblMessage.ForeColor = Color.IndianRed;
                btnDeleteEntry.Enabled = false;
            }
        }

        private void btnDeleteEntry_Click(object sender, EventArgs e)
        {
            DialogResult result = MessageBox.Show
                ("Are you sure you want to remove this entry?",
                "Information", MessageBoxButtons.YesNo,
                MessageBoxIcon.Information);

            if (result == DialogResult.Yes)
            {
                // SQL query which will delete entry by using entry ID.
                string sql = "DELETE FROM PersonalData WHERE DataID = " +
                    txtEntryID.Text;

                DeleteData(sql);

                lblMessage.Text = "Entry was deleted!";
                lblMessage.ForeColor = Color.Green;
            }
            else
            {
                // Do nothing.
            }
        }

        private void DeleteData(string sql)
        {
            HashPhrase hash = new HashPhrase();

            string hashShortPass = hash.ShortHash(pass);

            // Creating a connection string. Using placeholders make code
            // easier to understand.
            string connectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};
                  Persist Security Info=False; Jet OLEDB:Database Password={1};";

            using (OleDbConnection connection = new OleDbConnection())
            {
                // Creating command object.
                // Using a string formatting let me to insert data into
                // place holders I have used earlier.
                connection.ConnectionString =
                    string.Format(connectionString, filePath, hashShortPass);

                using (OleDbCommand command = new OleDbCommand(sql, connection))
                {
                    OleDbParameter prmDataID = new OleDbParameter
                        ("@DataID", txtEntryID.Text);

                    command.Parameters.Add(prmDataID);

                    try
                    {
                        connection.Open();
                        command.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error: " + ex.Message);
                    }
                }
            }

            // Refreshing state of main window.
            mainWindow.DisplayFileContent(filePath);

            lblMessage.Text = "Data was successfully deleted.";
            lblMessage.ForeColor = Color.Green;

            // Clearing text box field.
            txtEntryID.Clear();
        }

        private bool DoesIDExist(int dataID, string filePath)
        {
            HashPhrase hash = new HashPhrase();
            DataTable temp = new DataTable();

            string hashShortPass = hash.ShortHash(pass);
            bool result = false;

            // Creating a connection string. Using placeholders make code
            // easier to understand.
            string connectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0};
                  Persist Security Info=False; Jet OLEDB:Database Password={1};";

            string sql = string.Format
                ("SELECT * FROM PersonalData WHERE [DataID] = {0}", dataID);

            using (OleDbConnection connection = new OleDbConnection())
            {
                // Creating command object.
                // Using a string formatting let me to insert data into
                // place holders I have used earlier.
                connection.ConnectionString =
                    string.Format(connectionString, filePath, hashShortPass);

                using (OleDbCommand command = new OleDbCommand(sql, connection))
                {
                    // Creating command object.
                    // Using a string formatting let me to insert data into
                    // place holders I have used earlier.
                    connection.ConnectionString =
                        string.Format(connectionString, filePath, hashShortPass);

                    try
                    {
                        // Open database connection.
                        connection.Open();

                        using (OleDbDataReader read = command.ExecuteReader())
                        {
                            // Checking if there is any data in the file.
                            if (read.HasRows)
                            {
                                // Reading information from the file.
                                while (read.Read())
                                {
                                    if (read.GetInt32(0) == dataID)
                                        return true;
                                }
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Error: " + ex.Message);
                    }
                }
            }

            return result;
        }
    }
}
\$\endgroup\$
2
\$\begingroup\$

Setting up a connection to a database on every handle of the event is going to be really slow. You would be best running an initial call to the database that retrieves the Id's stored within it and caches the results inside your code. Then, when you perform your validation, just do a look up inside this cache. That will be a much faster check. Since you are using integer Id values, you could optimize this further by storing the Id results in an array that you can sort. You can then use Array.BinarySearch(...) to run a faster lookup.

Note: Using this approach, your validation check will not necessarily be working on the latest set of Id values (if they can be updated separately). In that case, either you will need to run a refresh on the database regularly, or change your validation to be a slower check that is performed after the user has finished changing the text (not on each change).

\$\endgroup\$
  • \$\begingroup\$ This is a good point. Checking elements from an array would be much faster and rechecking for ID numbers can be done less frequently. \$\endgroup\$ – HelpNeeder Dec 6 '11 at 17:23
  • 2
    \$\begingroup\$ Even if he validates the (partial) ID against the database each time, he still shouldn't be creating the connection every time; that should be managed externally (Usually injected). Unfortunately, I'm not sure how to do that in C#... \$\endgroup\$ – Clockwork-Muse Dec 6 '11 at 17:26
  • \$\begingroup\$ @X-Zero: This is why I have posted this question. I have already figured out how to do this from an array. Earlier I was using TextChanged event handler for a textbox so each time user was typing it was checking and updating things. \$\endgroup\$ – HelpNeeder Dec 6 '11 at 17:28
0
\$\begingroup\$

This is probably (I repeat: probably) going to be unacceptably slow for users who type fast, but then again it will probably perform much better than you might expect. Try adding some fake data to your database to test the performance of the scheme before optimizing anything. If it turns out that there is a performance problem, try opening the connection when 'DeleteEntryWindow' opens, and closing it when it closes. Make sure that an index has been defined in the database for DataID. If there is still a performance problem, then move the checking code to a separate thread. Do NOT do gimmicks like caching the IDs in an array, you will regret it later.

\$\endgroup\$
  • \$\begingroup\$ Why an array wouldn't be a good idea? That's what I am using right now. \$\endgroup\$ – HelpNeeder Dec 8 '11 at 6:15
  • \$\begingroup\$ One reason is that you will find it hard to guarantee that the contents of your cache are in sync with the contents of your database. (What if an item gets deleted after the cache has been populated?) Another reason is that it is not scalable. But the main reason is because it is a premature optimization, and premature optimization is the root of all evil. \$\endgroup\$ – Mike Nakis Dec 8 '11 at 9:14
0
\$\begingroup\$

If the database field is indexed it probably won't hurt performance a lot. Furthermore, database connections are usually kept in a pool, so reopening one shouldn't take a long time. That said, you should just try and see how fast it is with real-world data, as Mike already pointed out. If it is 'fast enough', don't optimize. If it is too slow, try to find out what the bottleneck is and see if it can be fixed.

I once implemented something similar using a timer: It was disabled on KeyDown, and enabled on KeyUp. So if the user stops typing, the timer starts, and after a certain amount of time (say 0.5 sec), the search is executed. As long as the user keeps on typing, nothing happens.

\$\endgroup\$

Your Answer

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

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