防止重复名称 [英] Prevent Duplicate name

问题描述

您好，



我使用此代码在此表中添加名称

Hello ,

I using this code to add name in this table

private void button1_Click(object sender, EventArgs e)
{
    string Coonstring = "datasource=localhost;port=3306;username=root;password=****;Charset=utf8";
    string cmd = "Insert into project.name_registry (name ) values('" + this.txt.Text + "');";
    MySqlConnection connectionDatabase = new MySqlConnection(Coonstring);
    MySqlCommand cmddata = new MySqlCommand(cmd, connectionDatabase);
    MySqlDataReader myreader;

    try
    {
        connectionDatabase.Open();
        myreader = cmddata.ExecuteReader();
        MessageBox.Show("Done");
        while (myreader.Read())
        {
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

当我按下此按钮时我需要（添加按钮）检查是否显示插入名称消息框告诉我名称是否存在并阻止添加。如果没有告诉我插入完成。我怎么能这样做。



问候

I need when I press on this button ( Add button) check if the insert name found messagebox appear tell my the name exists and prevent the add. If not tell me the insert Done. How i can do this.

Regards

推荐答案

正如Wes所说，你需要做的首先是SELECT语句。



例如：

As Wes said, you'll need to do a SELECT statement first.

For example:

SELECT * FROM name_registry WHERE name = @name

不要连接字符串来执行SQL，因为您可以接受SQL注入。

Don't concatenate strings to do your SQL because you are open to SQL injections.

cmd.Parameters.AddWithValue("@name", txt.Text);

然后检查你的datareader是否有行。如果确实如此，那意味着它已经存在，那么你可以做任何你需要做的事情。

Then check if your datareader has rows. If it does, that means it already exists and then you can do whatever you need to do at that point.

SELECT很容易 - 你知道如何做到这一点，或者你应该在之前做你得到INSERT语句。



但首先......不要做那样的事情！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。

处理Connection和Command对象也是一个好主意 - 它们是稀缺资源。

最简单的方法是使用使用块：

SELECT is easy - you know how to do that already, or you should before you get to INSERT statements.

But first...don't do anything like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
It's also a good idea to Dispose of Connection and Command objects - they are a scarce resource.
The easiest way is to use a using block:

string Coonstring = "datasource=localhost;port=3306;username=root;password=****;Charset=utf8";
using (MySqlConnection connectionDatabase = new MySqlConnection(Coonstring))
    {
    try
        {
        connectionDatabase.Open();
        using (MySqlCommand select = new MySqlCommand("SELECT COUNT(*) FROM project.name_registry WHERE Name=@NM", connectionDatabase))
            {
            select.Parameters.AddWithValue("@NM", txt.Text);
            if (select.ExecuteScalar() != 0)
                {
                MessageBox.Show("Name exists");
                return;
                }
            }
        using (MySqlCommand cmddata = new MySqlCommand("INSERT INTO project.name_registry (name) VALUES(@NM)", connectionDatabase))
            {
            cmddata.Parameters.AddWithValue("@NM", txt.Text);
            cmddata.ExecuteNonQuery();
            MessageBox.Show("Done");
            }
        }
    catch (Exception ex)
        {
        MessageBox.Show(ex.Message);
        }
    }

SQL注入是好点。最好有一个参数化查询，如解决方案2所示。但我会检查SQL中的行存在如下：



[updated（compiled） ]

SQL injection is good point. it is better to have a parameterized query as indicated in Solution 2. But I would check the row existence in SQL as the following:

[updated("compiled")]

void UpdateTable(string connStr, string nameVal)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                string cmdStr = @"insert into project.name_registry(name) 
    select * from (select @pname) as tmp
    where not exists (select * from project.name_registry where name=@pname)";
                using (MySqlCommand cmd = new MySqlCommand(cmdStr, conn))
                {
                    cmd.Parameters.AddWithValue("@pname", nameVal);
                    if (cmd.ExecuteNonQuery() > 0)
                    {
                        Console.WriteLine("added");
                    }
                    else
                    {
                        Console.WriteLine("exists");
                    }
                }
            }
        }

这篇关于防止重复名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！