how to find duplicate data while uploadind excel file

Jun 28 2019 11:31 PM

Hello,

i am creating a form to upload data from excel file to sql database.

but having an issue of saving data due to 1 field as unique, so i can't insert data it gives error.

can anyone tell me how to modify my code so i can append data to sql database using my web form and removing duplicates and giving notification how many removed kind ?/

please help me.

here is my frontend code:
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using System.Data;  
  8. using System.Data.SqlClient;  
  9. using System.Configuration;  
  10. using System.Data.OleDb;  
  11. using System.Data.Common;  
  12. public partial class uploadxml : System.Web.UI.Page  
  13. {  
  14. protected void Page_Load(object sender, EventArgs e)  
  15. {  
  16. }  
  17. protected void btnUpload_Click(object sender, EventArgs e)  
  18. {  
  19. if (FileUpload1.PostedFile!=null)  
  20. {  
  21. try  
  22. {  
  23. string path = string.Concat(Server.MapPath("~/UploadFile/" + FileUpload1.FileName));  
  24. FileUpload1.SaveAs(path);  
  25. // Connection String to Excel Workbook  
  26. string excelCS = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);  
  27. using (OleDbConnection con = new OleDbConnection(excelCS))  
  28. {  
  29. OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);  
  30. con.Open();  
  31. // Create DbDataReader to Data Worksheet  
  32. DbDataReader dr = cmd.ExecuteReader();  
  33. // SQL Server Connection String  
  34. string CS = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;  
  35. // Bulk Copy to SQL Server  
  36. SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);  
  37. bulkInsert.DestinationTableName = "Employee";  
  38. bulkInsert.WriteToServer(dr);  
  39. // BindGridview();  
  40. lblMessage.Text = "Your file uploaded successfully";  
  41. lblMessage.ForeColor = System.Drawing.Color.Green;  
  42. }  
  43. }  
  44. catch (Exception)  
  45. {  
  46. lblMessage.Text = "Your file not uploaded";  
  47. lblMessage.ForeColor = System.Drawing.Color.Red;  
  48. }  
  49. }  
  50. }  
  51. } 

Answers (3)