当被检查的查询执行时，ExecuteReader不返回结果 [英] ExecuteReader returns no results, when inspected query does

问题描述

考虑以下代码：

        StringBuilder textResults = new StringBuilder();
        using(SqlConnection connection = new SqlConnection(GetEntityConnectionString()))
        {
            connection.Open();
            m.Connection = connection;
            SqlDataReader results = m.ExecuteReader();
            while (results.Read())
            {
                textResults.Append(String.Format("{0}", results[0]));
            }
        }

我在Sql Server Mgmt Studio中使用了活动监视器数据库以检查发送的确切查询。然后，我将该查询文本复制到SSMS内的查询编辑器窗口中，该查询返回了预期的结果。但是， SqlDataReader结果始终为空，指示枚举未返回任何结果。

I used Activity Monitor within Sql Server Mgmt Studio on the database to inspect the exact query that was being sent. I then copied that query text to a query editor window within SSMS, and the query returned the expected results. However, SqlDataReader results is always empty, indicating "The enumeration returned no results."

我的怀疑是某种程度上结果未正确返回，这使我认为上面的代码有问题，而不是查询本身正在传递。

My suspicion is that somehow the results are not being returned correctly, which makes me think there's something wrong with the code above, and not the query itself being passed.

是否有任何原因会导致这种情况在上面的代码中？还是我忽略了的东西？

Is there anything that would cause this in the code above? Or something I've overlooked?

编辑：

这里是查询如SQLCommand对象所指示：

Here is the query as indicated by the SQLCommand object:

SELECT DISTINCT StandardId,Number 
FROM vStandardsAndRequirements 
WHERE StandardId IN ('@param1','@param2','@param3') 
ORDER BY StandardId

这是活动监视器中显示的查询：

Here is the query as it appears in Activity Monitor:

SELECT DISTINCT StandardId,Number 
FROM vStandardsAndRequirements 
WHERE StandardId IN ('ABC-001-0','ABC-001-0.1','ABC-001-0') 
ORDER BY StandardId

查询正在针对单个视图工作。

The query is working against a single view.

当我对数据库运行第二个查询时，它返回3行。

When I ran the second query against the database, it returned 3 rows.

SqlDataReader指示0行。

The SqlDataReader indicates 0 rows.

推荐答案

StringBuilder textResults = new StringBuilder();

        using (var conn = new SqlConnection(GetEntityConnectionString())))
        {
            using (
                var cmd = new SqlCommand(
            "SELECT DISTINCT StandardId,Number" +
                "FROM vStandardsAndRequirements " +
            "WHERE StandardId IN (@param1,@param2,@param3)" +
            "ORDER BY StandardIdl"

       , conn))
            {

                var dSet = new DataSet();
                var dt = new Datatable();

                var da = new SqlDataAdapter(cmd);

                cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value = "ABC-001-0";
        cmd.Parameters.Add("@param2", SqlDbType.VarChar, 50).Value = "ABC-001-0.1";
                cmd.Parameters.Add("@param3", SqlDbType.VarChar, 50).Value = "ABC-001-0";
                try
                {

                    da.Fill(dSet);

        dt = dSet.Tables[0];

        foreach(Datarow a in dt.Rows)
        {

            textResults.Append(a["StandardId"].tostring()).AppendLine();


        }

        Messabox.Show(textResults.tostring);

                }
                catch (SqlException)
                {
                    throw;
                }

       finally
                {
                    if (conn.State == ConnectionState.Open) conn.Close();
                }

            }
        }

致谢。

这篇关于当被检查的查询执行时，ExecuteReader不返回结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！