Wednesday, May 22, 2013

Map App for Windows: Release 1

Map App for Windows is the Modern version of Map App for SharePoint 2013. This version of Map App for Windows is totally a free application. Using this application, you can manage all your favorite geographic locations from a single place. This version supports following features.
  • Get current location. Search locations. 
  • Save favorite locations by simply dragging a pushpin around. 
  • You can provide a location description and even an image. All these information will be displayed on the map tip. 
  • View favorite locations.
Here are some of the screenshots.

Default
Search
Saved Locations
Rich Infobox
I know this might look like a very basic application and even no snapping is supported. Anyway hoping to provide more features with the coming versions. Appreciate your feedback.

Happy Coding.

Regards,
Jaliya

Map App for Windows Privacy Policy

Map App Privacy Policy

The "Map App for Windows" privacy policy ensures the information collected from you via "Map App for Windows" is totally secured. Your privacy is extremely important to me. As a result, I have implemented this Privacy Policy to ensure your information is safe.

Personal Data

This application does not share personal information with third parties nor does it store any information about you. (e.g. your name, address, telephone number or e-mail address). If you have any questions feel free to contact me directly at following email addresses.

     jaliya.udagedara@hotmail.com
     jaliya.udagedara@gmail.com

Thank You.

Sincerely,
Jaliya Udagedara

Wednesday, May 15, 2013

Windows Store App with a SQLite Database

In Windows Store Apps there are variety of ways/methods to store your application data. Examples of such mechanisms would be Web Storage, IndexedDB etc. and even SkyDrive. Today I am going to write about my favorite data storage method in developing Windows Store Apps which is “SQLite”.

SQLite is a relational database management system contained in a small C programming library. SQLite stores the entire database as a single cross-platform file on a host machine. You can examine this file using tools such as SQLite Database Browser which you can download from here. SQLite does not support all the SQL features. Here is a list of features which are not supported in SQLite.

Now let’s see how we can use SQLite in a Windows Store App. I am creating a blank Windows Store App. Now I need to get SQLite binaries for Windows Runtime. You can download Precompiled Binaries for Windows Runtime from here.

0
Precompiled Binaries for Win RT

Once the vsix file has completed installing, then you need to add a reference to "SQLite for Windows Runtime" from your project.
1
Add Reference
One important thing. If you want to publish a app with SQLite to Windows Store, make sure to add reference to Microsoft Visual C++ Runtime package as well. Now after adding the reference, when I compile the project, I am getting a error.
2
Platform Error
To resolve the error here, I am changing the projects target platform to “x64”.
3
Changing Target Plaform
Now I am going to add a wrapper for SQLite which was written using C#. So I can write my code fairly easily. I am going to Nuget and searching online for “sqlite-net” and I am adding it to my project.
4
Adding sqlite-net
Once the package has completed configuring, you can see two new classes (“SQLite.cs” and “SQLiteAsync.cs”) added to your project. Now I am almost done configuring the project for SQLite. Now let's move into writing some codes.

Here I am using the Repository Pattern. Repository Pattern is a software design pattern where the entity classes and the entity functionalities are separated. To my project I am adding following folders,
  • “DataModel” – Stores classes which model tables (entities) inside my SQLite database.
  • “Repository” – Stores classes which has CRUD methods of Entity models.
  • “DataAccess” – Stores classes for database connection.
First I am adding DbConnection.cs  and IDbConnection.cs to “DataAccess” folder.

IDbConnection.cs
using System.Threading.Tasks;
using SQLite;
 
namespace SQLiteModernApp.DataAccess
{
    public interface IDbConnection
    {
        Task InitializeDatabase();
        SQLiteAsyncConnection GetAsyncConnection();
    }
}

DbConnection.cs
using System.IO;
using System.Threading.Tasks;
using SQLite;
using SQLiteModernApp.DataModel;
 
namespace SQLiteModernApp.DataAccess
{
    public class DbConnection : IDbConnection
    {
        string dbPath;
        SQLiteAsyncConnection conn;        

        public DbConnection()
        {
            dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "MyTable.sqlite");
            conn = new SQLiteAsyncConnection(dbPath);
        }

        public async Task InitializeDatabase()
        {
            await conn.CreateTableAsync<Department>();
            await conn.CreateTableAsync<Employee>();
        }

        public SQLiteAsyncConnection GetAsyncConnection()
        {
            return conn;
        }
    }
}

Here I am getting the path of the current application and creating my SQLite database there. Now I am adding two classes to “DataModel” folder which are “Employee” and “Department”.

Department.cs
using SQLite;

namespace SQLiteModernApp.DataModel
{
    [Table("Department")]
    public class Department
    {
        [PrimaryKey]
        public int DepartmentId { get; set; }

        [MaxLength(30)]
        public string DepartmentName { get; set; }
    }
}
Employee.cs
using SQLite;

namespace SQLiteModernApp.DataModel
{
    [Table("Employee")]
    public class Employee
    {
        [PrimaryKey, AutoIncrement]
        public int EmployeeId { get; set; }

        [MaxLength(30)]
        public string FirstName { get; set; }
 
        public string LastName { get; set; }
 
        public string Email { get; set; }

        public int DepartmentId { get; set; }
    }
}

