public static ModelTrainingRecord GetModelIdByDocumentFormat(string documentFormat) { var mt = new ModelTrainingRecord(); using (SqlConnection connection = new SqlConnection(sqlConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); command.CommandType = CommandType.StoredProcedure; command.CommandText = "GetModelByDocumentFormat"; command.Parameters.Add(new SqlParameter("@DocumentFormat", documentFormat)); command.Connection = connection; try { SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { mt.DocumentFormat = Convert.ToString(reader["DocumentFormat"]); mt.ModelId = Convert.ToString(reader["ModelId"]); mt.ModelVersion = Convert.ToInt32(reader["ModelVersion"]); mt.UpdatedDateTime = Convert.ToDateTime(reader["UpdatedDateTime"]); mt.AverageModelAccuracy = Convert.ToDecimal(reader["AverageModelAccuracy"]); } } finally { reader.Close(); } } finally { } return(mt); } }
public static ModelTrainingRecord UpdateModelTraining(ModelTrainingJob m, ILogger log) { using (SqlConnection connection = new SqlConnection(sqlConnectionString)) { connection.Open(); SqlCommand command = connection.CreateCommand(); SqlTransaction transaction; transaction = connection.BeginTransaction("TrainingRequestTransaction"); command.Connection = connection; command.Transaction = transaction; int currentVersion = 0; int newVersion = 0; try { command.CommandText = $"SELECT MAX(ModelVersion) AS Current_Version from ModelTraining WHERE DocumentFormat='{m.DocumentFormat}'"; SqlDataReader reader = command.ExecuteReader(); try { while (reader.Read()) { if (reader["Current_Version"] != System.DBNull.Value) { currentVersion = Convert.ToInt32(reader["Current_Version"]); } else { currentVersion = 0; } } } finally { reader.Close(); } newVersion = currentVersion + 1; // Add the row string insertClause = $"Insert into ModelTraining (DocumentFormat, ModelVersion, ModelId, CreatedDateTime, UpdatedDateTime, BlobSasUrl, BlobfolderName, IncludeSubFolders, UseLabelFile, AverageModelAccuracy, TrainingDocumentResults"; string valuesClause = $" VALUES ('{m.DocumentFormat}', '{newVersion}','{m.ModelId}', '{m.CreatedDateTime:yyyy-MM-dd HH:mm:ss.fff}', '{m.UpdatedDateTime:yyyy-MM-dd HH:mm:ss.fff}', '{m.BlobSasUrl}', '{m.BlobFolderName}','{m.IncludeSubFolders}', '{m.UseLabelFile}','{m.AverageModelAccuracy}','{m.TrainingDocumentResults}'"; insertClause += ") "; valuesClause += ")"; command.CommandText = insertClause + valuesClause; command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception e) { log.LogError($"Exception prevented writing training request for document format {m.DocumentFormat} model id={m.ModelId} to database (transaction was rolled back). Message is {e.Message}"); transaction.Rollback(); throw e; } ModelTrainingRecord mtr = new ModelTrainingRecord { ModelId = m.ModelId, ModelVersion = newVersion, AverageModelAccuracy = m.AverageModelAccuracy, DocumentFormat = m.DocumentFormat, UpdatedDateTime = m.UpdatedDateTime }; log.LogInformation($"Training request for document format {m.DocumentFormat}, version={newVersion}, model id={m.ModelId} was written to the database"); return(mtr); } }