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

  • 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.

    Wednesday, February 17, 2016 6:27 PM

Answers

  • User-718146471 posted

    Piotrek, what I did in the past was I would feed the entire spreadsheet into a datatable, then I would compare those entries to what was in my database. Records that match would be dropped from the data table. Once I was sure there were no duplicate records, I would use BulkSQLCopy to pull the entire record set in as one batch. You can look at my previous postings here and you will be able to piece together what I did.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, February 17, 2016 6:39 PM