Home how to Update the value if already exists in database, if not then insert new record?
Reply: 1

how to Update the value if already exists in database, if not then insert new record?

Ali Harahap
1#
Ali Harahap Published in 2017-09-14 08:06:56Z

I've created a validation to check the value from excel file if row value it's already in the database. I want to create action if the value already exists then my record update, if not exist on databese then inserted as new record.

Here The full code.

[ValidateAntiForgeryToken]
public ActionResult ImportDataAgens(HttpPostedFileBase excelfileRekn)
{
    if (excelfileRekn == null || excelfileRekn.ContentLength == 0)
    {
        ViewBag.Error = "Please Select File...";
        return View("ImportDataAgens");
    }
    else
    {
        if (excelfileRekn.FileName.EndsWith("xls") || excelfileRekn.FileName.EndsWith("xlsx"))
        {
            string newFileName = DateTime.Now.ToString("yyyyMMddHHmmssfff");

            string filename = Path.GetFileName(excelfileRekn.FileName);
            string DocFileNames = newFileName + "-" + filename;
            string path = System.IO.Path.Combine(Server.MapPath("~/UploadFile/DataAgen/"), DocFileNames);

            if (System.IO.File.Exists(path)) System.IO.File.Delete(path);
                excelfileRekn.SaveAs(path);
                //read data from file excel 

                Excel.Application application = new Excel.Application();
                Excel.Workbook workbook = application.Workbooks.Open(path);
                Excel.Worksheet worksheet = workbook.ActiveSheet;
                Excel.Range range = worksheet.UsedRange;
                List<DMInformasiDataAgen> listTempRekn = new List<DMInformasiDataAgen>();

                for (int row = 3; row <= range.Rows.Count; row++)
                {

                    DMInformasiDataAgen rk = new DMInformasiDataAgen();

                    /* var NewUserIDAgent = ((Excel.Range)range.Cells[row, 1]).Text;

                    var NewNamaAgen = ((Excel.Range)range.Cells[row, 2]).Text;
                    //rk.NamaAgen = NewNamaAgen;*/
                    rk.SandiBank = ((Excel.Range)range.Cells[row, 1]).Text;
                    rk.UserIDAgen = ((Excel.Range)range.Cells[row, 2]).Text;
                    rk.NamaAgen = ((Excel.Range)range.Cells[row, 3]).Text;
                    var NewNomorIdentifikasiAgen = ((Excel.Range)range.Cells[row, 4]).Text;
                    rk.NomorIdentifikasiAgen = NewNomorIdentifikasiAgen;


                    if (db.DMInformasiDataAgens.Any(ac => ac.NomorIdentifikasiAgen.Equals((rk.NomorIdentifikasiAgen))))
                    {
                        int RowNo = row;
                        var hitung = listTempRekn.Count + 4;
                        ModelState.AddModelError("", "Nomor Identifikasi Sudah ada sebelumnya ! Pada baris ke " + hitung + " Pada EXCEL File !");
                        return View("ImportDataAgens");
                    }

                    rk.NomordanTanggalPerjanjian = ((Excel.Range)range.Cells[row, 5]).Text;


                    rk.TglPelaksanaan = ((Excel.Range)range.Cells[row, 6]).Value;

                    rk.JenisAgen = ((Excel.Range)range.Cells[row, 7]).Text;
                    rk.KlasifikasiAgen = ((Excel.Range)range.Cells[row, 8]).Text;
                    rk.JenisUsahaAgen = ((Excel.Range)range.Cells[row, 9]).Text;
                    rk.KodePos = ((Excel.Range)range.Cells[row, 10]).Text;
                    rk.LatitudeLongitudinal = ((Excel.Range)range.Cells[row, 11]).Text;
                    rk.KabupatenKota = ((Excel.Range)range.Cells[row, 12]).Text;
                    rk.ElectronicDevice = ((Excel.Range)range.Cells[row, 13]).Text;
                    rk.JaringanKantorBank = ((Excel.Range)range.Cells[row, 14]).Text;
                    rk.KategoriJaringanKantorBank = ((Excel.Range)range.Cells[row, 15]).Text;
                    rk.StatusPerkembanganAgen = ((Excel.Range)range.Cells[row, 16]).Text;
                    rk.Keterangan = ((Excel.Range)range.Cells[row, 17]).Text;

                    if (((Excel.Range)range.Cells[row, 18]).Value == null)
                    {

                        int RowNo = row;
                        var hitung = listTempRekn.Count + 4;
                        ModelState.AddModelError("", "Tolong periksa PERIODE Pada baris ke " + hitung + " Pada EXCEL File !");
                        return View("ImportDataAgens");
                    }

                    rk.Periode = ((Excel.Range)range.Cells[row, 18]).Value;
                    string valueA = System.Web.HttpContext.Current.User.Identity.Name;
                    rk.CreateBy = valueA;
                    rk.CreateDate = DateTime.Today;

                    if (db.DMInformasiDataAgens.Any(ac => ac.NomorIdentifikasiAgen.Equals((rk.NomorIdentifikasiAgen))))
                    {
                        int RowNo = row;
                        var hitung = listTempRekn.Count + 4;
                        ModelState.AddModelError("", "The Number Of Agent Already Exist on  " + hitung + " the Template EXCEL File !");
                        return View("ImportDataAgens");
                    }

                    if (ModelState.IsValid)
                    {
                        listTempRekn.Add(rk);
                        db.DMInformasiDataAgens.Add(rk);
                        db.SaveChanges();
                        var hitung = listTempRekn.Count;
                        //range.Rows.Count - 2;
                        TempData["AlertMessage"] = "Import Data Selesai " + hitung + " Records Sudah Tersimpan...";
                    }
                }

                return RedirectToAction("Index", "DataAgens");
        }
        else
        {
            ViewBag.Error = "File Type is incorrect <br>";
            return View("ImportDataAgens");
        }

    }

}

Here the Code whe I check the already exist Value :

**if (db.DMInformasiDataAgens.Any(ac => ac.NomorIdentifikasiAgen.Equals((rk.NomorIdentifikasiAgen))))
{
    int RowNo = row;
    var hitung = listTempRekn.Count + 4;
    ModelState.AddModelError("", " The Number Of Agent Already Exist on  " + hitung + " the Template EXCEL File !");
    return View("ImportDataAgens");
}**

How to create action if the value already exists (Number Of Agent) then my record update, if not then insert new record ?

Hossein Daneshfar
2#
Hossein Daneshfar Reply to 2017-09-14 09:03:05Z
var chkItem = x.firstOrDefault(your condition);
if(chkItem != null)
{
    //update your record.
}
else{
    //insert into database
}
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.345256 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO