I'm a new self taught "programmer" that has been given the task at my job to parse through generated .txt test records and insert the data into a database. Usually when given a task like this I just hack together something that works, but I want to get better at creating smarter code.
Basically, the program generates a list of log files from a folder. It then loops through that list of files and reads, parses, and inserts new data into the database, ignoring previously inserted data along the way. I'm using Entity Framework to handle the database bits. My database is split into 4 tables, BoardType, UUT, TestRecord, and TestStep, with BoardType being the top-most "level" and the other tables referencing the previous one going down the list.
The ReadFile method starts off the process, it reads through the file line by line until it detects that a record is complete, then it takes those lines and processes them as a chunk using ParseRecord, parsing data into the database object attributes. This then calls the InsertIntoDatabase method, which checks to see if the objects already exist in the database, and inserts them if not. The TestStep objects are handled differently, being collected in a list of up to 250,000 objects and bulk inserting them all at once. This process then repeats until it's read through all of the logs files.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity.Infrastructure;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntityFramework.BulkInsert.Extensions;
using SimpleLogger;
namespace FP2DB_EF
{
public class Program
{
List<string> TestRecordLines = new List<string>();
List<TestStep> TestSteps = new List<TestStep>();
List<TestStep> lastTestSteps = new List<TestStep>();
BoardType board = new BoardType();
UUT uut = new UUT();
TestRecord testRecord = new TestRecord();
TestRecord lastTestRecord = new TestRecord();
UUT lastUUT = new UUT();
BoardType lastBoard = new BoardType();
string[] fileArray = Directory.GetFiles(@"...", "*....");
// Grabs acceptable products from config file for easy editing
string[] productDesignators = ConfigurationManager.AppSettings["des"].Split(',');
Stopwatch sw = new Stopwatch();
FPContext db = new FPContext();
List<TestStep> bulkTestSteps = new List<TestStep>();
int insertCounter = 0;
static void Main(string[] args)
{
SimpleLog.SetLogFile(logDir: ".\\Log", prefix: "ErrorLog_", writeText: false, check: false);
Program program = new Program();
Console.WriteLine("Press any key to begin storing data.");
Console.ReadKey();
Stopwatch totalTime = new Stopwatch();
totalTime.Restart();
Console.WriteLine("Running...");
// Main production loop
foreach (string file in program.fileArray)
{
foreach (string designator in program.productDesignators)
{
if (Path.GetFileNameWithoutExtension(file).Contains(designator))
{
program.ReadFile(file);
}
}
}
totalTime.Stop();
Console.WriteLine(totalTime.Elapsed);
Console.WriteLine("\nFINISHED - Data stored in ... database.");
Console.WriteLine("\nPress any key to exit..");
Console.ReadKey();
}
public void InsertIntoDatabase()
{
sw.Restart();
if (board.Board_Model != lastBoard.Board_Model || board.Board_Rev != lastBoard.Board_Rev)
{
if (!db.BoardTypes.Any(o => o.Board_Model == board.Board_Model && o.Board_Rev == board.Board_Rev))
{
db.BoardTypes.Add(board);
try
{
db.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Ran into error when trying to save BoardType to database. Data logged, continuing.");
SimpleLog.Error($@"SaveChanges threw exception {e.Message}. BoardType is {board.Board_Model} and {board.Board_Rev}");
}
}
else
{
board = db.BoardTypes.SingleOrDefault(o => o.Board_Model == board.Board_Model && o.Board_Rev == board.Board_Rev);
uut.FK_BOARD_ID = board.Board_ID;
}
}
else
{
board = lastBoard;
}
if (uut.UUT_SN != lastUUT.UUT_SN)
{
if (!db.UUTs.Any(o => o.UUT_SN == uut.UUT_SN && o.FK_BOARD_ID == board.Board_ID))
{
uut.FK_BOARD_ID = board.Board_ID;
board.UUTs.Add(uut);
db.UUTs.Add(uut);
try
{
db.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Ran into error when trying to save UUT to database. Data logged, continuing.");
SimpleLog.Error($@"SaveChanges threw exception {e.Message}. UUT is {uut.UUT_SN}");
}
}
else
{
uut = db.UUTs.SingleOrDefault(o => o.UUT_SN == uut.UUT_SN && o.FK_BOARD_ID == board.Board_ID);
testRecord.FK_UUT_ID = uut.UUT_ID;
}
}
else
{
uut = lastUUT;
}
if (!db.TestRecords.Any(o => o.TR_OverallResult == testRecord.TR_OverallResult && o.TR_TestDate == testRecord.TR_TestDate && o.FK_UUT_ID == uut.UUT_ID))
{
testRecord.FK_UUT_ID = uut.UUT_ID;
uut.TestRecords.Add(testRecord);
db.TestRecords.Add(testRecord);
try
{
db.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Ran into error when trying to save TestRecord to database. Data logged, continuing.");
SimpleLog.Error($@"SaveChanges threw exception {e.Message}. TestRecord is Date: {testRecord.TR_TestDate} and Result: {testRecord.TR_OverallResult} and Side: {testRecord.TR_Side}");
}
foreach (TestStep ts in TestSteps)
{
ts.FK_TR_ID = testRecord.TR_ID;
bulkTestSteps.Add(ts);
}
}
else
{
// Separated comparison for debug
bool stepComparison = lastTestSteps == TestSteps;
// This solves the issue where sometimes 2 test records have the same overall result and test date. This should not happen more than once.
if (lastTestRecord.TR_OverallResult == testRecord.TR_OverallResult && lastTestRecord.TR_TestDate == testRecord.TR_TestDate && !stepComparison
&& db.TestRecords.Count(o => o.TR_OverallResult == testRecord.TR_OverallResult && o.TR_TestDate == testRecord.TR_TestDate) == 1)
{
testRecord.FK_UUT_ID = uut.UUT_ID;
uut.TestRecords.Add(testRecord);
db.TestRecords.Add(testRecord);
try
{
db.SaveChanges();
}
catch (Exception e)
{
Console.WriteLine("Ran into error when trying to save TestRecord to database. Data logged, continuing.");
SimpleLog.Error($@"SaveChanges threw exception {e.Message}. TestRecord is Date: {testRecord.TR_TestDate} and Result: {testRecord.TR_OverallResult} and Side: {testRecord.TR_Side}");
}
foreach (TestStep ts in TestSteps)
{
ts.FK_TR_ID = testRecord.TR_ID;
bulkTestSteps.Add(ts);
}
}
}
lastBoard = board;
lastUUT = uut;
lastTestSteps = TestSteps.ToList();
lastTestRecord = testRecord;
sw.Stop();
Console.WriteLine(sw.Elapsed + " - " + board.Board_Model);
resetObjects();
// Refreshes context tracking of objects every 25 inserts, stops insert time from being bogged down by unnecessary tracking
++insertCounter;
if (insertCounter == 25)
{
db = new FPContext();
insertCounter = 0;
}
}
public void ReadFile(string path)
{
var fileStream = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
using (var streamReader = new StreamReader(fileStream, Encoding.UTF8))
{
int lineNumber = 1;
string line;
int counter = 0;
List<string> errors = new List<string>();
while ((line = streamReader.ReadLine()) != null)
{
// Read line by line. After seeing a certain number of @ symbols, process the chunk of text that was read.
if ((line == "@" && counter == 2))
{
if (!ParseRecord(TestRecordLines, ref errors, lineNumber))
{
string errorMessage = GetErrors(errors, String.Format("in log file {0} in entry before line {1}", path, lineNumber));
Console.WriteLine("Error: Entry {0} missing metadata. Check error logs. Skipping database entry of missing record.", path);
errors.Clear();
}
counter = 1;
TestRecordLines.Clear();
if (bulkTestSteps.Count >= 250000)
{
db.BulkInsert(bulkTestSteps);
bulkTestSteps.Clear();
}
}
else if (line == "@")
{
TestRecordLines.Add(line);
counter++;
}
else
{
TestRecordLines.Add(line);
}
++lineNumber;
}
// Process last record when we hit the end of the file
if (TestRecordLines.Count > 0)
{
if (!ParseRecord(TestRecordLines, ref errors, lineNumber))
{
string errorMessage = GetErrors(errors, String.Format("in log file {0} in entry before line {1}", path, lineNumber));
Console.WriteLine("Error: Entry {0} missing metadata. Check error logs. Skipping database entry of missing record.", path);
errors.Clear();
}
}
}
if (bulkTestSteps.Count > 0)
{
db.BulkInsert(bulkTestSteps);
bulkTestSteps.Clear();
}
}
public bool ParseRecord(List<String> record, ref List<string> errors, int line)
{
bool recordParsed = true;
Dictionary<string, string> data = new Dictionary<string, string>();
data.Add("Overall Result", "");
data.Add("Board Model", "");
data.Add("Board Revision", "");
data.Add("Date", "");
data.Add("Serial Number", "");
data.Add("Test Side", "");
for (int i = 0; i < record.Count; i++)
{
// Contains the overall test result surrounded by *
if (record[i].StartsWith("*") && i <= 7)
{
data["Overall Result"] = record[i].Trim('*').Trim();
testRecord.TR_OverallResult = data["Overall Result"];
}
else if (record[i].Split(':')[0].Trim().Contains("Model", StringComparison.OrdinalIgnoreCase) && i <= 7)
{
data["Board Model"] = record[i].Split(':')[1].Trim();
board.Board_Model = data["Board Model"];
}
else if (record[i].Split(':')[0].Trim().Contains("Revision", StringComparison.OrdinalIgnoreCase) && i <= 7)
{
data["Board Revision"] = record[i].Split(':')[1].Trim();
board.Board_Rev = data["Board Revision"];
}
else if (record[i].Split(':')[0].Trim().Contains("Side", StringComparison.OrdinalIgnoreCase) && i <= 7)
{
data["Test Side"] = record[i].Split(':')[1].Trim();
testRecord.TR_Side = data["Test Side"];
}
else if (record[i].Split(':')[0].Trim().Contains("Date", StringComparison.OrdinalIgnoreCase) && i <= 7)
{
DateTime date;
data["Date"] = record[i].Split(new char[] { ':' }, 2)[1].Trim();
if (DateTime.TryParse(data["Date"], out date))
{
testRecord.TR_TestDate = date;
}
}
else if (record[i].Split(':')[0].Trim().Contains("Serial", StringComparison.OrdinalIgnoreCase) && i <= 7)
{
data["Serial Number"] = record[i].Split(':')[1].Trim().TrimEnd('^');
if (data["Serial Number"] == null)
{
uut.UUT_SN = " ";
}
else
{
uut.UUT_SN = data["Serial Number"];
}
}
else if (i >= 8 && Utilities.IsNumeric(record[i].Split(',')[0]))
{
try
{
string[] TestStepData = record[i].Split(',');
// Log files must be in this order to put data in proper columns, edit test programs that don't use this format
TestStep ts = new TestStep
{
TS_Order = int.Parse(TestStepData[0]),
TS_Parts = TestStepData[1],
TS_Value = TestStepData[2],
TS_Comment = TestStepData[3],
TS_Func = TestStepData[4],
TS_HPin = TestStepData[5],
TS_LPin = TestStepData[6],
TS_GP1 = TestStepData[7],
TS_GP2 = TestStepData[8],
TS_Netname1 = TestStepData[9],
TS_Netname2 = TestStepData[10],
TS_Netname3 = TestStepData[11],
TS_Netname4 = TestStepData[12],
TS_StepResult = TestStepData[13],
TS_Element = TestStepData[14],
TS_Reference = TestStepData[15],
TS_Test1 = TestStepData[16],
TS_Test2 = TestStepData[17],
TS_BMComment = TestStepData[18]
};
TestSteps.Add(ts);
}
catch (IndexOutOfRangeException e)
{
SimpleLog.Error(String.Format("{0} in log for board {1} on record before line {2}. Relative record line: {3}", e.Message, data["Board Model"], line, i));
File.AppendAllText(@".\\linelogs.txt", String.Format("{0} - {1}{2}", data["Board Model"], ((line - record.Count) + i).ToString(), Environment.NewLine));
}
}
}
foreach (KeyValuePair<string, string> entry in data)
{
if (String.IsNullOrEmpty(entry.Value) && entry.Key != "Test Side")
{
errors.Add(entry.Key);
if (entry.Key != "Test Side" && entry.Key != "Serial Number")
{
recordParsed = false;
}
}
}
if (recordParsed)
InsertIntoDatabase();
}
void resetObjects()
{
TestRecordLines.Clear();
TestSteps.Clear();
board = new BoardType();
uut = new UUT();
testRecord = new TestRecord();
}
private string GetErrors(List<String> ErrorList, string logMessage)
{
string errorString = "";
foreach (string error in ErrorList)
{
errorString += String.Format("{0} missing{1}", error, Environment.NewLine);
}
SimpleLog.Error(errorString + logMessage);
return errorString;
}
}
public static class StringExtensions
{
public static bool Contains(this string source, string toCheck, StringComparison comp)
{
return source?.IndexOf(toCheck, comp) >= 0;
}
}
}
Here is what I would like to know:
- Is there a way my code should process records that is more efficient than how I am handling it? I need the functionality of checking existing database records and only inserting new ones, which unfortunately means lots of calls to the database. Right now the
TestSteptable has about 29 million records in it, and it takes almost 20 minutes just to check existing records, which will only get longer as more records are added. Once I know the program is stable I can start archiving these .txt files to bring the time down, but for now I would like to make it as efficient as possible. - I want to get better at breaking up my code into smaller chunks, rather than having long methods or classes that do many things. This is a concept I have really been struggling with, knowing how to split methods and classes up like this. As a specific example on this program, would it make sense to put parsing functionality and file reading functionality into their own classes? How would you split up this program using a more object oriented mindset? I want to make sure the base design is good before I go further, and I want to increase readability for anyone in the future that may have to work with this code.
