In my last post I gave an introduction to local databases in Windows Phone. You can have a look at it again to refresh things.
I will start from where I left in the last post so do have a look.
Creating a database
Now that you have added reference to
System.Data.Linq, you can create a local database in your app. The tables of the database can be created anywhere as long as it is accessible globally in the app. Still I would recommend that make a separate class DB.cs for your tables.
Step 1. Add a new class DB.cs
Right click on your project in solution explorer and choose to add a new item. Choose a class in the dialog box that opens up and name it DB.cs
Step 2. Add the namespace System.Data.Linq.Mapping
Remove the class DB that was created in the file DB.cs when you added the class. Also add the following reference in your file.
Step 3. Creating a table
Now our first task is to create a database which is used locally isolated to the application. Since we are going to use the LINQ to SQL data context as a bridge between the local database and the data context we need to create 2 classes as shown below. The screen shot also shows a snapshot of a table whose code is given below. Add this table in DB.cs.
Note the following
- The table name is
User_detailswhich is just below the declaration for the table. A table can be visualized as a class with attributes which can be imagined as data members.
- The columns are added in a similar fashion as data members are added in a class. The data type of the column is defined along with the column name.
- Notice that column
IDis defined as the primary key for the table.
- The table has three columns,
Next you need to define the
User_details which is basically used as the database schema to create an instance, copy the code from the below code block as shown in the screen below.
DBConnectionString is used to identify the database in the isolated storage. An app could contain multiple databases and you should be able to identify each of them uniquely so each one is given a unique name. Here we will be using a single database so you don’t need to worry about the naming.
In our app the table
User_details will be referred by the name
Users. For each table of the database you need to create its data context. Here’s a screen shot from another app showing how the data context was defined for its database which contains multiple tables.
Step 4. Add the code to create database if it doesn’t exist when the app is opened
InitializeComponent in Mainpage.xaml add the following code which checks if a database exists and creates one if doesn’t.
Adding records to the database
Now that you have added a table in your database and set up the
DataContext for connecting to that database, you can now add records to your table. Again it would be convenient for you if you create a separate class DatabaseAdd.cs for adding any record to your database. Here’s how you could add records to a table
Step 1. Add a new class DatabaseAdd.cs in your project
We will create a new class DatabaseAdd.cs for adding any record to our database
Step 2. Create a function AddUser in DatabaseAdd.cs
AddUser takes name and email id as parameters and adds a record to
User_details table. It uses the
UserDataContext to connect to the database and creates a new instance of
User_details class (table) and lastly inserts the record.
Note the following
UserDataContextis used to establish a connection with the database
- A new instance of
- The data members are initialized with the values to be inserted
InsertOnSubmitinserts the record to the table
SubmitChangescommits the changes to the database
Here’s the code for
Now that we have created a function to add records to the table, let us make a page in our app where one could enter the details and insert it to the database.
Step 3. Add a new page AddUsers.xaml and create a form to insert users in the database
Go to solution explorer and add a new page to your project and name is AddUsers.xaml. Create a form with two textboxes and a button as shown below.
Step 4. Add the button click event handler which when clicked will insert records to the table
Click event handler for the button. The event handler takes the text from the two textboxes and checks if they are empty. If the entered name and email are not empty then it calls the
AddUser function by creating an object of
Fetching records from database
Now that we have inserted records to our table, we would like to fetch records from it. Again I would like you to create a separate class FetchDatabase.cs to fetch records from any table of our database. Here’s how could fetch records from a table
Step 1. Add a new class FetchDatabase.cs to fetch records from any table of the database
Add a new class to your project and name is FetchDatabase.cs to your project
Step 2. Add a public function GetAllUsers which returns a list of records from a table
GetAllUsers returns a
IList which contains all the records fetched from the database. We have set up the connection using the data context and then queried records from the
User_details table. The query returns a list of records which is saved to a
IList is nothing but a collection of objects of same type.
Step 3. Create a class Users to hold the details
We define a class with data-members
Step 4. Create a function getUsers to fetch and return a list of Users
GetAllUsersfunction returned a list of records. We call
GetAllUsersand keep the fetched records in a
- We also create a new list
allmsgsto hold the details of all users
- We then iterate through all the
usrsand put the details of each user to a new instance of
Usersclass. We then add this instance to our list
- Lastly we return the list
Now that the function to fetch records is ready, let us create a new page to display records from the database.
Step 5. Add a new page AllUsers.xaml and create a Listbox to display records from the table
Go to solution explorer and a new page AllUsers.xaml. Add a
ListBox to this page and create its data template. The data template contains two
TextBlock are contained in a
StackPanel and its tag property is binded to the
id. We won’t be needing this tag now but when we need to manipulate the selected record, the
id binded to the tag will let us identify the record.
Step 6. Add the code to fetch records and bind it to the listbox
In the constructor of the page after call to
InitializeComponent create an instance of
FetchDatabase and call the
getUsers function as shown below
Now let us run the project and see if everything works perfectly.
UPDATE: Here’s the link to other posts in this series
- Working with local database in Windows Phone 8- Part II
- Working with local database in Windows Phone 8- Part III
Get the full source code here,