public HttpResponseMessage Post([FromBody] string projectName) { int?projectId = null; // Using LINQ to SQL to insert excel data into database using (var fpContext = new OCPSQLEntities()) { // string query = "Select [CATEGORY], [SUBCATEGORY], [PARAMETER], [RATIO], [LOWER_CI], [UPPER_CI], [COMMENT] from [Sheet1$]"; if (fpContext.SYSTEM_USER.Any(u => u.USER_NAME == User.Identity.Name)) { var user = fpContext.SYSTEM_USER.SingleOrDefault(u => u.USER_NAME == User.Identity.Name); // Insert new project data into database var newProject = new FPTOOLS_PROJECT() { USER_ID = user.USER_ID, FILE_NAME = "", PROJECT_NAME = projectName ?? "", }; // Initialize new Plot data into database var newPlot = new IPORTAL_FP { TITLE = "", SCALE_ID = 1, FOOTNOTE = "", XLABEL = "", FP_STYLE_ID = 1, RANGE_BOTTOM = 10, RANGE_TOP = 30, RANGE_STEP = 3, }; IPORTAL_FP_ROW newPlotData = new IPORTAL_FP_ROW(); { newPlotData.CATEGORY = ""; newPlotData.SUBCATEGORY = ""; newPlotData.PARAMETER = ""; newPlotData.RATIO = 0; newPlotData.LOWER_CI = 1; newPlotData.UPPER_CI = 10; newPlotData.COMMENT = "New Plot comment"; newPlotData.FP_ID = newPlot.FP_ID; newPlot.IPORTAL_FP_ROW.Add(newPlotData); } newProject.IPORTAL_FP.Add(newPlot); fpContext.FPTOOLS_PROJECT.Add(newProject); fpContext.SaveChanges(); // Retrieve the id of the submitted project projectId = newProject.PROJECT_ID; } else // TODO: error check for no login user { throw new HttpResponseException(HttpStatusCode.InternalServerError); }; } return(Request.CreateResponse(HttpStatusCode.OK, projectId)); }
public async Task <HttpResponseMessage> Post() { int?projectId = null; // check if the request contains multipart / form-data. if (!Request.Content.IsMimeMultipartContent()) { throw new HttpResponseException(HttpStatusCode.UnsupportedMediaType); } var provider = new InMemoryMultipartFormDataStreamProvider(); // Read the form data and return an async task. await Request.Content.ReadAsMultipartAsync(provider); // Throw exception if the project name is null var projectName = provider.FormData["projectName"]; if (string.IsNullOrWhiteSpace(projectName) || projectName == "null") { throw new Exception(); } // Read file name and stream string filename = provider.Contents.FirstOrDefault().Headers.ContentDisposition.FileName.Trim('\"'); var fileStream = new MemoryStream(); provider.Contents.FirstOrDefault().ReadAsStreamAsync().Result.CopyTo(fileStream); // Using LINQ to SQL to insert excel data into database using (var fpContext = new OCPSQLEntities()) { if (fpContext.SYSTEM_USER.Any(u => u.USER_NAME == User.Identity.Name)) { var user = fpContext.SYSTEM_USER.SingleOrDefault(u => u.USER_NAME == User.Identity.Name); // Insert new project data into database var newProject = new FPTOOLS_PROJECT() { USER_ID = user.USER_ID, FILE_NAME = filename, PROJECT_NAME = projectName ?? "", }; // Initialize new Plot data into database var newPlot = new IPORTAL_FP { TITLE = "", SCALE_ID = 1, FOOTNOTE = "", XLABEL = "", FP_STYLE_ID = 1, RANGE_BOTTOM = 0, RANGE_TOP = 5, RANGE_STEP = 0.1, }; IExcelDataReader excelReader; // Reading from a binary Excel file ('97-2003 format; *.xls) if (filename.Split('.').Last().Equals("xls")) { excelReader = ExcelReaderFactory.CreateBinaryReader(fileStream); } else // Reading from a OpenXml Excel file (2007 format; *.xlsx) { excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileStream); } excelReader.IsFirstRowAsColumnNames = true; var excelDataset = excelReader.AsDataSet(); string category = null, subcategory = null; foreach (DataRow row in excelDataset.Tables[0].Rows) { // regex to match an alphanumeric character var re = new Regex(@"\w"); // Update category if present var rowCategory = row.GetColumnValue(excelReaderExtension.ColumnTypes.CATEGORY); if (rowCategory != null && re.IsMatch(rowCategory)) { category = rowCategory; } // Update subcategory if present var rowSubcategory = row.GetColumnValue(excelReaderExtension.ColumnTypes.SUBCATEGORY); if (rowSubcategory != null && re.IsMatch(rowSubcategory)) { subcategory = rowSubcategory; } // Determine if a parameter is present in the row var rowParameter = row.GetColumnValue(excelReaderExtension.ColumnTypes.PARAMETER); if (rowParameter != null && re.IsMatch(rowParameter)) { // Determine if row values can be parsed as double double ratio, lowerCI, higherCI; if (double.TryParse(row.GetColumnValue(excelReaderExtension.ColumnTypes.RATIO), out ratio) && double.TryParse(row.GetColumnValue(excelReaderExtension.ColumnTypes.LOWERCI), out lowerCI) && double.TryParse(row.GetColumnValue(excelReaderExtension.ColumnTypes.UPPERCI), out higherCI)) { // Create a new row entity IPORTAL_FP_ROW FP_RowTable = new IPORTAL_FP_ROW(); FP_RowTable.CATEGORY = category; FP_RowTable.SUBCATEGORY = subcategory; FP_RowTable.PARAMETER = rowParameter; FP_RowTable.RATIO = ratio; FP_RowTable.LOWER_CI = lowerCI; FP_RowTable.UPPER_CI = higherCI; // Save comment only if it contains alphanumeric info var comment = row.GetColumnValue(excelReaderExtension.ColumnTypes.COMMENT); FP_RowTable.COMMENT = (comment != null && re.IsMatch(comment)) ? comment : ""; FP_RowTable.FP_ID = newPlot.FP_ID; newPlot.IPORTAL_FP_ROW.Add(FP_RowTable); } } } newProject.IPORTAL_FP.Add(newPlot); fpContext.FPTOOLS_PROJECT.Add(newProject); fpContext.SaveChanges(); // Retrieve the id of the submitted project projectId = newProject.PROJECT_ID; } else // TODO: error check for no login user { throw new HttpResponseException(HttpStatusCode.InternalServerError); }; } // return projectId return(Request.CreateResponse(HttpStatusCode.OK, projectId)); }