In my last blog post I have explained you how to install and setup SQLite Database in your Windows Phone app Project. In this post I will explain you how to create or delete a database and how to perform different operations like insertion, deletion & searching etc.
We will be working on the same project that we have created in last post. So if you have missed that post you can view it from the link given below. And follow the steps given below.
SQLite Database in Windows phone app – Part 1 (Installation and Setup)
Step 1: Create a new folder called Database in your project and add a class to it having the name Schema.cs.
Step 2: Now create a table called Students in form of class that we need to include in our Records.sqlite database.
The class having the name Students with all the getter and setter methods have been given below.
Note: One property needs to act as primary key and its value should be unique for each record. In this example Id of student acts as primary key.
Step 3: So it’s time to perform all SQLite operations. So I thought to make a single helperclass called ‘DatabaseHelper.cs’ in Database folder and handle all the SQLite operations with this helperclass.
Step 4: This is the main step where we will be writing all the SQLite operations in the helperclass.
1. Creating a Database While creating a database we need to first check whether it exists or not. If not then create a new database. The code for the following is given below.
Note: You can create more than one table in a database. In this post we have just created one table to keep things simple.
2. Deleting a Database
Note that database is created as a storage file in Windows Phone app so to delete it you only need to delete the following storage file. The code for the following is given below.
3. Adding a new record The best practice while adding a new record in the database is to first check whether database with following name exists previously or not and then insert a new record. The code for the following is given below.
4. Updating an existing record
To update a record in database we first need to query that object from the database and then only we can make changes to it. The code for the following is given below.
Note: In this example we are updating the marks of the student with given id using the search query.
5. Deleting an existing record Similar to updating an existing record you first need to query the record from the database and then perform the deletion operation. The code for the following is given below.
6. Selecting all students from the database
The code for fetching all the records from student table is given below.
Note: The full code for the DatabaseHelper.cs is given in this link.
Step 5: Now after writing the code for database operations the next step is to create the UI for the above and bind the data to it. On MainPage.xaml create 4 different buttons as shown in the figure below.
The code for the Design view is as following
Different buttons are
1. Create Button– This button is used to create a new database. The code for the click event handler is as given below
2. Delete Button– This button is used to delete the current database if exists. The code for click event handler is given below.
3. Add a new record Button– This button navigates to new page. Where we can add a new record into the students table. The code for click event handler is given below.
4. Display all records Button– This button navigates to a new page. Where we can view all the records and delete them from the table. The code for click event handler is given below.
Step 6: Create two new pages having the name AddRecord.xaml and Display.Xaml in your project.
In this page we insert a new record into the table
The code for xaml page is given below
When use clicks on the insert button the record is inserted into the database. The code for the click event handler is given below.
In this page we display all the records that are present in the database as shown in the figure below.
The code for the xaml is
And the code for the Display.xaml.cs is as following
Note: In both of the pages we have made use of the functions defined in DatabaseHelper.cs.
Step 7: Add the following code for back button press in App.xaml.cs to control the navigation from one page to another.
If you have any doubts related to this post you can download the full source code of the project from the link given below.
In my next blog post I shall explain you how to explore SQLite Database using IsoStoreExplorer tool.