Silverlight Attendance Demo Using Sterling Silverlight Database

• Download source code - 4 MB

A Silverlight Database

• Live example: http://silverlight.adefwebserver.com/simpleattendance
• Also see: Silverlight Super Tabs Interface (Using View Model / MVVM)

A feature of Silverlight is Isolated Storage. It allows your Silverlight program to store practically any type of data, or objects, locally on the user's computer. Basically, it's like a very large web browser cookie. Some call it 'Big Cookie'.

The reason you would want to do this is that it is faster for the user. It is also very useful with Windows Phone 7 applications. The problem with Isolated Storage is, interacting with it is very rudimentary. This is where the Sterling Isolated Storage Database with LINQ for Silverlight and Windows Phone 7 project is able to help. It allows us to store and retrieve data in Isolated Storage (almost) as if it were in a normal relational database.

Sterling Isolated Storage Database

The Sterling Isolated Storage Database with LINQ for Silverlight and Windows Phone 7 project, created by Jeremy Likness, provides the following features:

• It's free - What more can you say?
• It provides full source code - Therefore no risk to your project.
• Excellent documentation - This is so important with something this complex. It also comes with sample, Silverlight out of browser, and Windows Phone 7 projects.
• It has very fast performance and has Indexes - The point of using Isolated Storage is speed (and convenience). The sample project in the download of the project on the CodePlex site shows how the code can handle very large amounts of data quickly.
• Small and lightweight - This is both in size and features. It has a lot of features, but it only handles reading and writing data, and anything related to those objectives. You don't have to use View Model, MVVM, or a particular Design Pattern.

Note, there are other free Open Source Silverlight Object Databases that you can find at this link.

Using an Object Database

Working with Sterling is very straightforward. You first define your tables. Next, you save data (objects) to your tables like this:

SterlingService.Current.Database.Save(
        new Attendance()
        {
            AttendanceKey = "1-23324353453534",
            EnrollmentId = 1,
            AttendanceDate = new DateTime(2010, 1, 1),
            AttendanceStatus = "P"
        });

To update an object, simply save the object with a key of an existing object.

Now, to get your data, you have to adjust your way of thinking when using an object database such as Sterling. You can't just define an object with fields and then query on any field you want to get data.

You have to either get your data using the key for the entity:

Attendance objAttendanceRecord = 
  SterlingService.Current.Database.Load<Attendance>("1-23324353453534");

or query it using a pre-defined Index you create, like "ATTENDANCE_ENROLLMENTIDANDDATE":

var result = (from Attendance in SterlingService.Current.Database.Query<Attendance, 
   int, DateTime, string>(AttendanceDatabase.ATTENDANCE_ENROLLMENTIDANDDATE)
   where Attendance.Index.Item1 == EnrollmentId
   where Attendance.Index.Item2 == AttendanceDate
   select Attendance).FirstOrDefault();

The Sample Application: Simple Attendance

To demonstrate the functionality of Sterling, I decided to demonstrate using a small but non-trivial example. This attendance application is small, but it is not without its complications. You are able to choose a day, and you will only see the students enrolled on that day. When you enter an attendance status, it is saved immediately.

In some cases, you are creating new attendance records, and in some cases, you are updating attendance records. The program handles everything seamlessly, and moves as fast as you can click your mouse.

The application primarily consists of a Student class, an Enrollment class, and an Attendance class. Sterling does not have foreign keys, but as you will see later, we will be able to create Joins between entities.

Set-Up Your Project

The documentation that comes with Sterling is top-notch so you will want to read it. This article is going to give you a general idea of the steps required to use Sterling in your project.

The first step is to reference the Sterling project, or include its assemblies in your project (you can download an installer for the assemblies from its download page).

Next, you create a class that inherits from BaseDatabaseInstance. You then define your tables and indexes:

protected override List<ITableDefinition> _RegisterTables()
{
    return new List<ITableDefinition>
    {
        CreateTableDefinition<Student, int>(fg => fg.StudentId).WithIndex<Student, 
            string, int>(STUDENT_NAME, fg => fg.Name),
        CreateTableDefinition<Enrollment, 
            int>(fd => fd.EnrollmentId).WithIndex<Enrollment, 
            int, int>(ENROLLMENT_ENROLLMENTID, fd =>fd.EnrollmentId).WithIndex<Enrollment, 
            DateTime, int>(ENROLLMENT_STARTDATE, fd =>fd.StartDate).WithIndex<Enrollment, 
            DateTime, int>(ENROLLMENT_STOPDATE, fd =>fd.StopDate),
        CreateTableDefinition<Attendance, 
            string>(fd => fd.AttendanceKey).WithIndex<Attendance, 
            int, DateTime, string>(ATTENDANCE_ENROLLMENTIDANDDATE,
            fd =>
            Tuple.Create(fd.EnrollmentId, fd.AttendanceDate)),
    };
}

You can copy and paste a SterlingService class into your project and simply change one line to register the database class you just created (in this example, AttendanceDatabase):

