CODEWITHSUNDEEP
c#asp.net
Ravinther M
Ravinther M

Reputation: 263

How to stop data duplication in database

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");
                }
            }

Upvotes: 0

Views: 1060

Answers (2)

Dreamweaver
Dreamweaver

Reputation: 1346

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");
}

Upvotes: 0

Ray Krungkaew
Ray Krungkaew

Reputation: 6965

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

  1. 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
)
  1. Use Merge statement to avoid duplicated row insertion. https://msdn.microsoft.com/en-us/library/bb510625.aspx?f=255&MSPPError=-2147217396

Upvotes: 1

Related Questions