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