执行第一个命令后，我将如何执行另一个SELECT命令？

问题描述：

protected void Button1_Click(object sender, EventArgs e) 
    { 
     string client = TextBox1.Text; 
     string selected = RadioButtonList1.SelectedValue; 
     string calendar = Calendar1.SelectedDate.ToShortDateString(); 
     string disease = txtDisease.Text; 

     SqlCommand insert = new SqlCommand("insert into Appointment(Client_ID, DateofAppointment, TimeofAppointment, Disease) values(@Client_ID, @DateofAppointment, @TimeofAppointment, @Disease)", conn); 
     try 
     { 
      conn.Open(); 

      SqlCommand cmd = new SqlCommand("select * from Client where Client_ID = @Client_ID", conn); 
      cmd.Parameters.AddWithValue("@Client_ID", TextBox1.Text); 
      SqlDataAdapter da = new SqlDataAdapter(cmd); 
      DataTable dt = new DataTable(); 
      da.Fill(dt); 
      if (dt.Rows.Count > 0) 
      {  
        insert.Parameters.AddWithValue("@Client_ID", client); 
        insert.Parameters.AddWithValue("@DateofAppointment", calendar); 
        insert.Parameters.AddWithValue("@TimeofAppointment", selected); 
        insert.Parameters.AddWithValue("@Disease", disease); 
        insert.ExecuteNonQuery(); 
        TextBox1.Text = ""; 
        RadioButtonList1.SelectedIndex = -1; 
        txtDisease.Text = ""; 
        ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Appointment submitted! Please wait for your SMS confirmation. Thank you!')</script>"); 
      } 
      else 
      { 
       TextBox1.Text = ""; 
       RadioButtonList1.SelectedIndex = -1; 
       txtDisease.Text = ""; 
       ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You are not a registered client!')</script>"); 
      } 
     } 
     catch 
     { 
      conn.Close(); 
     } 

    } 


}

我想创建另一个命令来搜索日期和领带是否已经被插入到我的数据库之前。执行第一个命令后，我将如何执行另一个SELECT命令？

创建第二个命令对象，并调用它，就像第一个 –

甚至更好，为什么不写一个存储过程，一切都被在执行 –

你能告诉我怎么做吗？我试着在insert.parameters之前创建另一个命令，但它不起作用 –

答

不确定你最初的例外情况是什么，但在发布问题时加入该例外很有帮助。

另外关于您的连接，通常不推荐使用级别连接。 Sql Server为你处理连接池，所以最好的做法是在你完成使用后立即处理你的连接。当你想重新查询（在另一个方法或类中），然后创建一个新的连接。同时将您的连接包裹在using区块中，以确保它们关闭并丢弃。

而关于你的日期，最好始终坚持原生类型的数据。这意味着您的日期的列类型应该是Sql日期，而不是字符串。这也意味着您的日历参数值应为DateTime calendar = Calendar1.SelectedDate;而不是string。

protected void Button1_Click(object sender, EventArgs e) 
{ 
    string client = TextBox1.Text; 
    string selected = RadioButtonList1.SelectedValue; 

    // this should be a DateTime instance, not a string 
    string calendar = Calendar1.SelectedDate.ToShortDateString(); 

    string disease = txtDisease.Text; 

    try 
    { 
     conn.Open(); 

     SqlCommand cmd = new SqlCommand("select 1 from Appointment WHERE DateofAppointment = @DateofAppointment AND TimeofAppointment = @TimeofAppointment", conn); 
     cmd.Parameters.AddWithValue("@DateofAppointment", calendar); 
     cmd.Parameters.AddWithValue("@TimeofAppointment", selected); 
     bool exists = false; 
     using(var reader = cmd.ExecuteReader()) { 
      exists = reader.Read(); // if this returns true there is a record 
     } 

     // do something based on exists 

     SqlCommand insert = new SqlCommand("insert into Appointment(Client_ID, DateofAppointment, TimeofAppointment, Disease) values(@Client_ID, @DateofAppointment, @TimeofAppointment, @Disease)", conn); 

     // repurpose same pointer but to different instance 
     cmd = new SqlCommand("select * from Client where Client_ID = @Client_ID", conn); 
     cmd.Parameters.AddWithValue("@Client_ID", TextBox1.Text); 
     SqlDataAdapter da = new SqlDataAdapter(cmd); 
     DataTable dt = new DataTable(); 
     da.Fill(dt); 
     if (dt.Rows.Count > 0) 
     {  
       insert.Parameters.AddWithValue("@Client_ID", client); 
       insert.Parameters.AddWithValue("@DateofAppointment", calendar); 
       insert.Parameters.AddWithValue("@TimeofAppointment", selected); 
       insert.Parameters.AddWithValue("@Disease", disease); 
       insert.ExecuteNonQuery(); 
       TextBox1.Text = ""; 
       RadioButtonList1.SelectedIndex = -1; 
       txtDisease.Text = ""; 
       ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Appointment submitted! Please wait for your SMS confirmation. Thank you!')</script>"); 
     } 
     else 
     { 
      TextBox1.Text = ""; 
      RadioButtonList1.SelectedIndex = -1; 
      txtDisease.Text = ""; 
      ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You are not a registered client!')</script>"); 
     } 
    } 
    catch 
    { 
     conn.Close(); 
    } 
}

