Hi PRA,
Check this example. Now please take its reference and correct your code.
In the below example i have checked with CustomerId. You need to change according to your table structure and condition.
Database
I have made use of the following table Customers with the schema as follows.
You can download the database table SQL by clicking the download link below.
Download SQL file
Namespaces
C#
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
VB.Net
Imports System.ComponentModel
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.IO
Code
C#
private string Excel03ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
private string Excel07ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ConnectionString);
private void btnSelect_Click(object sender, EventArgs e)
{
openDlg.ShowDialog();
}
private void openDlg_FileOk(object sender, CancelEventArgs e)
{
string filePath = openDlg.FileName;
string extension = Path.GetExtension(filePath);
string header = rbHeaderYes.Checked ? "YES" : "NO";
string conStr, sheetName;
conStr = string.Empty;
switch (extension)
{
case ".xls": //Excel 97-03
conStr = string.Format(Excel03ConString, filePath, header);
break;
case ".xlsx": //Excel 07
conStr = string.Format(Excel07ConString, filePath, header);
break;
}
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = con.CreateCommand())
{
con.Open();
DataTable dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
con.Close();
}
}
DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(conStr))
{
using (OleDbCommand cmd = con.CreateCommand())
{
using (OleDbDataAdapter d = new OleDbDataAdapter())
{
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
con.Open();
d.SelectCommand = cmd;
d.Fill(dt);
con.Close();
}
}
}
InsertDelete(dt);
}
private void InsertDelete(DataTable dt)
{
foreach (DataRow dr in dt.Rows)
{
int id = Convert.ToInt32(dr["Id"]);
string name = dr["Name"].ToString();
string country = dr["Country"].ToString();
if (!DataExist(id))
{
// Insert new record into main table.
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "INSERT INTO Customers VALUES(@Name,@Country)";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
else
{
// Insert record into copy table and delete from main table.
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "INSERT INTO CustomersCopy VALUES(@Name,@Country);DELETE FROM Customers WHERE CustomerId = @Id";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Id", id);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
private bool DataExist(int id)
{
bool isExist = false;
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string query = "SELECT COUNT(*) FROM Customers WHERE CustomerId = @Id";
using (SqlConnection con = new SqlConnection(conString))
{
SqlCommand cmd = new SqlCommand(query, con);
cmd.Parameters.AddWithValue("@Id", id);
con.Open();
isExist = Convert.ToInt32(cmd.ExecuteScalar()) > 0 ? true : false;
con.Close();
}
return isExist;
}
VB.Net
Private Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
Private Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
Private con As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("constr").ConnectionString)
Private Sub btnSelect_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSelect.Click
openDlg.ShowDialog()
End Sub
Private Sub openDlg_FileOk(ByVal sender As Object, ByVal e As CancelEventArgs) Handles openDlg.FileOk
Dim filePath As String = openDlg.FileName
Dim extension As String = Path.GetExtension(filePath)
Dim header As String = If(rbHeaderYes.Checked, "YES", "NO")
Dim conStr, sheetName As String
conStr = String.Empty
Select Case extension
Case ".xls"
conStr = String.Format(Excel03ConString, filePath, header)
Case ".xlsx"
conStr = String.Format(Excel07ConString, filePath, header)
End Select
Using con As OleDbConnection = New OleDbConnection(conStr)
Using cmd As OleDbCommand = con.CreateCommand()
con.Open()
Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
con.Close()
End Using
End Using
Dim dt As DataTable = New DataTable()
Using con As OleDbConnection = New OleDbConnection(conStr)
Using cmd As OleDbCommand = con.CreateCommand()
Using d As OleDbDataAdapter = New OleDbDataAdapter()
cmd.CommandText = "SELECT * FROM [" & sheetName & "]"
con.Open()
d.SelectCommand = cmd
d.Fill(dt)
con.Close()
End Using
End Using
End Using
InsertDelete(dt)
End Sub
Private Sub InsertDelete(ByVal dt As DataTable)
For Each dr As DataRow In dt.Rows
Dim id As Integer = Convert.ToInt32(dr("Id"))
Dim name As String = dr("Name").ToString()
Dim country As String = dr("Country").ToString()
If Not DataExist(id) Then
' Insert new record into main table.
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "INSERT INTO Customers VALUES(@Name,@Country)"
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
Else
' Insert record into copy table and delete from main table.
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "INSERT INTO CustomersCopy VALUES(@Name,@Country);DELETE FROM Customers WHERE CustomerId = @Id"
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@Id", id)
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End If
Next
End Sub
Private Function DataExist(ByVal id As Integer) As Boolean
Dim isExist As Boolean = False
Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Dim query As String = "SELECT COUNT(*) FROM Customers WHERE CustomerId = @Id"
Using con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand(query, con)
cmd.Parameters.AddWithValue("@Id", id)
con.Open()
isExist = If(Convert.ToInt32(cmd.ExecuteScalar()) > 0, True, False)
con.Close()
End Using
Return isExist
End Function
