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