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; } }