Using database in Windows Phone app

Ahad, 11 Mei 2014, 12:33 am0

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();