C# How check value from Excel before update or insert to DB SQL

Question

User-17528063 posted

Hi all sorry i'm newbie  in c#.

I have excel spreadsheet with the data in columns:

CompanyName|Product|Volume|Month

for example: Microsoft|Windows|123|02.2016

Ok and i want before importing to database check are there the same entries as excel. For example I import from excel:

Microsoft|Windows|123|02.2016

Linux|Debian|350|02.2016

And in database is only one record: Microsoft|Windows|123|02.2016,

And I try check before insert to DB there are the same entries in db and worksheet if YES Microsoft|Windows|123|02.2016 = Microsoft|Windows|123|02.2016 skip insert to db if NO add entries to db.

But if Value for CompanyName|Product|Month are exist and they are the same in db but value for Volume is other, script only update for value Volume from worksheet.

This is my code but I do not know how to do this

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
 
namespace MS2
{
    public partial class _Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                PopulateData();
                lblMessage.Text = "Current Database Data!";
            }
        }
 
        private void PopulateData()
        {
            using (BazaMSEntities dc = new BazaMSEntities())
            {
                gvData.DataSource = dc.TabelaImport.ToList();
                gvData.DataBind();
            }
        }
 
        protected void btnImport_Click(object sender, EventArgs e)
        {
            if (FileUpload1.PostedFile.ContentType == "application/vnd.ms-excel" ||
                FileUpload1.PostedFile.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                try
                {
                    string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName));
                    FileUpload1.PostedFile.SaveAs(fileName);
 
                    string conString = "";
                    string ext = Path.GetExtension(FileUpload1.PostedFile.FileName);
                    if (ext.ToLower() == ".xls")
                    {
                        conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; ;
                    }
                    else if (ext.ToLower() == ".xlsx")
                    {
                        conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
                    }
 
                    string query = "Select [CompanyName],[Product],[Volume],[Month] from [Arkusz1$]";
                    OleDbConnection con = new OleDbConnection(conString);
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    OleDbCommand cmd = new OleDbCommand(query, con);
                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    da.Dispose();
                    con.Close();
                    con.Dispose();
 
                    // Import to Database
                    using (BazaMSEntities dc = new BazaMSEntities())
                    {
                        foreach (DataRow dr in ds.Tables[0].Rows)
                        {
                            string empID = dr["CompanyName"].ToString() + dr["Product"].ToString()+ dr["Month"].ToString();
                            var v = dc.TabelaImport.Where(a => a.Id.Equals(empID)).FirstOrDefault();
                            if (v != null)
                            {
                                // Update here
                     
                                v.Volume = dr["Volume"].ToString();
                              
                            }
                            else
                            {
                                // Insert
                                dc.TabelaImport.Add(new TabelaImport
                                {
                                   
                                    CompanyName= dr["CompanyName"].ToString(),
                                    Product = dr["Product"].ToString(),
                                    Volume = dr["Volume"].ToString(),
                                    Month = dr["Month"].ToString()
 
                                });
                            }
                        }
 
                        dc.SaveChanges();
                    }
 
                    PopulateData();
                    lblMessage.Text = "Successfully data import done!";
                }
                catch (Exception)
                {
                    throw;
                }
            }
        }

Thx for help me.