public void ImportDataFile(ClassDataFile file) { try { string newFileLoc = this.UpdateTemplate(file); Application app = new Application(); Package package = null; //Load the SSIS Package which will be executed package = app.LoadPackage(newFileLoc, null); //Pass the varibles into SSIS Package //Execute the SSIS Package and store the Execution Result Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute(); //Check the results for Failure and Success if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure) { string err = ""; foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors) { string error = local_DtsError.Description.ToString(); err = err + error; } } } catch (Exception ex) { throw ex; } }
public static string VerifyFileIntact(ClassDataFile datFile, ClassAuditEntriesDataTable auditEntries = null) { string message = "Not successful. No Audit entry found for this file"; //• Audit Type depends on if the auditEntries table has been supplied or not if (auditEntries == null) { if (datFile.AmountOfLines == datFile.IntendedAmountOfLines) message = "Self Check Successful"; else message = "Self Check Not Successful. File is not complete:CDR MISMATCH"; } else { foreach (DataRow dr in auditEntries.entriesTable.Rows) { if (dr["FileName"].ToString() == datFile.FileName.Substring(0, 8)) { if (Convert.ToInt16(dr["CDR_Count"]) == datFile.AmountOfLines) { message = "AuditFile Check Successful"; break; } else { message = "AuditFile Check Not Successful. File is not complete:CDR MISMATCH"; break; } } } } return message; }
//Methods public void AddNewRow(ClassDataFile dataFile, ref ClassImportInfoDataTable infoTable) { DataRow newRow = infoTable.infoTable.NewRow(); newRow["File_Name"] = dataFile.FileName; newRow["Self_Check_Result"] = ""; newRow["AuditFile_Check_Result"] = "Not Checked Yet"; infoTable.infoTable.Rows.Add(newRow); infoTable.infoTable.AcceptChanges(); }
public static List<ClassDataFile> CompileFileList(string pickUpLocation, ref ClassImportInfoDataTable infoTable, bool useAuditFile) { List<ClassDataFile> fileList = new List<ClassDataFile>(); String[] filePaths = Directory.GetFiles(pickUpLocation); ClassDataFile[] datFiles = new ClassDataFile[filePaths.Count()]; int count = 0; if (useAuditFile) { string auditFileLocation = Properties.Settings.Default.AuditFileLocation; ClassAuditFile auditFile = new ClassAuditFile(auditFileLocation); DataTables.ClassAuditEntriesDataTable auditEntriesTable = new DataTables.ClassAuditEntriesDataTable(); auditEntriesTable = DataTables.ClassAuditEntriesDataTable.FillEntriesTable(auditFile); if (auditEntriesTable.entriesTable.Rows.Count >= 1) { foreach (String fileLoc in filePaths) { datFiles[count] = new ClassDataFile(filePaths[count]); infoTable.AddNewRow(datFiles[count], ref infoTable); string message = string.Empty; message = ClassAuditing.SelfCheck(datFiles[count]); infoTable.UpdateRow(datFiles[count], "Self_Check_Result", message, ref infoTable); message = ClassAuditing.AuditFileCheck(datFiles[count], auditEntriesTable); infoTable.UpdateRow(datFiles[count], "AuditFile_Check_Result", message, ref infoTable); //• ONLY ADD FILE TO LIST IF AUDIT PASSED if ((datFiles[count].PassedSelfCheck == true) && (datFiles[count].PassedAuditFileCheck == true)) fileList.Add(datFiles[count]); count++; } } } else { foreach (String fileLoc in filePaths) { datFiles[count] = new ClassDataFile(filePaths[count]); infoTable.AddNewRow(datFiles[count], ref infoTable); string message = string.Empty; message = ClassAuditing.SelfCheck(datFiles[count]); infoTable.UpdateRow(datFiles[count], "Self_Check_Result", message, ref infoTable); //• ONLY ADD FILE TO LIST IF AUDIT PASSED if (datFiles[count].PassedSelfCheck == true) fileList.Add(datFiles[count]); count++; } } return fileList; }
public static string SelfCheck(ClassDataFile datFile) { string message; if (datFile.AmountOfLines == datFile.IntendedAmountOfLines) { message = "Self Check Successful"; datFile.PassedSelfCheck = true; } else { message = "Self Check Not Successful. File is not complete:CDR MISMATCH"; datFile.PassedSelfCheck = false; } return message; }
public static void UpdateInCompleteAudits(DataTable table, ClassAuditEntriesDataTable entriesTable, ref ClassImportInfoDataTable infoTable, string connectionString) { string message; foreach (DataRow dataRow in table.Rows) { ClassDataFile datFile = new ClassDataFile(dataRow); infoTable.AddNewRow(datFile, ref infoTable); message = ClassGatherInfo.VerifyFileIntact(datFile, entriesTable); message = AuditFileCheck(datFile, entriesTable); infoTable.UpdateRow(datFile, "AuditFile_Check_Result", message, ref infoTable); //• ONLY UPDATE THE ENTRY IF AUDIT PASSED if (datFile.PassedAuditFileCheck) ClassSQLAccess.UpdateIncompleteAudit(datFile, connectionString); } }
public static void MoveFiles(ClassDataFile[] files, string outputLoaction) { foreach (ClassDataFile file in files) { string yearNumber = file.Year; if (Directory.Exists(outputLoaction)) { if (Directory.Exists(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\")) { if (Directory.Exists(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day)) { File.Copy(file.Location, outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName); file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName; } else { Directory.CreateDirectory(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\"); File.Copy(file.Location, outputLoaction + " \\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName); file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName; } } else { Directory.CreateDirectory(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\"); File.Copy(file.Location, outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName); file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName; } } else { Directory.CreateDirectory(outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\"); File.Copy(file.Location, outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName); file.NewLocation = outputLoaction + "\\" + file.MonthName + " " + yearNumber + "\\" + file.Day + "\\" + file.FileName; } File.Delete(file.Location); } }
public static string AuditFileCheck(ClassDataFile datFile, ClassAuditEntriesDataTable auditEntries) { string message = "Not successful. No Audit entry found for this file"; foreach (DataRow dr in auditEntries.entriesTable.Rows) { if (dr["FileName"].ToString() == datFile.FileName.Substring(0, 8)) { if (Convert.ToInt16(dr["CDR_Count"]) == datFile.AmountOfLines) { message = "AuditFile Check Successful"; datFile.PassedAuditFileCheck = true; datFile.AuditFileEntryAmount = Convert.ToInt16(dr["CDR_Count"]); break; } else { message = "AuditFile Check Not Successful. File is not complete:CDR MISMATCH"; datFile.PassedAuditFileCheck = false; break; } } } return message; }
public static bool UpdateIncompleteAudit(ClassDataFile datFile, string connectionString) { SqlConnection sqlConnection = new SqlConnection(); SqlCommand sqlCommand = new SqlCommand(); sqlConnection.ConnectionString = connectionString; sqlCommand.Connection = sqlConnection; sqlCommand.CommandText = BuildUpdateInCompleteAuditsQuery(datFile); sqlConnection.Open(); int count = sqlCommand.ExecuteNonQuery(); sqlConnection.Close(); bool _result = (count >= 1) ? true : false; return _result; }
private string UpdateTemplate(ClassDataFile file) { string line; using (StreamReader sr = new StreamReader(this.templateLocation)) { line = sr.ReadToEnd(); sr.Close(); } line = this.TagReplace(file, line); string path; path = file.NewLocation.Replace(file.FileName, this.templateName); System.IO.File.WriteAllText(path, line); return path; }
public string TagReplace(ClassDataFile file, string line) { line = line.Replace("{ConnectionString}", this.connectionString); line = line.Replace("{FileLocation}", file.NewLocation); line = line.Replace("{FileName}", file.FileType + file.Day + file.Month + file.FileNumber); return line; }
public static ClassDataFile ReturnDataFileByNameAndActualAmount(string fileName, int actualAmount, List<ClassDataFile> dataFileList) { ClassDataFile selectedDataFile = new ClassDataFile(); foreach(ClassDataFile currentFile in dataFileList) { if ((currentFile.FileName == fileName) && (currentFile.AmountOfLines == actualAmount)) selectedDataFile = currentFile; } return selectedDataFile; }
public static ClassDataFile ReturnDataFileByID(int ID, List<ClassDataFile> dataFileList) { ClassDataFile selectedDataFile = new ClassDataFile(); foreach(ClassDataFile currentFile in dataFileList) { if (currentFile.MTN_APN_Data_File_ID == ID) selectedDataFile = currentFile; } return selectedDataFile; }
private static string BuildUpdateInCompleteAuditsQuery(ClassDataFile datFile) { StringBuilder sqlQuery = new StringBuilder(); sqlQuery.AppendLine("USE [APN_DATA]"); sqlQuery.AppendLine(""); sqlQuery.AppendLine("UPDATE [MTN_APN_Data_File]"); sqlQuery.AppendLine("SET Audit_File_CDR_Amount = " + datFile.AuditFileEntryAmount.ToString()); sqlQuery.AppendLine("WHERE"); sqlQuery.AppendLine(" ID =" + datFile.MTN_APN_Data_File_ID.ToString()); return sqlQuery.ToString(); }
public void UpdateRow(ClassDataFile dataFile, string resultType, string resultMessage, ref ClassImportInfoDataTable infoTable) { DataRow row = infoTable.infoTable.Select("File_Name = '" + dataFile.FileName + "'").FirstOrDefault(); row[resultType] = resultMessage.ToString(); infoTable.infoTable.AcceptChanges(); }
public static DataTable InsertNewFileId(ClassDataFile[] dataFiles, string connectionString) { DataTable pTable = new DataTable(); int fileCount = 1; DataTable resultTable = new DataTable(); SqlConnection sqlConnection = new SqlConnection(); SqlCommand sqlCommand = new SqlCommand(); sqlConnection.ConnectionString = connectionString; StringBuilder sqlQuery = new StringBuilder(); sqlQuery.AppendLine("USE [APN_DATA]"); sqlQuery.AppendLine(""); sqlQuery.AppendLine("INSERT INTO "); sqlQuery.AppendLine(" MTN_APN_Data_File(File_Name,Date_Uploaded,Actual_CDR_Amount, Self_Check_CDR_Amount)"); sqlQuery.AppendLine(" VALUES"); foreach (ClassDataFile newDataFile in dataFiles) { sqlQuery.AppendLine("('" + newDataFile.FileName + "',GETDATE()," + newDataFile.AmountOfLines.ToString() + "," + newDataFile.IntendedAmountOfLines.ToString() + "),"); if (fileCount == (dataFiles.Count())) sqlQuery = sqlQuery.Remove(sqlQuery.Length - 3, 1); else fileCount++; } sqlQuery.AppendLine(""); sqlQuery.AppendLine("SELECT TOP " + fileCount + ""); sqlQuery.AppendLine(" [ID],"); sqlQuery.AppendLine(" [FILE_NAME],"); sqlQuery.AppendLine(" [Actual_CDR_Amount],"); sqlQuery.AppendLine(" [Self_Check_CDR_Amount]"); sqlQuery.AppendLine("FROM"); sqlQuery.AppendLine(" MTN_APN_Data_File"); sqlQuery.AppendLine("ORDER BY"); sqlQuery.AppendLine(" ID DESC"); sqlCommand.CommandText = sqlQuery.ToString(); sqlCommand.Connection = sqlConnection; using (SqlDataAdapter sqlAdaptor = new SqlDataAdapter(sqlCommand.CommandText, sqlConnection)) { sqlAdaptor.Fill(pTable); } sqlConnection.Close(); return pTable; }
public static void CreateNewMonthDir(ClassDataFile dataFile, string newLoc) { Directory.CreateDirectory(newLoc + "\\" + dataFile.Month); }