I have created a program in ASP.NET to import values from Excel into a SQL Server database table using ASP.NET. The problem is that whenever I am trying to update values in my Excel file columns and try to import the file again to update the SQL Server table, the columns instead of replacing the already existing file of the same column it are again copied, so the whole data values with modified values end up in a new row.
My html code for uploading the file:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Import" OnClick="btnUpload_Click" />
<br />
<br />
</div>
</form>
</body>
</html>
My ASP.NET code for importing data from excel to sql server database:
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
public partial class Default2 : System.Web.UI.Page
{
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string sPath = Server.MapPath("~/BulkFolder/" + FileUpload1.FileName);
FileUpload1.SaveAs(sPath);
ImporttoSQL(sPath);
}
}
private void ImporttoSQL(string sPath)
{
string sSourceConstr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", sPath);
string sDestConstr = ConfigurationManager.ConnectionStrings["masterConnectionString1"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("select [Employee Name],[Designation],[Posting],[Dept] FROM [{0}]", "Sheet1$");
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "Employee";
bulkCopy.WriteToServer(dr);
}
}
}
}
}
Code for creating table in SQL Server:
CREATE TABLE [dbo].[Employee]
(
[Employee Name] [varchar](max) NOT NULL,
[Designation] [varchar](max) NOT NULL,
[Posting] [varchar](max) NOT NULL,
[Dept] [varchar](max) NOT NULL
)
Take a look at the Excel file:
My Excel file columns and their values
I have replaced Employee Name - "Robert Vodro" with "saurabh sharma"
But instead of replacing the value in database it has copied the entire table values again with the modification. Please help me with this..
If possible is there any way which I can implement in my code which will clear all the old values stored in the SQL Server table and replace it with the new values whenever I run the code?
