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:
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Data;
- using System.Data.SqlClient;
- using System.Configuration;
- using System.Data.OleDb;
- using System.Data.Common;
- public partial class uploadxml : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- }
- protected void btnUpload_Click(object sender, EventArgs e)
- {
- if (FileUpload1.PostedFile!=null)
- {
- try
- {
- string path = string.Concat(Server.MapPath("~/UploadFile/" + FileUpload1.FileName));
- FileUpload1.SaveAs(path);
-
- string excelCS = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);
- using (OleDbConnection con = new OleDbConnection(excelCS))
- {
- OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$]", con);
- con.Open();
-
- DbDataReader dr = cmd.ExecuteReader();
-
- string CS = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
-
- SqlBulkCopy bulkInsert = new SqlBulkCopy(CS);
- bulkInsert.DestinationTableName = "Employee";
- bulkInsert.WriteToServer(dr);
-
- lblMessage.Text = "Your file uploaded successfully";
- lblMessage.ForeColor = System.Drawing.Color.Green;
- }
- }
- catch (Exception)
- {
- lblMessage.Text = "Your file not uploaded";
- lblMessage.ForeColor = System.Drawing.Color.Red;
- }
- }
- }
- }