我想OP会检查日期和时间是否可用，而不考虑clientID，即从约会W中选择1这里约会的日期= @DateofAppointment“AND TimeofAppointment = @TimeofAppointment –

@inquisitive_mind - gotcha，我会改变它。 – Igor

它不工作.... :( –

答

您可以在第一个之前创建的命令并执行第二前检查结果：

cmd.CommandText = "SELECT COUNT(*) FROM Client where {you conditions}"; 
Int32 count = (Int32) cmd.ExecuteScalar();

，然后检查count大于0，则执行第二。

它不工作..... –

你可以发布你的更新代码吗？ – Yaser

我发布了它，请检查我需要这个wooork。/ –

答

保护无效Button1_Click（对象发件人，EventArgs e） { string client = TextBox1.Text; string selected = RadioButtonList1.SelectedValue; string calendar = Calendar1.SelectedDate.ToShortDateString（）; string disease = txtDisease.Text;

 SqlCommand insert = new SqlCommand("insert into Appointment(Client_ID, DateofAppointment, TimeofAppointment, Disease) values(@Client_ID, @DateofAppointment, @TimeofAppointment, @Disease)", conn); 
     try 
     { 
      conn.Open(); 

      SqlCommand cmd = new SqlCommand("select * from Client where Client_ID = @Client_ID", conn); 
      cmd.Parameters.AddWithValue("@Client_ID", TextBox1.Text); 
      SqlDataAdapter da = new SqlDataAdapter(cmd); 
      DataTable dt = new DataTable(); 
      da.Fill(dt); 
      if (dt.Rows.Count > 0) 
      { 
       SqlCommand search = new SqlCommand("select * from Appointment where DateofAppointment = @DateofAppointment and TimeofAppointment = @TimeofAppointment", conn); 
       search.Parameters.AddWithValue("@DateofAppointment", calendar); 
       search.Parameters.AddWithValue("@TimeofAppointment", selected); 
       Int32 count = (Int32)search.ExecuteScalar(); 
       if (count > 0) 
       { 
        ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Error!')</script>"); 
       } 
       else 
       { 
        insert.Parameters.AddWithValue("@Client_ID", client); 
        insert.Parameters.AddWithValue("@DateofAppointment", calendar); 
        insert.Parameters.AddWithValue("@TimeofAppointment", selected); 
        insert.Parameters.AddWithValue("@Disease", disease); 
        insert.ExecuteNonQuery(); 
        TextBox1.Text = ""; 
        RadioButtonList1.SelectedIndex = -1; 
        txtDisease.Text = ""; 
        ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('Appointment submitted! Please wait for your SMS confirmation. Thank you!')</script>"); 
       } 
      } 
      else 
      { 
       TextBox1.Text = ""; 
       RadioButtonList1.SelectedIndex = -1; 
       txtDisease.Text = ""; 
       ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language='javascript'>alert('You are not a registered client!')</script>"); 
      } 
     } 
     catch 
     { 
      conn.Close(); 
     } 

    } 


}

它仍然没有工作... :(

执行第一个命令后，我将如何执行另一个SELECT命令？

相关推荐