在asp.net中使用SQL查询变量（C＃） [英] Using variables in SQL queries in asp.net (C#)

问题描述

我有这种形式的SQL查询

I have an SQL query of this form

string cmdText = "Select * from " + searchTable 
  + "WHERE " + searchTable 
  + "Name =' " +   searchValue + "'";

基本上，我试图做的是从数据库中获取的演员表中特定演员的信息。变量searchTable有哪些是表名值'演员'和searchValue有演员的名字（这是重新由ActorName属性在演员的表psented $ P $，在这里我想通过连接形成属性的名称的话'演员'和'名'）

Basically what I am trying to do is get a particular actor's info from the database's Actors table. The variable searchTable has the value 'Actor' which is the table name and searchValue has the actor's name (which is represented by the ActorName attribute in the Actor's table, here I am trying to form the name of the attribute by concatenating the words 'Actor' and 'Name' )

所以，好了，这一切的拼接结果（或至少应该导致）的形式进行查​​询：

So, well, all this concatenation results in (or at least should result in) a query of the form:

Select * from Actor where ActorName ='some actor';

但是，当我尝试运行这一点，让我在浏览器中的错误附近有语法错误'='。谁能请帮助？

But when I try to run this it gives me the error "Incorrect syntax near '=' " in the browser. Could anyone please help?

推荐答案

您可以把（和应！）参数转换为例如值SQL查询你的 WHERE 子句 - 但您不能参数多态的东西，如您的表名

You can put (and should!) parameters into your SQL queries for the values in e.g. your WHERE clause - but you cannot parametrize stuff like your table name.

所以我重写查询是：

SELECT (list of columns)
FROM dbo.Actor
WHERE ActorName = @ActorName

，然后在短短的值传递为 @ActorName 。

如果你需要做董事同样的事情，你必须有第二个查询

If you need to do the same thing for directors, you'd have to have a second query

SELECT (list of columns)
FROM dbo.Directors
WHERE DirectorName = @DirectorName

使用的参数是这样

Using parameters like this

• 增强了安全性（禁止SQL注入攻击！）


• 增强性能：该查询的查询计划可以被缓存和重用第二，第三道

PS：在你的安装原来的问题是这样的：你没有你的表名的首次出现和 WHERE 子句之间的任何空间 - 这样你会得到：

PS: the original problem in your setup is this: you don't have any space between the first occurence of your table name and the WHERE clause - thus you would get:

SELECT * FROM ActorWHERE ActorName ='.....'

如果你真的坚持串联起来你的SQL语句（我会不会推荐它！），那么你需要把你的表名和你的 WHERE之间有一个空格！

If you really insist on concatenating together your SQL statement (I would NOT recommend it!), then you need to put a space between your table name and your WHERE !

更新：一些资源在ADO.NET了解参数化查询：

Update: some resources for learning about parametrized queries in ADO.NET:

• 的C＃ADO.NET站教程/ 06课：添加命令参数


• Using使用SqlDataSource
参数化查询

• The C# Station ADO.NET Tutorial / Lesson 06: Adding Parameters to Commands
• Using Parameterized Queries with the SqlDataSource

这篇关于在asp.net中使用SQL查询变量（C＃）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！