Working with local database in Windows Phone 8- Part II

This is the third post of the Windows Phone’s local database series and I will discuss updating and deleting data from a database. Here are the links to other posts in this series

Deleting data from a table

Follow these steps to delete data from a local database’s table

Step 1. Create a new class DatabaseDelete.cs

This class will contain all the methods needed in the project to delete records from any table. It is always beneficial to follow a modular approach keeping similar codes in same file.

 

 

Step 2. Add functions DeleteAllUsers and DeleteUser(string id)

We are going to create two separate functions for two different purposes. The function DeleteAllUsers() will delete all records from User_details table and DeleteUser function accepts a parameter id and deletes a single user whose id matches the parameter passed.

 

Note

  • The function DeleteAllUsers call DeleteAllOnSubmit to execute the delete operation. This function accepts a list of records and deletes all the records in that list

    If ever you need to delete duplicate records from a table then this article may prove to be useful. http://www.maskaravivek.com/blog/c/how-to-delete-duplicates-records-from-a-table-using-linq-in-c/

  • The function DeleteUser calls DeleteOnSubmit accepts a single record and deletes it.
  • In both case the changes are saved when SubmitChanges() function is called

We are done with the back end work. Let us work with the UI to use both these functions

Step 3. All a `Delete all users` button in MainPage.xaml to delete all records

<Button Content=”Delete all users” Height=”80″ Click=”Button_Click2″/>

 


 

Step 4. Add the click event handler for this button and put the code to delete all users

On the click event of this button we will insert the code to delete all users by calling DeleteAllUsers() function

 

Step 5. Modify the data template of listbox on AllUsers.xaml page to delete a single user

Now to delete a single user let us move over to AllUsers page. We will eidt the data template of the listbox to add a delete button to it. Notice the change in the XAML code where we have added a delete button and placed the id of that user in its Tag attribute.

 

 

Step 6. Add the click event handler code for this button

In the click event handler we first fetch the id from the tag element of the button which sent the click event and then call the code to delete the record from the table. Then we update the list to reflect the changes

 

The code is self-explanatory and it accomplishes the desired result.

 

Updating records in a table

Updating records is very similar to adding records to a table.

Step 1. Add a new class DatabaseUpdate.cs in your project

Continuing with our modular approach we create a new class for all database updates. Unlike delete operation there’s no function to update all records. You need to iterate manually through all records to do so.

 


 

Step 2. Add UpdateUsers function to this class

UpdateUsers function accepts id, name and email id and updates the row whose id matches in the database. It’s not necessary to update all columns in the table. You could update all columns or even just one.

Note

  • We first match the id passed in the parameter with the ID in the database
  • We take the first matched record in the entityToUpdate variable
  • We simply assign the new values and submit the changes to update the database
  • The database is not updated until we call the SubmitChanges() function

Now let us try updating all records. We will add an update function that will convert all the user_name values to lower case

Note

  • We store the fetched records in a IList
  • We then use a foreach iterator to go through all the records and update the user_name by replacing it with its lower case equivalent
  • Notice that we have used the SubmitChanges() after the foreach loop. This lets us submit all the changes at once

Now let us move over to the design part as the backend work is done.

Step 3. Add a button on MainPage.xaml to update all user names to its lower case equivalent

Again we update the MainPage.xaml to add one more button to it.

<Button Content=”Update all user names to lower case” Height=”80″ Click=”Button_Click3″/>

 

 

Step 4. Add the click event handler for the update button

The code behind is simple as you have already done all the hard work. Just call the UpdateUserToLower() function and you are done

 

Step 5. Move over to AllUsers.xaml and add an update button in its ListBox data template

The data template is already clumsily handled but that’s not a big concern right now. So go ahead and add an update button to its data template.

 

Step 6. Add a new page Update.xaml and put two text boxes in it for name and email id respectively and an Update button

We will be needing this page to update the record once it is selected. Add two text boxes and a button in it

 

 

Step 7. Add the LayoutRoot_Loaded event handler to get the id passed from AllUsers.xaml page

When an update button is clicked on AllUsers.xaml page we pass the id of the clicked button while navigating to Update.xaml. We use the navigation context to fetch the id when the layout root is loaded. We already have variables id, nam and email_id global to this page. So in the LayoutRoot_Loaded event handler just add the following line of code

id=NavigationContext.QueryString[“id”];

 

Step 8. Add the Button_Click event handler to call the UpdateUser function

On Update button click we update the user by calling the UpdateUser function which we created earlier in DatabaseUpdate.cs class


 

Step 9. Move back to AllUsers.xaml and add the code behind to handler the Update button click event

We just have to navigate to the Update.xaml page passing the id with the navigation Uri.


 

Now you are done with the code. Let run the app in the emulator to see if things work as intended.

 

I can’t show you all the screen shots but things do work. Try implementing it in your app and sound off below if you face any difficulties. In the next part of this series I will talk about updating database schema to add columns to existing tables, adding new tables or adding attributes to tables. Also we will see how one can move a referential database from app resources to isolated storage.

Get the source code here

Download full project Database-Part-2.zip

UPDATE: More posts have been added to this series. Here are the links to other posts in this series

Vivek Maskara

I am pursuing BTech in Software Engineering from Delhi Technological University. I develop apps, create websites and blog about my app experiences.

  • Eric

    Thanks for this great article. However, the update function does not work. It only calls a blank Update.xaml.

    • Thanks for pointing that out. We have updated the source code file to fix the bug. We forgot to assign the textbox’s values to the parameters being passed.

  • fateme

    How can I implement tree view control in my windows phone app? I want to fetch data from data base and show that in tree view.

  • Venkatachalapathi G

    Hi, first of all thanks for the good articles… it helps a lot..
    i am getting the exception “row not found or change”(changeConflictException). what could be the reason?

    • Are you getting this error during the update operation? And did you put the code to create database in your app?

      • Venkatachalapathi G

        i added [Column(UpdateCheck = UpdateCheck.Never)] for the attributes while declaring my table.. it solved my problem.

  • Gert

    I can’t find updated source code could you please repost link.
    On all you source code there is still the same error in update user. How to forgot to assign the textbox’s values into update textbox.
    THX for great article

Read more:
How to create your first Windows Phone app
How to change the App name and Tile name of your Windows phone app
Data binding in Windows Phone app – One way binding
Close