Import Excel and Move existing rows if record exists using C# and VB.Net in Windows Application

Last Reply 6 months ago By dharmendr

Posted 6 months ago

Hi!

I have two tables in sql. Import data from excel by using c#. Each data have unique code. Now I want import data from excel if by unique code excel data exist in database then insert existing data into another table tblCopy. If not exist data insert only new data remove existing data to another table and delete from inform table.

CREATE TABLE [dbo].[Inform](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](20) NOT NULL,
    [UCode] [int] NOT NULL
)

INSERT [dbo].[Inform] ([Id], [Name], [UCode]) VALUES (1, N'Rustam', 10005)
INSERT [dbo].[Inform] ([Id], [Name], [UCode]) VALUES (2, N'Firuzjon', 10201)
INSERT [dbo].[Inform] ([Id], [Name], [UCode]) VALUES (3, N'Asror', 11006)
INSERT [dbo].[Inform] ([Id], [Name], [UCode]) VALUES (4, N'Sadriddin', 10002)

CREATE TABLE [dbo].[tblCopy](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](20) NOT NULL,
    [UCode] [int] NOT NULL
)

 

namespace ImportFromExcel
{
    public partial class Form1 : Form
    {
        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["source"].ConnectionString);

        public Form1()
        {
            InitializeComponent();
        }

        private void btnSelect_Click(object sender, EventArgs e)
        {
            openDlg.ShowDialog();
        }        

        private void btnAdd_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < Grid.Rows.Count; i++)
            {
                InsertDepData(Grid.Rows[i].Cells[0].Value.ToString(), Convert.ToInt32(Grid.Rows[i].Cells[1].Value));
            }
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void InsertDepData(string name, int ucode)
        {
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandText = "insert into inform(name, ucode)values('" + name + "','" + ucode + "')";
            con.Open();
            try
            {
                cmd.ExecuteReader();
            }
            catch { }
            con.Close();
        }

        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();
                }
            }

            using (OleDbConnection con = new OleDbConnection(conStr))
            {
                using (OleDbCommand cmd = con.CreateCommand())
                {
                    using (OleDbDataAdapter d = new OleDbDataAdapter())
                    {
                        DataTable dt = new DataTable();
                        cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                        con.Open();
                        d.SelectCommand = cmd;
                        d.Fill(dt);
                        con.Close();
                        Grid.DataSource = dt;
                    }
                }
            }
        }
    }
}

 

Posted 6 months ago

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