コード例 #1
0
        /// <summary>
        /// Constructs SQL query format for expense in the form: '(cols) VALUES (vals)'. If the expense has a valid id that will also be included.
        /// The command is constructed so that it is parameterized. Defending from SQLInjection attacks.
        /// </summary>
        /// <param name="prefix">The command to perform such as 'INSERT INTO tablename '.</param>
        /// <param name="cmd">The command to populate with parameters and command text.</param>
        /// <param name="expense">Expense object with populated fields.</param>
        private static void PrepareColValueCommand(string prefix, SqliteCommand cmd, Expense expense)
        {
            string cols = string.Empty;
            string vals = string.Empty;

            if (expense.Id > -1)
            {
                cols += "(Id, Value, Date, Place, Tag, Notes, ";
                vals += "VALUES (@id, @value, @date, @place, @tag, @notes, ";
                cmd.Parameters.Add(new SqliteParameter("@id", expense.Id));
                cmd.Parameters.Add(new SqliteParameter("@value", expense.Value));
                cmd.Parameters.Add(new SqliteParameter("@date", expense.Date));
                cmd.Parameters.Add(new SqliteParameter("@place", expense.Place));
                cmd.Parameters.Add(new SqliteParameter("@tag", string.Join(":", expense.Tag)));
                cmd.Parameters.Add(new SqliteParameter("@notes", expense.Notes));
            }
            else
            {
                cols += "(Value, Date, Place, Tag, Notes, ";
                vals += "VALUES (@value, @date, @place, @tag, @notes, ";
                cmd.Parameters.Add(new SqliteParameter("@value", expense.Value));
                cmd.Parameters.Add(new SqliteParameter("@date", expense.Date));
                cmd.Parameters.Add(new SqliteParameter("@place", expense.Place));
                cmd.Parameters.Add(new SqliteParameter("@tag", string.Join(":", expense.Tag)));
                cmd.Parameters.Add(new SqliteParameter("@notes", expense.Notes));
            }

            if (expense.Method is Cash)
            {
                // This is an undesireable solution - ideally would like a way for derived classes to provide this information themselves,
                // but we dont want that functionality available outside of the engine.
                Cash c = (Cash)expense.Method;
                cols += "Type, Currency) ";
                vals += "@type, @currency);";
                cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CASH));
                cmd.Parameters.Add(new SqliteParameter("@currency", c.Currency));
            }
            else if (expense.Method is Card)
            {
                Card c = (Card)expense.Method;
                cols += "Type, Debit, Provider, Number, Name) ";
                vals += "@type, @debit, @provider, @number, @name);";
                cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CARD));
                cmd.Parameters.Add(new SqliteParameter("@debit", c.Debit));
                cmd.Parameters.Add(new SqliteParameter("@provider", c.Provider));
                cmd.Parameters.Add(new SqliteParameter("@number", c.Number));
                cmd.Parameters.Add(new SqliteParameter("@name", c.Name));
            }
            else if (expense.Method is DirectDeposit)
            {
                DirectDeposit d = (DirectDeposit)expense.Method;
                cols += "Type, Savings, Bankname, Number, Name) ";
                vals += "@type, @savings, @bankname, @number, @name);";
                cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.DIRECT_DEPOSIT));
                cmd.Parameters.Add(new SqliteParameter("@savings", d.Savings));
                cmd.Parameters.Add(new SqliteParameter("@bankname", d.BankName));
                cmd.Parameters.Add(new SqliteParameter("@number", d.Number));
                cmd.Parameters.Add(new SqliteParameter("@name", d.Name));
            }

            cmd.CommandText = prefix + cols + vals;
            cmd.Prepare();
        }
