无法获取参数以在C#中与OdbcConnection一起使用 [英] Can't get parameters to work with OdbcConnection in C#

问题描述

我正在运行一个有几个日期约束的查询.如果我执行以下带有硬编码日期且没有参数的代码，则会得到预期的结果(一个简单的整数).但是，当我尝试使用参数时，得到的结果集为空.没有错误，只有结果.

I'm running a query with a couple date constraints. If I execute the code below with the dates hardcoded and no parameters, I get a result I expect (a simple integer). However, when I try to use parameters I get an empty result set. No errors, just no results.

log("Connecting to SQL Server...");
string connectionString = "DSN=HSBUSTEST32;";

string queryString = "SELECT COUNT(*) FROM Table WHERE myDateTime >= '?' AND myDateTime < '?'";
//string queryString = "SELECT COUNT(*) FROM Table WHERE myDateTime >= '@startDate' AND myDateTime < '@endDate'";

string startDate = "2016-08-23";
string endDate = "2016-08-24";

using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    OdbcCommand command = new OdbcCommand(queryString, connection);
    command.Parameters.AddWithValue("startDate",startDate);
    command.Parameters.AddWithValue("endDate", endDate);
    //command.Parameters.Add("startDate", OdbcType.VarChar).Value = "2016-08-23";
    //command.Parameters.Add("endDate", OdbcType.VarChar).Value = "2016-08-24";

    try
    {
        connection.Open();
        OdbcDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            log(reader[0].ToString());
        }
        reader.Close();
    }
    catch (Exception ex)
    {
        log(ex.Message);
    }
}

如您所见，我已经尝试了命名参数以及使用?占位符.我还尝试了几种添加参数Add()和AddWithValue()的不同方法，尽管实际上我不理解它们之间的区别.

As you can see I've tried both named parameters as well as using the ? placeholder. I've also tried a couple different methods of adding the parameters, Add() and AddWithValue(), though truthfully I don't understand the difference.

是什么导致空结果?

推荐答案

像我之前所说的查询问题是单引号.如果您传入的值不带参数，则需要使用单引号，因为这会在语句中定义字符串.

The problem with your query like i said before is the single quote. If you pass in the value without a parameter you need to use those single quotes because this defines a string in a statement.

该框架使用参数为您处理所有这些工作.它还检查sql注入并删除拒绝的字符.特别是对于字符串和日期时间值，这确实很有帮助.

With the parameters the framework handels all this stuff for you. It also checks for sql injection and removes dissalowed chars. Especially for string and datetime values this is really helpful.

这篇关于无法获取参数以在C#中与OdbcConnection一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！