ASP.NET C# Search in a SQL Server Database Table

Question

I'm building a portal, which isn't a blogging engine, but is quite similar to one. In SQL Server, I have a database containing a table that is the basis for the "posts". This Posts table includes the following columns:

• ID
• Author(s)
• Tags
• Title
• Markdown post content

This is my first time building such a portal, and I'd like to implement some sort of ASP.NET search over these rows, preferably using all of the properties (columns), except for the ID one. Also, in the long run, I'm considering the possibility of implementing a search of this and comments to those posts, which would be stored in a different table.

Are there any open-source implementations or example code online for accomplishing this search? If not, how can I get started? Could you point me towards some tutorials w/ sample code on how to accomplish this with ASP.NET and C#? Also, has Google (or some other company) created any things for this?

I hope my question isn't too broad or vague. Thanks in advance!

Answer 1

Are you using Sql Server 2008? If so, you could leverage the full-text search features built right into it. Then it would be as simple as passing the user's (sanitized) input into a sql query.

For example, here's a query that would search the Author, Title and PostContent fields for the user's inputted text.

SELECT Author, Title FROM Posts
WHERE CONTAINS((Author, Title, PostContent), @userInput);

SQL Server 2008 supports different search methods too, like simple token, weighted word values, synonym, proximity and prefix searches... it's pretty awesome.

Answer 2

If this is publicly exposed, you can always have google index it. Google has an appliance which you can purchase to do this as well.

If you dont want to roll your own you could look at:

1. Sharepoint (regular [comes with Win 2003+] or portal server)
2. SOLR (Apache Lucene project)

If you want to roll your own, I suggest looking into SQL Server Analysis Services to build the search indexes for you on a regular basis.

Answer 3

Have you thought of implementing search through the use of Full-Text Search? It seems like a great scenario for it. This link might provide useful information on architecture and development of Full-Text Search. http://msdn.microsoft.com/en-us/library/ms142571.aspx

Answer 4

I agree with Womp - Full text index is the way to go. You could also look at NLucene if you need to index more than just the database table.

Answer 5

i think you should try to use Lucene which you can use to index all your data and build a really good search engine. i can give more information about that if you like.