来访人员登记系统（五）查询和删除模块

查询功能

查询功能是本系统常用的一个功能，主要分为按姓名查询、按身份证查询和按日期查询，这里以最复杂的按日期查询为例，在数据库中找到与输入日期相同的记录并在datagridview控件中显示出来。

private void button_delete_date_Click(object sender, EventArgs e)
{
    try
    {
        // 查找日期
        string cmd = "select number as '序号', name as '姓名', company as '单位', idcard as '身份证号', phone as '电话', " +
                     "staff as '陪同人员', ETA as '预约时间', date as '预约日期', relevants as '涉及设备/系统', " +
                     "intention as '来访目的' from registration where date='" + 
                     dateTimePicker_delete.Value.ToString("yyyy-MM-dd") + "';";

        MySqlCommand SQLCmd = new MySqlCommand(cmd, DBconn);
        MySqlDataReader dr = SQLCmd.ExecuteReader();

        if (dr.HasRows)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            dt.Load(dr);
            dataGridView2.DataSource = dt;
        }
        else
        {
            MessageBox.Show("未找到" + dateTimePicker_delete.Value.ToString("yyyy-MM-dd") + "的预约", "提示", MessageBoxButtons.OK);
        }
        dr.Close();
    }
    catch (MySqlException ex)
    {
        MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);
    }
}

---

删除功能

实现：选中datagridview中的某一单元格，点击删除后将此单元格对应的记录从数据库中删除。

private void button_delete_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("您确定要删除选中的记录吗？", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        try
        {
            string cmd = "select * from registration";
            MySqlCommand SQLCmd = new MySqlCommand(cmd, DBconn);
            MySqlDataReader dr = SQLCmd.ExecuteReader();
            if (dr.Read())
            {
                dr.Close();
                string value = dataGridView2.CurrentRow.Cells[0].Value.ToString();
                int number = Convert.ToInt32(value);
                cmd = "delete from registration where number=" + number + ";";
                SQLCmd = new MySqlCommand(cmd, DBconn);
                SQLCmd.ExecuteNonQuery();
                MessageBox.Show("删除成功", "提示", MessageBoxButtons.OK);
                button_delete_reset_Click(sender, e);
            }
            else
            {
                dr.Close();
                MessageBox.Show("当前表中无记录，无法执行删除操作", "提示", MessageBoxButtons.OK);
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK);
        }
    }
}

---

总结

删除时需先确认数据库中存在待删除记录，否则将会导致运行时报错。