Windows Phone 7 (Mango) Tutorial - 24 - Local Database Support, CRUD operation with Demo



In the last two chapters of the tutorial series, we discussed about the local database support in Windows Phone 7 (Mango). We learnt how to create a DataContext class from existing SQL CE database and also we learnt about the issues that arises once we add the class file in the project to build. We learnt the steps to resolve those issues too.

 

In this article, we are going to discuss about the CRUD operations that we can do using the DataContext class with a small application. We will learn how to create and delete a database, we will also learn how to insert or fetch records. After reading this tutorial you will also be able to do other operations like edit, search etc.

 

Index - Windows Phone 7 (Mango) Tutorial

 

Using Helper class to do DB Operations

We will create a DBHelper class in this step and write down all the required DB operations using static methods. First of all we need to create the connection string for the database. Generally we will store the local database to the IsolatedStorage. To do this, our connection string will look as:  "isostore:/DBNAME.sdf".

 

We will add method named "CreateDatabase()" and inside that method, we will create the instance of the class called NorthwindDataContext and from the instance we will call CreateDatabase() method which is already present in the context.

 

Similarly, we will create another method which will delete the database on user's request. If the database is exist, it will delete it.

 

Have a look into the below code implementation for further details:

 

 
using System.Collections.Generic;
using System.Linq;
 
namespace LocalDBDemo
{
    public class DBHelper
    {
        private const string ConnectionString = @"isostore:/Northwind.sdf";
 
        public static void CreateDatabase()
        {
            using (var context = new NorthwindDataContext(ConnectionString))
            {
                if (!context.DatabaseExists())
                {
                    // create database if it does not exist
                    context.CreateDatabase();
                }
            }
        }
 
        public static void DeleteDatabase()
        {
            using (var context = new NorthwindDataContext(ConnectionString))
            {
                if (context.DatabaseExists())
                {
                    // delete database if it exist
                    context.DeleteDatabase();
                }
            }
        }
 
        public static void AddEmployee(Employee employee)
        {
            using (var context = new NorthwindDataContext(ConnectionString))
            {
                if (context.DatabaseExists())
                {
                    context.Employees.InsertOnSubmit(employee);
                    context.SubmitChanges();
                }
            }
        }
 
        public static IList<Employee> GetEmployees()
        {
            IList<Employee> employees;
            using (var context = new NorthwindDataContext(ConnectionString))
            {
                employees = (from emp in context.Employees select emp).ToList();
            }
 
            return employees;
        }
    }
}

 

Similar way we will create an AddEmployee() method passing the Employee object, which will insert the object to the database. If database exists, we will add it to the table. The context class has Employees table and each table consists of a method called "InsertOnSubmit()". This actually inserts the object to the DB.

 

GetEmployee() method retrieves the employee details from the database and set it back to the client. Similarly you can search for employees or update employee records using the LINQ queries.

 

Create MainPage UI for different DB Operation

To do DB operations, we need to create the Main Page UI from where user will be able to create database, delete database, add employee and view employees. To do this, we will add some buttons in the XAML page. Here is the code snippet of the UI for your reference:

 
<!--ContentPanel - place additional content here-->
<StackPanel x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0" Orientation="Vertical">
    <Button Width="300" Height="75" Content="Create Database" Click="CreateDatabase"/>
    <Button Width="300" Height="75" Content="Delete Database" Click="DeleteDatabase"/>
    <Line Height="20"/>
    <Button Width="300" Height="75" Content="Add Employee" Click="AddEmployee"/>
    <Button Width="300" Height="75" Content="View Employees" Click="ViewEmployees"/>
</StackPanel>

 

If you run the application with this XAML, it will look as below:

WP7.1 LocalDBDemo - Create Main Page UI

 

 

As the button says, create database button will directly call the DBHelper.CreateDatabase(), delete database will directly call the DBHelper.DeleteDatabase() method. Once user clicks the add button or view button, the main page will navigate to the respective page.

 

Sharing the whole code of the implementation here:

 

 
private void CreateDatabase(object sender, RoutedEventArgs e)
{
    DBHelper.CreateDatabase();
}
 
private void DeleteDatabase(object sender, RoutedEventArgs e)
{
    DBHelper.DeleteDatabase();
}
 
