如何在SQL查询中包含变量？ [英] How do I include a variable in an SQL query?

问题描述

我正在VS 2015中创建一个应用程序，使用SQL Server 2016 Express创建数据库。



为了缩短C＃中的代码，我在SQL中创建了Views Server 2016所以我可以简单地在我的C＃代码中调用它们，但是一些查询必须产生依赖于用户输入的结果，比如它们键入的名称，数字等。



例如：

  string  name = txtName.Text.ToString（）; 
 
 command.CommandText =   SELECT * FROM table1 WHERE name =' + name +  '; 

如何在View中执行此操作？所以我不必在我的C＃代码中输入整个查询？

所以我可以将查询命名为，例如，'LoadNames'，然后就这样做：

 command.CommandText =   SELECT * FROM LoadNamesView 

我的尝试：



我见过：

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

但我不确定这是否是正确答案？

解决方案

看起来你正在寻找SQL参数：

SQL Injec [ ^ ]

参数的一个优点是它可以避免SQL注入的问题。

在这个命令中

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

如果C变量 name 输入为user，它打开了注入的大门。

我建​​议使用存储过程：

如何：执行返回行的存储过程 [ ^ ]

如何：执行a使用带有输入和输出参数的存储过程进行查询 [ ^ ]

如何：使用带参数的存储过程 [ ^ ]



优点：

1.存储过程的名称仅用于

2.存储过程提供传递参数的方法

如果你在意见然后没有后顾之忧。

下面是如何使用SQL视图处理多个参数的方法。



创建一个名为Where的变量，传递所有SQL参数通过连接。



示例：如果您正在使用EmployeeMst表并且您创建了一个名为View_Select_All_Employees的视图，请按以下方式调用此视图。

  string 其中=  其中1 = 1; 
 
  if （txtFName.Text.Trim（）！= Null）
 {
其中+ =  和EmpFName =' + txtFName.Text.Trim（）+   '; 
} 
  if （txtLName.Text.Trim（）！= Null）
 {
其中+ =  和EmpLName =' + txtLName.Text.Trim（）+  '; 
} 
 
 
  string  commandText =  < span class =code-string> select * from View_Select_All_Employees + Where; 
 SqlCommand command =  new  SqlCommand（commandText，connection）; 
 command.Parameters.Add（  @ studentid， 101 ）; 

希望这会对你有所帮助.. !! ！ ：）

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:

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:

command.CommandText = "SELECT * FROM LoadNamesView"

What I have tried:

I've seen:

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

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

解决方案

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

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

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

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

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.

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..!!! :)

这篇关于如何在SQL查询中包含变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！