public void RecordWatsonResults(ActionToAnalyze actionToAnalyze)
{
// insert ActionSentiment
ActionSentiment sentiment = InsertActionSentiment(_db, actionToAnalyze);
_db.SubmitChanges(); // get the DB generated ID
int actionSentimentID = sentiment.ActionSentimentID;
// insert child records - ActionSentimentScore(s)
List <Tones> tones = actionToAnalyze.GetTones();
List <ActionSentimentScore> scores = InsertSentimentScores(tones, _db, actionSentimentID);
// update the corresponding ticket sentiment
if (!actionToAnalyze.IsAgent)
{
ActionSentimentScore maxScore = scores.Where(s => s.SentimentScore == scores.Max(a => a.SentimentScore)).First();
TicketSentiment.TicketSentimentStrategy(_db, actionToAnalyze, maxScore);
}
// Delete ActionToAnalyze
actionToAnalyze.DeleteOnSubmit(_db);
_db.SubmitChanges();
}
/// <summary>
/// Create the ActionSentiment for the ActionID
/// </summary>
/// <param name="db">context for insert/submit</param>
/// <param name="actionToAnalyze">action to analyze</param>
/// <returns></returns>
static ActionSentiment InsertActionSentiment(DataContext db, ActionToAnalyze actionToAnalyze)
{
// already exists?
Table <ActionSentiment> table = db.GetTable <ActionSentiment>();
if (table.Where(u => u.ActionID == actionToAnalyze.ActionID).Any())
{
WatsonEventLog.WriteEntry("duplicate ActionID in ActionSentiment table " + actionToAnalyze.ActionID, EventLogEntryType.Error);
}
// Insert
ActionSentiment sentiment = new ActionSentiment
{
ActionID = actionToAnalyze.ActionID,
TicketID = actionToAnalyze.TicketID,
UserID = actionToAnalyze.UserID,
OrganizationID = actionToAnalyze.OrganizationID,
IsAgent = actionToAnalyze.IsAgent,
DateCreated = DateTime.UtcNow
};
table.InsertOnSubmit(sentiment);
return(sentiment);
}
/// <summary>
/// Create the ActionSentiment for the ActionID
/// </summary>
/// <param name="db">context for insert/submit</param>
/// <param name="a">action to analyze</param>
/// <returns></returns>
static ActionSentiment InsertActionSentiment(DataContext db, ActionToAnalyze a)
{
// already exists?
Table <ActionSentiment> table = db.GetTable <ActionSentiment>();
if (table.Where(u => u.ActionID == a.ActionID).Any())
{
throw new Exception("Error: ActionSentiment already exists?");
}
// Insert
ActionSentiment sentiment = new ActionSentiment
{
ActionID = a.ActionID,
TicketID = a.TicketID,
UserID = a.UserID,
OrganizationID = a.OrganizationID,
IsAgent = a.IsAgent,
DateCreated = DateTime.Now
};
table.InsertOnSubmit(sentiment);
return(sentiment);
}
/// <summary>
/// Constructor to wrap the "using" of SqlConnection, SqlTransaction, and DataContext
/// </summary>
/// <param name="tones"></param>
/// <param name="actionToAnalyze"></param>
public WatsonResultsTransaction(Utterance utterance, ActionToAnalyze actionToAnalyze, Action <WatsonTransactionCallback> callback)
{
List <Tones> tones = utterance.tones;
if (tones == null)
{
return; // ?
}
// open the connection
try
{
_singleThreadedTransactions.WaitOne();
string connectionString = ConfigurationManager.AppSettings.Get("ConnectionString");
_connection = new SqlConnection(connectionString); // using
_connection.Open(); // connection must be open to begin transaction
// start the transaction
_transaction = _connection.BeginTransaction(); // using
// create a data context
_db = new DataContext(_connection); // using
_db.Transaction = _transaction;
// insert ActionSentiment
ActionSentiment sentiment = InsertActionSentiment(_db, actionToAnalyze);
_db.SubmitChanges(); // get the DB generated ID
int actionSentimentID = sentiment.ActionSentimentID;
// insert child records - ActionSentimentScore(s)
List <ActionSentimentScore> scores = InsertSentimentScores(tones, _db, actionSentimentID);
// Delete ActionToAnalyze
actionToAnalyze.DeleteOnSubmit(_db);
_db.SubmitChanges();
if (callback != null)
{
WatsonTransactionCallback transaction = new WatsonTransactionCallback()
{
_db = _db,
_sentiment = sentiment,
_scores = scores
};
callback(transaction);
}
// Success!
_transaction.Commit();
}
catch (Exception e)
{
if (_transaction != null)
{
_transaction.Rollback();
}
EventLog.WriteEntry(EVENT_SOURCE, "********************PublishToTable SubmitTransaction: Exception " + e.Message + " ### " + e.Source + " ### " + e.StackTrace.ToString());
Console.WriteLine("Exception at insert into Action Sentiment:" + e.Message + "###" + e.Source + " ----- STACK: " + e.StackTrace.ToString());
}
finally
{
_singleThreadedTransactions.ReleaseMutex();
}
}
