How do I build a search mechanism for my application?

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!

From stackoverflow

• 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.

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

  Jonathan Leffler : Does Xapian index the data for a DBMS separately from the DBMS's own storage of the data? A cursory glance at the features list suggests that is what happens.

  Judge Maygarden : I believe Xapian maintains its own data structure/database for indexing.

  Judge Maygarden : Actually, it looks like you can plug in your on indexer if you want...

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

  Sample usage:

  See Source code of DotNetKicks starting from codebehind of search page

• 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 :)

• 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.

  Perpetualcoder : seems like the way I might need to go.

  Alabaster Codify : With so many existing tools out there which do a great job a full-text indexing (including Lucene.NET and SQLServer's own full-text search), I would strongly recommend against writing your own search engine for anything but the most unusual situation.

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

• 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.