How to stop data duplication in database

Question

I am making timetable management.I want to display the table in grid view. I have done every thing except these data duplication is database.Repeated values should not accept in database.I have tried these below code but it is displaying duplicate values is there any problem in these code?

 protected void Button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Enfosys-8\Documents\Visual Studio 2012\Projects\NewTimeTable\NewTimeTable\App_Data\Admindetail.mdf;Integrated Security=True"))
        {
            con.Open();
            SqlDataAdapter cmd = new SqlDataAdapter("select * from datelist where WeekMonth='" + txtmonth.Text + "' and subject='" + ddlsubject.SelectedValue + "' and daywk='" + ddlweek.SelectedValue + "' ", con);
            DataTable dt = new DataTable("dt");
            cmd.Fill(dt);
            Gv3.DataSource = dt;
            Gv3.DataBind();
        }

    }


 using (SqlDataReader Reader = cmd.ExecuteReader())
            {
                if (Reader.HasRows)
                {
                    throw new ApplicationException("data is duplicated");
                }
            }

You are using `ExecuteReader` on a command that contains an insert-query. That doesn't make much sense. You have to use a select-query. But this is inefficient and more important: a race condition. Instead [create a unique database index](http://stackoverflow.com/questions/64981/sql-server-2005-how-create-a-unique-constraint) with these columns and handle the possible `SqlException` in your code. — Tim Schmelter, May 05 '15 at 10:54

Ray Krungkaew · Answer 1 · 2015-05-05T12:03:34.773

I'm not sure about your database design. There are many way to achieve this.

Use constraint

1.1 You can try to create composite primary key in your table. An attempt to insert duplicated value will result in exception

ALTER TABLE test ADD CONSTRAINT PK_test PRIMARY KEY (datedif,hour1,hour2,hour3,hour4,hour5,dayweek,weekmonth)

1.2 Use unique constraint

ALTER TABLE test
ADD CONSTRAINT uc_test UNIQUE
(
   datedif,hour1,hour2,hour3,hour4,hour5,dayweek,weekmonth
)

Use Merge statement to avoid duplicated row insertion. https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396

score 0 · Accepted Answer · answered May 05 '15 at 12:23

As suggested by Rawitas Krungkaew, Add unique constraint to Table for which the columns should not have duplicate value, After adding the unique column, We can have many approaches: One is explained below:

First in store procedure, Insertion will fail due to constraint, so we can use try\catch to handle the exception.

try
{
 int i =  cmd.ExecutenonQuery())
}
catch (ConstraintException ex)
{
  throw new ApplicationException("data is duplicated");
}

How to stop data duplication in database

2 Answers2