SQLDataReader不读

Question

我试图建立一个警报系统，检查药物相关过敏和病人相关过敏(见下图)。

​

当我运行代码时，它似乎完全跳过了SQLDataReader，我对if(reader.HasRows)执行了检查，它只是显示阅读器中没有行。我所要做的就是在阅读器的末尾显示一个带有选定的过敏名称的消息框。我正在使用SQL Server 2014，任何帮助都将不胜感激。

private void button_addItem_Click(object sender, RoutedEventArgs e)
{
    if (!string.IsNullOrEmpty(comboBox_select_Item.Text.ToString()))
    {
        using (SqlConnection conn = new SqlConnection(connection))
        {
            try
            {
                SqlCommand sqlCmd2 = new SqlCommand("SELECT allergyName, allergyDescription FROM Allergies A INNER JOIN PatientAllergies PA ON A.allergyID = PA.allergyID WHERE A.allergyID = PA.allergyID AND PA.allergyID = (SELECT allergyID FROM Medication_Allergies MA WHERE MA.medID = " + comboBox_select_Item.SelectedValue.ToString() + ")", conn);
                conn.Open();
                SqlDataReader sqlReader = sqlCmd2.ExecuteReader();
                Allergies allergies = new Allergies();

                while (sqlReader.Read())
                {
                    allergies.allergyName = Convert.ToString(sqlReader["allergyName"]);
                    allergies.allergyDescription = Convert.ToString(sqlReader["allergyDescription"]);
                }

                MessageBox.Show(allergies.allergyName);
                sqlReader.Close();
                FillSalesItemGrid();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString(), ex.ToString());
            }
        }
    }
}

Answer 1

首先，您的SqlCommand容易受到sql注入攻击--您应该始终使用SqlParameter，因为它可以帮助您防止sql注入。因此，您的SqlCommand看起来应该是：

SqlCommand sqlCmd2 = new SqlCommand("SELECT allergyName, allergyDescription FROM Allergies A INNER JOIN PatientAllergies PA ON A.allergyID = PA.allergyID WHERE A.allergyID = PA.allergyID AND PA.allergyID = (SELECT allergyID FROM Medication_Allergies MA WHERE MA.medID = @medID)", conn);

您可以传递参数：

  sqlCmd2.Parameters.AddWithValue("@medID",comboBox_select_Item.SelectedValue);

此外，查询中的以下语句是多余的

WHERE A.allergyID = PA.allergyID 

因为你在这个领域有内部连接

FROM Allergies A INNER JOIN PatientAllergies PA ON A.allergyID = PA.allergyID 

这样，您就可以从查询中删除冗余语句。

SELECT allergyName, allergyDescription FROM Allergies A INNER JOIN PatientAllergies PA ON A.allergyID = PA.allergyID WHERE PA.allergyID = (SELECT allergyID FROM Medication_Allergies MA WHERE MA.medID = @medID)

我很确定您的代码的其余部分或多或少都很好。如果查询返回任何行，请查看。例如，您可以复制查询并将@medID替换为来自comboBox的值。