Check if the Excel entries has duplicate in upload asp.net webform

Question

I'm trying to work with uploading excel in asp.net c# web form and save in sql however it also upload duplicate data in row.

I tried only tried to save the upload in sql however with duplicate/ same data in upload/save in sql. I wanted to void the duplication of the data

private bool uploadfile1()
{
    try
    {
        if (FileUpload1.PostedFile.FileName != "" || FileUpload1.PostedFile.FileName != "File Template")
        {
            this.checkDirectory();
            FileUpload1.PostedFile.SaveAs(Server.MapPath("") + "\\Uploads\\" + FileUpload1.FileName);
            filename = FileUpload1.FileName;
            path = Server.MapPath("") + "\\Uploads\\";
            ExcelCS xl = new ExcelCS();
            xl.UserId = (Session["userid"] != null) ? Session["userid"].ToString() : String.Empty;
            xl.Session = (Session["sessionID"] != null) ? Session["sessionID"].ToString() : String.Empty;
            if (xl.ParseExcelFile(path + filename, true, 1))
            {
                listdata save = new listdata();
                if (save.save())
                {
                    Class.JSclass.ShowAlert(save.RecordsUploaded.ToString() + " item(s) uploaded and " + save.TotalUpdated.ToString() + " item(s) updated out of " + save.TotalRecords.ToString() + " Record(s).");
                    listweb.Class.Logger.writeLog(Session["userid"].ToString(), Session["sessionID"].ToString(), "frmUploadFile.uploadfile1", "File uploaded successfully!", "", "", Class.LogCategories.SELECT);
                    Class.JSclass.refreshMainPage();
                    return true;
                }
            }
            else
            {
                Class.JSclass.ShowAlert("Invalid file format please use specified template.");
                listweb.Class.Logger.writeLog(Session["userid"].ToString(), Session["sessionID"].ToString(), "frmUploadFile.uploadfile1.ParseExcelFile", "Error: " + xl.Message, "", "", Class.LogCategories.SELECT);
                return false;
            }
        }
    }
    catch (Exception e)
    {
        Class.JSclass.ShowAlert("Error: " + e.Message);
        listweb.Class.Logger.writeLog(Session["userid"].ToString(), Session["sessionID"].ToString(), "frmUploadFile.uploadfile1", "Error: " + e.Message, "", "", Class.LogCategories.SELECT);
    }

    Class.JSclass.ShowAlert("File upload failed!");
    return false;
}

Tuhin Paul · Answer

an example of how you can modify your uploadfile1() method to check for duplicates: private bool uploadfile1() { try { if (FileUpload1.PostedFile.FileName != "" || FileUpload1.PostedFile.FileName != "File Template") { this.checkDirectory(); FileUpload1.PostedFile.SaveAs(Server.MapPath("") + "\Uploads\" + FileUpload1.FileName); filename = FileUpload1.FileName; path = Server.MapPath("") + "\Uploads\"; ExcelCS xl = new ExcelCS(); xl.UserId = (Session["userid"] != null) ? Session["userid"].ToString() : String.Empty; xl.Session = (Session["sessionID"] != null) ? Session["sessionID"].ToString() : String.Empty; if (xl.ParseExcelFile(path + filename, true, 1)) { listdata save = new listdata(); DataTable dt = xl.ExcelData.Tables[0]; // Get the first worksheet in the Excel file HashSet uniqueValues = new HashSet (); // Create a hash set to store the unique values foreach (DataRow row in dt.Rows) { string value = row["ColumnName"].ToString(); // Replace "ColumnName" with the actual name of the column that contains the unique values if (!uniqueValues.Contains(value)) // Check if the hash set already contains the value { uniqueValues.Add(value); // Add the value to the hash set if it's unique // Add code to save the row to the SQL database here } } // Add code to display the upload summary and refresh the page here return true; } else { Class.JSclass.ShowAlert("Invalid file format please use specified template."); listweb.Class.Logger.writeLog(Session["userid"].ToString(), Session["sessionID"].ToString(), "frmUploadFile.uploadfile1.ParseExcelFile", "Error: " + xl.Message, "", "", Class.LogCategories.SELECT); return false; } } }

Tuhin Paul · Answer

Before iterating over the Excel rows, retrieve the primary key column name of the table where you will be storing the data. This can typically be found in the connection string or determined programmatically based on the table structure. Loop through each row in the Excel sheet, extract the data into variables, and then query the database to see if there already exists a record with the same value for the primary key column. If so, skip processing that row. Otherwise, insert the row into the database as usual.

Tuhin Paul · Answer

To avoid duplicates when uploading Excel files in your ASP.NET C# web forms application, you should first check if the uploaded file contains duplicate rows before saving them in SQL. One way to do this is by adding a loop to iterate over each row in the Excel worksheet and comparing its values against existing records in the database.

Code Ricx · Answer

Hi @Tuhin Paul, Thank you for your response however I have a question where did you get the Table[0] in DataTable dt = xl.ExcelData.Tables[0];