eliminating duplicate records insertion into database

Question

The below is my code to insert gridview data into a database. However, using this I want to check and restrict insertion into the database where records have the same name, location, education and salary. If all of these are the same and those already present in database they should not get inserted. If any one column is different then they should get inserted.

protected void btn_insert_Click(object sender, EventArgs e)
        {
            foreach (GridViewRow g1 in GridView1.Rows)
            {
                SqlConnection con = new SqlConnection(connStr);
                cmd = new SqlCommand("insert command", con);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
            UploadStatusLabel.Text = "Records Inserted Successfully";
        }

Related : http://stackoverflow.com/questions/2513174/how-to-avoid-duplicates-in-insert-into-select-query-in-sql-server — , Jul 01 '16 at 11:07
but i am not having two tables it is the spread sheet data that is exported to gridview — , Jul 01 '16 at 11:09
First perform a Select Query to DataBase checking if data exists. Only insert if Select returns nothing. This method is prone to errors since the user can make a typo. For example what happens if the user types Depa instead of Deepa? — jdweng, Jul 01 '16 at 11:13
How about little bit googling, there are even benchmark for that : http://cc.davelozinski.com/sql/fastest-way-to-insert-new-records-where-one-doesnt-already-exist — , Jul 01 '16 at 11:14
if the user types Depa instead of Deepa then the record should get inserted as there is a change — , Jul 01 '16 at 11:15

Zein Makki · Answer 1 · 2016-07-04T06:35:53.880

0

I think hitting the database inside a for loop is a very bad idea when you have other options. I'm not tackling this issue in the below sample.

Your code may be subject to SQL Injection, you need to use parameters to pass your values. If someone filled the input with ";DROP TABLE OpenOfficetext;" and they have DROP permissions, it will be a problem if you're just concatenating strings.

To avoid duplicates, you can check first if a similar record exists.

foreach (GridViewRow g1 in GridView1.Rows)
{
    string insertCommand = "insert into OpenOfficetext(Name, Location, Education, Salary) values(@p1, @p2, @p3, @p4)";
    string selectCommand = "SELECT COUNT(*) FROM OpenOfficetext WHERE Name = @p1 AND Location = @p2 AND Education = @p3 AND Salary = @p4";

    SqlConnection con = new SqlConnection(connStr);
    SqlCommand cmd = new SqlCommand(selectCommand, con);

    con.Open();
    cmd.Parameters.AddWithValue("@p1", g1.Cells[0].Text);
    cmd.Parameters.AddWithValue("@p2", g1.Cells[1].Text);
    cmd.Parameters.AddWithValue("@p3", g1.Cells[2].Text);
    cmd.Parameters.AddWithValue("@p4", g1.Cells[3].Text);

    if (Convert.ToInt32(cmd.ExecuteScalar()) == 0)
    {
        cmd.CommandText = insertCommand;
        cmd.ExecuteNonQuery();
    }

    con.Close();
}

edited Jul 04 '16 at 06:35

answered Jul 01 '16 at 11:15

Zein Makki

29,485
6
52
63

@abcde there is no syntax error in the code used in my answer. Can you edit your answer and paste the code that is generating the erro ? – Zein Makki Jul 04 '16 at 05:29
@abcde you mean at runtime. I fixed it now. Try it :) – Zein Makki Jul 04 '16 at 05:38
@abcde edited.Be aware, that this is done inside a loop. – Zein Makki Jul 04 '16 at 06:06
Sorry I'm not very knowledgeable, but can't you use .distinct() for the Rows on ForEach? – denchu Jul 04 '16 at 06:14
@abcde i think you need to open another question for this, on how to get distinct rows from GridView. If this answer was helpful, please accept or app-vote. – Zein Makki Jul 04 '16 at 06:16
@abcde sorry i forgot this is ASP.NET . I don't really know how to send a message to the client-side. This is another topic. You can google it or ask another question. – Zein Makki Jul 04 '16 at 06:22

score -2 · Answer 2 · answered Jul 01 '16 at 11:25

-2

please use the below code

if not exist (select * from OpenOfficetext where Name='" + g1.Cells[0].Text + "' and Location='" + g1.Cells[1].Text + "' and  Education = '" + g1.Cells[2].Text + "' and Salary = '" + g1.Cells[3].Text + "' )

Begin
SqlConnection con = new SqlConnection(connStr);
 cmd = new SqlCommand("insert into OpenOfficetext(Name,Location,Education,Salary) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "')", con);

  con.Open();
  cmd.ExecuteNonQuery();
  con.Close();

End

answered Jul 01 '16 at 11:25

Mohit Diwakar

56
5

but it is to be done using c# not with vb .net how can i do this – Jul 02 '16 at 14:48
see the logic, dont go for spoon feeding – Mohit Diwakar Jul 04 '16 at 08:20

eliminating duplicate records insertion into database

2 Answers2