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.
http://www.windowsapptutorials.com/windows-phone/working-with-local-databases-in-windows-phone-8/
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.
using System.Data.Linq.Mapping;
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_details
which 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
ID
is defined as the primary key for the table.
- The table has three columns,
ID
, user_name
and user_email
Next you need to define the DataContext
for 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.
Note the 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
Below 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
Our function 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
UserDataContext
is used to establish a connection with the database
- A new instance of
User_details
is created
- The data members are initialized with the values to be inserted
InsertOnSubmit
inserts the record to the table
SubmitChanges
commits the changes to the database
Here’s the code for AddUser
function
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
Add a 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 DatabaseAdd
class.
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
The function 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
list. 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 id
, name
and email
. It will be used to hold the details from the fetched records.
Step 4. Create a function getUsers to fetch and return a list of Users
GetAllUsers
function returned a list of records. We call GetAllUsers
and keep the fetched records in a Ilist
usrs.
- We also create a new list
allmsgs
to hold the details of all users
- We then iterate through all the
usrs
and put the details of each user to a new instance of Users
class. We then add this instance to our list allmsgs
- 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
with name
and email
binded to it. These 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
Get the full source code here,
Download full project Databases.zip
Recent Comments