public static void InsertCTO(CTOModel ctoModel)
{
string procedure = "[dbo].[InsertCTO]";
object values = new { @material = ctoModel.Material, @materialDescrition = ctoModel.MaterialDescription, @CatalogNumber = ctoModel.CatalogNumber };
executeProcedure(procedure, values);
}
public static int SelectCTOID(CTOModel ctoModel)
{
string procedure = "[dbo].[SelectCTOid]";
var p = new DynamicParameters();
p.Add("@id", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
p.Add("@material", ctoModel.Material);
int i;
using (IDbConnection connection = new SqlConnection(sapConnect))
{
try
{
var retrun = connection.Query(procedure, p, commandType: CommandType.StoredProcedure);
i = p.Get <int>("@id");
}
catch (Exception e)
{
txtWriter.writeInfo($"{ctoModel.Material}, {e}");
throw;
}
}
return(i);
}
/// <summary>
/// parse CTO data and insert data into SQL
/// </summary>
private static void CTO()
{
bool success = true;
int errorCount = 0;
int newLineCount = 0;
List <string> errors = new List <string>();
string[] lines;
string transaction = "CTO";
try
{
lines = File.ReadAllLines(fileCTOCatalog);
}
catch (Exception e)
{
success = false;
lines = null;
string buildError = "No file found: \n\t" + e.ToString();
errorCount++;
txtWriter.Log(buildError, transaction, errorCount, newLineCount);
}
//define counters for info tracker to count each pass in the foreach function
int countPassedUpdate = 0;
int countInserCommand = 0;
if (success)
{
CTOModel ctoModel;
foreach (string line in lines)
{
ctoModel = new CTOModel();
ctoModel.ParseCTO(line);
ctoModel.ID = sqlTransactions.SelectCTOID(ctoModel);
if (ctoModel.ID < 1)
{
try
{
sqlTransactions.InsertCTO(ctoModel);
newLineCount++;
countInserCommand++;
}
catch (Exception ee)
{
txtWriter.Log(ee.ToString(), ctoModel.CatalogNumber);
}
}
else
{
countPassedUpdate++;
}
}
string strgInfo = $"{transaction} had: \n \tInserts:{countInserCommand}\n\tUpdates:NA\n\tNo Transaction: {countPassedUpdate}\n\n";
txtWriter.writeInfo(strgInfo);
//define streams and send to log writer
txtWriter.Log(errors, transaction, errorCount, newLineCount);
success = true;
}
}
