Create to check for the duplicated name before being entered in the database)

private void btn_Submit_Click(object sender, EventArgs e)
{
    con.Open();

    SqlCommand cmd = con.CreateCommand();

    if (txt_Name.Text == "Employee")
    {
        cmd.ExecuteNonQuery();
        con.Close();

        display_data();
        MessageBox.Show("Name existed");
    }
    else
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into Employee values('"   txt_Name.Text   "','"   txt_Contact.Text   "','"   txt_Address.Text   "','"   txt_Email.Text   "','"   txt_Password.Text   "')";

        cmd.ExecuteNonQuery();

        con.Close();

        display_data();
        MessageBox.Show("Inserted successfully");
    }
}

CodePudding user response：

It is a security risk to concat the string with the parameters, use the sqlcommand parameters instead https://learn.microsoft.com/de-de/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-6.0

Example:

var query = "INSERT INTO Employee VALUES (@name, @contact, @address, @email, @password)";
cmd.CommandText = query;

cmd.Parameters.AddWithValue("@name", txt_Name.Text);
cmd.Parameters.AddWithValue("@contact", txt_Contact.Text);
cmd.Parameters.AddWithValue("@address", txt_Address.Text);
cmd.Parameters.AddWithValue("@email", txt_Email.Text);
cmd.Parameters.AddWithValue("@password", txt_Password.Text);

There are several ways to the goal for your problem

• Unique Index
  You can add an unique index on the table, this throws an exception when executing the command

• Transact-SQL
  It is also possible to move the whole verification logic to the database server https://dba.stackexchange.com/questions/125886/check-if-a-user-exists-in-a-sql-server-database

IF NOT EXISTS (SELECT * FROM Employee WHERE Name = @name)
BEGIN
    INSERT INTO Employee VALUES (@name, @contact, @address, @email, @password)
END

• Query before insert
  Execute a second query with a filter by name and check if a row exists. However, this is not an absolute protection. Duplicate data can still occur here with parallel executions.
  
  If you start a transaction you could secure this block but this would restrict the database access for other users during this time. https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16

CodePudding user response：

You can get the number of rows with the same name. If it is greater than zero, do not add new row... But the better way is to create a UNIQUE INDEX for the name column in sql like:

CREATE UNIQUE INDEX uidx_pid
ON Employee(name);

then on c# code:

        string sqlQuery = "insert into Employee values('"   txt_Name.Text   "','"   txt_Contact.Text   "','"   txt_Address.Text   "','"   txt_Email.Text   "','"   txt_Password.Text   "')";
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlCommand cmd = new SqlCommand(sqlQuery , con))
            {
                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();
                    display_data();
                    MessageBox.Show("Inserted successfully");
                }
                catch(Exception ex) {
                    display_data();
                    MessageBox.Show("Name existed");
                };
                
            }
        }