public void Starting()
{
    if (DesignerProperties.IsInDesignTool) return;

    _engine.Activate();

    // Put the type of the Database Class after RegisterDatabase<
    Database = _engine.SterlingDatabase.RegisterDatabase<AttendanceDatabase>();
}

You then add the following lines to your app.xaml page to start the service:

<Application.ApplicationLifetimeObjects>
    <SimpleAttendance:SterlingService/>
</Application.ApplicationLifetimeObjects>

I discovered that if someone disables their Isolated Storage, the app won't work (people have the right to disable Isolated Storage the same way they can disable web browser cookies), so I include this in the app.xaml.cs file:

public App()
{
    this.Startup += this.Application_Startup;
    UnhandledException += Application_UnhandledException;

    try
    {
        // Test for Isolated Storage
        using (IsolatedStorageFile oStore =
                IsolatedStorageFile.GetUserStoreForApplication())
        {
            InitializeComponent();
        }
    }
    catch (IsolatedStorageException)
    {
        MessageBox.Show("You need to right-click and " + 
                        "enable Isolated Storage to use this application");
    }
}

That it, you're all set up!

Give Me Some Sample Data!

This sample application uses the View Model (MVVM) Design Pattern. The first thing it does when it loads is that a InvokeCommandAction Behavior (that is set to trigger when the User Control has "Loaded") calls the LoadDatabaseCommand (ICommand).

The LoadData method calls the SetupData method if it detects that the Student database table is empty:

