多ExecuteReader优化

Question

有没有办法优化下面的代码。我使用3个executeReader来获得不同的结果

SqlCommand command = new SqlCommand("select DeliveryID,Name from deliveryphone WHERE PhoneNumber= '" + textBox1.Text + "'", con);
        SqlDataReader read = command.ExecuteReader();

        while (read.Read())
        {

            SqlDeliveryID = (read["DeliveryID"].ToString());
            textBox2.Text = (read["Name"].ToString());

        }
        read.Close();
        SqlCommand command2 = new SqlCommand("select  Adress from DeliveryAdress WHERE DeliveryID= '" + SqlDeliveryID + "' ", con);
        SqlDataReader read2 = command2.ExecuteReader();

        while (read2.Read())
        {
            comboBox1.Items.Add(read2["Adress"].ToString());

        }
        read2.Close();
        SqlCommand command3 = new SqlCommand("select top 1 Adress,Location,Floor,Comments from DeliveryAdress WHERE DeliveryID= '" + SqlDeliveryID + "' order by DefaultAdress desc", con);
        SqlDataReader read3 = command3.ExecuteReader();

        while (read3.Read())
        {
            comboBox1.Text = (read3["Adress"].ToString());
            textBox3.Text = (read3["Location"].ToString());
            comboBox2.Text = (read3["Floor"].ToString());
            textBox5.Text = (read3["Comments"].ToString());

        }

有没有办法把这3个阅读器合并成1？

Answer 1

您可能希望在单个读取器中使用多个结果集。

How to read multiple resultset from SqlDataReader?

Answer 2

1. Google sql how to join tables，，它将帮助您了解如何创建单个查询来获取所需的所有数据。
2. 始终对您的值使用参数！请参阅如何将用户提供的输入添加到SQL语句？关于如何参数化您的查询。如果您不认为这一点很重要，那么如果有人在using块中输入textBox1.Text.
3. Wrap中的可处置类型的文本值'; DROP TABLE deliveryphone，以便在它们超出范围时将其关闭/释放，会发生什么情况。

const string query = @"SELECT dp.DeliveryID, dp.Name, da.Adress, da.Location, da.Floor, da.Comments
    FROM DeliveryPhone dp INNER JOIN DeliveryAdress da ON dp.DeliveryID = da.DeliveryID
    WHERE dp.PhoneNumber=@phoneNumber";


using(SqlCommand command = new SqlCommand(query, con))
{
  // I guessed on the sql type and length
  command.Parameters.Add(new SqlParameter("@phoneNumber", SqlDbType.VarChar, 50) {Value = textBox1.Text});
  con.Open(); // is the connection always open? Really you should create connections on an as needed basis and then dispose of them
  using(SqlDataReader read = command.ExecuteReader())
  {
    if(reader.Read())
    {
      textBox2.Text = read.GetString(1);
      comboBox1.Text = read.GetString(2);
      textBox3.Text = read.GetString(3);
      comboBox2.Text = read.GetString(4);
      textBox5.Text = read.GetString(5);
      // if you need the other values you can get those as well
    }
  }
}