SqlBulkCopy Avoid duplicate inserts while importing Excel File

Last Reply on Aug 01, 2013 03:18 AM By Mudassar

Posted on Aug 01, 2013 02:29 AM

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:

 

 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="way2.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <style type="text/css">
        .auto-style1 {
            width: 217px;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
    <div>
<table style="height: 88px; width: 429px">
<tr>
<td>
<span style="color: Red">*</span>Attach Excel file
</td>
<td class="auto-style1">
<asp:FileUpload ID="fileuploadExcel" runat="server" Height="22px" Width="300px" />
</td>
</tr>
<tr>
<td>
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Way2" />
    </td>
<td class="auto-style1">
<asp:Button ID="btnSend" runat="server" Text="Export" onclick="btnSend_Click"  />
</td>
</tr>
</table>

</div>
    </form>
</body>
</html>

 

Backend code:

 

 

protected void btnSend_Click(object sender, EventArgs e)
{
//file upload path
string path = fileuploadExcel.PostedFile.FileName;
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\xxxxxx\Desktop\filename.xlsx';Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection =new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand("Select * from [Sheet1$]",excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
//Give your Destination table name
sqlBulk.DestinationTableName = "Traffic";
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
}

 

please help  ! a code will be more helpful.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html