How to design generic search forms

[supix]

Hi all.

I'm facing the issue of providing the user of my applications with
easy-to-use search forms. In my mind, a search form is structured as a
grid whose columns are the "most important properties" of an entity,
those to be searched in.

Without NHibernate I created a function implementing the "Search
Service", this way:

int ok = Search(Connection, TableName, VisibleFieldsList,
IndexFieldName, SortedFieldName, WinCaption, ref IndexValue);

The function shows a grid directly bound to a DBSource and populated
with data retrieved by an sql statement, dynamically created starting
from Search() input data. The user, while searching for a row, can re-
sort records, filter by any column, etc... interacting with the grid
(so triggering changes in the original sql statement --- expecially
the where condition and the order-by clause).

Though strongly bound to all-the-DB-stuff, this approach has the great
advantage of allowing an easy search on virtually any table in my DB,
without developing anything.

Is there some cots component implementing the NHibernate counterpart
for such a search form?
If not, how do you approach this problem in your applications?

Thanx a lot,
Marcello.

[Thomas Koch]

Hi Marcello - the Criteria API is you friend here.

A good example by Ayende is found here:
http://ayende.com/Blog/archive/2006/12/07/ComplexSearchingQueryingWit...

Cheers,
Thomas

[supix]

Thomas,

thanks for your reply. I have read the interesting article you
suggested, and it will improve my knowledge about NH.

Actually, I was thinking of a slightly different type of search form:

1) winform based;
2) the search ends up with identifying exactly one (and only one)
record;
3) the search has to be as simple as possible, because it is (pheraps
the most) common use case;
4) multicriteria search (although acceptable) is unlikely;
4) the search has no statistical purposes.

For instance, I am thinking about a user which has to fill in a new
invoice from scratch. He has to perform:

- one search for the right customer;
- one search for the right payment conditions;
- N searches for the products.

These operation should be as fast as possible (e.g. click on "New
Invoice" button, focus the ClientID text box, press F1, the modal
search form appears, type the first three characters of the customer
name, double-click on the right customer --- which is shown among the
other customers matching the same name-criterium --- the form closes
and all the chosen customer data are put in the invoice form).

Where should I start to get such a "search component"?
I hope not from placing a DataGridView onto an empty form. :-)

Thanks,
Marcello.

[Fabio Maulo]

Only a comment here...

between the "type three" and double-click there is nothing that mean you:

upload all customers and filter in ram for the first three chars during users typing

I hope your system is very little (max 500 customers).

2009/7/2 supix <ing.marcel...@gmail.com>

--
Fabio Maulo

[supix]

Hi Fabio.
Thanks for your reply (my honour).

> Only a comment here...between the "type three" and double-click there is

> nothing that mean you:
> upload all customers and filter in ram for the first three chars during
> users typing
>
> I hope your system is very little (max 500 customers).

While typing the first three characters and clicking on the right
username, ok, there is a query execution.
The query should retrieve only the first N customers matching the
filter, where N might be equal to the double of the rows currently
visible in the DataGridView.
If the user clicks on the scroll bar to go down through the list
(rather than narrowing the search with more characters), on necessity,
a similar query should pick the following records (e.g. N more
records).

If the user narrows the search, the search form should be possibly
smart enough to avoid to fire one more query, and perform an in memory
filtering on the already available records.

Furthermore, another performance improvement, while the user types his
three characters, a little "inactivity-delay" might be set before
firing the search (e.g. 400ms). If the user is fast enough (as he
should be since, ehm..., this is one of the most common use cases),
the query should be fired only when the search filter has been
completed, so avoiding to fire unnecessary queries.

Ok. Though I see this kind of smart search form not impossible to be
coded, maybe there are some technical obstacles I don't see here.
Maybe SetMaxResults() is not portable onto all DBMSs and sometimes
performs just an in-memory-filtering after having downloaded the
entire world.

But, in this case, I can't figure out how do you let your users easily
choose the right invoice customer starting from the customer name.

Thank you.
Marcello.

[rg]

My company's product has exactly the features you are discussing
- generic but customizable search form that allows you to specify
criteria for all entity fields, but also for related entities, like
Invoice.Customer.Address.Country == 'Germany'
- also we have generic 'reference fields' for selecting a single
entity, they provide search/autocompletion feature.
And there's a problem with generic lists - usually you are giving your
users too much search options. At first this is great because they can
define their own sophisticated filters but very soon they start using
them as a report tool. And when the amount of data grows the searches
become more and more expensive until they saturate the database. Now
it's hard to optimize it because you would have to add many indexes
for the most-frequently searched columns, which slows down
insert&update, and you usually cannot remove the more costly search
options because users will protest. I'm not saying generic search is
bad, it's great you don't have to develop anything, but if you don't
limit the search options at the beginning you'll be blamed when the
application runs slow. I'd rather give only few search fields (or just
a single one, like in Google) and build database indexes for these
fields.
R