Пример #1
0
        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);
        }
Пример #2
0
        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);
        }
Пример #3
0
        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);
        }
Пример #4
0
        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);
        }
Пример #5
0
        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);
        }
Пример #6
0
        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();
            }
        }
Пример #7
0
        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();
        }