private void AddEmployee(object sender, RoutedEventArgs e)
{
    NavigationService.Navigate(new Uri("/AddEmployee.xaml", UriKind.RelativeOrAbsolute));
}
 
private void ViewEmployees(object sender, RoutedEventArgs e)
{
    NavigationService.Navigate(new Uri("/ViewEmployees.xaml", UriKind.RelativeOrAbsolute));
}

 

Implement Add Employee Operation

First we need to create the XAML page. Add a new portrait page with the following XAML code which replaces the actual Content Panel Find the code below:

 
<!--ContentPanel - place additional content here-->
<StackPanel x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
    <TextBox x:Name="txtFirstname" Width="500" Height="75" Text="Firstname"/>
    <TextBox x:Name="txtLastname" Width="500" Height="75" Text="Lastname"/>
    <Button Content="Add Employee" Width="300" Height="75" Click="AddNewEmployee"/>
</StackPanel>

 

This will render the following UI in the screen which will take Firstname and Lastname from the user and once the user clicks the button called "Add Employee", it will submit the record to the database.

 

WP7.1 LocalDBDemo - Create Add Page UI

 

 

In the code behind, we will implement the following code to submit the record. We will create the employee object from the entered values and pass it to the AddEmployee method of the helper class. Next we will return back to the main page.

 

 
private void AddNewEmployee(object sender, RoutedEventArgs e)
{
    var employee = new Employee 
                    { 
                        FirstName = txtFirstname.Text, 
                        LastName = txtLastname.Text 
                    };
    DBHelper.AddEmployee(employee);
 
    NavigationService.GoBack();
}

 

 

Implement View Employees Operation

This is the last step we need to implement here i.e. the view which will fetch the records of employees from the database and place in the page inside a ListBox. Let's add a new portrait page into our project and name it as ViewEmployees.xaml.

 

Add the below code in the XAML page by replacing the ContentPanel:

 
<!--ContentPanel - place additional content here-->
<StackPanel x:Name="ContentPanel" Grid.Row="1" Margin="12,0,12,0">
    <ListBox x:Name="lstEmployees" Height="500">
        <ListBox.ItemTemplate>
            <DataTemplate>
                <StackPanel Orientation="Horizontal">
                    <TextBlock FontSize="40">
                        <Run Text="{Binding FirstName}"/>
                        <Run Text="{Binding LastName}"/>
                    </TextBlock>
                </StackPanel>
            </DataTemplate>
        </ListBox.ItemTemplate>
    </ListBox>
    <Button Content="Back" Width="300" Height="75" Click="Back"/>
</StackPanel>

 

The data template of the ListBox has a TextBlock which shows both the first name and last name to the end user. The code behind implementation of the same for fetching the records is shown below:

 
void ViewEmployees_Loaded(object sender, RoutedEventArgs e)
{
    lstEmployees.ItemsSource = DBHelper.GetEmployees();
}

 

Once we enter into the view from the MainPage, we will see the entered records in the UI as shown below:

 

WP7.1 LocalDBDemo - Create View Page UI

 

 

Hope this chapter was helpful to learn the local database support feature in Windows Phone 7.1 Mango. Remember that, this post was done based on the public beta 1 version. In beta 2 or in future RTM version, there may be some changes. Keep learning, keep reading my blog posts. Cheers.

7 comments

  1. Have you had a look at SQL Server Compact Toolbox, it can generate the DataContext for you + add Indexes, http://sqlcetoolbox.codeplex.com

    ReplyDelete
  2. Hi Kunal ,
    This is very nice article. I appreciate your good work& thanks a lot for the same.

    ReplyDelete
  3. It isnt working ..when i add an employee details and click on view nothing gets displayed..cant figure out where exactly did i screw up

    ReplyDelete
  4. Nice and easy post.. but can u tell me how to test this example on emulator? cz running this one on emulator gives an error message!!

    ReplyDelete
  5. Hey nice post but the problem in my case is i have a db created beforehand which is also populated with data and tables. So how can i use the same db for my app (developed in sql compact CE). Moreover how can i use the db in emulator ??

    ReplyDelete
  6. pls put your source code..its abit hard to understand the code by just reading it..thank you!!!!

    ReplyDelete
  7. Nice Post. This helped lot while creating my first mobile App

    ReplyDelete


 
© 2008-2014 Kunal-Chowdhury.com | Designed by Kunal Chowdhury
Back to top