Click here to Skip to main content
15,904,416 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have an excel file and a table in SQL database structure the same.

Now I want to import all the data in Excel file to table in SQL database.

If there are duplicate records then update, else add new.

I hope you help me, now I'm using C #
Posted

rosy84 wrote:
Now I want to import all the data in Excel file to table in SQL database.

Have a look at these artile for it:
How to import data from Excel to SQL Server[^]
Inserting, Updating or Deleting Data in SQL Server from an Excel Spreadsheet[^]
Export data from Excel to new SQL Server table[^]


rosy84 wrote:
If there are duplicate records then update, else add new

For this, you need to write your own logic before inserting the data. Find it, if found update it.
 
Share this answer
 
Thanks you,
But I want design an Program C# use Interface.

Today I writen Program but I think it have Problem,

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;


namespace Testinsertdata
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string excelFilePath;
            if (txtopen.Text.Length > 0)
            {
                try
                {
                    excelFilePath = txtopen.Text;
                    string sExcelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 8.0;HDR=YES;\"";
                    string sSqlConnectionString = "SERVER=.;USER ID=sa;PASSWORD=;DATABASE=EQMT_Database;CONNECTION RESET=FALSE";

                    //  Excel
                    OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
                    OleDbConn.Open();
                    OleDbDataAdapter OA = new OleDbDataAdapter("SELECT * FROM [Tbl_EQMT$]", OleDbConn);
                    //OleDbCommand OM = new OleDbCommand();
                    DataSet Ods = new DataSet();
                    DataTable Odt = new DataTable();
                    OA.Fill(Odt);
                    DataRow Orow;
                    
                    //  SQL
                    SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
                    SqlConn.Open();
                    SqlDataAdapter Sda = new SqlDataAdapter("Select * from Tbl_EQMT", SqlConn);
                    DataTable Sdt = new DataTable();
                    SqlCommand Scm = new SqlCommand();
                    Scm.Connection = SqlConn;
                    Scm.CommandType = CommandType.Text ;
                    Sda.Fill(Sdt);
                    int Snum = Sdt.Rows.Count;
                    DataRow Srow;
               
               
                    for (int i = 0; i <= Odt.Rows.Count-1; i++)
                    {
                        for (int j = 0; j <= Odt.Columns.Count-1; j++)
                        {
                            Scm.CommandText = "Insert  into Tbl_EQMT (" + Sdt.Columns[j].ColumnName.Trim() + ") values ( '" + Odt.Rows[i].ItemArray.GetValue(j) + "')";
                            Scm.ExecuteNonQuery();
                            
                        }                                                
                    }
                    MessageBox.Show("Success full");
                }
                     
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message.ToString());
                }
            }
            else{MessageBox.Show("You not Select Excel File Excel","Error",MessageBoxButtons.OK,MessageBoxIcon.Error);}
         }
    

        private void btopen_Click(object sender, EventArgs e)
        {
            this.Cursor = System.Windows.Forms.Cursors.WaitCursor;
            OpenFileDialog dialog = new OpenFileDialog();
            dialog.Filter = "Excel files|*.xls|Excel files (*.xls)|*.xls";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    if (dialog.FileName.Length > 0)
                    {
                        txtopen.Text = dialog.FileName;
                    }
                }
                catch
                {
                    MessageBox.Show("File Error", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
            this.Cursor = System.Windows.Forms.Cursors.Default;
        }

    }


}


Pls, help me redisign it.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900