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

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