SQLCommand and SqlDataReader error

Question

当

不存在数据时尝试读取无效。

此错误发生在此行上

string ingredientName = reader2.GetString(0);

我的代码：

var ingredientList = new List<Ingredient>();
SqlCommand staffCommand = new SqlCommand();
string conString = EventsUnlimited.Properties.Settings.Default.DatabaseEventsUnlimitedConnectionString;
using (SqlConnection connection = new SqlConnection(conString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("SELECT IngredientID, Quantity FROM CourseIngredients WHERE CourseID =" + courseID, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Obtains the columns from customer
                int ingredientID = reader.GetInt32(0);
                decimal quantity = reader.GetDecimal(1);
                string ingredientNameConstruct;
                SqlCommand ingredientCommand = new SqlCommand();
                string conString2 = EventsUnlimited.Properties.Settings.Default.DatabaseEventsUnlimitedConnectionString;
                using (SqlConnection connection2 = new SqlConnection(conString2))
                {
                    connection2.Open();
                    using (SqlCommand command2 = new SqlCommand ("SELECT IngredientName FROM Stock WHERE IngredientID =" + ingredientID, connection2))
                    {
                        using (SqlDataReader reader2 = command2.ExecuteReader())
                        {
                            string ingredientName = reader2.GetString(0);
                            ingredientNameConstruct = ingredientName;
                        }  
                    }
                }
                ingredientList.Add(new Ingredient(courseID, ingredientNameConstruct, ingredientID, quantity));
            }
        }
    }
    return ingredientList;
}

我不确定是什么导致了这个问题。表中有数据以及我尝试从中读取的行。

Answer 1

您没有为实际根据返回的结果读取行的reader2对象调用Read()方法，请在实际读取列值之前添加该方法：

using (SqlDataReader reader2 = command2.ExecuteReader())
{
      if(reader2.Read()) // this is needed
      {
          string ingredientName = reader2.GetString(0);
          ingredientNameConstruct = ingredientName;
      }
}  

如果您期望多行，请使用while循环，如果总是单行作为结果，您也可以使用reader2.ExecuteScalar，如注释中所述@bradbury9

：

string ingredientName = command2.ExecuteScalar()?.ToString();
ingredientNameConstruct = ingredientName;

Answer 2

在 reader2 DataReader 中调用 DataReader.Read 方法以获取结果。