In my “Repository”, I am going to have CRUD operations for above two entities. Here I am only pasting the code for Employee entity.

IEmployeeRepository.cs
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLiteModernApp.DataModel;

namespace SQLiteModernApp.Repository
{
    interface IEmployeeRepository
    {
        Task InsertEmployeeAsync(Employee employee);
        Task UpdateEmployeeAsync(Employee employee);
        Task DeleteEmployeeAsync(Employee employee);
        Task<List<Employee>> SelectAllEmployeesAsync();
        Task<List<Employee>> SelectEmployeesAsync(string query);
    }
}

EmployeeRepository.cs

In my EmployeeRepository class I am implementing the above IEmployeeRepository interface.
using System.Collections.Generic;
using System.Threading.Tasks;
using SQLite;
using SQLiteModernApp.DataAccess;
using SQLiteModernApp.DataModel;

namespace SQLiteModernApp.Repository
{
    public class EmployeeRepository : IEmployeeRepository
    {
        SQLiteAsyncConnection conn;

        public EmployeeRepository(IDbConnection oIDbConnection)
        {
            conn = oIDbConnection.GetAsyncConnection();
        }
 
        public async Task InsertEmployeeAsync(Employee employee)
        {
            await conn.InsertAsync(employee);
        }

        public async Task UpdateEmployeeAsync(Employee employee)
        {
            await conn.UpdateAsync(employee);
        }
 
        public async Task DeleteEmployeeAsync(Employee employee)
        {
            await conn.DeleteAsync(employee);
        }

        public async Task<List<Employee>> SelectAllEmployeesAsync()
        {
            return await conn.Table<Employee>().ToListAsync();
        }
 
        public async Task<List<Employee>> SelectEmployeesAsync(string query)
        {
            return await conn.QueryAsync<Employee>(query);
        }
    }
}
Now I am moving to the UI. In my MainPage.xaml, I have a stack panel which contain controls for user to insert data. And then I have a list view to show data stored in the database.
<Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">
    <StackPanel Name="sPanelEmployee" Orientation="Vertical" Margin="10,11,1040,23">
        <TextBlock Text="First Name" Margin="2 2 2 2" FontSize="14"/>
        <TextBox Name="txtFirstName" Text="{Binding FirstName, Mode=TwoWay}"/>
        <TextBlock Text="Last Name" Margin="2 2 2 2" FontSize="14"/>
        <TextBox Name="txtLastName" Text="{Binding LastName, Mode=TwoWay}"/>
        <TextBlock Text="Email" Margin="2 2 2 2" FontSize="14"/>
        <TextBox Name="txtEmail" Text="{Binding Email, Mode=TwoWay}"/>
        <TextBlock Text="Department" Margin="2 2 2 2" FontSize="14" />
        <ComboBox Name="cboDepartment" ItemsSource="{Binding Department}" DisplayMemberPath="DepartmentName" SelectedValuePath="DepartmentId" SelectedValue="{Binding DepartmentId, Mode=TwoWay}" />
        <StackPanel Orientation="Horizontal" HorizontalAlignment="Right" Margin="0 10 0 0"> 
            <Button x:Name="btnCreate" Content="Create" Click="btnCreate_Click" />
            <Button x:Name="btnUpdate" Content="Update" Click="btnUpdate_Click" />
            <Button x:Name="btnDelete" Content="Delete" Click="btnDelete_Click" />
        </StackPanel>
    </StackPanel>

    <ListView Name="lstViewEmployees" ItemsSource="{Binding}" Margin="399,11,10,10" SelectionChanged="lstViewEmployees_SelectionChanged" >
        <ListView.ItemTemplate>
            <DataTemplate>
                <StackPanel Orientation="Vertical"  Margin="4">
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="Employee Id" Width="300"></TextBlock>
                        <TextBlock Text="{Binding EmployeeId}" ></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="First Name" Width="300"></TextBlock>
                        <TextBlock Text="{Binding FirstName}" ></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="Last Name" Width="300"></TextBlock>
                        <TextBlock Text="{Binding LastName}"></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal">
                        <TextBlock Text="Email" Width="300"></TextBlock>
                        <TextBlock Text="{Binding Email}" ></TextBlock>
                    </StackPanel>
                    <StackPanel Orientation="Horizontal" >
                        <TextBlock Text="Department" Width="300"></TextBlock>
                        <TextBlock Text="{Binding DepartmentId}" ></TextBlock>
                    </StackPanel>
                </StackPanel>
            </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
</Grid>
Now in my code behind, first I am initializing the database.
DepartmentRepository oDepartmentRepository;
EmployeeRepository oEmployeeRepository;
 
private async Task InitializeDatabase()
{
    DbConnection oDbConnection = new DbConnection();
    await oDbConnection.InitializeDatabase();
    oDepartmentRepository = new DepartmentRepository(oDbConnection);
    oEmployeeRepository = new EmployeeRepository(oDbConnection);
}
Now I can call methods in my Repository classes, through these objects. Here are some screenshots of the final application.

5
Add New Employee
6
View/Update/Delete Employee
I am uploading the full sample to my SkyDrive. Do check it out.

Appreciate your feedback.

Happy Coding.

Regards,
Jaliya