SQLReader HasRows = true，ResultsView为空，Enumeration没有产生任何结果 [英] SQLReader HasRows = true, ResultsView empty, Enumeration yielded no results

问题描述

我在VS中查询了一个表，结果得到了两行。  将select查询复制到字符串查询中，然后用params替换搜索值，如下所示

 
 string selectQuery =" select * from Table其中ColumnX = @ Value1和ColumnY = @ Value2" ;; 
 SqlCommand Query = new SqlCommand（selectQuery，Connection）; 
 Query.Parameters.AddWithValue（" @ Value1"，object.GetValue1（））; 
 Query.Parameters.AddWithValue（" @ Value2"，object.GetValue2（））; 
 

执行期间正在使用正确的getter值，但执行后

 SqlDataReader reader = Query.ExecuteReader（）; 

读者HasRows为true，ViewResults为空。 枚举没有产生任何结果。  查询如何返回行但没有结果？  逻辑上来说，如果ViewResult为空，则HasRows应为false。  可能导致这种情况的原因是什么？

解决方案

您需要通过调试器运行代码，以查看传递给命令的值。在下面的示例中，我对硬编码值进行了更加清晰的操作。

 public void Demo（）
 {
 using（SqlConnection cn） = new SqlConnection（ConnectionString））
 {
 
 string commandText = @" SELECT Identifier，CompanyName，ContactName" + 
" FROM Customers" + 
" WHERE Con​​tactTitle = @ContactTitle AND Country = @ Country" ;; 
 
使用（SqlCommand cmd = new SqlCommand（commandText，cn））
 {
 cmd.CommandText = commandText; 
 cmd.Parameters.AddWithValue（" @ ContactTitle"，" Owner"）; 
 cmd.Parameters.AddWithValue（" @ Country"，" Mexico"）; 
 
 cn.Open（）; 
 SqlDataReader reader = cmd.ExecuteReader（）; 
 if（reader.HasRows）
 {
 while（reader.Read（））
 {
 Console.WriteLine（

"Id：{reader.GetInt64（0）}公司：{reader.GetString（1）}"）;
}
}
}
}
}

IDE输出窗口中显示的结果

 Id：2公司：Ana Trujillo Emparedados y helados 
 Id：3公司：AntonioMorenoTaquería
 Id：53公司：Tortuga Restaurante 

在SQL Server Management Studio中运行查询以验证结果

I queried a table in VS and got two rows as result.  Copied the select query into string query, then replaced search values with params as below

string selectQuery = "select * from Table where ColumnX = @Value1 and ColumnY= @Value2";
SqlCommand Query = new SqlCommand(selectQuery, Connection);
Query.Parameters.AddWithValue("@Value1", object.GetValue1());
Query.Parameters.AddWithValue("@Value2", object.GetValue2());

During execution the correct getter values are being used, yet after executing

SqlDataReader reader = Query.ExecuteReader();

reader HasRows is true, ViewResults empty. Enumeration yielded no results.  How can a query return rows but no results?  Logically to me, if ViewResult is empty, HasRows should be false.  What could be causing this?

解决方案

You need to run your code through the debugger to see what values are being passed to the command. In the following example I hard coded values so that makes things a bit clearer.

public void Demo()
{
    using (SqlConnection cn = new SqlConnection(ConnectionString))
    {

        string commandText = @"SELECT Identifier,CompanyName,ContactName " + 
                                "FROM Customers " + 
                                "WHERE ContactTitle = @ContactTitle AND Country = @Country";

        using (SqlCommand cmd = new SqlCommand(commandText, cn))
        {
            cmd.CommandText = commandText;
            cmd.Parameters.AddWithValue("@ContactTitle", "Owner");
            cmd.Parameters.AddWithValue("@Country", "Mexico");

            cn.Open();
            SqlDataReader  reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine(

"Id: {reader.GetInt64(0)} Company: {reader.GetString(1)}"); } } } } }

Result shown in the IDE Output window

Id: 2 Company: Ana Trujillo Emparedados y helados
Id: 3 Company: Antonio Moreno Taquería
Id: 53 Company: Tortuga Restaurante

Ran the query in SQL Server Management Studio to verify results

这篇关于SQLReader HasRows = true，ResultsView为空，Enumeration没有产生任何结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！