Windows-8.1

Working with local database in Windows Phone- Part I

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

Vivek Maskara

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

  • Junior Cardoso

    I was looking how to do this!
    Thanks

  • venue

    hi, may i know for the step 6 ,fetch record part,where does the
    allusers get from? Because i have an error at the allusers.ItemsSource = fetch.getBooking();

    • We created a getUsers function in step 4 which is being called in step 6.

      • LP

        Hi
        i think the x:Name=”allusers” in the ListBox is missing…

  • Triza

    hey am getting an IndexOutOfRangeException when adding a record to the database. I have followed all the steps you stipulated. Please help

    • Did you add IsDbGenerated = true in the primary key column of your table.

      • Triza

        yes i did

  • ante_geia

    hi,thanks for the article.I am having a problem..I get a blank page at AllUsers page and an error :

    System.Windows.Data Error: Cannot get ‘id’ value (type ‘System.Int32’) from ‘car_fix_volos.Users’ (type ‘car_fix_volos.Users’). BindingExpression: Path=’id’ DataItem=’car_fix_volos.Users’ (HashCode=112774938); target element is ‘System.Windows.Controls.StackPanel’ (Name=”); target property is ‘Tag’ (type ‘System.Object’).. System.MethodAccessException: Attempt to access the method failed: car_fix_volos.Users.get_id()

    at System.Reflection.RuntimeMethodInfo.InternalInvoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, StackCrawlMark& stackMark)

    at System.Reflection.RuntimePropertyInfo.InternalGetValue(PropertyInfo thisProperty, Object obj, Object[] indA first chance exception of type ‘System.MethodAccessException’ occurred in mscorlib.dll

    the same error comes for the “name” parametre…i think the error is at AllUsers.xaml but i cannot find it.can u help me plz?

    • ante_geia

      solved it by adding in the AssemblyInfo.cs the following line :

      [assembly:System.Runtime.CompilerServices.InternalsVisibleTo(“System.Windows”)]

  • becks

    how do i add multiple instances to a table within the same AddItem function?

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