当当前列表在列表中时，跳过在数据库中插入现有数据/列表 [重复]

Question

这是保存列表的功能，有什么方法可以让我将列表保存在数据库中，并跳过现有的列表，这样它就会与现有的列表重复。

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

下面是我保存列表的SQL

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

Answer 1

您的插入应该是这样的：

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 + "')")

正如Dale K提到的，您确实应该避免使用这种连接技术来运行SQL命令，因为SQL注入是通过使用参数实现的。

您可以创建如下存储过程：

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