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