public bool SaveCurrencyExchangeRatesToDatabase(List <CurrencyExchangeRates> CurrencyExchangeRatesList)
{
//Declare Return Variable
bool returnValue = false;
try
{
if (CurrencyExchangeRatesList != null && CurrencyExchangeRatesList.Count() > 0)
{
using (SQLiteConnection sqliteConnection = new SQLiteConnection(GetSQLiteDatabaseConnectionString()))
{
sqliteConnection.Execute("DELETE FROM CurrencyExchangeRates");
sqliteConnection.InsertAll(objects: CurrencyExchangeRatesList, runInTransaction: false);
//Populate TableRefreshLog Table - For CurrencyExchangeRates Table
TableRefreshLog tableRefreshLog = new TableRefreshLog()
{
TableName = Constants.Constants.CurrencyExchangeRatesTable,
RefreshDate = DateTime.Now.ToString(Constants.Constants.DateFormat)
};
sqliteConnection.Execute("DELETE FROM TableRefreshLog WHERE TableName = \"" + Constants.Constants.CurrencyExchangeRatesTable + "\" AND RefreshDate = \"" + DateTime.Now.ToString(Constants.Constants.DateFormat) + "\"");
sqliteConnection.Insert(tableRefreshLog);
//Success
returnValue = true;
}
}
}
catch (Exception ex)
{
//Error
returnValue = false;
throw ex;
}
//Return
return(returnValue);
}
private bool CreateSQLiteDatabaseTables(string destinationSQLiteDatabasePath)
{
//Declare Return Variable
bool returnValue = false;
try
{
//Create SQLite Database Connection
using (SQLiteConnection sqliteConnection = new SQLiteConnection(destinationSQLiteDatabasePath))
{
//Create Table CurrencyCodes
sqliteConnection.Execute("CREATE TABLE IF NOT EXISTS CurrencyCodes (CurrencyCode TEXT NOT NULL, CurrencyDescription TEXT NOT NULL, PRIMARY KEY(CurrencyCode));");
//Create Table CurrencyExchangeRates
sqliteConnection.Execute("CREATE TABLE IF NOT EXISTS CurrencyExchangeRates (Date TEXT NOT NULL, BaseCurrencyCode TEXT NOT NULL, TargetCurrencyCode TEXT NOT NULL, ExchangeRate NUMERIC NOT NULL, FOREIGN KEY(TargetCurrencyCode) REFERENCES CurrencyCodes(CurrencyCode), FOREIGN KEY(BaseCurrencyCode) REFERENCES CurrencyCodes(CurrencyCode), PRIMARY KEY (Date,BaseCurrencyCode,TargetCurrencyCode));");
//Create Table CurrencyExchangeRatesArchive
sqliteConnection.Execute("CREATE TABLE IF NOT EXISTS CurrencyExchangeRatesArchive (Date TEXT NOT NULL, BaseCurrencyCode TEXT NOT NULL, TargetCurrencyCode TEXT NOT NULL, ExchangeRate NUMERIC NOT NULL, FOREIGN KEY(TargetCurrencyCode) REFERENCES CurrencyCodes(CurrencyCode), FOREIGN KEY(BaseCurrencyCode) REFERENCES CurrencyCodes(CurrencyCode), PRIMARY KEY (Date,BaseCurrencyCode,TargetCurrencyCode));");
//Create Table CurrencyArbitrageLog
sqliteConnection.Execute("CREATE TABLE IF NOT EXISTS CurrencyArbitrageLog (Date TEXT NOT NULL, BaseCurrencyCode TEXT NOT NULL, IntermediateCurrencyCodes TEXT NOT NULL, TargetCurrencyCode TEXT NOT NULL, Degree INTEGER NOT NULL, ImpliedValue NUMERIC NOT NULL, ActualValue NUMERIC NOT NULL, PRIMARY KEY (Date,BaseCurrencyCode,IntermediateCurrencyCodes,TargetCurrencyCode,Degree), FOREIGN KEY(TargetCurrencyCode) REFERENCES CurrencyCodes(CurrencyCode), FOREIGN KEY (BaseCurrencyCode) REFERENCES CurrencyCodes(CurrencyCode));");
//Create Table DataRefreshLog
sqliteConnection.Execute("CREATE TABLE IF NOT EXISTS TableRefreshLog (TableName TEXT NOT NULL, RefreshDate TEXT NOT NULL, PRIMARY KEY(TableName, RefreshDate));");
//Populate CurrencyCodes Metadata
List <CurrencyCodes> PreferredCurrencyCodesList = new List <CurrencyCodes>();
PreferredCurrencyCodesList.AddRange(Constants.Constants.CurrencyKeyValuePairs
.Select(type => new CurrencyCodes()
{
CurrencyCode = type.Key,
CurrencyDescription = type.Value
})
.ToList());
sqliteConnection.Execute("DELETE FROM CurrencyCodes");
sqliteConnection.InsertAll(objects: PreferredCurrencyCodesList, runInTransaction: false);
//Populate TableRefreshLog Table - For CurrencyCodes Table
TableRefreshLog tableRefreshLog = new TableRefreshLog()
{
TableName = Constants.Constants.CurrencyCodesTable,
RefreshDate = DateTime.Now.ToString(Constants.Constants.DateFormat)
};
sqliteConnection.Execute("DELETE FROM TableRefreshLog WHERE TableName = \"" + Constants.Constants.CurrencyCodesTable + "\" AND RefreshDate = \"" + DateTime.Now.ToString(Constants.Constants.DateFormat) + "\"");
sqliteConnection.Insert(tableRefreshLog);
}
}
catch (Exception ex)
{
//Error
returnValue = false;
throw ex;
}
//Return
return(returnValue);
}
