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

Basic Navigation and XAML

In the last post we looked at the structure of the database. Now we get to dive into WPF application creation.

My original Mortgage Calculator suffered from a horrible GUI. Not only did it use modal windows when it wasn't necessary, the whole layout is incomprehensible.

Notice that the top 3/4 is wasted on user input, buttons and a text box that when clicked gave a summary of the information.

WPF has addressed these issues by providing layout components such as DockPanel, StackPanel, Expander and ScrollViewer to name some. More on that later.

Basic Page Setup

As noted before, I wanted to have 1 window with multiple states. WPF lets us accomplish this by using the NavigationWindow class. NavigationWindow allows an application to "navigate" between any .NET Framework object and HTML page. It's mostly used with the Page class, which is what we will be using it for.

Lets create our first Navigation Window.

<NavigationWindow x:Class="MortCalc.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Mortgage Calculator" Height="350" Width="500" WindowState = "Maximized" Source="MortgageHome.xaml">

</NavigationWindow>

That was simple. I should change my xml namespaces : ) We have specified the title, default size, how the window looks when we open it (WindowState), and the first Page to show in our Navigation Window (Source).

Lets take a look at the code behind this xaml file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace MortCalc
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : NavigationWindow
    {
        public MainWindow()
        {
            InitializeComponent();
        }
    }
}

Again relatively straight forward.

Home Page

The first page should list all of the mortgages currently in the database and allow the user add new Mortgages.

Lets take a look at it.

Notice that we can select the Mortgage Based upon the Name. In my previous version I listed all the relevant details if the Name did not provide enough information. This would make a decent future improvement.

Also the user can create a new Mortgage.

The xaml is again relatively straight forward.

<Page x:Class="MortCalc.MortgageHome"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Mortgage - Home" Height="auto" Width="auto" Loaded="Page_Loaded" Background="Beige">
    <DockPanel LastChildFill="True" Margin="10,0,10,10">
        
        <Label DockPanel.Dock="Top" Style="{StaticResource headerTextStyle}" >
            View Mortgages:
        </Label>
        
        <Grid DockPanel.Dock="Bottom" >
            <Grid.RowDefinitions>
                <RowDefinition/>
            </Grid.RowDefinitions>
            <Grid.ColumnDefinitions>
                <ColumnDefinition />
                <ColumnDefinition Width="Auto"/>
            </Grid.ColumnDefinitions>
            <Button Grid.Row="0" Grid.Column="0" HorizontalAlignment="Right" Click="New_Button_Click" Style="{StaticResource buttonStyle}">
                New
            </Button>
            <Button Grid.Row="0" Grid.Column="1" HorizontalAlignment="Right" Click="Amortize_Button_Click" Style="{StaticResource buttonStyle}">
                Amortize
            </Button>
        </Grid>

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

The whole page is contained inside a DockPanel. DockPanel allows for children UI elements to be position relative to one another. LastChildFill="True" specifies that the last item in the list of children expands to fill any left over space in the Panel. In this case our ListBox with the Names of the Mortgages is the "Fill."

Each child element can specify what type of positioning inside of the DockPanel it is using with DockPanel.Dock. Our "View Mortgages:" Label uses DockPanel.Dock="Top" to position it at the top of the page.

We have put the buttons inside of a grid to ensure proper spacing. The Grid must contain Row and Column Definition sections. We have set the 2nd column to Width="Auto" which ensures auto sizing of the column.

Children inside of the Grid need to specify their Row and Column location. You can see this in the Grid.Row="" and Grid.Column="" values.

There are Several Style="{...}" attributes in this file. In particular these point to values specified somewhere else. They could be listed in the Page.Resources section or in this case in our App.xaml file.

<Style x:Key="headerTextStyle">
    <Setter Property="Label.VerticalAlignment" Value="Center"></Setter>
    <Setter Property="Label.FontFamily" Value="Trebuchet MS"></Setter>
    <Setter Property="Label.FontWeight" Value="Bold"></Setter>
    <Setter Property="Label.FontSize" Value="18"></Setter>
    <Setter Property="Label.Foreground"Value="#000000"></Setter>
</Style>

<!-- Button style -->
<Style x:Key="buttonStyle" TargetType="{x:Type Button}">
    <Setter Property="Width" Value="125" />
    <Setter Property="Height" Value="25" />
    <Setter Property="Margin" Value="0,10,0,0" />
    <Setter Property="HorizontalAlignment" Value="Right" />
</Style>

When MortgageHome.xaml's button specified a Style="{StaticResource buttonStyle}" it was specifically referencing the App.xmal's buttonStyle Style element.

Click="New_Button_Click" specifies a function that gets called in the code behind file when the click event gets triggered.

Lastly, DisplayMemberPath="Name" is a form of binding.

We will be diving into the click event and binding more in future posts. The next post when we see how CRUD gets implemented.


Download the Code Here