Dynamic Query Building on the basis of User input

Question

1.00/5 (1 vote)

See more:

I have a Windows Form application connecting to a SQLServer DB.
I have a search screen where user can enter any search criterion and submit the information.
The information is collected and passed to an SQL server Stored Proc. where a dynamic query formation based on the search citerion I want to make.
For Ex
if user entered emp no then

the dynamic query formed would be

SQL

select * from emp where empNo = p_empno;

If the user entered emp no and salary then
the dynamic query formed would be

SQL

select * from emp where empNo = p_empno and sal = p_salary;

but what will i do if user select random column(different columns) using random operators(and /or)??

Posted 17-Oct-13 4:08am

Muhamad Faizan Khan

Updated 17-Oct-13 18:28pm

v3

Add a Solution

Comments

Nelek 17-Oct-13 11:02am

You have marked the question as C# as well. If you are interacting with the user through c#, you can check and validate the input of the user before building the query. So you can avoid random or non present querys.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Govindaraj Rangaraj · Answer 1 · 2013-10-17T04:57:00

Solution 1

I am not sure what do you mean by random operators. If you want to form a dynamic sql query do it this way!

C#

var sql = "select * from emp where 1 = 1";
if(p_empno <> "")
    sql = sql & " AND  empNo = " + p_empno
if(sqlTextBox.Text <> "")
    sql = sql & " AND sal = " + p_salary

Posted 17-Oct-13 4:57am

Govindaraj Rangaraj

Comments

Muhamad Faizan Khan 18-Oct-13 0:30am

random operator means and or . YOu wrote query with 'And' what with and another query? it means to many queryies

Govindaraj Rangaraj 18-Oct-13 4:19am

Hi it is same for all the operators.
To do OR, replace AND keyword with OR.

But I would suggest you to do it as a stored procedure then to do it like this as Sql injection can easily be exploited. Refer these:
http://www.codeproject.com/Articles/21234/Implementing-Dynamic-WHERE-Clause-in-Static-SQL#Content2
http://www.sqlteam.com/article/implementing-a-dynamic-where-clause

The other best option is to use Linq
Refer this: http://stackoverflow.com/questions/30879/is-there-a-pattern-using-linq-to-dynamically-create-a-filter
See the response from "David B" in this link. Nice way to do the dynamic queries.

thatraja · Answer 2 · 2013-10-17T05:00:00

Solution 2

Check this
SelectQueryBuilder: Building complex and flexible SQL queries/commands from C#[^]

Posted 17-Oct-13 5:00am

thatraja

Chris Ross 2 · Answer 3 · 2013-10-17T05:04:00

A user-selectable, complex query builder is a wonderful thing. What I hope you're not considering is letting the user type search logic into a text box and then using that text as part of your SQL. That approach would be very error prone and would expose you to all sorts of SQL injection attacks.

Assuming you're giving the user a dynamically configured set of and/or/not logic, field names (that you provide, in a drop-down list, perhaps) and value-entry fields for the user's actual filter values (these should be the only text entry opportunities for the user) then, in fact, you are already in control of column names and operators - you just need to translate the tree of UI controls into an appropriate set of conditions in the where clause.

For each text (or numeric, bool or enum) value the user supplies you should put a parameter placeholder in your generated SQL, and add a parameter to the SQL command object, setting the value on the parameter.

Dipesh Wadhwa · Answer 4 · 2013-10-17T20:41:00

C#

string SearchQuery="select * from emp where empNo = '"+ Desire_TextBox.Text+"'";
SqlCommand cmd=new SqlCommand(SearchQuery,Connection Here);

Or you can use SQL Parameter

C#

SqlCommand cmd=new SqlCommand("select * from emp where empNo=@col1 and empName=@col2",ConnectionHere)
cmd.Parameters.AddWithValue("@col1",DesireTextBox.Text);
cmd.Parameters.AddWithValue("@col2",DesireTextBox2.Text);

Dynamic Query Building on the basis of User input

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0