Bill Pay Tracker – More .NET Database Stuff

MyMoney MyMoneyDataset

MainForm CategoriesForm

CategoriesBreakdownForm ViewNotesForm

Here’s another take on using a database with C# .NET. THis is a small bill-pay program that’s used to keep track of expenses and payments. There’s a lot going on here so bear with me on the screenshots. I’ll try to explain each one as best as I can. If you prefer to skip it and go ahead to view the code or source then go on ahead. Also take note that I added a context menu not shown in the screenshots. This menu gives you the ability to do various tasks in each form.

In the MainForm you can see the interface that displays the existing expenses/payments drawn from the database. Along with the interface to add a new payment / expense. The Category drop down lists all of the categories available from another dataset pulling data from the dataset.

The CategoriesForm displays all of the available categories, descriptions, and lets you know if the it’s a debit or credit. You can add, remove, and edit the entries via the table adapter interface.

The CategoriesBreakDown form let’s you search expenses / payments via a date search. This is so you can search at any given time what expenses / payments there have been.

The ViewNotes form was something I added at the last minute. With this form you can view the Description, Amount, and Data of the expense / payment. The main purpose of this form is so you can add extra notes to further describe what the expense / payment is.

Cheers

Tails8
MainForm Code


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyMoney
{
    public partial class MainForm : Form
    {
        //Private class variables.
        private decimal total = 0.0m;       //Variable that stores the balance total.

        public MainForm()
        {
            InitializeComponent();
        }

        private void MainForm_Load(object sender, EventArgs e)
        {
            //This loads data into the 'myMoneyDataSet.RegisterCategory' table.
            this.registerCategoryTableAdapter.Fill(this.myMoneyDataSet.RegisterCategory);

            //This loads data into the 'myMoneyDataSet.Categories' table.  
            this.categoriesTableAdapter.Fill(this.myMoneyDataSet.Categories);

            //Lambda expression for the Update Balance Button.
            updateBalanceButton.Click += (from, ea) => updateBalance();

            //Lambda expression for the Update Balance context menu item.
            updateBalanceToolStripMenuItem.Click += (from, ea) => updateBalance();

            //Lambda expression for the Clear Balance button and resetting the balance text.
            clearBalanceButton.Click += (from, ea) => resetBalance();

            //Lambda expression for the Clear Balance context menu item.
            clearBalanceToolStripMenuItem.Click += (from, ea) => resetBalance();
        }

        //Method that takes care of the file -> menu item. 
        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Closing the application.
            this.Close();
        }

        //Method that takes care of the file -> categories menu item.
        private void categoriesToolStripMenuItem_Click(object sender, EventArgs e)
        {
            using (CategoriesForm categoriesForm = new CategoriesForm())
            {
                categoriesForm.ShowDialog();
            }
            updateCategoryDropDown();
        }

        //Method that takes care of the reports -> category menu item.
        private void categoryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            using (CategoriesBreakDownForm categoryBreakDownForm = new CategoriesBreakDownForm())
            {
                categoryBreakDownForm.ShowDialog();
            }
            updateCategoryDropDown();
        }

        //Method that displays a delete dialog when you select a record to remove.
        private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (registerDataGridView.CurrentRow.Index >= 0)
            {
                string registerIDString = registerDataGridView.Rows[registerDataGridView.CurrentRow.Index].Cells[0].
                    Value.ToString();

                int registerID = -1;

                if (int.TryParse(registerIDString, out registerID))
                {
                    if (DialogResult.Yes == MessageBox.Show("Delete Row " + registerDataGridView.CurrentRow.Index +
                        " ?", "Delete Entry", MessageBoxButtons.YesNo))
                    {
                        registerTableAdapter.Delete(registerID);
                        registerCategoryTableAdapter.Fill(myMoneyDataSet.RegisterCategory);
                    }
                }
            }
        }

        //Method that takes care of the mouse button events inside the data grid.
        private void registerDataGridView_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex >= 0)
                {
                    registerDataGridView.CurrentCell = registerDataGridView.Rows[e.RowIndex].Cells[1];

                    Point mousePoint = registerDataGridView.PointToClient(Cursor.Position);
                    DataGridView.HitTestInfo hitTestInfo = registerDataGridView.HitTest(mousePoint.X, mousePoint.Y);
                    
                    if (hitTestInfo.Type == DataGridViewHitTestType.Cell)
                        registerContextMenuStrip.Show(registerDataGridView, mousePoint);
                }
            }
        }

        //Method for when the add button is clicked on.
        private void addButton_Click(object sender, EventArgs e)
        {
            //Getting the selected category ID number.
            int categoryID = Convert.ToInt32(categoryComboBox.SelectedValue);

            //Getting the amount entered in.
            decimal amount = 0.0m;
            decimal.TryParse(amountTextBox.Text, out amount);

            //Determining if the category is a debit, and adjusting the amount sign accordingly.
            bool? isDebit = categoriesTableAdapter.IsCategoryDebit(categoryID);
            if (!isDebit.HasValue)
                isDebit = false;

            amount = Math.Abs(amount);
            if (isDebit.Value == true)
                amount *= -1.0m;

            //Getting the date from the DateTimePicker.
            DateTime date;
            DateTime.TryParse(dateTimePicker.Value.ToShortDateString(), out date);

            //Inserting the data into the Dataset.
            try
            {
                descriptionLabel.ForeColor = Color.Black;

                registerTableAdapter.Insert(
                    categoryID,
                    string.IsNullOrWhiteSpace(identifierTextBox.Text) ? null : identifierTextBox.Text,
                    string.IsNullOrWhiteSpace(descriptionTextBox.Text) ? null : descriptionTextBox.Text,
                    string.IsNullOrWhiteSpace(noteTextBox.Text) ? null : noteTextBox.Text,
                    amount, date);
            }
            //Catching the error if the user leaves the Description box empty.
            catch (ArgumentNullException)
            {
                descriptionLabel.ForeColor = Color.Red;
                MessageBox.Show("You must enter a Description", this.Text, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            //Refresh the data from the dataset.
            registerCategoryTableAdapter.Fill(myMoneyDataSet.RegisterCategory);

            //Clear the add controls after the data has been added to the dataset.
            identifierTextBox.Text = "";
            descriptionTextBox.Text = "";
            amountTextBox.Text = "";
            noteTextBox.Text = "";
        }

        private void viewNotesToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Getting the rrow index number of the clicked on row.
            int rowIndex = registerDataGridView.CurrentRow.Index;
            if (rowIndex >= 0)
            {
                string registerID = registerDataGridView[0, rowIndex].Value.ToString();
                int bindingSourceId = registerCategoryBindingSource.Find("registerID", registerID);

                if (bindingSourceId >= 0)
                {
                    ViewNotesForm viewNotesForm = new ViewNotesForm();
                    viewNotesForm.Position = bindingSourceId;

                    //If the user clicks the save notes button then update the data and the datagridview.
                    if (viewNotesForm.ShowDialog() == DialogResult.OK)
                    {
                        this.registerCategoryTableAdapter.Fill(this.myMoneyDataSet.RegisterCategory);
                        registerDataGridView.ClearSelection();
                        registerDataGridView.Rows[rowIndex].Selected = true;
                    }
                }
            }
        }

        private void updateBalance()
        {
            //Declaring a variable to store the amount.
            decimal amount = 0.0m;
            decimal getTotal = total;

            //This try catch block is mainly for the button controls. If they are clicked on with nothing in the datagrid and exception is thrown.
            //This catches and handles that just incase it happens.
            try
            {
                //Getting the amount from the user selected row/cell and converting it into a number to use.
                decimal.TryParse(registerDataGridView.Rows[registerDataGridView.CurrentRow.Index].Cells[3].Value.ToString(),
                    out amount);
            }
            //Making sure there are entries in the table before trying to update the balance.
            catch (NullReferenceException)
            {
                MessageBox.Show("You must add an entry before updating the balance", this.Text, MessageBoxButtons.OK,
                    MessageBoxIcon.Error);
            }

            //Adding or subtracting from the total.
            total = getTotal + amount;

            //Checking to see if the account is overdrawn. If so changing the text color for a user notification.
            if (total < 0)
                balanceToolStripStatusLabel.ForeColor = Color.Red;      //If the account is overdrawn.
            else
                balanceToolStripStatusLabel.ForeColor = Color.Black;    //If there are enough available funds.

            //Setting the balance text label to the stored amount.
            balanceToolStripStatusLabel.Text = "Balance: $" + total;
        }

        private void resetBalance()
        {
            //Resetting the balance back to zero and the text color back to black.
            total = 0.0m;
            balanceToolStripStatusLabel.ForeColor = Color.Black;
            balanceToolStripStatusLabel.Text = "Balance: $" + total;
        }

        private void updateCategoryDropDown()
        {
            //Do stuff later.
        }
    }
}

 

