Threading and Saving Data with LINQ to SQL

In our last post we looked at the actual calculations of the amortization.

Threading

Depending on the machine and the values the user has specified the calculation and displaying of the mortgage could lag. 100-200 milliseconds is the threshold before a user notices lag. We want the transition from MortgageHome Page to MortgageReport Page to be as seamless as possible while still immediately amortizing the mortgage. In order to accomplish this we need to run the calculations in their own worker thread, outside the UI thread.

In the MortgageReport page we set up the following threading.

//start a worker thread so it does not bog down the gui
private void Amortize()
{
    BackgroundWorker worker = new BackgroundWorker();

    worker.DoWork += delegate(object s, DoWorkEventArgs args) 
    {
        args.Result = RunAmortize(); 
    }; 
    worker.RunWorkerCompleted += delegate(object s, 
        RunWorkerCompletedEventArgs args) 
    {
        theMI = (MonthIteration)args.Result;
        SetGridData();
    }; 
    worker.RunWorkerAsync();
}

private MonthIteration RunAmortize()
{
    MonthIteration thisMI = new MonthIteration(m);
    return thisMI;
}

In the last post we saw how the MonthIteration class constructor ran all of our calculations for us. As soon as the first line in RunAmortize is complete so are our calculations.

I choose a BackgroundWorker due to it's straight forward approach of assignment to clearly spelled out events. In this case we are creating 2 anonymous delegates; one to start the thread and do the work and one to notify the thread that the work is complete. There are other events for updating progress and when dispose on the component is called. This could have easily just been pulled into its own class. However, since it's relatively uninvolved I kept it in the MortgageReport Page.

Saving Data with LINQ to SQL

The last piece of the puzzle is to save the data to the database for use later on. Because we put the legwork in for LINQ to SQL in a previous post, all we need to do now is use that code to save the data.

private bool IsValid(ref String em, bool checkForName)
{
    List<String> errors = new List<string>();

    if (checkForName && m.Name == null)
        errors.Add("You must specify a Name.");
    else if (checkForName && m.Name.Trim() == String.Empty)
        errors.Add("You must specify a Name.");
    if (m.Principal <= 0)
        errors.Add("You must specify Principal Amount greater than $0.00.");
    if (m.Term <= 0)
        errors.Add("You must specify a Loan Term greater than 0.");
    if (m.InterestRate <= 0)
        errors.Add("You must specify an Interest Rate.");
    em = String.Join(Environment.NewLine, errors);

    if (errors.Count > 0)
        return false;
    else
        return true;
}

Before we can even attempt to save we need to validate all the necessary values. In this case we pass in a string by ref to ensure the calling method can show the user the problems encountered.

