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 ...- Article authored by Kunal Chowdhury on .
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.
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.
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:
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));
}
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.
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();
}
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:
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.
Thank you for visiting our website!
We value your engagement and would love to hear your thoughts. Don't forget to leave a comment below to share your feedback, opinions, or questions.
We believe in fostering an interactive and inclusive community, and your comments play a crucial role in creating that environment.
