CODEWITHSUNDEEP
c#.netsqlsql-serverwpf
Dot NET
Dot NET

Reputation: 4897

Efficiency & Performance Requirement

I've got a number of textboxes within a form that the user is to fill in, and I want to check to see if the user's input exists within the database or not. Similar to how some websites check a username as you type it in. If the latter username is present within the database, then it marks it as green or with a checkbox.

In my case, the user must fill in a whole form, where each textbox represents a different column within a table. What would be the best way to check if the value exists within the database? It would have to be the fastest approach, as I cannot afford to have lag.

This is the approach I thought of adopting:

Before the user starts inputting data, an SQL query will be carried out on the database to retrieve all records within that table (a simple SELECT * FROM table). Next, this would be saved in a DataTable.

When the user is done from entering text in a textbox (i.e. the respective textbox is unfocussed), all the textboxes in the form would be checked to see that their inputs exist in the database. This would be carried out by running a LINQ statement against the DataTable for each textbox to see if the value is contained in the DataTable.

Would this approach work? I want it to be as fast as possible, and any lag cannot be tolerated.

Upvotes: 0

Views: 61

Answers (2)

Gil Peretz
Gil Peretz

Reputation: 2419

I would recommend not to check all the fields in one shot. I would consider the approach of narrowing down the data as long as the fields are getting filled.

Let me explain. lets assume that you have 10000 records of users in the DataTable. after the user complete to fill in the first field (usually 'First Name'), then check only this column with your DB (SELECT users.firstName FROM table), then you can update your DataTable according to the result (SELECT * FROM table WHERE firstName like ('%firstNameField%')).

You can do it only for the fields that are load with data and which they can save some extra time for you.

Hope you understand the idea :)

Upvotes: 1

Daniel Hilgarth
Daniel Hilgarth

Reputation: 174299

If the number of rows in table is not too big, that's the approach I would use.
If the number is too big to reasonably hold them in memory, you will have to hit the database everytime.
You can make the following optimizations:

  1. Add an index to each column you need to search. Please be aware that this increases the time needed for adding a row to that table
  2. Only search the database for text boxes that actually changed
  3. While searching the database, give the user visual feedback in the form of a small spinner beside the text box.

Upvotes: 2

Related Questions