public ResponseSOAPModel ImportScanData(ImportDataModel postData) { var res = new ResponseSOAPModel() { Status = "201", Message = "", }; // check role, login info. var user = _context.TblUsers.FirstOrDefault(o => o.UserName == postData.User); #region Validate if (user == null) { res.Message = "Account with user name " + postData.User + " is not existed"; res.Status = "500"; return(res); } if (user.Token != postData.Token) { res.Message = "Token is not correct"; res.Status = "500"; return(res); } if (user.RoleId != Constant.Users.Admin && user.RoleId != Constant.Users.User && user.RoleId != Constant.Users.Superadmin) { res.Message = "You dont have permission to do."; res.Status = "500"; return(res); } #endregion try { Guid g; g = Guid.NewGuid(); var x = new TblScanData { ScanId = g.ToString(), Uid = user.Id, CreatedOn = DateTime.UtcNow, StationName = postData.StationName, Payload = postData.ScanData.Payload, DataType = postData.ScanData.DataType, FileName = postData.ScanData.FileName, Status = postData.ScanData.Status, DeletedOn = null, }; _context.TblScanData.Add(x); _context.SaveChanges(); x.U = null; res.Message = "Scan data is created"; //res.Data = x; } catch (Exception ex) { res.Status = "500"; res.Message = ex.Message; } return(res); }
public ActionResult Index(ImportDataModel importModel) { var validImageTypes = new [] { "application/vnd.ms-excel" }; bool errorExcel; if (importModel.UploadFile != null && importModel.UploadFile.ContentLength > 0) { errorExcel = !validImageTypes.Contains(importModel.UploadFile.ContentType); } else { errorExcel = true; } if (errorExcel) { ModelState.AddModelError("UploadFile", "Por favor seleccione un archivo de Excel"); } var academicYear = _academicYearRepository.GetByFields(importModel.Year, importModel.GradeImport, importModel.Section); if (academicYear == null) { ModelState.AddModelError("Year", "No existe ese año academico"); } ViewBag.GradeId = new SelectList(_gradeRepository.Query(x => x), "Id", "Name", 0); if (!ModelState.IsValid) { return(View(importModel)); } var myDataSet = _importDataRepository.GetDataSetFromExcelFile(importModel.UploadFile); try { _importDataRepository.Import(myDataSet, academicYear); } catch (Exception ex) { _viewMessageLogic.SetNewMessage("Error!", ex.Message, ViewMessageType.ErrorMessage); return(RedirectToAction("Index")); } const string title = "Importación de Datos Correcta"; var content = string.Format("Se importaron datos para el año: {0}, grado: {1} y sección: {2}" , importModel.Year // 0 , importModel.GradeImport // 1 , importModel.Section // 2 ); _viewMessageLogic.SetNewMessage(title, content, ViewMessageType.InformationMessage); return(RedirectToAction("Index")); }
public ActionResult Index() { _viewMessageLogic.SetViewMessageIfExist(); var importModel = new ImportDataModel(); ViewBag.GradeId = new SelectList(_gradeRepository.Query(x => x), "Id", "Name", 0); ViewBag.Year = new SelectList(_academicYearRepository.Filter(x => x.IsActive).Select(x => x.Year).Distinct().ToList()); ViewBag.Section = new SelectList(new List <string> { "A", "B", "C" }, "A"); return(View(importModel)); }
public ActionResult ImportData() { ImportDataModel model = new ImportDataModel { }; model.UploadFile = new FileUpload1 { SelectButtonText = this.__ResStr("btnImport", "Import Data..."), SaveURL = GetActionUrl("ImportPackageData", new { __ModuleGuid = Module.ModuleGuid }), RemoveURL = GetActionUrl("RemovePackageData", new { __ModuleGuid = Module.ModuleGuid }), }; return(View(model)); }
public IActionResult ImportScanData([FromBody] ImportDataModel tblscandata) { return(Ok(_IScanDataService.ImportScanData(tblscandata))); }
public string ReadData(string filePath) { if (System.IO.File.Exists(filePath)) //dont use Full qualified namespaces// { string extension = Path.GetExtension(filePath); switch (extension) { case ".xls": //Excel 97-03. conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; break; case ".xlsx": //Excel 07 and above. conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString; break; } DataTable dt = new DataTable(); conString = string.Format(conString, filePath); using (OleDbConnection connExcel = new OleDbConnection(conString)) { using (OleDbCommand cmdExcel = new OleDbCommand()) { using (OleDbDataAdapter odaExcel = new OleDbDataAdapter()) { cmdExcel.Connection = connExcel; //Get the name of First Sheet. connExcel.Open(); DataTable dtExcelSchema; dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); //connExcel.Close(); conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString; using (SqlConnection con = new SqlConnection(conString)) { String TruncateQuery = "DELETE FROM PtacDealer_TB"; SqlCommand command = new SqlCommand(TruncateQuery, con); con.Open(); command.ExecuteNonQuery(); con.Close(); //Read Data from First Sheet. //connExcel.Open(); cmdExcel.CommandText = "SELECT * From [" + sheetName + "]"; odaExcel.SelectCommand = cmdExcel; odaExcel.Fill(dt); foreach (DataRow row in dt.Rows) { var importData = new ImportDataModel(); importData.Description = row["SUBFDESC"].ToString().Trim(); var formattedData = ConvertToPascalCase(importData.Description); importData.Address = row["SUBFADR1"].ToString().Trim(); importData.City = row["SUBFCITY"].ToString().Trim(); importData.State = row["SUBFSTATE"].ToString().Trim(); importData.ZipCode = row["SUBFZIP"].ToString().Trim(); importData.PhoneNumber = row["PhoneNum"].ToString().Trim(); var data = GetPhoneNumber(importData.PhoneNumber); importData.PhoneNumber2 = row["PhoneNum2"].ToString().Trim(); var data1 = GetPhoneNumber(importData.PhoneNumber2); String query = "INSERT INTO dbo.PtacDealer_TB (Description,Address,City,State,ZipCode,PhoneNumber,PhoneNumber2) VALUES ('" + formattedData.Replace("'", "''") + "','" + importData.Address.Replace("'", "''") + "','" + importData.City.Replace("'", "''") + "','" + importData.State + "','" + importData.ZipCode + "','" + data + "','" + data1 + "')"; SqlCommand command1 = new SqlCommand(query, con); con.Open(); command1.ExecuteNonQuery(); con.Close(); //command.ExecuteNonQuery(); connExcel.Close(); } } } } } } return(filePath); }
public static ImportDataModel GetImportModels(string path) { try { var model = new ImportDataModel(); var stream = File.OpenRead(path); IExcelDataReader excelReader = null; //Reading from a binary Excel file ('97-2003 format; *.xls) if (Path.GetExtension(path).Equals(".xls", StringComparison.InvariantCultureIgnoreCase)) { excelReader = ExcelReaderFactory.CreateBinaryReader(stream); } //Reading from a OpenXml Excel file (2007 format; *.xlsx) if (Path.GetExtension(path).Equals(".xlsx", StringComparison.InvariantCultureIgnoreCase)) { excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); } if (excelReader == null) { return(model); } var pageIndex = 0; do { pageIndex++; var items = new List <ImportModel>(); var category = (CategoryCode)pageIndex; var rowIndex = 0; while (excelReader.Read()) { rowIndex++; if (rowIndex < StartRowIndex) { continue; } if (excelReader.IsDBNull(3)) { continue; } var item = new ImportModel(); item.V1 = excelReader.IsDBNull(0) ? (int?)null : excelReader.GetInt32(0); item.V2 = excelReader.GetString(1); item.V3 = excelReader.GetString(2); item.V4 = excelReader.GetString(3); item.V5 = excelReader.GetString(4); item.V6 = excelReader.GetString(5); item.V7 = excelReader.GetString(6); item.V8 = excelReader.GetString(7); item.V9 = excelReader.GetString(8); item.V10 = excelReader.GetString(9); item.V11 = excelReader.IsDBNull(10) ? (DateTime?)null : excelReader.GetDateTime(10); item.V12 = excelReader.IsDBNull(11) ? (double?)null : excelReader.GetDouble(11); item.V13 = excelReader.IsDBNull(12) ? (double?)null : excelReader.GetDouble(12); item.V14 = excelReader.IsDBNull(13) ? (double?)null : excelReader.GetDouble(13); item.V15 = excelReader.IsDBNull(14) ? (double?)null : excelReader.GetDouble(14); item.V16 = excelReader.IsDBNull(15) ? (double?)null : excelReader.GetDouble(15); item.V17 = excelReader.IsDBNull(16) ? (double?)null : excelReader.GetDouble(16); item.V18 = excelReader.IsDBNull(17) ? (double?)null : excelReader.GetDouble(17); item.V19 = excelReader.IsDBNull(18) ? (double?)null : excelReader.GetDouble(18); item.V20 = excelReader.IsDBNull(19) ? (double?)null : excelReader.GetDouble(19); item.V21 = excelReader.IsDBNull(20) ? (double?)null : excelReader.GetDouble(20); item.V22 = excelReader.IsDBNull(21) ? (double?)null : excelReader.GetDouble(21); item.V23 = excelReader.IsDBNull(22) ? (double?)null : excelReader.GetDouble(22); item.V24 = excelReader.IsDBNull(23) ? (double?)null : excelReader.GetDouble(23); item.V25 = excelReader.IsDBNull(24) ? (DateTime?)null : excelReader.GetDateTime(24); item.V26 = excelReader.IsDBNull(25) ? (double?)null : excelReader.GetDouble(25); item.V27 = excelReader.IsDBNull(26) ? (DateTime?)null : excelReader.GetDateTime(26); item.V28 = excelReader.IsDBNull(27) ? (DateTime?)null : excelReader.GetDateTime(27); item.V29 = excelReader.IsDBNull(28) ? (DateTime?)null : excelReader.GetDateTime(28); item.V30 = excelReader.IsDBNull(29) ? (DateTime?)null : excelReader.GetDateTime(29); item.V31 = excelReader.IsDBNull(30) ? (DateTime?)null : excelReader.GetDateTime(30); item.V32 = excelReader.IsDBNull(31) ? (DateTime?)null : excelReader.GetDateTime(31); item.V33 = excelReader.IsDBNull(32) ? (DateTime?)null : excelReader.GetDateTime(32); item.V34 = excelReader.GetString(33); item.V35 = excelReader.GetString(34); item.V36 = excelReader.GetString(35); item.V37 = excelReader.IsDBNull(36) ? (DateTime?)null : excelReader.GetDateTime(36); item.V38 = excelReader.GetString(37); item.V39 = excelReader.IsDBNull(38) ? (DateTime?)null : excelReader.GetDateTime(38); item.V40 = excelReader.IsDBNull(39) ? (double?)null : excelReader.GetDouble(39); items.Add(item); } model.Items.Add(category, items); model.PageRows.Add(category, rowIndex); } while (excelReader.NextResult()); excelReader.Close(); return(model); } catch (Exception ex) { throw ex; } }