添加参数，一个SQL查询的WHERE子句 [英] Adding parameters to the WHERE clause of a SQL query

问题描述

我使用ADO.NET来通过ODBC连接到Oracle数据库。一切工作正常，除了绑定参数用一个简单的SQL查询：

  Connection.Open（）;
IDbCommand的命令= Connection.CreateCommand（）;
command.CommandText =SELECT长度activity_type其中name =：name_of_activity;
VAR参数= command.CreateParameter（）;
parameter.ParameterName =：name_of_activity;
parameter.Value =Short_break;
command.Parameters.Add（参数）;
INT结果= Convert.ToInt32（command.ExecuteScalar（））;
的Connection.close（）;
 

它总是返回0的结果（从空的ExecuteScalar（） - 从读者一样的）。但是，如果我把一个简单的SQL查询是这样的： command.CommandText =选择长度activity_type其中name ='Short_break'它的工作就像一个魅力。什么是更多，我使用了类似的建筑遍布$ C $下 INSERT INTO 条款，而且他们确定。

我缺少的东西？

解决方案

从文档的<一个href="http://msdn.microsoft.com/en-us/library/system.data.odbc.odbccommand.parameters%28v=VS.100%29.aspx"><$c$c>OdbcCommand.Parameters:

  

当CommandType设置为文本，在.NET Framework数据提供ODBC不支持将命名参数以一个SQL语句或存储过程调用一个的OdbcCommand。在这两种情况下，使用问号（？）占位符。例如：

  SELECT * FROM客户WHERE客户id =？
 

换句话说，你的code应该是这样的：

  Connection.Open（）;
IDbCommand的命令= Connection.CreateCommand（）;
command.CommandText =SELECT长度activity_type其中name =？;
VAR参数= command.CreateParameter（）;
parameter.Value =Short_break;
command.Parameters.Add（参数）;
INT结果= Convert.ToInt32（command.ExecuteScalar（））;
的Connection.close（）;
 

（或许你也应该考虑使用使用语句，你要知道......否则的话，这将引发异常，你将不会被关闭连接。）

I'm using ADO.NET to connect to an Oracle DB through ODBC. Everything is working fine, besides binding parameters with a simple SQL query:

Connection.Open();
IDbCommand command = Connection.CreateCommand();
command.CommandText = "SELECT length FROM activity_type WHERE name = :name_of_activity";
var parameter = command.CreateParameter();
parameter.ParameterName = ":name_of_activity";
parameter.Value = "Short_break";
command.Parameters.Add(parameter);
int result = Convert.ToInt32(command.ExecuteScalar());
Connection.Close();

It always returns 0 results (a null from ExecuteScalar() - the same from a reader). But if I'd put a straightforward SQL query like this: command.CommandText = "SELECT length FROM activity_type WHERE name = 'Short_break'" it would work like a charm. Whats more, I used similar constructions all over the code for INSERT INTO clauses, and they were OK.

Am I missing something?

解决方案

From the docs for OdbcCommand.Parameters:

When CommandType is set to Text, the .NET Framework Data Provider for ODBC does not support passing named parameters to an SQL statement or to a stored procedure called by an OdbcCommand. In either of these cases, use the question mark (?) placeholder. For example:

SELECT * FROM Customers WHERE CustomerID = ?

In other words, your code should look like this:

Connection.Open();
IDbCommand command = Connection.CreateCommand();
command.CommandText = "SELECT length FROM activity_type WHERE name = ?";
var parameter = command.CreateParameter();
parameter.Value = "Short_break";
command.Parameters.Add(parameter);
int result = Convert.ToInt32(command.ExecuteScalar());
Connection.Close();

(You should probably be considering using using statements, mind you... otherwise if this throws an exception, you won't be closing the connection.)

这篇关于添加参数，一个SQL查询的WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！