private void XMLGenerate_SAPInsert(ManufacturerViewModels model) { try { ApplicationUser currentUser = ApplicationUserManager.GetApplicationUser(User.Identity.Name, HttpContext.GetOwinContext()); //unique id generation Guid GuidRandomNo = Guid.NewGuid(); string UniqueID = GuidRandomNo.ToString(); //fill view model Viewmodel_AddManufacturer xmlAddManufacture = new Viewmodel_AddManufacturer(); xmlAddManufacture.UniqueID = UniqueID.ToString(); xmlAddManufacture.manufacturer_Name = model.Manufacturer.Manufacturer_Name; xmlAddManufacture.CreatedUser = currentUser.Id.ToString(); // "1"; //GetUserId() xmlAddManufacture.CreatedBranch = currentUser.Created_Branch_Id.ToString(); // "1";//GetBranchId(); xmlAddManufacture.CreatedDateTime = DateTime.Now.ToString(); //generate xml manufacturerRepository.GenerateXML(xmlAddManufacture, UniqueID); } catch (Exception ex) { ExceptionHandler.LogException(ex); ViewBag.AppErrorMessage = ex.Message; } }
public ActionResult Upload(HttpPostedFileBase file) { if (file != null && file.ContentLength > 0) { try { ApplicationUser currentUser = ApplicationUserManager.GetApplicationUser(User.Identity.Name, HttpContext.GetOwinContext()); string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName); string fileName = System.IO.Path.GetFileName(Request.Files["file"].FileName.ToString()); if (fileExtension == ".xls" || fileExtension == ".xlsx") { string fileLocation = string.Format("{0}/{1}", Server.MapPath("~/App_Data/ExcelFiles"), fileName); if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } Request.Files["file"].SaveAs(fileLocation); string excelConnectionString = string.Empty; excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; //connection String for xls file format. if (fileExtension == ".xls") { excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } //connection String for xlsx file format. else if (fileExtension == ".xlsx") { excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\""; } //Create Connection to Excel work book and add oledb namespace OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); DataTable dt = new DataTable(); string exquery; dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (dt == null) { return(null); } String[] excelSheets = new String[dt.Rows.Count]; int t = 0; //excel data saves in temp file here. foreach (DataRow row in dt.Rows) { excelSheets[t] = row["TABLE_NAME"].ToString(); t++; } DataSet ds = new DataSet(); OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString); exquery = string.Format("Select * from [{0}]", excelSheets[0]); using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(exquery, excelConnection1)) { dataAdapter.Fill(ds);//fill dataset } if (ds != null) { #region Check Manufacturer Name foreach (DataRow dr in ds.Tables[0].Rows) { string mExcelManu_Name = Convert.ToString(dr["Manufacturer Name"]); if (mExcelManu_Name != null && mExcelManu_Name != "") { var data = manufacturerRepository.CheckDuplicateName(mExcelManu_Name); if (data != null) { // return Json(new { success = true, Message = "Manufacturer Name: " + mExcelManu_Name + " - already exists in the master." }, JsonRequestBehavior.AllowGet); ViewBag.AppErrorMessage = "Manufacturer Name: " + mExcelManu_Name + " - already exists in the master."; return(View("Error")); } } else { //return Json(new { success = false, Error = "Manufacture name cannot be null it the excel sheet" }, JsonRequestBehavior.AllowGet); ViewBag.AppErrorMessage = "Manufacture name cannot be null it the excel sheet"; return(View("Error")); } } #endregion #region Check Level foreach (DataRow dr in ds.Tables[0].Rows) { if (dr["Level"].ToString() != null && dr["Level"].ToString() != "") { int mExcelManu_Level = Convert.ToInt32(dr["Level"]); if (mExcelManu_Level >= 0 && mExcelManu_Level <= 100) { } else { return(Json(new { success = true, Message = "Allowed range for Level is 0 to 100" }, JsonRequestBehavior.AllowGet)); } } else { return(Json(new { success = false, Error = "Level cannot be null it the excel sheet" }, JsonRequestBehavior.AllowGet)); } } #endregion # region Already exists in sheet int i = 1; int ii = 1; string itemc = string.Empty; foreach (DataRow dr in ds.Tables[0].Rows) { itemc = Convert.ToString(dr["Manufacturer Name"]); if ((itemc == null) || (itemc == "")) { } else { foreach (DataRow drd in ds.Tables[0].Rows) { if (ii == i) { } else { if (itemc == Convert.ToString(drd["Manufacturer_Name"])) { return(Json(new { success = true, Message = "Manufacturer Name: " + itemc + " - already exists in the excel." }, JsonRequestBehavior.AllowGet)); } } ii = ii + 1; } } i = i + 1; ii = 1; } #endregion #region BulkInsert if (ds.Tables[0].Rows.Count > 0) { List <Manufacture> mlist = new List <Manufacture>(); for (int j = 0; j < ds.Tables[0].Rows.Count; j++) { Manufacture mItem = new Manufacture(); if (ds.Tables[0].Rows[j]["Manufacturer Name"] != null) { mItem.Manufacturer_Name = ds.Tables[0].Rows[j]["Manufacturer Name"].ToString(); } if (ds.Tables[0].Rows[j]["Level"] != null) { mItem.Level = Convert.ToInt32(ds.Tables[0].Rows[j]["Level"]); } mItem.IsActive = "Y"; mItem.Created_User_Id = currentUser.Id; // 1; //GetUserId(); mItem.Created_Branc_Id = currentUser.Created_Branch_Id; // 2; //GetBranchId(); mItem.Created_Dte = DateTime.Now; mlist.Add(mItem); //unique id generation Guid GuidRandomNo = Guid.NewGuid(); string UniqueID = GuidRandomNo.ToString(); //fill viewmodel Viewmodel_AddManufacturer xmlAddManufacture = new Viewmodel_AddManufacturer(); xmlAddManufacture.UniqueID = UniqueID.ToString(); xmlAddManufacture.manufacturer_Name = ds.Tables[0].Rows[j]["Manufacturer Name"].ToString(); xmlAddManufacture.CreatedUser = currentUser.Id.ToString(); // "1"; xmlAddManufacture.CreatedBranch = currentUser.Created_Branch_Id.ToString(); // "1"; xmlAddManufacture.CreatedDateTime = DateTime.Now.ToString(); //generate xml manufacturerRepository.GenerateXML(xmlAddManufacture, UniqueID); } if (manufacturerRepository.InsertFileUploadDetails(mlist)) { //return Json(new { success = true, Message = mlist.Count + " Records Uploaded Successfully" }, JsonRequestBehavior.AllowGet); return(RedirectToAction("Index", "Manufacturer")); } } else { return(Json(new { success = false, Error = "Excel file is empty" }, JsonRequestBehavior.AllowGet)); } #endregion } else { return(Json(new { success = false, Error = "Excel file is empty" }, JsonRequestBehavior.AllowGet)); } } }