C#检查重复项，如果没有重复则运行插入查询

Question

大家好，我需要这方面的帮助。我想在这里做的是检查我的 mysql 数据库，如果一个成员已经存在，使用基于我的 winform 上的数据条目的成员的名称。重复条目的验证有效，以我想要的方式完美显示消息框，但它不会执行我在找不到现有成员的情况下所做的插入查询。

我这样做正确吗？还是有另一种方法让它按照我想要的方式工作。

这是我的代码:

private void metroButton1_Click(object sender, EventArgs e) {
    using (con = new MySqlConnection(constring)) {      
        string selectquery = "SELECT * FROM sbis.sb_members WHERE lname ='" + this.lnametxtbox.Text + "' AND fname = '" + this.fnametxtbox.Text + "' AND mname ='" + this.mnametxtbox.Text + "'  ";
        MySqlCommand command2 = new MySqlCommand(selectquery, con);

        string insertquery = "INSERT INTO sbis.sb_members (lname, fname, mname, position, appointment, address, contactnum, birthdate, civilstatus, educattainment, eligibility, terms_idterms, polparties_id, sex) VALUES (@lname, @fname, @mname, @position, @appointment, @address, @contactnum, @birthdate, @civilstatus, @educattainment, @eligibility, @terms_idterms, @polparties_id, @sex)";
        MySqlCommand cmd = new MySqlCommand(insertquery, con);
        string lname, fname, mname, address, contactnum, educattainment;

        lname = lnametxtbox.Text;
        fname = fnametxtbox.Text;
        mname = mnametxtbox.Text;
        address = addresstxtbox.Text;
        contactnum = contacttxtbox.Text;
        educattainment = eductxtbox.Text;
        var birthdate = birthdatedtp.Value.Date;

        cmd.Parameters.AddWithValue("@lname", lname);
        cmd.Parameters.AddWithValue("@fname", fname);
        cmd.Parameters.AddWithValue("@mname", mname);
        cmd.Parameters.AddWithValue("@address", address);
        cmd.Parameters.AddWithValue("@contactnum", contactnum);
        cmd.Parameters.AddWithValue("@position", position);
        cmd.Parameters.AddWithValue("@appointment", appointment);
        cmd.Parameters.AddWithValue("@birthdate", birthdate);
        cmd.Parameters.AddWithValue("@sex", sex);
        cmd.Parameters.AddWithValue("@eligibility", eligibility);
        cmd.Parameters.AddWithValue("@civilstatus", civilstatus);
        cmd.Parameters.AddWithValue("@terms_idterms", terms);
        cmd.Parameters.AddWithValue("@polparties_id", polparties);
        cmd.Parameters.AddWithValue("@educattainment", educattainment);

        try {
            con.Open();
            MySqlDataReader cr = command2.ExecuteReader();
            while (cr.Read()) {
                if (cr.HasRows == true) {
                    MessageBox.Show("Member already exists.", "Duplicate Entry", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                } else if (cr.HasRows == false) {
                    if (cmd.ExecuteNonQuery() > 0) {
                        MessageBox.Show("Saved", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
            }
            cr.Close();     
        } catch (Exception ex) {
            MessageBox.Show(ex.Message);
        }
    }
}

Answer 1

您似乎知道如何使用准备好的语句。所以为什么在你的 SELECT 中使用字符串连接是个谜

string selectquery = "SELECT * FROM sbis.sb_members WHERE lname ='" + this.lnametxtbox.Text + "' AND fname = '" + this.fnametxtbox.Text + "' AND mname ='" + this.mnametxtbox.Text + "'  ";

但好消息是您根本不需要这个 SELECT。你可以把它扔掉。并利用 mysql 中的 IGNORE 功能。或者，如果您想显示该成员已存在的消息，只需捕获异常即可!

 using (con = new MySqlConnection(constring))
    {

        string insertquery = "INSERT INTO sbis.sb_members (lname, fname, mname, position, appointment, address, contactnum, birthdate, civilstatus, educattainment, eligibility, terms_idterms, polparties_id, sex) VALUES (@lname, @fname, @mname, @position, @appointment, @address, @contactnum, @birthdate, @civilstatus, @educattainment, @eligibility, @terms_idterms, @polparties_id, @sex)";
        MySqlCommand cmd = new MySqlCommand(insertquery, con);
        string lname, fname, mname, address, contactnum, educattainment;


        lname = lnametxtbox.Text;
        fname = fnametxtbox.Text;
        mname = mnametxtbox.Text;
        address = addresstxtbox.Text;
        contactnum = contacttxtbox.Text;
        educattainment = eductxtbox.Text;
        var birthdate = birthdatedtp.Value.Date;

        cmd.Parameters.AddWithValue("@lname", lname);
        cmd.Parameters.AddWithValue("@fname", fname);
        cmd.Parameters.AddWithValue("@mname", mname);
        cmd.Parameters.AddWithValue("@address", address);
        cmd.Parameters.AddWithValue("@contactnum", contactnum);
        cmd.Parameters.AddWithValue("@position", position);
        cmd.Parameters.AddWithValue("@appointment", appointment);
        cmd.Parameters.AddWithValue("@birthdate", birthdate);
        cmd.Parameters.AddWithValue("@sex", sex);
        cmd.Parameters.AddWithValue("@eligibility", eligibility);
        cmd.Parameters.AddWithValue("@civilstatus", civilstatus);
        cmd.Parameters.AddWithValue("@terms_idterms", terms);
        cmd.Parameters.AddWithValue("@polparties_id", polparties);
        cmd.Parameters.AddWithValue("@educattainment", educattainment);

        try
        {
             if (cmd.ExecuteNonQuery() > 0)
             {
                   MessageBox.Show("Saved", "Save", MessageBoxButtons.OK, MessageBoxIcon.Information);
             }
        }
        catch (Exception ex)
        {
             if (ex.Message.ToLower().Contains("duplicate key"))
             {
                    MessageBox.Show("Member already exists.", "Duplicate Entry", MessageBoxButtons.OK, MessageBoxIcon.Warning);
             }
             else {
                  MessageBox.Show(ex.Message);
             }
        }
    }
}

上面的例子捕获重复键错误以显示用户存在的消息而不是忽略错误。

当然，所有这些都假定您已经在不想重复的列上创建了 UNIQUE 或 PRIMARY KEY 约束。这是数据库设计的基本部分。标准做法是依靠数据库来强制执行唯一性和其他约束，而不是在应用程序级别执行