Categories Form


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyMoney
{
    public partial class CategoriesForm : Form
    {
        public CategoriesForm()
        {
            InitializeComponent();
        }

        private void closeButton_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void CategoriesForm_Load(object sender, EventArgs e)
        {
            //This line of code loads data into the 'myMoneyDataSet.CategoriesHits' table.
            this.categoriesHitsTableAdapter.Fill(this.myMoneyDataSet.CategoriesHits);

        }

        private void categoriesHitsBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.categoriesHitsBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.myMoneyDataSet);

        }

        private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {
            if (categoriesHitsDataGridView.CurrentRow.Index >= 0)
            {
                string categoryName = categoriesHitsDataGridView.CurrentRow.Cells[1].Value.ToString();
                int hits = 0;
                int.TryParse(categoriesHitsDataGridView.CurrentRow.Cells[4].Value.ToString(), out hits);

                if (hits > 0)
                    MessageBox.Show(categoryName + " category is in use and may not be deleted.",
                        "Delete Category");
                else
                {
                    if (DialogResult.Yes == MessageBox.Show("Delete " + categoryName + "?", "Delete Category",
                        MessageBoxButtons.YesNo))
                    {
                        categoriesHitsBindingSource.RemoveCurrent();
                        categoriesHitsTableAdapter.Update(myMoneyDataSet.CategoriesHits);
                    }
                }
            }
        }
    }
}

 

