Using database in Windows Phone app

Database in windows phone app is using LINQ to SQL, a .NET component that provide infrastructure for managing relational data as objects (reference). Creating and manipulating the database require a few steps from defining the data structure to querying the database

For this example, we have a database named School with one table Student

Student
- id <int> (PK)
- name <string>
- biography <string>
- registered <datetime>
- major <StudentMajor>

StudentMajor (enumerable)
- Engineering
- Medical
- Computer Science

First, define the table and enum class above. ‘ID’ is primary key, autoincrement field. ‘Biography’ field wont be checked for consistency when doing update operation. ‘Major’ contains enumerable value

[Table]
public class Student
{
    private int id;
    [Column(IsPrimaryKey = true, IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int ID
    {
        get { return id; }
        set { id = value; }
    }

    private string name;
    [Column(DbType = "NVARCHAR(255)")]
    public string Name
    {
        get { return name; }
        set { name = value; }
    }

    private string biography;
    [Column(DbType = "NText", UpdateCheck=UpdateCheck.Never)]
    public string Biography
    {
        get { return biography; }
        set { biography = value; }
    }

    private DateTime registered;
    [Column]
    public DateTime Registered
    {
        get { return registered; }
        set { registered = value; }
    }

    private StudentMajor major;
    [Column]
    public StudentMajor Major
    {
        get { return major; }
        set { major = value; }
    }
}

public enum StudentMajor
{
    Engineering,
    Medical,
    ComputerScience
}

Next, define a database by extending DataContext class and override its base constructor

public class SchoolContext : DataContext
{
    public static string ConnectionString = "Data Source=isostore:/School.sdf";
    public Table<Student> Students;
    public SchoolContext(string connectionString) : base(connectionString) { }
}

Now, we can open the database and do some query on it.

SchoolContext db = new SchoolContext(SchoolContext.ConnectionString);

To query for student with ID 100

var result = from Student s in db.Students
             where s.ID == 100
             select s;
Student student = result.FirstOrDefault(); // return null if not found

To query for students enroll in Engineering major

var result = from Student s in db.Students
             where s.Major == StudentMajor.Engineering
             select s;
Student[] students = result.ToArray();

Note: for insert, update or delete operation, you need to call SubmitChanges() to save the changes to the database. Also note that you need to wrap SubmitChanges() in try catch block, to catch any possible errorĀ raised when saving the changes.

To insert new student record. (reference)

db.Students.InsertOnSubmit(new Student()
{
    Name = "John Doe",
    Biography = "Description about this student",
    Registered = DateTime.UtcNow,
    Major = StudentMajor.Medical
});
db.SubmitChanges();

To update a student record (reference)

var result = from Student s in db.Students
             where s.ID == 100
             select s;
Student student = result.FirstOrDefault();
if (student != null)
{
    student.Name = "James Bond";
    db.SubmitChanges();
}

To delete a student record (reference), for example student with ID 90

var result = from Student s in db.Students
             where s.ID == 100
             select s;
Student student = result.FirstOrDefault();
if (student != null)
{
    db.Students.DeleteOnSubmit(student);
    db.SubmitChanges();
}

To delete bulk record of students, for example student enrolled in ComputerScience major

var result = from Student s in db.Students
             where s.Major == StudentMajor.ComputerScience
             select s;
db.Students.DeleteAllOnSubmit(result.ToList());
db.SubmitChanges();

Database migration

Basic idea of database migration is database schema, which is organized into versions, need to be upgraded or downgraded from previous to current version. Refer to the diagram below, a simple database to store a blog post:

db_migration

On initial release (version 1), the application only have 4 columns – id, title, content & created. When new version come out (version 2), developer added two new columns – updated & cover_img.

For each version change, there must be upgrade() and downgrade() function. upgrade() is to upgrade the schema from previous to current version, while downgrade() is to revert changes to the previous version. Here’s how the upgrade & downgrade SQL queries going to be:

upgrade version 0 to 1

CREATE TABLE IF NOT EXISTS tbl_post (
  id INT NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  created DATETIME,
  PRIMARY KEY (id)
)

downgrade version 1 to 0

DROP TABLE tbl_post

upgrade version 1 to 2

ALTER TABLE tbl_post ADD (
  updated DATETIME,
  cover_img VARCHAR(255) NOT NULL
)

downgrade version 2 to 1

ALTER TABLE tbl_post
  DROP updated,
  DROP cover_img

To implement database versioning to support database migration, developer need to create a table to store information about the current database version that is in effect. It could be as simple as this table:

migration_tbl