public void LoadData()
{
    bool hasKeys = false;
    foreach (var item in SterlingService.Current.Database.Query<Student, int>())
    {
        hasKeys = true;
        break;
    }

    if (!hasKeys)
    {
        SetupData();
    }

SetupData creates all the Students and their Enrollments. It only creates one sample Attendance record:

private void SetupData()
{
    // Students
    var StudentData = new List<Student>()
    {
        new Student() { StudentId = 1 , Name = "Student One" },
        new Student() { StudentId = 2 , Name = "Student Two" },
        new Student() { StudentId = 3 , Name = "Student Three" },
        new Student() { StudentId = 4 , Name = "Student Four" },
        new Student() { StudentId = 5 , Name = "Student Five" },
        new Student() { StudentId = 6 , Name = "Student Six" },
        new Student() { StudentId = 7 , Name = "Student Seven" },
        new Student() { StudentId = 8 , Name = "Student Eight" },
        new Student() { StudentId = 9 , Name = "Student Nine" },
        new Student() { StudentId = 10 , Name = "Student Ten" },
    };

    foreach (var item in StudentData)
    {
        SterlingService.Current.Database.Save(item);
    }

    // Enrollments
    var EnrollmentData = new List<Enrollment>()
    {
        new Enrollment() {EnrollmentId = 1, StudentId = 1, 
            StartDate = new DateTime(2010,1,1), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 2, StudentId = 2, 
            StartDate = new DateTime(2010,1,1), 
            StopDate = new DateTime(2010,2,11) },
        new Enrollment() {EnrollmentId = 3, StudentId = 3, 
            StartDate = new DateTime(2010,1,3), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 4, StudentId = 4, 
            StartDate = new DateTime(2010,1,3), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 5, StudentId = 5, 
            StartDate = new DateTime(2010,1,10), 
            StopDate = new DateTime(2010,2,15) },
        new Enrollment() {EnrollmentId = 6, StudentId = 6, 
            StartDate = new DateTime(2010,1,3), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 7, StudentId = 7, 
            StartDate = new DateTime(2010,1,22), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 8, StudentId = 8, 
            StartDate = new DateTime(2010,1,1), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 9, StudentId = 9, 
            StartDate = new DateTime(2010,1,1), 
            StopDate = new DateTime(2010,2,1) },
        new Enrollment() {EnrollmentId = 10, StudentId = 10, 
            StartDate = new DateTime(2010,1,1), 
            StopDate = new DateTime(2010,2,1) }
    };

    foreach (var item in EnrollmentData)
    {
        SterlingService.Current.Database.Save(item);
    }

    // Sample Attendance
    SterlingService.Current.Database.Save(new Attendance()
    {
        AttendanceKey = String.Format("{0}-{1}", "1", 
                        new DateTime(2010, 1, 1).Ticks.ToString()),
        EnrollmentId = 1,
        AttendanceDate = new DateTime(2010, 1, 1),
        AttendanceStatus = "P"
    });
}

Note, with the Attendance record, we do not simply use an integer as a counter field. We create a key that consists of the Enrollment and the Date expressed in Ticks (String.Format("{0}-{1}", "1", new DateTime(2010, 1, 1).Ticks.ToString())). This is the true key because we never want an Attendance record for the same Enrollment on the same day in the database.

Back in the LoadData method, we load the contents of the Student and Enrollment "tables" into public collections in our View Model. This will allow us to easily query the data using LINQ to Objects.

foreach (var item in SterlingService.Current.Database.Query<Student, int>())
{
    Students.Add(item.LazyValue.Value);
}

foreach (var item in SterlingService.Current.Database.Query<Enrollment, int>())
{
    Enrollments.Add(item.LazyValue.Value);
}

IsDataLoaded = true;
GetAttendanceForDay();

However, we will not load the Attendance records, because even in a small demo like this, there can be a lot of Attendance records. We retrieve the Students who are Enrolled for the selected day using this code:

private void GetAttendanceForDay()
{
    colAttendanceForDay.Clear();

    var result = from Enrollment in Enrollments
                    from Student in Students
                    where Enrollment.StudentId == Student.StudentId
                    where Enrollment.StartDate >= AttendanceDate
                    where Enrollment.StopDate <= AttendanceDate
                    select new AttendanceForDay
                    {
                        AttendanceKey = String.Format("{0}-{1}", 
                          Enrollment.EnrollmentId, AttendanceDate.Ticks.ToString()),
                          AttendanceStatus = 
                          GetAttendanceStatus(Enrollment.EnrollmentId, AttendanceDate),
                          EnrollmentId = Enrollment.EnrollmentId,
                          Name = Student.Name,
                          StudentId = Student.StudentId
                    };

    foreach (var item in result)
    {
        // Add an instance of this View Model to the object
        // This will allow us to get an instance of the View Model
        // to raise events such as the UpdateAttendanceCommand
        item.objMainPageModel = this;
        colAttendanceForDay.Add(item);
    }
}

That query calls this method that queries against the Index we defined for the table (ATTENDANCE_ENROLLMENTIDANDDATE), to get the Attendance status:

private static string GetAttendanceStatus(int EnrollmentId, DateTime AttendanceDate)
{
    var result = (from Attendance in SterlingService.Current.Database.Query<Attendance, 
                  int, DateTime, string>(AttendanceDatabase.ATTENDANCE_ENROLLMENTIDANDDATE)
                  where Attendance.Index.Item1 == EnrollmentId
                  where Attendance.Index.Item2 == AttendanceDate
                  select Attendance).FirstOrDefault();

    if (result != null)
    {
        return result.LazyValue.Value.AttendanceStatus;
    }
    else
    {
        return "";
    }

When we need Attendance data, we only pull it directly out of the Sterling database one record at a time. However, it is very fast because the data is sitting on the user's computer,.

A Note About the Radio Buttons

The radio buttons are all bound to the same field (AttendanceStatus). They each use two Value Converters. One that creates a unique name for their GroupName (this is required when using radio buttons with View Model (MVVM)), and one that binds them to a single possible value (such as "P" for Present).

In the binding, we specify the Value Converter, and we specify a Converter parameter. Each radio button uses a different parameter. Here is the Value Converter for the IsChecked field:

public class AttendanceConverter : IValueConverter
{
    #region Methods
    public object Convert(object value, Type targetType, object parameter, 
           System.Globalization.CultureInfo culture)
    {
        if (value == null || parameter == null)
        {
            return value;
        }

        return value.ToString() == parameter.ToString();
    }
    public object ConvertBack(object value, Type targetType, object parameter, 
           System.Globalization.CultureInfo culture)
    {
        if (value == null || parameter == null)
        {
            return value;
        }

        return (String)parameter;
    }
    #endregion Methods
}

Also note that the DataGrid has to be set to ReadOnly to prevent binding errors if a user clicks on the DataGrid but not on a radio button (more than one radio button could then be selected on a single row).

A Behavior on each button updates the Attendance by calling the following ICommand:

#region UpdateAttendanceCommand
public ICommand UpdateAttendanceCommand { get; set; }
public void UpdateAttendance(object param)
{
    AttendanceForDay objAttendanceForDay = (AttendanceForDay)param;

    // Get the Attendnace record if there is one
    Attendance objAttendanceRecord = 
        SterlingService.Current.Database.Load<Attendance>(
        objAttendanceForDay.AttendanceKey);

    if (objAttendanceRecord == null) // New Record
    {
        // If Attendance Record was not found create a new record
        Attendance NewAttendanceRecord = new Attendance();
        NewAttendanceRecord.AttendanceKey = objAttendanceForDay.AttendanceKey;
        NewAttendanceRecord.AttendanceDate = AttendanceDate;
        NewAttendanceRecord.EnrollmentId = objAttendanceForDay.EnrollmentId;
        NewAttendanceRecord.AttendanceStatus = objAttendanceForDay.AttendanceStatus;
        SterlingService.Current.Database.Save(NewAttendanceRecord);
        SterlingService.Current.Database.Flush();
    }
    else // Update Record
    {
        // Set Attendanace status and save the record
        objAttendanceRecord.AttendanceStatus = objAttendanceForDay.AttendanceStatus;
        SterlingService.Current.Database.Save(objAttendanceRecord);
        SterlingService.Current.Database.Flush();
    }
}

A Powerful Tool Can Save Time and Reduce Bugs

A good use for a database such as Sterling would allow a user to enter attendance, and then hit a button to have it transmitted to a central server. Any conflicts could be resolved in a secondary step. Perhaps a screen could pop up showing any conflicts and allow the user to decide if they want to override a record. The nice thing is that the application would move very fast, and a user would never "time out". If their computer lost power, they would not lose any records.

Applications like this show the real power of Silverlight for Line Of Business (LOB) applications.