How do I include a variable in an SQL query?

Question

0.00/5 (No votes)

See more:

I'm creating an application in VS 2015, created the database using SQL Server 2016 Express.

To shorten the code in C#, I created Views in SQL Server 2016 so I can just simply call these in my C# code, but some queries will have to produce results that depend on the user input, like what name did they type, number, etc.

Like for example:

C#

string name = txtName.Text.ToString();

command.CommandText = "SELECT * FROM table1 WHERE name = '"+name+"'";

How do I do this in the View? So I won't have to type the whole query in my C# code?
So I can name the query like, 'LoadNames' for example, and then just do this:

C#

command.CommandText = "SELECT * FROM LoadNamesView"

What I have tried:

I've seen:

SQL

DECLARE @theDate DATETIME
SET @theDate = '2010-01-01'

But I'm not sure if this is the correct answer?

Posted 28-Aug-16 4:52am

kmllev

Updated 29-Aug-16 1:58am

Maciej Los

v2

Add a Solution

Comments

Philippe Mori 28-Aug-16 17:51pm

You code is subject to SQL injection. Never write code like that.

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)

Patrice T · Answer 1 · 2016-08-28T05:43:00

Looks like you are looking for SQL parameters:
SQL Injection[^]
An advantage of parameters is that it avoid the problem of SQL injection.
In this command

C#

command.CommandText = "SELECT * FROM table1 WHERE name = '"+name+"'";

if the C variable name is input be user, it opens the door to injection.

Maciej Los · Answer 2 · 2016-08-28T05:38:00

I'd suggest to use stored procedures:
How to: Execute a Stored Procedure that Returns Rows[^]
How to: Execute a Query Using a Stored Procedure with In and Out Parameters[^]
How to: Use Stored Procedures that Take Parameters[^]

Advantages:
1. The name of stored procedure is used only
2. A stored procedure provides a way to pass parameters

Bhavikkumar Chaudhari · Answer 3 · 2016-08-29T01:58:00

If you are with "Views" then no worries.
Below is the way how to handle multiple parameters with SQL Views.

Create a variable called "Where" inside pass all your SQL parameters by concatenating.

Example : If you are having EmployeeMst table and you have created a view called "View_Select_All_Employees" then call this view as below.

C#

string Where = " Where 1=1 ";

if(txtFName.Text.Trim() != Null)
{
   Where += " And EmpFName ='"+ txtFName.Text.Trim() +"'";
}
if(txtLName.Text.Trim() != Null)
{
   Where += " And EmpLName ='"+ txtLName.Text.Trim() +"'";
}


string commandText = "select * from View_Select_All_Employees " + Where;
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@studentid", 101);

Hope this will help you..!!! :)

ljdhameliya · Answer 4 · 2016-08-29T00:24:00

Solution 4

C#

string commandText = "select * from Students where studentid=@studentid"
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@studentid", 101);

Posted 29-Aug-16 0:24am

ljdhameliya

How do I include a variable in an SQL query?

4 solutions

Solution 2

Solution 1

Solution 5

Solution 4

Add your solution here

Preview 0