Skip inserting existing data/list in database when the current listing is in the list

Question

Here is the function for saving the list, is there any way for me to save the list in the database and skip the existing one so it will duplicate with the existing one.

private void SaveEventDetailEdit()
{
    clsGlobalVariable gv = clsGlobalVariable.GetInstance;

    gv.EventIdEdit = txtEventIDEdit.Text;

    int counter = 0;

    foreach (ListViewItem item in lvwAttendeesEdit.Items)
    {
        counter++;
        gv.ItemNoEdit = counter;
        gv.DEmpIdEdit = item.SubItems[1].Text.ToString();
        gv.DAttendeesEdit = item.SubItems[2].Text.ToString();
        gv.EdeptEdit = item.SubItems[3].Text.ToString();

        DataTable dt = new DataTable();
        DataSet ds = new DataSet();

        DBSQLStatement dbSQLLayer = new DBSQLStatement();
        ds = (DataSet)dbSQLLayer.SaveEventDetailEdit();
    }
}

Here is my SQL that saves the list

public object SaveEventDetailEdit()
{
    DBConnection conn = new DBConnection();
    DataSet ds = new DataSet();
    StringBuilder sbSQL = new StringBuilder();

    clsGlobalVariable gv = clsGlobalVariable.GetInstance;
    
    sbSQL.AppendLine(" INSERT INTO tblEventDetail( EventID, EmpID, Attendees, Dept, ItemNo)");
    sbSQL.AppendLine(" VALUES ( '" + gv.EventIdEdit + "', '" + gv.DEmpIdEdit + "', '" + gv.DAttendeesEdit + "', '" + gv.EdeptEdit + "', '" + gv.ItemNoEdit + "')");
    

    ds = (DataSet)conn.SelectData(sbSQL.ToString());
    return ds;
}

1) Use parameters for your SQL insert (not string concat which is open to SQL injection). 2) Use a `WHERE NOT EXISTS (...)` in your insert statement. 3) Pass all your data in one go to a SP using a Table Valued Parameter for better performance. — Dale K, Jul 28 '22 at 02:29
@JeremyLakeman Hmmm... there are still many good reasons not to use merge. — Dale K, Jul 28 '22 at 04:07
@DaleK I hadn't really looked into it, since I don't use it much myself either. Does this still apply? https://michaeljswart.com/2021/08/what-to-avoid-if-you-want-to-use-merge/ — Jeremy Lakeman, Jul 28 '22 at 04:16

Hugo Vares · Answer 1 · 2022-07-28T04:12:31.683

Your insert should be something like:

sbSQL.AppendLine(" INSERT INTO tblEventDetail tb1( EventID, EmpID, Attendees, Dept, ItemNo)");
sbSQL.AppendLine(" VALUES ( '" + gv.EventIdEdit + "', '" + gv.DEmpIdEdit + "', '" + gv.DAttendeesEdit + "', '" + gv.EdeptEdit + "', '" + gv.ItemNoEdit + "')");
sbSQL.AppendLine(" WHERE NOT EXISTS ( SELECT 1 FROM tblEventDetail tb2 WHERE tb2.EventID= '" + gv.EventIdEdit + "')")

Like Dale K mentioned, you really should avoid this concatenate technique to run your SQL commands because SQL Injection by using parameters.

You could create a stored procedure like:

sp_InsertEventDetail (@pEventID INT, @pEmpID INT, @pAttendees VARCHAR(MAX), @pDept VARCHAR(MAX), @pItemNo BIGINT)
AS
BEGIN

    INSERT INTO tblEventDetail tb1(EventID, EmpID, Attendees, Dept, ItemNo)
    SELECT @pEventID,@pEmpID,@pAttendees,@pDept,@pItemNo
    WHERE NOT EXISTS WHERE NOT EXISTS (SELECT 1 FROM tblEventDetail tb2 WHERE tb2.EventID = @pEventID)3
    
END

If you're going to use an SP use a Table Valued Parameter to save multiple trips to the database. — Dale K, Jul 28 '22 at 03:22
You can't use `VALUES ( ... ) WHERE ....` this is not a valid syntax. Change to `SELECT @pEventID,@pEmpID ... WHERE ....` — Squirrel, Jul 28 '22 at 03:42

Skip inserting existing data/list in database when the current listing is in the list

1 Answers1