I Don't Know What to Write To Prevent Duplicate / Same Records in Database C#

Question

So, I need these codes to prevent duplicate Records when i'm inserting a new record. Basically when i'm trying to insert a new label, first of all it should check either this record already exist in the database or not.

This is the Button Code:

private void newBtn_Click_1(object sender, EventArgs e) {
  Check_Item();
  con = new SqlConnection(cs);
  con.Open();
  adapt = new SqlDataAdapter("INSERT INTO tbl_list (Label) VALUES ('" + lblBox.Text + "')", 
                              con);
  adapt.SelectCommand.ExecuteNonQuery();
  con.Close();
  MessageBox.Show("New Data Added Successfully", 
                  "Notice", 
                  MessageBoxButtons.OK, 
                  MessageBoxIcon.Information);
  clearEditTab();
}

and i don't know what to write in the Check_Item

Read about primary keys, unique constraints and unique indexes . — Zohar Peled, Oct 05 '19 at 04:11
Nearly everything about that code is messed up- dataadapters aren't meant to have an insert shoved into their select command and they manage connection opening and closing for you, and using string concat to build sql strings opens you up to injection hacking. Have you considered using Dapper? It would clean your code up a bit and take away all this manual low level database stuff — Caius Jard, Oct 05 '19 at 05:17
I'm sorry, i'm pretty new with all this coding things and all things that i've learn is from some india guys on youtube. If you guys can teach me or recommend me on how to make my code better please let me know — Hermada Deva, Oct 05 '19 at 16:41

Caius Jard · Answer 1 · 2019-10-05T06:01:58.870

I recommend you read http://dapper-tutorial.net then this will make more sense (it should be pretty readable anyway and there are dapper tutorials all over in case that site ever goes away, but bar a couple of lines where you set up a database connection factory this is pretty much all you need to get dapper to count how many times X appears in a table, and insert a record if the count is zero):

var result = dapper.ExecuteScalar<int>("select count(*) from tbl_List where label = @p", new { p = lblBox.Text });

if(result == 0)
  dapper.Insert(new tbl_List(){ Label = lblBox.Text });
else
  MessageBox.Show("Label exists");

Dapper is a device that can take all your nicely typed objects like Person with its FirstName and LastName properties , and map them to a database table called Person that has FirstName and LastName columns. You query the person table with a bit of SQL and get a List or Person objects back. You make a new Person object and pass it to Insert and Dapper makes a new record in person to match it.

I'm recommending you use it because you look like you know a bit of sql, it's not as heavy as EF and is easy to get started with quicker, it will save you a bunch of time using dataadapters (incorrectly) and connections etc and stop you from writing injection hacking prone code.

Honestly; all you need to read is the first page of that tutorial website. I'll be amazed if it doesn't make you go "heck yeah, this will change my database life" after just 2 minutes

plus one for documentation link! – Prashant Pimpale Oct 05 '19 at 05:42 — Prashant Pimpale, Oct 05 '19 at 05:42

score 1 · Accepted Answer · answered Oct 05 '19 at 05:12

You can use this code (by SqlCommand and more Sql):

private void newBtn_Click(object sender, EventArgs e)
        {  
            //No Need : Check_Item();         
            SqlCommand cmd = new SqlCommand(); 
            con = new SqlConnection(cs);
            cmd.Connection = con;
            cmd.CommandText = "IF NOT EXISTS(select * from tbl_list where Label = '" + lblBox.Text + "') begin INSERT INTO tbl_list(Label) VALUES('" + lblBox.Text + "') end";
            try
            {
                con.Open();

                int affected=cmd.ExecuteNonQuery();
                if (affected > 0)
                {
                    MessageBox.Show("New Data Added Successfully",
                              "Notice",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("Duplicate Data",
                              "Notice",
                              MessageBoxButtons.OK,
                              MessageBoxIcon.Information);
                }
            }
            catch(SqlException err)
            {
                MessageBox.Show(err.Message,
                         "Error",
                         MessageBoxButtons.OK,
                         MessageBoxIcon.Error);
            }
            finally
            {
                con.Close();
            }           

            clearEditTab();
        }

Or you can use stored procedures : this post

+1 for the effort and because it solves major question, but there's still a lot of problems with this implementation, especially on obvious SQL injection flaw. Whether it's better than first post and it can help OP to improve his understanding, you should warn him that we don't recommand him to use this for any serious usage. — AFract, Oct 05 '19 at 05:42

score 0 · Answer 3 · answered Oct 05 '19 at 04:17

0

You can declare tbl_list Label column as primary key.Or you can iterate through all data in Label column.

answered Oct 05 '19 at 04:17

Sadiqul

145
1
11

i already used the Primary Key on other Columns and used it on some other code so.... – Hermada Deva Oct 05 '19 at 04:27
You can iterate through all data in Label column.And check whether new data existing or not in your database.Select all data from Label column from the table and then you can easily check data duplication.Let me know if you need code for this operation. – Sadiqul Oct 05 '19 at 04:39

I Don't Know What to Write To Prevent Duplicate / Same Records in Database C#

3 Answers3