Preventing duplicate records in a database using visual c#

[Woodenhousen]

I am completely new to the C# development world and I am working on a small application which will allow a user to add basic employee details to a employee table. I can get the code to add a row to the table but the question I asked myself is "What would happen if the user were to add a record which already exists?" Answer, it would simply add another row and so I could end up with duplicated records, how could I go about preventing this using a try/catch block?

The columns I have are EmployeeID (Unique and PK), FirstName, Surname and JobTitle.

The code I have so far is:

System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlDataAdapter adapter;
DataSet DSTestingDataSet;

private void Form1_Load(object sender, EventArgs e)

        {

            connection = new System.Data.SqlClient.SqlConnection();
            DSTestingDataSet = new DataSet();
            connection.ConnectionString = "Data Source=.\\SQLEXPRESS; etc etc";

            connection.Open();
            string sql = "SELECT * FROM Employee";
            adapter = new System.Data.SqlClient.SqlDataAdapter(sql, connection);
            adapter.Fill(DSTestingDataSet, "Employee");
            connection.Close();

        }

 private void button1_Click(object sender, EventArgs e)
        {

            System.Data.SqlClient.SqlCommandBuilder builder;
            builder = new System.Data.SqlClient.SqlCommandBuilder(adapter);

            DataTable dTable = DSTestingDataSet.Tables["Employee"];
            DataRow dRow = DSTestingDataSet.Tables["Employee"].NewRow();
            
            dRow["FirstName"] = textBox1.Text;
            dRow["Surname"] = textBox2.Text;
            dRow["JobTitle"] = textBox3.Text;

            DSTestingDataSet.Tables["Employee"].Rows.Add(dRow);

            adapter.Update(DSTestingDataSet, "Employee");

[thines01]

A try/catch block would only help if there is a unique index on the table that prevents duplicates.
A different solution is to first test to are if the record exists before insertion.

[Woodenhousen]

A try/catch block would only help if there is a unique index on the table that prevents duplicates.
A different solution is to first test to are if the record exists before insertion.

As I am a neewbie to C#, could you kindly suggest how I would do this.

[ChrisHunter]

you could do an INSERT statement with a WHERE clause to prevent a new row being added to a table where the same records already exits.

would be something like (but dont take my word for it)

INSERT INTO Employee AS E (Name, Age, DOB) VALUES (value1, value1, value3) WHERE E.Name != value1 AND E.Age != value2 AND E.DOB != value3;

or you could do a int recordCount = select COUNT(*) from Employee WHERE the table values match the values entered. If the value of recordCount is 0 then do the insert (as above but without the where clause in it)

Do some research on it though to make sure.

hope this helps

[thines01]

Those are great options, ChrisHunter!

[sohaiby]

IF NOT EXISTS (SELECT * FROM dbo.tableName WHERE PrimaryKey = @PrimaryKey)
INSERT INTO dbo.tableName(list of columns)
VALUES (list of values)