コード例 #2
0
        /// <summary>
        /// Summarizes the ExpenseFilters set of expenses in SQL, that can be easily added to a where clause.
        /// Sorts by date.
        /// </summary>
        private static void PrepareFilterConstraintCommand(string prefix, SqliteCommand cmd, ExpenseFilter filter)
        {
            // TODO: TEST THIS
            string outstring = string.Empty;

            // add date clause
            outstring += "(Date BETWEEN @mindate AND @maxdate) AND ";
            cmd.Parameters.Add(new SqliteParameter("@mindate", filter.MinDate));
            cmd.Parameters.Add(new SqliteParameter("@maxdate", filter.MaxDate));

            // handle value clause
            outstring += "(Value BETWEEN @minvalue AND @maxvalue)";
            cmd.Parameters.Add(new SqliteParameter("@minvalue", filter.MinValue));
            cmd.Parameters.Add(new SqliteParameter("@maxvalue", filter.MaxValue));

            // handle place clause
            if (filter.Place.Count > 0)
            {
                int i = 0;
                outstring += " AND (Place IN (";
                foreach (var place in filter.Place)
                {
                    outstring += "@place" + i;
                    outstring += (filter.Place.Count - 1 == i) ? string.Empty : ", ";
                    cmd.Parameters.Add(new SqliteParameter("@place" + i, place));
                    ++i;
                }

                outstring += "))";
            }

            // handle tag clause
            if (filter.Tag.Count > 0)
            {
                int i = 0;
                outstring += " AND (";
                foreach (var tag in filter.Tag)
                {
                    outstring += "(Tag LIKE @tag" + i + ")";
                    outstring += (filter.Tag.Count - 1 == i) ? string.Empty : " OR ";
                    cmd.Parameters.Add(new SqliteParameter("@tag" + i, "%" + tag + "%"));
                    ++i;
                }

                outstring += ")";
            }

            // handle keyword clause
            if (filter.Keywords.Count > 0)
            {
                int i = 0;
                outstring += " AND (";
                foreach (var word in filter.Keywords)
                {
                    outstring += "(Notes LIKE @word" + i + ")";
                    outstring += (filter.Keywords.Count - 1 == i) ? string.Empty : " OR ";
                    cmd.Parameters.Add(new SqliteParameter("@word" + i, "%" + word + "%"));
                    ++i;
                }

                outstring += ")";
            }

            // prepare purchasemethod args
            if (filter.Method != null)
            {
                if (filter.Method is Cash)
                {
                    // This is an undesireable solution - ideally would like a way for derived classes to provide this information themselves,
                    // but we dont want that functionality available outside of the engine.
                    Cash c = (Cash)filter.Method;
                    outstring += " AND (Type = @type) AND (Currency = @currency)";
                    cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CASH));
                    cmd.Parameters.Add(new SqliteParameter("@currency", c.Currency));
                }
                else if (filter.Method is Card)
                {
                    Card c = (Card)filter.Method;
                    outstring += " AND (Type = @type) AND (Debit = @debit) AND (Provider = @provider) AND (Number = @number) AND (Name = @name)";
                    cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.CARD));
                    cmd.Parameters.Add(new SqliteParameter("@debit", c.Debit));
                    cmd.Parameters.Add(new SqliteParameter("@provider", c.Provider));
                    cmd.Parameters.Add(new SqliteParameter("@number", c.Number));
                    cmd.Parameters.Add(new SqliteParameter("@name", c.Name));
                }
                else if (filter.Method is DirectDeposit)
                {
                    DirectDeposit d = (DirectDeposit)filter.Method;
                    outstring += " AND (Type = @type) AND (Savings = @savings) AND (Bankname = @bankname) AND (Number = @number) AND (Name = @name)";
                    cmd.Parameters.Add(new SqliteParameter("@type", (int)EpurchaseMethod.DIRECT_DEPOSIT));
                    cmd.Parameters.Add(new SqliteParameter("@savings", d.Savings));
                    cmd.Parameters.Add(new SqliteParameter("@bankname", d.BankName));
                    cmd.Parameters.Add(new SqliteParameter("@number", d.Number));
                    cmd.Parameters.Add(new SqliteParameter("@name", d.Name));
                }
            }

            outstring += " ORDER BY Date ASC;";

            cmd.CommandText = prefix + outstring;
            cmd.Prepare();
        }
コード例 #3
0
ファイル: Cash.cs プロジェクト: colletht/ExpenseTrackerPlus
        /// <inheritdoc/>
        public override bool Equals(object obj)
        {
            Cash c = (Cash)obj;

            return(this.Currency == c.Currency);
        }