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.
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.
- 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
- 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
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