public bool ExtractCSV(string filelocation, string ntLogin) { bool retValue = false; try { string filename = Path.GetFileNameWithoutExtension(filelocation); using (StreamReader sr = new StreamReader(filelocation)) { string currentLine; #region Setup Site, Campaign and LOB Id string[] siteCampaignLobIds = filename.Split('.'); int siteID = Convert.ToInt32(siteCampaignLobIds[0]); int campaignID = Convert.ToInt32(siteCampaignLobIds[1]); int lobID = Convert.ToInt32(siteCampaignLobIds[2]); #endregion #region Method Variable(s) string segmentName = string.Empty; int col = 0; int row = 0; int ctr = 0; // currentLine will be null when the StreamReader reaches the end of file Regex regx = new Regex(",(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))"); #endregion #region Deactivate existing Raw data in DB string procName = "wfmpcp_ActivateDeactiviateCSV_sp"; string[] parameters = { "Active", "SiteID", "CampaignID", "LoBID" }; object[] paramValues = { false, siteID, campaignID, lobID }; _context.Execute(procName, parameters, paramValues); #endregion #region Read CSV while ((currentLine = sr.ReadLine()) != null) { string[] splitted = regx.Split(currentLine); #region Iterate CSV Values foreach (var data in splitted) { col++; ctr++; string value = data.Replace("%", "").Replace("#VALUE!", "").Replace("#DIV/0!", "") .Replace("-", "").Trim(); #region Setup Date if (row == 1 && col > 2) { //Header string[] splittedDate = data.Trim().Split('/'); string mm = splittedDate[0].ToString().Trim().Length == 1 ? ("0" + splittedDate[0].ToString()) : splittedDate[0].ToString().Trim(); string dd = splittedDate[1].ToString().Trim().Length == 1 ? ("0" + splittedDate[1].ToString()) : splittedDate[1].ToString().Trim(); string yyyy = splittedDate[2].ToString(); value = string.Format("{0}-{1}-{2}", yyyy, mm, dd); } #endregion #region Just to add value to segment column if (col == 1) { if (!string.IsNullOrEmpty(value)) { segmentName = value; } value = segmentName; row++; } #endregion if (string.IsNullOrEmpty(value)) { value = "0"; } #region Insert New Record procName = "wfmpcp_CreateCSVRawData_sp"; parameters = new string[] { "Data", "CreatedBy", "RowNumber", "ColumnNumber", "SiteID", "CampaignID", "LoBID", "Filename" }; paramValues = new object[] { value.Replace(",", "").Replace("\"", "").Replace("`", "").Trim(), ntLogin, row, col, siteID, campaignID, lobID, string.Format("{0}.csv", filename) }; _context.Execute(procName, parameters, paramValues); #endregion if (col == 46) { col = 0; //reset col value } } #endregion } #endregion #region Setup Values to save //delete first to staging procName = "wfmpcp_DeleteStagingAHWeeklyDatapoint_sp"; parameters = new string[] { "SiteID", "CampaignID", "LobID" }; paramValues = new object[] { siteID, campaignID, lobID }; _context.Execute(procName, parameters, paramValues); //get data from csv #endregion Create Dataset #region Insert data to Staging table procName = "wfmpcp_CreateStagingWeeklyAHDatapoint_sp"; parameters = new string[] { "SiteID", "CampaignID", "LoBID", "Active" }; paramValues = new object[] { siteID, campaignID, lobID, true }; _context.Execute(procName, parameters, paramValues); #endregion #region Save data to WeeklyAHDatapoint and It's corresponding tables procName = "wfmpcp_SaveAHCStagingToActual_sp"; parameters = new string[] { "SiteID", "CampaignID", "LoBID" }; paramValues = new object[] { siteID, campaignID, lobID }; _context.Execute(procName, parameters, paramValues); #endregion #region Log to Audit Trail string entry = string.Empty; entry = string.Format("UploadAHCServce.ExtractCSV({0},{1});SiteID:{2};Campaign:{3};LoBID:{4};SUCCESS;", filelocation, ntLogin, siteID, campaignID, lobID); AuditTrail model = new AuditTrail() { AuditEntry = entry, CreatedBy = "System Generated", DateCreated = DateTime.Now, DateModified = DateTime.Now }; _context.Add <AuditTrail>(model); _context.SaveChanges(); #endregion retValue = true; sr.Dispose(); } } catch (Exception ex) { retValue = false; string entry = string.Empty; entry = string.Format("UploadAHCServce.ExtractCSV({0},{1});Error:{2};FAIL;", filelocation, ntLogin, ex.Message); AuditTrail model = new AuditTrail() { AuditEntry = entry, CreatedBy = "System Generated", DateCreated = DateTime.Now, DateModified = DateTime.Now }; _context.Add <AuditTrail>(model); _context.SaveChanges(); } return(retValue); }