How do I build a search mechanism for my application?

Question

It seems to be a common requirement nowadays to have a search feature that can search almost anything you want. Can anyone give me samples or tips as to how to go about building a one stop search for an application?

For example: you have 3 tables customers, products, employees. The application has a master page that has a textbox at the right hand corner very similar to what you have on stackoverflow.

How do I have a search say for term say "Phoenix" and have results like

Customers

Result 1
......

Products

Result 1
......

Employees

Result 1
......

Any tips, tutorials and hints would be really appreciated. My environment is Win2k3,.net3.5,C#,ASP.net.

EDIT: Looking specifically at performance and scalability.

Thanks in advance!

Answer 1

One way would be to find the searched value in all tables in the database. Of course this code can be altered to focus on specific tables and columns.

Answer 2

Xapian is an Open Source Search Engine Library with the features you are seeking.

Answer 3

Lucene.NET is an extremely fast full text search engine.

Sample usage:

See Source code of DotNetKicks starting from codebehind of search page

Answer 4

To follow up on fallen888's answer:

You could create a class with a method taking an array or list of search keywords for input. Create LINQ queries related to each table, and for instance use the "LIKE" operator in some of the ways explained in this blogpost to search the right columns for matching content. Of course, this will not be very optimized, but will work for small systems :)

Answer 5

If you want something really scalable, I guess you'll have to build first a data dictionnary, listing each table and each column in your database. The building of such a table can be fully automated.

You will indicate in this table which fields are available for straight or soft (with wild card) searching.

You'll then be able to build some nice and smart user interface where the machine will be able to find that a code such as '*823*' refer both to 'INV-0823456' and 'INV-0880823' invoice numbers, as long as invoice number field is available for soft searching. The user might even be given the option to choose which column(s) to search, as scanning all telephone numbers in the database might not allways make sense.

You should not try to index all searchable columns. A "full-index" strategy can become very costy in termes of disk volume\server overhead, and will not allways make sense for rarely searched columns. Don't worry a lot about that: users will not mind some delay in getting their answer.

Answer 6

If you are using SQL Server for your database then you can try SQL's full text search. I have used it quite successfully.

Answer 7

If you want a one stop search solution and want to have a search working within minutes on your site or blog you can always use BuildaSearch. BuildaSearch makes custom search creation fast and easy. Simply copy and paste your custom javascript code and instantly, you have a fully functional custom search working on your website. The most interesting feature about this search service is that you do not need to write a single line of code and works on any web server environment.