CategoryBreakDown Form


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyMoney
{
    public partial class CategoriesBreakDownForm : Form
    {
        //Private class variables.
        private decimal total = 0.0m;       //Variable that stores the balance total.

        public CategoriesBreakDownForm()
        {
            InitializeComponent();
        }

        private void CategoriesBreakDownForm_Load(object sender, EventArgs e)
        {
            //Setting the DateTimePicker(s) initial dates for the 1st and last day of the current month/year.
            DateTime firstDayOfTheMonth = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            DateTime lastDayOfTheMonth = firstDayOfTheMonth.AddMonths(1).AddDays(-1);
            startDateTimePicker.Value = firstDayOfTheMonth;
            endDateTimePicker.Value = lastDayOfTheMonth;

            //Lambda expression for the Update Balance context menu strip item.
            updateBalanceToolStripMenuItem.Click += (from, ea) => updateBalance();

            //Lambda expression for the Clear Balance context menu strip item.
            clearBalanceToolStripMenuItem.Click += (from, ea) => resetBalance();

            //Update the report.
            updateReport();
        }

        private void closeButton_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void startDateTimePicker_ValueChanged(object sender, EventArgs e)
        {
            //Update the report if the Time/Date value changes.
            updateReport();
        }

        private void endDateTimePicker_ValueChanged(object sender, EventArgs e)
        {
            //Update the report if the Time/Date value changes.
            updateReport();
        }

        //Method that takes care of the mouse button events inside the data grid.
        private void categoryBreakdownDataGridView_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            //Making sure that the right mouse button is clicked.
            if (e.Button == MouseButtons.Right)
            {
                if (e.RowIndex >= 0)
                {
                    categoryBreakdownDataGridView.CurrentCell = categoryBreakdownDataGridView.Rows[e.RowIndex].Cells[1];

                    Point mousePoint = categoryBreakdownDataGridView.PointToClient(Cursor.Position);
                    DataGridView.HitTestInfo hitTestInfo = categoryBreakdownDataGridView.HitTest(mousePoint.X, mousePoint.Y);

                    if (hitTestInfo.Type == DataGridViewHitTestType.Cell)
                        categoryBreakdownContextMenuStrip.Show(categoryBreakdownDataGridView, mousePoint);
                }
            }
        }

        private void updateReport()
        {
            //Updating the table with the database info based on the data range.
            categoryBreakdownTableAdapter.Fill(this.myMoneyDataSet.CategoryBreakdown,
                startDateTimePicker.Value, endDateTimePicker.Value);
        }

        //Method used to update the account balance.
        private void updateBalance()
        {
            //Storing the amount from the datagrid and getting the total amount.
            decimal amount = 0.0m;
            decimal getTotal = total;

            //Getting the amount from the user selected row/cell and converting it to a number to use.
            decimal.TryParse(categoryBreakdownDataGridView.Rows[categoryBreakdownDataGridView.CurrentRow.Index].Cells[1].Value.ToString(),
                out amount);

            //Adding or subtracting based on the amount.
            total = getTotal + amount;

            //Checking to see if the account is overdrawn. If so changing the text color for a user notification.
            if (total < 0)
                categoryBalanceToolStripStatusLabel.ForeColor = Color.Red;      //If the account is overdrawn.
            else
                categoryBalanceToolStripStatusLabel.ForeColor = Color.Black;    //If the account isn't overdrawn.

            //Updating the label to the current balance.
            categoryBalanceToolStripStatusLabel.Text = "Balance: $" + total;
        }

        //Method used to reset the overall balance back to zero.
        private void resetBalance()
        {
            total = 0.0m;
            categoryBalanceToolStripStatusLabel.ForeColor = Color.Black;
            categoryBalanceToolStripStatusLabel.Text = "Balance: $" + total;
        }
    }
}

 

ViewNotes Form


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MyMoney
{
    public partial class ViewNotesForm : Form
    {
        public int Position { get; set; }

        public ViewNotesForm()
        {
            Position = -1;
            InitializeComponent();
        }

        private void ViewNotesForm_Load(object sender, EventArgs e)
        {
            this.registerNotesTableAdapter.Fill(this.myMoneyDataSet.RegisterNotes);

            //Lambda expression for the cancel button.
            cancelButton.Click += (from, ea) => this.Close();

            //Lambda expression for the save button. This saves any changes made to the notes textbox.
            saveNotesButton.Click += (from, ea) =>
                {
                    this.registerNotesBindingSource.EndEdit();
                    this.Validate();
                    this.tableAdapterManager.UpdateAll(this.myMoneyDataSet);
                    this.Close();
                };

            if (Position >= 0)
                this.registerNotesBindingSource.Position = Position;
        }
    }
}

 

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s