c# - 如何防止秘书使用sql查询插入与另一个相同专业的医生相似的医生可用性？

Question

我有一个用于医疗诊所的 Windows 窗体应用程序 C#，秘书必须使用它来插入医生的空闲时间。我想阻止秘书在同一天将类似的空闲时间插入到另一个具有相同专业的医生中。

1-我的数据库中有这些表:

2- 我的 Windows 窗体是:

3-我的代码是:

 private void bt_addDisp_Click(object sender, EventArgs e)
    {
        int selectedDoctor = dgv_DispMed.CurrentRow.Index;
        string prenomMed = dgv_DispMed.Rows[selectedDoctor].Cells[1].Value.ToString();
        string nomMed = dgv_DispMed.Rows[selectedDoctor].Cells[2].Value.ToString();

        if (MessageBox.Show("Do you realy want to add this disponibility for Dr "+prenomMed+" "+nomMed+" ?", "Confirm Add Disponibility", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
        {
            string spec_id = dgv_DispMed.Rows[selectedDoctor].Cells[6].Value.ToString();


            int jr = int.Parse(cmb_day.SelectedValue.ToString());

            string Str_hdeb = cmb_startTime.Text.ToString();
            string Str_hfin = cmb_endTime.Text.ToString();

            string[] tab_hdeb = Str_hdeb.Split(':');
            int hdeb = int.Parse(tab_hdeb[0]);

            string[] tab_hfin = Str_hfin.Split(':');
            int hfin = int.Parse(tab_hfin[0]);

            MySqlConnection c1 = new MySqlConnection();
            MySqlCommand cmd = new MySqlCommand();
            c1.ConnectionString = "Database=growing_together;Data Source=localhost;User Id=root;password=;CHARSET=utf8;";
            c1.Open();
            string Q = "select count(*) as nb from medecin,disp_medecin,disponibilite where medecin_id=med_id and disp_id=disponibilite_id and speciality_id='" + spec_id + "' and jour=" + jr + " and "+ hdeb + " between heure_deb and heure_fin and " + hfin + " between heure_deb and heure_fin";
            cmd = new MySqlCommand(Q, c1);
            MySqlDataReader R = cmd.ExecuteReader();

            int nb_disp = 0;
            while (R.Read())
            {
                nb_disp = int.Parse(R.GetString("nb"));
            }
            c1.Close();

            MessageBox.Show(nb_disp.ToString());   

        }

问题是:

如果数据库中的空闲时间与秘书想要插入的空闲时间相似，则查询返回 1，否则返回 0，表示他可以插入该空闲时间。

例如医生 A 和医生 B 都是心脏病专家(相同专业)，

如果医生 A 周一从 9:00 到 11:00 有空，如果我尝试在同一天的时间内为同一专业的另一位医生提供类似的空缺，则查询将返回 1，但是:

当我尝试插入医生 B 从 8:00 到 10:00 或从 10 到 12 的空闲时间时，查询返回 0，这意味着在此期间我可以插入这个新的空闲时间，并且必须撤消该操作因为在这种情况下，我希望查询返回 1，因为 10:00 是在医生 A 的开始时间和结束时间之间，这意味着在这种情况下，同一天、同一时间有 2 位相同专业的医生可以使用。时间(10:00)!

如何阻止这种情况发生？请帮忙。

Answer 1

让我们从负数开始。我可以在您的框架中看到很多问题，所以让我们介绍一下主要问题:

• 为什么将 DATETIME 存储为一天(整数)和一小时(整数)？将它们存储为 DATETIME 不是更高效吗？日(jour)甚至代表什么，是一月中的这一天(在这种情况下，您如何处理不同长度的月份或闰年)，或者是一周中的某一天(在这种情况下，您如何预订提前一周以上预约)？
• 此代码很容易受到 SQL 注入(inject)，因为您在构建查询时没有保护所添加的参数。是的，添加的数据都是来自下拉，所以理论上应该是可以控制的，但是这样还是极不安全的；
• 如果您的 SQL 连接在方法中途断开会发生什么？我强烈建议添加一些基本的错误捕获，还添加一些“使用” block ，以允许在发生故障时正确处理所有这些对象(连接、命令对象、数据读取器)；
• 您声明查询返回 0 表示没有冲突，或返回 1 表示存在冲突，但事实并非如此。它实际上返回冲突的计数，因此如果您以某种方式允许冲突(例如直接向数据库后端添加一个)，那么它可能会返回 2，这可能无法正确处理？

您的 SQL 查询可以分割为以下内容(为了清楚起见，我重新格式化了它):

SELECT 
    COUNT(*) AS nb 
FROM 
    medecin m
    disp_medecin dm ON dm.Med_id = m.medecin_id
    disponibilite d ON d.disponibilite_id = dm.disp_id
WHERE 
    speciality_id = <spec_id>
    AND jour = <jr>
    AND <hdeb> BETWEEN heure_deb AND heure_fin 
    AND <hfin> BETWEEN heure_deb AND heure_fin;

我已经通过粘贴在参数周围来指示要添加的参数的位置。

用英语表达我的理解是:

• 查找有多少医生；
• 具有所要求的专业；
• 预约的日期与该医生的预约相符；
• 并且约会开始时间与该约会相交；
• 并且约会结束时间与该约会相交。

以您的示例为例，A 医生的预约时间为 9 点至 11 点，我们允许某人在 8 点至 10 点预约 B 医生，即使他与 A 医生具有相同的专业，因为预约开始时间与 A 医生的预约开始时间不相交预约(即使结束时间确实如此)。

所以我认为(考虑到上述所有内容)，修复方法就是简单地改变这一点:

and jour=" + jr + " and "+ hdeb + " between heure_deb and heure_fin and " + hfin + " between heure_deb and heure_fin";

对此:

and jour= " + jr + " and ("+ hdeb + " between heure_deb and heure_fin OR " + hfin + " between heure_deb and heure_fin)";

因此，我们现在不是说开始时间和结束时间必须与现有约会相交，而是说如果预订的开始时间或结束时间与现有约会相交，那么这是不行的。

我可能偏离了轨道，所以请告诉我这是否有帮助......