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!

womp · Accepted Answer · 2009-12-20 01:01:50Z

9

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.

edited Dec 20, 2009 at 1:01

answered Dec 19, 2009 at 23:08

womp

117k27 gold badges240 silver badges271 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maxim Zaslavsky Over a year ago

Could you point me to a tutorial w/ sample code for that? Thanks!

womp Over a year ago

Here's a pretty thorough walkthrough: simple-talk.com/sql/learn-sql-server/…

Guillermo Gomez · Accepted Answer · 2009-12-19 23:11:06Z

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

answered Dec 19, 2009 at 23:11

Guillermo Gomez

1,8054 gold badges17 silver badges21 bronze badges

Comments

GrayWizardx · Accepted Answer · 2009-12-19 23:10:03Z

1

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:

Sharepoint (regular [comes with Win 2003+] or portal server)
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.

answered Dec 19, 2009 at 23:10

GrayWizardx

21.5k2 gold badges33 silver badges44 bronze badges

Comments

Mark Ewer · Accepted Answer · 2009-12-20 00:52:25Z

0

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.

answered Dec 20, 2009 at 0:52

Mark Ewer

1,83513 silver badges26 bronze badges

Comments

Hiyasat · Accepted Answer · 2009-12-20 08:27:09Z

0

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.

answered Dec 20, 2009 at 8:27

Hiyasat

8,9667 gold badges36 silver badges60 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related