private void SaveB_Click(object sender, RoutedEventArgs e)
{
    String em = "";
    if (!IsValid(ref em, true))
    {
        MessageBoxResult result = MessageBox.Show((Window)this.Parent, 
            em, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
    else
    {
        m.Date = DateTime.Now;

        if (isNew)
        {
            if (DBApi.DB.mortDB.Mortgage.Count() > 0)
            {
                m.Key = (from thisMort in DBApi.DB.mortDB.Mortgage
                            select thisMort.Key).Max() + 1; 
            }
            else
                m.Key = 0;
            DBApi.DB.mortDB.Mortgage.InsertOnSubmit(m);
        }

        DBApi.DB.mortDB.SubmitChanges();
        isNew = false;
    }
}

If the values are invalid we give the user immediate feedback in the form a error MessageBox.

One problem that crept up on me when I was working on this is the defaulting of the Key to 0. Since Key 0 already exists in the database, we are now no longer allowed to add new records. To get around this I simply did a quick LINQ to SQL query to find the Max Key and incremented it by 1.

InsertOnSubmit is the same as the insert command in T-SQL. SubmitChanges looks for updates, inserts or deletions and sends them to the database.

That's it! We now have a fully functional Mortgage Calculator that looks pleasant, is somewhat dynamic to the users preferences, stores UI state and gives the user a lot of useful information.

Future Improvements

When coding it's almost impossible to not notice improvements. The following are some features I would like to see in the Mortgage Calculator in the future.

1. An installer package
2. In-grid adjustments
3. Better color schema
4. More detailed stats on values such as
1. Total insurance paid
2. Total taxes paid
5. Home page selection memory
6. Years and months term input fields instead of just months
7. PMI
8. Date tracking by listing the start date
9. Monthly payment date to adjust interest and principal to the day payment was received
10. Application Icon
11. MortgageHome showing more details about each Mortgage
12. Start amortizing in the middle of the year and figure costs accordingly like Tax increasing at the end of the year, which might be 4 months into the loan.
13. Graphs and charts
14. User accounts
15. User account summaries of the user's entire portfolio

Download the Code Here

DataContext on the Mortgage Home Page

In our last post we covered integrating the database CRUD operations through our MortDB DataContext class. In this post we get to see how simple working with a DataContext can be.

The MortgageHome Page

In a previous post we stopped just short of diving into the MortgageHome.xaml's code behind.

Here is the code behind:

namespace MortCalc
{
    /// <summary>
    /// Code Behind for MortgageHome.xaml
    /// </summary>
    public partial class MortgageHome : Page
    {

        public MortgageHome()
        {
            InitializeComponent();
        }

        private void Amortize_Button_Click(object sender, RoutedEventArgs e)
        {
            //error message tell them to select a mortgage
            if (mortgageListBox.SelectedItem == null)
            {
                MessageBoxResult result = MessageBox.Show(
                    (Window)Parent, "Please Select a Mortgage.",
                    "Error", MessageBoxButton.OK, 
                    MessageBoxImage.Error);
            } else{
                // View Mortgage Amortization Report
                MortgageReportPage mortgageReportPage = new 
                    MortgageReportPage(mortgageListBox.SelectedItem);
                this.NavigationService.Navigate(mortgageReportPage);
            }
        }

        private void New_Button_Click(object sender, RoutedEventArgs e)
        {
            // View Mortgage Amortization Report
            MortgageReportPage mortgageReportPage = 
                new MortgageReportPage();
            this.NavigationService.Navigate(mortgageReportPage);
        }

        private void Page_Loaded(object sender, RoutedEventArgs e)
        {
            var results = from m in DBApi.DB.mortDB.Mortgage
                            select m;

            mortgageListBox.ItemsSource = results.ToList();
        }
    }
}

When looking at the form there are 3 main things that it needs to accomplish:

1. Load the Names of the Mortgages
2. Create a new Mortgage
3. Amortize a selected Mortgage

Load the Names of the Mortgages

The Mortgage Names are loaded into the Listbox component.

<ListBox Name="mortgageListBox"  DisplayMemberPath="Name" FontSize="18" />

We can see that DisplayMemberPath is set to "Name". "Name" in this context makes little sense.

Name is a property in the Mortgage class. Knowing this we can now make sense of the Page_Loaded event function.

private void Page_Loaded(object sender, RoutedEventArgs e)
{
    var results = from m in DBApi.DB.mortDB.Mortgage
                    select m;

    mortgageListBox.ItemsSource = results.ToList();
}

The LINQ to SQL code:

var results = from m in DBApi.DB.mortDB.Mortgage select m;

is doing a SQL like query from the Mortgage table in our database, in code. This is equivalent to the following T- SQL:

SELECT *
FROM Mortgage

We just avoided creating a connection, sending a query, iterating through a result set, ect...

Now all that's left is to bind the results to the ListBox.

mortgageListBox.ItemsSource = results.ToList();

By binding the data we avoided for loops of loading details into the list box and matching names to objects located globally in the Page class. Data binding is powerful and elegant.

Create a New Mortgage

When the New button is clicked the Click event gets fired per our xaml code.

<Button Grid.Row="0" Grid.Column="0" HorizontalAlignment="Right" Click="New_Button_Click" Style="{StaticResource buttonStyle}">
    New
</Button>

Here is the event click function:

private void New_Button_Click(object sender, RoutedEventArgs e)
{
    // View Mortgage Amortization Report
    MortgageReportPage mortgageReportPage = new MortgageReportPage();
    this.NavigationService.Navigate(mortgageReportPage);
}

All we are doing here is navigating to the new page. We instantiate the Page object and tell our NavigationWindow class to Navigate to it. All specifying and saving of data will take place in this new Page.

Amortize a selected Mortgage

When someone selects an existing Mortgage we need a way to tell the MortgageReportPage which record we are working on.

private void Amortize_Button_Click(object sender, RoutedEventArgs e)
{
    //error message tell them to select a mortgage
    if (mortgageListBox.SelectedItem == null)
    {
        MessageBox.Show((Window)Parent, 
            "Please Select a Mortgage.",
            "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    } else{
        // View Mortgage Amortization Report
        MortgageReportPage mortgageReportPage = new MortgageReportPage
            (mortgageListBox.SelectedItem);
            this.NavigationService.Navigate(mortgageReportPage);
    }
}

Check to make sure they selected something and if not let them know they need to.

The only way this Navigation to the MortgageReportPage differs from the last method is the passing in of the ListBox's selected item. I am not operating on the value at all and I am not casting as a Mortgage. Very Simple.

The next post will be dealing with the MortgageReport Page and how it binds data in the Mortgage class to the GUI components.


Download the Code Here

Database and CRUD

In the last post we covered some of the Navigation/xaml code and stopped just short of binding data and what the code behind looks like.

I have worked on past projects where CRUD operations were hand coded. This makes sense if you don't have binding. Now that binding, Entity Framework, LINQ to SQL, LINQ to Objects ect... exist hand coding CRUD is a more error prone and a completely antiquated waste of time.

In a previous post we created the database file. In this post we are going to go into integrating the database into our Mortgage Calculator.

SQL Metal

One requirement I had initially for the Database was use of LINQ to SQL. SQL Server Compact supports this. However, there's a catch!

Part of developing LINQ to SQL applications involves modeling the data after the structure of the database. A code file is then generated which contains a DataContext that your application can use to preform CRUD operations on.

Normally, if you are using any other SQL Server database type, in Server Explorer, you simply drag the tables in your project into the dbml file area.

Visual studio doesn't support SQL Server Compact LINQ to SQL DataContext file generation.

This is where SQL Metal comes into play. To generate your DataContext file you have to run the command line utility and specify your database and the output file you would like to generate.

1. Navigate to sqlmetal.exe
2. Specify your .sdf file location
3. Specify your c# file and location you wish to generate.
4. Run it.
5. Add the new cs file to your solution.

My Command line statement looked like this:
C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\bin>sqlmetal C:\MortCalc\MortCalc\DBApi\MortDB.sdf /code:C:\MortCalc\MortCalc\DBApi\MortDB.cs

DB API

Lets take a look at the DBApi folder:

DB.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MortCalc.DBApi
{
    static class DB
    {
        public static MortDB mortDB = new MortDB("DBApi\\MortDB.sdf");
    }
}

DB is a static class which contains an instantiation of MortDB. MortDB is itself a DataContext class. I wanted 1 and only 1 instance of MortDB accessible from the whole application. Problems arise if the same DataContext is not used everywhere.

For example: I had 2 different instances of MortDB getting created; one in the Home Page and one in a page where I saved data. When I navigated back to the Home Page the record I had just saved was missing. Having 1 MortDB solved this problem. I also wanted to avoid sending around objects inside the program unnecessarily.

MortDB.cs:

MortDB.cs is the file that SQL Metal generated. It contains the DataContext class MortDB which will handle all the CRUD operations. It also contains the Mortgage class. This is the only model class of the only table in our Database.

Inside of Mortgage all of our table columns have sections of code dedicated to them.

partial void OnPrincipalChanging(decimal value);
partial void OnPrincipalChanged();

...

[Column(Storage="_Principal", DbType="Decimal(18,2) NOT NULL")]
public decimal Principal
{
    get
    {
        return this._Principal;
    }
    set
    {
        if ((this._Principal != value))
        {
            this.OnPrincipalChanging(value);
            this.SendPropertyChanging();
            this._Principal = value;
            this.SendPropertyChanged("Principal");
            this.OnPrincipalChanged();
        }
    }
}

When something sets the property (the binded-to object) the partial unimplemented Changing and Changed methods are called.

MortDB.Helper1.cs:

MortDB.Helper1.cs is my other half of the MortDB.cs partial file implementation.

partial void OnPrincipalChanging(decimal value)
{
    if(value <= 0)
        AddError("Principal", "Principal must be greater than 0.");
    else
        RemoveError("Principal", "Principal must be greater than 0.");
}

I've added my own validation logic for incoming values to adjust errors accordingly.

If I were to add a new column to the Mortgage table I would be forced to regenerate the MortDB.cs file. Because of partial classes and my particular implementation being in a separate file, I don't have to worry about recoding my changes every time the file gets regenerated.

All that's left is to add a mechanism for individual columns to find their applicable errors.

public void AddError(string propertyName, string error)
{
    if (!errors.ContainsKey(propertyName))
        errors[propertyName] = new List<string>();

    if (!errors[propertyName].Contains(error))
        errors[propertyName].Add(error);
}

 
public void RemoveError(string propertyName, string error)
{
    if (errors.ContainsKey(propertyName) &&
        errors[propertyName].Contains(error))
    {
        errors[propertyName].Remove(error);
        if (errors[propertyName].Count == 0) 
            errors.Remove(propertyName);
    }
}

public string this[string columnName]
{
    get
    {
        return (!errors.ContainsKey(columnName) ? null :
            String.Join(Environment.NewLine, errors[columnName]));
    }
}

This last method gets called when a binded-to control requests errors for a column name.

In our next post we will be using the MortDB DataContext in the code.

Download the Code Here