public static List <Subscriber> GetAllSubscribers()
{
List <Subscriber> subscribers = new List <Subscriber>();
string sql = "select licencePlates, validTo from subscriber";
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordValidTo = rs.GetOrdinal("validTo");
rs.ReadFirst();
Subscriber subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
subscribers.Add(subscriber);
while (rs.Read())
{
subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
subscribers.Add(subscriber);
}
}
return(subscribers);
}
public static Vehicle GetVehicle(String licencePlates)
{
if (licencePlates == null || licencePlates.Length == 0)
{
return(null);
}
string sql = "select licencePlates, vehicleType, checkedIn, checkedInDate from vehicle where licencePlates=@licencePlates";
Vehicle vehicle = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@licencePlates", licencePlates);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordVehicleType = rs.GetOrdinal("vehicleType");
int ordCheckedIn = rs.GetOrdinal("checkedIn");
int ordCheckedInDate = rs.GetOrdinal("checkedInDate");
rs.ReadFirst();
vehicle = new Vehicle();
vehicle.LicencePlates = rs.GetString(ordLicencePlates);
vehicle.VehicleType = rs.GetString(ordVehicleType);
vehicle.CheckedIn = rs.GetBoolean(ordCheckedIn);
vehicle.CheckedInDate = rs.GetDateTime(ordCheckedInDate);
}
return(vehicle);
}
public List <OutgoingTwilioMessage> GetAllOutboxMessages()
{
List <OutgoingTwilioMessage> outboxMsgs = new List <OutgoingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableOutbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllOutboxMessages(): no messages to offload");
return(outboxMsgs);
}
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): offloading " + count + " outbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableOutbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
OutgoingTwilioMessage outMsg = new OutgoingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
From = rs.GetString(2),
To = rs.GetString(3),
Action = rs.GetString(4),
Method = rs.GetString(5),
Body = rs.GetString(6),
MediaURLs = rs.GetString(7),
Client = rs.GetString(8)
};
// DEBUG
App.logger.Log("DBStore.GetAllOutboxMessages(): message = " + outMsg.ToString());
outboxMsgs.Add(outMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllOutboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(outboxMsgs);
}
public static Subscriber GetSubscriber(string licencePlates)
{
string sql = "select licencePlates, validTo from subscriber where licencePlates=@licencePlates";
Subscriber subscriber = null;
SqlCeCommand cmd = new SqlCeCommand(sql, DatabaseConnector.DatabaseConnection);
cmd.Parameters.AddWithValue("@licencePlates", licencePlates);
cmd.CommandType = CommandType.Text;
SqlCeResultSet rs = cmd.ExecuteResultSet(
ResultSetOptions.Scrollable);
if (rs.HasRows)
{
int ordLicencePlates = rs.GetOrdinal("licencePlates");
int ordValidTo = rs.GetOrdinal("validTo");
rs.ReadFirst();
subscriber = new Subscriber();
subscriber.LicencePlates = rs.GetString(ordLicencePlates);
subscriber.ValidTo = rs.GetDateTime(ordValidTo);
}
return(subscriber);
}
public List <IncomingTwilioMessage> GetAllInboxMessages()
{
List <IncomingTwilioMessage> inboxMsgs = new List <IncomingTwilioMessage>();
SqlCeCommand cmd = dbConnection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM " + TableInbox;
Int32 count = (Int32)cmd.ExecuteScalar();
if (count == 0)
{
App.logger.Log("DBStore.GetAllInboxMessages(): no messages to offload");
return(inboxMsgs);
}
App.logger.Log("DBStore.GetAllInboxMessages(): offloading " + count + " inbox message(s)");
try
{
//cmd.CommandText = "SELECT TOP (" + maxBatchLoad + ") * FROM " + TableActivations;
cmd.CommandText = "SELECT * FROM " + TableInbox;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable | ResultSetOptions.Scrollable);
while (rs.Read())
{
IncomingTwilioMessage inMsg = new IncomingTwilioMessage
{
id = rs.GetInt32(0),
Timestamp = rs.GetDateTime(1),
AccountSid = rs.GetString(2),
ApiVersion = rs.GetString(3),
Body = rs.GetString(4),
From = rs.GetString(5),
FromCity = rs.GetString(6),
FromCountry = rs.GetString(7),
FromState = rs.GetString(8),
FromZip = rs.GetString(9),
MessageSid = rs.GetString(10),
NumMedia = rs.GetString(11),
NumSegments = rs.GetString(12),
SmsSid = rs.GetString(13),
SmsStatus = rs.GetString(14),
ToState = rs.GetString(15),
To = rs.GetString(16),
ToCity = rs.GetString(17),
ToCountry = rs.GetString(18),
ToZip = rs.GetString(19),
MediaURLs = rs.GetString(20)
};
// DEBUG
App.logger.Log("DBStore.GetAllInboxMessages(): message = " + inMsg.ToString());
inboxMsgs.Add(inMsg);
}
}
catch (Exception ex)
{
var message = "! Error in DBStore.GetAllInboxMessages(): " + ex.Message + "\n" + ex.TargetSite;
App.logger.Log(message);
}
return(inboxMsgs);
}
public frmMain()
{
InitializeComponent();
if (!File.Exists(string.Format("{0}\\MyPersonalIndex\\MPI.sqlite", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData))))
{
MessageBox.Show("Error updating! Please run MyPersonalIndex version 3.0 (and then close it) before using this upgrade program.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
throw new Exception();
}
if (!File.Exists(string.Format("{0}\\MyPersonalIndex\\MPI.sdf", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData))))
{
MessageBox.Show("Error updating! It does not appear you have MyPersonalIndex version 2.0 or later installed.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
throw new Exception();
}
try
{
cnLite = new SQLiteConnection(string.Format("Data Source={0}\\MyPersonalIndex\\MPI.sqlite", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)));
cnCe = new SqlCeConnection(string.Format("Data Source={0}\\MyPersonalIndex\\MPI.sdf", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)));
if (cnCe.State == ConnectionState.Closed)
{
cnCe.Open();
}
if (cnLite.State == ConnectionState.Closed)
{
cnLite.Open();
}
using (SQLiteCommand c = new SQLiteCommand("BEGIN", cnLite))
c.ExecuteNonQuery();
// Portfolios
if (Convert.ToDouble(ExecuteScalar("SELECT Version FROM Settings")) < 2.01)
{
MessageBox.Show("Error updating! Upgrade to version 2.0.1 before running this installer.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
throw new Exception();
}
Dictionary <int, int> portfolioMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Portfolios"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
string Name = rec.GetString(rec.GetOrdinal("Name"));
bool Dividends = rec.GetBoolean(rec.GetOrdinal("Dividends"));
bool HoldingsShowHidden = rec.GetBoolean(rec.GetOrdinal("HoldingsShowHidden"));
int CostCalc = rec.GetInt32(rec.GetOrdinal("CostCalc"));
bool NAVSort = rec.GetBoolean(rec.GetOrdinal("NAVSort"));
decimal NAVStartValue = rec.GetDecimal(rec.GetOrdinal("NAVStartValue"));
int AAThreshold = rec.GetInt32(rec.GetOrdinal("AAThreshold"));
bool AAShowBlank = rec.GetBoolean(rec.GetOrdinal("AAShowBlank"));
bool AcctShowBlank = rec.GetBoolean(rec.GetOrdinal("AcctShowBlank"));
bool CorrelationShowHidden = rec.GetBoolean(rec.GetOrdinal("CorrelationShowHidden"));
DateTime StartDate = rec.GetDateTime(rec.GetOrdinal("StartDate"));
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Portfolios (Description, StartValue, AAThreshold, ThresholdMethod, " +
" CostBasis, StartDate, Dividends, HoldingsShowHidden, AAShowBlank, CorrelationShowHidden, AcctShowBlank, " +
" NAVSortDesc) VALUES (@Description, @StartValue, @AAThreshold, @ThresholdMethod, " +
" @CostBasis, @StartDate, @Dividends, @HoldingsShowHidden, @AAShowBlank, @CorrelationShowHidden, @AcctShowBlank, " +
" @NAVSortDesc)", cnLite))
{
c.Parameters.AddWithValue("@Description", Name);
c.Parameters.AddWithValue("@StartValue", NAVStartValue);
c.Parameters.AddWithValue("@AAThreshold", AAThreshold);
c.Parameters.AddWithValue("@ThresholdMethod", 0);
c.Parameters.AddWithValue("@CostBasis", CostCalc + 1);
c.Parameters.AddWithValue("@StartDate", ConvertDateToJulian(StartDate));
c.Parameters.AddWithValue("@Dividends", Dividends ? 1 : 0);
c.Parameters.AddWithValue("@HoldingsShowHidden", HoldingsShowHidden ? 1 : 0);
c.Parameters.AddWithValue("@AAShowBlank", AAShowBlank ? 1 : 0);
c.Parameters.AddWithValue("@CorrelationShowHidden", CorrelationShowHidden ? 1 : 0);
c.Parameters.AddWithValue("@AcctShowBlank", AcctShowBlank ? 1 : 0);
c.Parameters.AddWithValue("@NAVSortDesc", NAVSort ? 1 : 0);
c.ExecuteNonQuery();
}
portfolioMapping.Add(ID, getIdentity());
}
}
// Asset Allocation
Dictionary <int, int> aaMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM AA"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio"));
string Description = rec.GetString(rec.GetOrdinal("AA"));
decimal?Target = null;
if (rec.GetValue(rec.GetOrdinal("Target")) != System.DBNull.Value)
{
Target = rec.GetDecimal(rec.GetOrdinal("Target")) / 100;
}
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO AA (PortfolioID, Description, Target) " +
" VALUES (@PortfolioID, @Description, @Target)", cnLite))
{
c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]);
c.Parameters.AddWithValue("@Description", Description);
c.Parameters.AddWithValue("@Target", Target.HasValue ? Target.Value : (object)System.DBNull.Value);
c.ExecuteNonQuery();
}
aaMapping.Add(ID, getIdentity());
}
}
// Accounts
Dictionary <int, int> acctMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Accounts"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio"));
string Description = rec.GetString(rec.GetOrdinal("Name"));
bool OnlyGain = rec.GetBoolean(rec.GetOrdinal("OnlyGain"));
decimal?TaxRate = null;
if (rec.GetValue(rec.GetOrdinal("TaxRate")) != System.DBNull.Value)
{
TaxRate = rec.GetDecimal(rec.GetOrdinal("TaxRate")) / 100;
}
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Acct (PortfolioID, Description, TaxRate, TaxDeferred, CostBasis) " +
" VALUES (@PortfolioID, @Description, @TaxRate, @TaxDeferred, @CostBasis)", cnLite))
{
c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]);
c.Parameters.AddWithValue("@Description", Description);
c.Parameters.AddWithValue("@TaxDeferred", !OnlyGain);
c.Parameters.AddWithValue("@CostBasis", 0);
c.Parameters.AddWithValue("@TaxRate", TaxRate.HasValue ? TaxRate.Value : (object)System.DBNull.Value);
c.ExecuteNonQuery();
}
acctMapping.Add(ID, getIdentity());
}
}
// Securities
Dictionary <int, int> securityMapping = new Dictionary <int, int>();
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Tickers"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
int ID = rec.GetInt32(rec.GetOrdinal("ID"));
int PortfolioID = rec.GetInt32(rec.GetOrdinal("Portfolio"));
string Ticker = rec.GetString(rec.GetOrdinal("Ticker"));
bool Active = rec.GetBoolean(rec.GetOrdinal("Active"));
int AA = rec.GetInt32(rec.GetOrdinal("AA"));
bool Hide = rec.GetBoolean(rec.GetOrdinal("Hide"));
int Account = rec.GetInt32(rec.GetOrdinal("Acct"));
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO Security (PortfolioID, Symbol, Account, DivReinvest, CashAccount, IncludeInCalc, Hide) " +
" VALUES (@PortfolioID, @Symbol, @Account, @DivReinvest, @CashAccount, @IncludeInCalc, @Hide)", cnLite))
{
c.Parameters.AddWithValue("@PortfolioID", portfolioMapping[PortfolioID]);
c.Parameters.AddWithValue("@Symbol", Ticker);
c.Parameters.AddWithValue("@Account", Account == -1 ? (object)System.DBNull.Value : acctMapping[Account]);
c.Parameters.AddWithValue("@DivReinvest", 0);
c.Parameters.AddWithValue("@CashAccount", Ticker == "$" ? 1 : 0);
c.Parameters.AddWithValue("@IncludeInCalc", Active ? 1 : 0);
c.Parameters.AddWithValue("@Hide", Hide ? 1 : 0);
c.ExecuteNonQuery();
}
securityMapping.Add(ID, getIdentity());
if (AA == -1 || !aaMapping.ContainsKey(AA))
{
continue;
}
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityAA (SecurityID, AAID, Percent) " +
" VALUES (@SecurityID, @AAID, @Percent)", cnLite))
{
c.Parameters.AddWithValue("@SecurityID", securityMapping[ID]);
c.Parameters.AddWithValue("@AAID", aaMapping[AA]);
c.Parameters.AddWithValue("@Percent", 1);
c.ExecuteNonQuery();
}
}
}
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Trades WHERE Custom IS NULL"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
DateTime TradeDate = rec.GetDateTime(rec.GetOrdinal("Date"));
int TickerID = rec.GetInt32(rec.GetOrdinal("TickerID"));
decimal Shares = rec.GetDecimal(rec.GetOrdinal("Shares"));
decimal Price = rec.GetDecimal(rec.GetOrdinal("Price"));
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityTrades (SecurityID, Type, Value, Price, Frequency, Date) " +
" VALUES (@SecurityID, @Type, @Value, @Price, @Frequency, @Date)", cnLite))
{
c.Parameters.AddWithValue("@SecurityID", securityMapping[TickerID]);
c.Parameters.AddWithValue("@Type", Shares < 0 ? 1 : 0);
c.Parameters.AddWithValue("@Value", Math.Abs(Shares));
c.Parameters.AddWithValue("@Price", Price);
c.Parameters.AddWithValue("@Frequency", 0);
c.Parameters.AddWithValue("@Date", ConvertDateToJulian(TradeDate));
c.ExecuteNonQuery();
}
}
}
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM CustomTrades"))
{
foreach (SqlCeUpdatableRecord rec in rs)
{
string Dates = rec.GetString(rec.GetOrdinal("Dates"));
int TickerID = rec.GetInt32(rec.GetOrdinal("TickerID"));
int TradeType = rec.GetInt32(rec.GetOrdinal("TradeType"));
int Frequency = rec.GetInt32(rec.GetOrdinal("Frequency"));
decimal Value = rec.GetDecimal(rec.GetOrdinal("Value"));
tradeType newTradeType = tradeType.tradeType_Purchase;
tradeFreq newFrequency = tradeFreq.tradeFreq_Once;
List <DateTime> ConvertedDates = new List <DateTime>();
switch ((DynamicTradeFreq)Frequency)
{
case DynamicTradeFreq.Daily:
ConvertedDates.Add(DateTime.MinValue);
newFrequency = tradeFreq.tradeFreq_Daily;
break;
case DynamicTradeFreq.Monthly:
ConvertedDates.Add(new DateTime(2009, 1, Convert.ToInt32(Dates)));
newFrequency = tradeFreq.tradeFreq_Monthly;
break;
case DynamicTradeFreq.Weekly:
ConvertedDates.Add(new DateTime(2009, 1, 4 + Convert.ToInt32(Dates)));
newFrequency = tradeFreq.tradeFreq_Weekly;
break;
case DynamicTradeFreq.Yearly:
ConvertedDates.Add(new DateTime(2009, 1, 1).AddDays(Convert.ToInt32(Dates) - 1));
newFrequency = tradeFreq.tradeFreq_Yearly;
break;
case DynamicTradeFreq.Once:
foreach (string s in Dates.Split('|'))
{
ConvertedDates.Add(new DateTime(Convert.ToInt32(s.Substring(0, 4)), Convert.ToInt32(s.Substring(4, 2)), Convert.ToInt32(s.Substring(6, 2))));
}
newFrequency = tradeFreq.tradeFreq_Once;
break;
}
switch ((DynamicTradeType)TradeType)
{
case DynamicTradeType.AA:
newTradeType = tradeType.tradeType_AA;
break;
case DynamicTradeType.Fixed:
newTradeType = Value < 0 ? tradeType.tradeType_FixedSale : tradeType.tradeType_FixedPurchase;
break;
case DynamicTradeType.Shares:
newTradeType = Value < 0 ? tradeType.tradeType_Sale : tradeType.tradeType_Purchase;
break;
case DynamicTradeType.TotalValue:
newTradeType = tradeType.tradeType_TotalValue;
break;
}
foreach (DateTime d in ConvertedDates)
{
using (SQLiteCommand c = new SQLiteCommand("INSERT INTO SecurityTrades (SecurityID, Type, Value, Frequency, Date) " +
" VALUES (@SecurityID, @Type, @Value, @Frequency, @Date)", cnLite))
{
c.Parameters.AddWithValue("@SecurityID", securityMapping[TickerID]);
c.Parameters.AddWithValue("@Type", (int)newTradeType);
c.Parameters.AddWithValue("@Value", Math.Abs(Value));
c.Parameters.AddWithValue("@Frequency", (int)newFrequency);
c.Parameters.AddWithValue("@Date", d == DateTime.MinValue ? (object)System.DBNull.Value : ConvertDateToJulian(d));
c.ExecuteNonQuery();
}
}
}
}
// Settings
using (SqlCeResultSet rs = ExecuteResultSet("SELECT * FROM Settings"))
{
rs.ReadFirst();
DateTime DataStartDate = rs.GetDateTime(rs.GetOrdinal("DataStartDate"));
int? LastPortfolio = null;
if (rs.GetValue(rs.GetOrdinal("LastPortfolio")) != System.DBNull.Value)
{
LastPortfolio = rs.GetInt32(rs.GetOrdinal("LastPortfolio"));
}
int WindowX = rs.GetInt32(rs.GetOrdinal("WindowX"));
int WindowY = rs.GetInt32(rs.GetOrdinal("WindowY"));
int WindowHeight = rs.GetInt32(rs.GetOrdinal("WindowHeight"));
int WindowWidth = rs.GetInt32(rs.GetOrdinal("WindowWidth"));
int WindowState = rs.GetInt32(rs.GetOrdinal("WindowState"));
bool Splits = rs.GetBoolean(rs.GetOrdinal("Splits"));
bool TickerDiv = rs.GetBoolean(rs.GetOrdinal("TickerDiv"));
using (SQLiteCommand c = new SQLiteCommand("UPDATE Settings SET DataStartDate = @DataStartDate, LastPortfolio = @LastPortfolio, " +
" WindowX = @WindowX, WindowY = @WindowY, WindowHeight = @WindowHeight, WindowWidth = @WindowWidth, " +
" WindowState = @WindowState, Splits = @Splits, CompareIncludeDividends = @CompareIncludeDividends", cnLite))
{
c.Parameters.AddWithValue("@DataStartDate", ConvertDateToJulian(DataStartDate));
c.Parameters.AddWithValue("@LastPortfolio", LastPortfolio.HasValue ? portfolioMapping[LastPortfolio.Value] : (object)System.DBNull.Value);
c.Parameters.AddWithValue("@WindowX", WindowX);
c.Parameters.AddWithValue("@WindowY", WindowY);
c.Parameters.AddWithValue("@WindowHeight", WindowHeight);
c.Parameters.AddWithValue("@WindowWidth", WindowWidth);
c.Parameters.AddWithValue("@WindowState", WindowState);
c.Parameters.AddWithValue("@Splits", Splits ? 1 : 0);
c.Parameters.AddWithValue("@CompareIncludeDividends", TickerDiv ? 1 : 0);
c.ExecuteNonQuery();
}
}
using (SQLiteCommand c = new SQLiteCommand("COMMIT", cnLite))
c.ExecuteNonQuery();
MessageBox.Show("Upgrade successful!", "Success");
}
catch (Exception e)
{
if (cnLite.State == ConnectionState.Open)
{
using (SQLiteCommand c = new SQLiteCommand("ROLLBACK", cnLite))
c.ExecuteNonQuery();
}
MessageBox.Show("Error updating! You can run this program again from the installation folder.\nError:\n" + e.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
finally
{
cnCe.Close();
cnLite.Close();
throw new Exception();
}
}
private static void Main(string[] args)
{
SqlCeConnection sqlCeCon = new SqlCeConnection("Data Source=\\endo.sdf");
try
{
sqlCeCon.Open();
Console.WriteLine("Connection is open");
SqlCeCommand cmd = new SqlCeCommand();
cmd.Connection = sqlCeCon;
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "SELECT * FROM Workout;";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
List <Workout> workoutList = new List <Workout>();
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("Id");
int ordWorkoutId = rs.GetOrdinal("WorkoutId");
int ordSport = rs.GetOrdinal("Sport");
int ordDuration = rs.GetOrdinal("Duration");
while (rs.Read())
{
Guid id = rs.GetGuid(ordId);
string workoutId = rs.GetString(ordWorkoutId);
int sport = rs.GetInt32(ordSport);
double duration = rs.GetDouble(ordDuration);
workoutList.Add(new Workout(id, workoutId, sport, duration));
}
}
int counter = 1;
foreach (Workout workout in workoutList)
{
cmd.CommandText = $"SELECT * FROM Track WHERE Track.WorkoutId='{workout.Id}';";
//Console.WriteLine(cmd.CommandText);
rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
List <Track> trackList = new List <Track>();
if (rs.HasRows)
{
int ordId = rs.GetOrdinal("Id");
int ordWorkoutId = rs.GetOrdinal("WorkoutId");
int ordTimestamp = rs.GetOrdinal("Timestamp");
int ordInstruction = rs.GetOrdinal("Instruction");
int ordLatitude = rs.GetOrdinal("Latitude");
int ordLongitude = rs.GetOrdinal("Longitude");
int ordDistance = rs.GetOrdinal("Distance");
int ordSpeed = rs.GetOrdinal("Speed");
int ordAltitude = rs.GetOrdinal("Altitude");
int ordSentToServer = rs.GetOrdinal("SentToServer");
while (rs.Read())
{
int id = rs.GetInt32(ordId);
Guid workoutId = rs.GetGuid(ordWorkoutId);
DateTime timestamp = rs.GetDateTime(ordTimestamp);
timestamp = timestamp.Subtract(new TimeSpan(2, 0, 0));
int instruction = rs.IsDBNull(ordInstruction) ? -1 : rs.GetInt32(ordInstruction);
double latitude = rs.GetDouble(ordLatitude);
double longitude = rs.GetDouble(ordLongitude);
double distance = rs.GetDouble(ordDistance);
double speed = rs.GetDouble(ordSpeed);
double altitude = rs.GetDouble(ordAltitude);
bool sentToServer = rs.GetBoolean(ordSentToServer);
trackList.Add(new Track(id, workoutId, timestamp, instruction, latitude, longitude, distance, speed, altitude, sentToServer));
}
string fileName;
fileName = String.Format("Endo_{0}_tcx.tcx", counter);
CreateXmlTcx(fileName, workout, trackList);
fileName = String.Format("Endo_{0}_gpx.gpx", counter);
CreateXmlGpx(fileName, workout, trackList);
}
counter++;
}
sqlCeCon.Close();
Console.WriteLine("Connection is closed");
}
catch (Exception ex)
{
Console.WriteLine(ex.Source + " - " + ex.Message);
}
//Console.ReadKey();
}