SQLite Database in Windows 8 RT application Part-2

Hi Devs
This is the Part 2 of the post
You can find Part 1 Here

This post will have all the Coding required
Here I'll show you how to make a simple Employee Management system.

Hope you have the Project created in Part 1,
First goto the Project Properties->Build and Make the Platform target to x86 or x64 because Any CPU is not supported yet.

This is the UI of the sample application.

This will be the structure of the solution,

 Here I'm having two classes to manage the database
First the Employee Class
The concept is just like SQL CE you have to create a class to represent a table and the use LINQ to work with the database.
This class will be representation of the Employee table, the explanation is given as the comment in the code.

public class Employee
{
    /// <summary>
    /// The ID of the Employee
    /// Used as the primary key
    /// Automatically incremented
    /// </summary>
    [PrimaryKey, AutoIncrement]
    public int ID { get; set; }

    /// <summary>
    /// Name of the Employee
    /// </summary>
    [MaxLength(30)]
    public string Name { get; set; }

    /// <summary>
    /// Address of the Employee
    /// </summary>
    [MaxLength(30)]
    public string Address { get; set; }
}

Now the Employee Manager class which handles the interaction with the database

public class EmployeeManager
{
    /// <summary>
    /// The Connection to SQLite database file
    /// </summary>
    private SQLiteConnection connection;

    /// <summary>
    /// The constructor
    /// Creates the connection to the database and creates the table
    /// </summary>
    public EmployeeManager()
    {
        //Windows.Storage.ApplicationData.Current.LocalFolder.Path is used to get the location of the Application
        connection = new SQLiteConnection(Windows.Storage.ApplicationData.Current.LocalFolder.Path+"\\EmployeeDB");
        connection.CreateTable<Employee>();
    }

    /// <summary>
    /// Insert a new employee to the database
    /// </summary>
    /// <param name="employee">The Employee to be inserted</param>
    /// <returns>ID of the inserted employee</returns>
    public int insertEmployee(Employee employee)
    {
        return connection.Insert(employee);
    }

    /// <summary>
    /// Delete and employee from the database
    /// </summary>
    /// <param name="ID">ID of the employee to be deleted</param>
    public int deleteEmployee(int ID)
    {
        return connection.Delete<Employee>(ID);
    }

    /// <summary>
    /// Update the details of an employee
    /// </summary>
    /// <param name="employee">New Employee object</param>
    public int updateEmployees(Employee employee)
    {
        return connection.Update(employee);
    }

    /// <summary>
    /// Get a employee object
    /// </summary>
    /// <param name="ID">ID of the employee</param>
    /// <returns>Employee with the given ID</returns>
    public Employee findEmployee(int ID)
    {
        List<Employee> employees = connection.Table<Employee>().Where(X=>X.ID==ID).ToList();

        if (employees.Count()==1)
        {
            return employees.First();
        }
        else
        {
            return null;
        }
    }

    /// <summary>
    /// Get a list of all the employees in a database
    /// </summary>
    /// <returns></returns>
    public List<Employee> getAllEmployees()
    {
        return connection.Table<Employee>().ToList();
    }

}

Thats it you can just call these methods from the Button click events to use the database.
You can find the UI calls in the sample application

Sample Application

Happy Coding
Please Leave a comment
Guruparan G