private Quote GetQuoteFromInput(QuoteInput quoteInput)
{
Quote addedQuote = new Quote();
int quoteId = 0;
if (Int32.TryParse(quoteInput.Id, out quoteId))
{
addedQuote.Id = quoteId;
}
addedQuote.OperationDate = DateTime.Parse(quoteInput.Date);
addedQuote.Name = quoteInput.Name;
addedQuote.Comment = quoteInput.Comment;
addedQuote.Customer = new Customer()
{
Name = quoteInput.CustomerName
};
int customerId = 0;
if (Int32.TryParse(quoteInput.CustomerId, out customerId))
{
addedQuote.Customer.Id = customerId;
}
addedQuote.TotalAmount = Convert.ToDouble(quoteInput.Price.Replace(".", ","));
return(addedQuote);
}
public void UpdateTransaction(QuoteInput quoteInput)
{
if (quoteInput != null)
{
transactionsService.UpdateQuote(quoteInput);
}
}
public void AddTransaction(QuoteInput quoteInput)
{
if (quoteInput != null)
{
transactionsService.AddQuote(quoteInput);
}
}
public void UpdateQuote(QuoteInput quoteInput)
{
Quote updatedQuote = GetQuoteFromInput(quoteInput);
quoteRepository.Update(updatedQuote);
}
public void AddQuote(QuoteInput quoteInput)
{
Quote addedQuote = GetQuoteFromInput(quoteInput);
quoteRepository.Add(addedQuote);
}
//
// Set up the database table(s) and seed the table where
// appropriate.
//
public static void SetupAndSeed()
{
_logger.Trace("Set up and seed tables...");
string cs = null;
try
{
cs = GetConnectionString();
}
catch (System.ArgumentException ex)
{
_logger.Error("Error: {0}.", ex.Message);
string errorMessage = "Cannot get connection string for " +
"database, unable to proceed";
_logger.Fatal(errorMessage);
Console.WriteLine($"Error: Operation aborted - {ex.Message}.");
return;
}
catch (Exception ex)
{
_logger.Error("Error: {0}.", ex.Message);
string errorMessage = "Failed to set up database table.";
_logger.Fatal(errorMessage);
Console.WriteLine($"Error: Operation aborted - {ex.Message}.");
return;
}
MySqlConnection conn = new MySqlConnection(cs);
PrincipalTable.DatabaseConnection = conn;
bool tableExists = false;
try
{
tableExists = PrincipalTable.TableExists();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
_logger.Fatal("Abort operation due to database error: " +
ex.Message);
Console.WriteLine($"Error: Operation aborted - {ex.Message}.");
return;
}
catch (Exception ex)
{
_logger.Fatal("Abort operation to set up and seed " +
"database due to error: " + ex.Message);
Console.WriteLine($"Error: Operation aborted - {ex.Message}.");
return;
}
if (tableExists)
{
string errorMessage = "Database table " +
PrincipalTable.Name + " " +
"already exists - " +
"table not created.";
_logger.Warn(errorMessage);
Console.WriteLine($"No action required - {errorMessage}.");
return;
}
try
{
PrincipalTable.CreateTable();
PrincipalTable.AddTableTriggers();
}
catch (Exception ex)
{
string message = "Failed table/trigger creation: "
+ ex.Message;
_logger.Error(message);
_logger.Fatal("Abort operation to set up database!");
Console.WriteLine($"Error: Operation aborted - {ex.Message}.");
return;
}
QuoteInput seed = new QuoteInput();
string filename = null;
filename = Configuration["seed"];
if (null == filename)
{
string errorMessage = "No seed file specified - " +
"table not seeded.";
_logger.Warn(errorMessage);
Console.WriteLine(
"Warn: Database not seeded, missing seed file.");
return;
}
_logger.Info("Seeding newly created database with " +
$"file {filename}.");
int count = PrincipalTable.AddQuotesFromFile(filename);
if (0 >= count)
{
Console.WriteLine("No quotes seeded into database.");
}
else
{
Console.WriteLine($"Number of quotes seeded = {count}.");
}
return;
}
//
// Read one or more json quote block from a file and insert
// quotes into the principal database tbale.
//
// The function returns the number of quotes inserted into the
// database.
//
// The function expects the file to contain json representation
// of quotes:
// [
// {
// "RefId": "<External Ref ID>",
// "Content": "<Quote Content>",
// "Source": "<Source of the Quote>",
// "SourceUrl": "<URL for the Source>"
// }
// ]
//
// The function throws an exception if the file supplied is null.
//
public static int AddQuotesFromFile(string file)
{
if (null == file)
{
string errorMsg = "Missing filename for adding quotes.";
logger.Error(errorMsg);
throw new System.ArgumentException(errorMsg, file);
}
List <QuoteInput> seedList = null;
try
{
logger.Trace("Opening file and loading content to stream...");
using (StreamReader fStream = File.OpenText(file))
{
string jsonText = fStream.ReadToEnd();
seedList = JsonConvert.DeserializeObject <List <QuoteInput> >(
jsonText);
}
}
catch (OutOfMemoryException ex)
{
logger.Error("Out of memory when reading file: " +
ex.Message);
logger.Error("Quotes not inserted into the table.");
return(0);
}
catch (Exception ex)
{
logger.Error("Encountered error while reading file: " +
ex.Message);
logger.Error("No quotes added to table.");
return(0);
}
int insertionCount = 0;
for (int i = 0; i < seedList.Count(); ++i)
{
QuoteInput seed = seedList[i];
if (true == AddQuote(seed))
{
++insertionCount;
}
}
logger.Info(insertionCount +
" quotes have been added to the database."
);
return(insertionCount);
}
//
// Insert a quote into the principal quote table.
//
// The function returns true on successfully inserting a quote
// entry into the database and false otherwise.
//
// The function expects the quote text and the quote's source to be
// non-empty strings. If an empty string is detected in either field,
// the function will not insert the quote into the database, even
// if other fields are non-empty.
//
// The function throws an exception if the database connection
// for the object is not initialized.
//
public static bool AddQuote(QuoteInput quoteInput)
{
bool inserted = false;
if (null == quoteInput.Content)
{
logger.Error("Quote is empty, insertion failed.");
return(inserted);
}
string table = Name;
string insertQuery =
"insert into " + table + " " +
"(" +
"quote_id" +
", ext_ref_id" +
", quote_text" +
", quote_source" +
", quote_source_link" +
") " +
"values (" +
"@quoteId" +
", @refId" +
", @quoteText" +
", @quoteSource" +
", @sourceUrl" +
") ";
logger.Trace($"Quote insertion query = {insertQuery}");
lock (DatabaseConnection)
{
if (null == DatabaseConnection)
{
string errorMsg =
"Database connection has yet to be initialized.";
logger.Error(errorMsg);
throw new System.ArgumentException(errorMsg);
}
if (DatabaseConnection.State != ConnectionState.Closed)
{
logger.Trace("Closing connection that was left opened...");
DatabaseConnection.Close();
}
if (DatabaseConnection.State != ConnectionState.Open)
{
try
{
logger.Trace("Opening DB connection...");
DatabaseConnection.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = DatabaseConnection;
cmd.CommandText = insertQuery;
cmd.Parameters.Add("@quoteId", DbType.String);
cmd.Parameters.Add("@refId", DbType.String);
cmd.Parameters.Add("@quoteText", DbType.String);
cmd.Parameters.Add("@quoteSource", DbType.String);
cmd.Parameters.Add("@sourceUrl", DbType.String);
cmd.Parameters["@quoteId"].Value = "";
cmd.Parameters["@refId"].Value = "";
if (null != quoteInput.RefId)
{
cmd.Parameters["@refId"].Value = quoteInput.RefId;
}
cmd.Parameters["@quoteText"].Value = quoteInput.Content;
cmd.Parameters["@quoteSource"].Value = quoteInput.Source;
cmd.Parameters["@sourceUrl"].Value = "";
if (null != quoteInput.SourceUrl)
{
cmd.Parameters["@sourceUrl"].Value =
quoteInput.SourceUrl;
}
cmd.Prepare();
int rowsAffected = cmd.ExecuteNonQuery();
if (1 == rowsAffected)
{
inserted = true;
logger.Trace("Quote successfully inserted.");
}
}
catch (MySqlException ex)
{
logger.Error("Database error: {0}.", ex.ToString());
logger.Error("Quote insertion has failed.");
return(inserted);
}
finally
{
if (ConnectionState.Open == DatabaseConnection.State)
{
logger.Trace("Closing DB connection...");
DatabaseConnection.Close();
}
}
}
}
return(inserted);
}
