Search Query - SQL Server 2005 - Ideas - Knowledge Sharing

Question

Currently I am designing a database schema where one table will contains details about all students of a university.

I am thinking the way how can I create the search engine query for administrators where they will search for students. (Some properties are Age, Location, Name, Surname etc etc) (approx 20 properties - 1 table)

My idea is to create the sql query dynamically from the code side. Is it the best way or is there any other better ways?

Shall I use a stored procedure?

Is there any other ways?

feel free to share

Answer 1

I am going to assume you have a front end that collects user input, executes a query and returns a result. I would say you HAVE to create the query dynamically from the code side. At the very least you will need to pass in variables that the user selected to query by. I would probably create a method that takes in the key/value search data and use that to execute the query. Because it will only be one table there would probably be no need for a view or stored procedure. I think a simple select statement including your search criteria will work fine.

Answer 2

I would suggest you to use LINQ to SQL and this will allow you to write such queries just in C# code without any SQL procedures. LINQ to SQL will care about security and prevent SQL injections

p.s. Do not ever compose SQL from concatenated strings like SQL = "select * from table where " + "param1=" + param1 ... :)