Monday, 7 October 2013

Simpler way to use SQLITE Local Database in windows 8 Metro

Hi guys,
Thanks for all your previous responses, I Got a feedback saying that the Previous post related to SQLITE was a little complicated one, So i decided to make it very simple this time,
Firstly create a new windows 8 metro project.

After creating the project we need few add on's to the project.

Click on the Tools tab -> Extensions and Updates -> Online -> Search for SQLITE.

Choose SQLITE for windows Runtime

Select it and Install.

After that we need few Nuget Packages. To add those, In the solution explorer right click on to the references -> Manage Nuget Packages -> Search for Sqlite-Net and you will see the following screen


After you do that, Right click on to the references and add reference -> Windows -> Extension.

you will see the following screen


 Select those in the screen.

Once you select those two extensions you will get 2 errors.
To rectify those errors Click on Build -> Configuration Manager -> Change the platform from Any Cpu to X86 or X64 or ARM.

and then rebuild the solution. and errors will be disappear.

Then you are ready to go.

For any database we need tables to store the data.
So here in SQLITE a table is a class file so create a new class file and name it as student.cs

here in this class we will specify the attributes and primary keys for that table.

to do that write the following code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;

namespace sqlitedemo
{
 
    class Student
    {
        [PrimaryKey]
        public int StudentId { get; set; }
       public String Name { get; set; }
       public String Email { get; set; }
    }
}

here we have 3 fields, and StudentId is the primary key.

Next save this.


Open your MainPage.xaml and create the UI as per requirement below.

Xaml Code for the above design

<Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">
        <Button x:Name="btninsert" Content="Insert" HorizontalAlignment="Left" Margin="117,87,0,0" VerticalAlignment="Top" Click="btninsert_Click"/>
        <TextBox x:Name="boxid" HorizontalAlignment="Left" Margin="117,166,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="118"/>
        <TextBox x:Name="boxname" HorizontalAlignment="Left" Margin="275,166,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="118"/>
        <TextBox x:Name="boxemail" HorizontalAlignment="Left" Margin="439,166,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="230"/>
        <TextBox x:Name="boxresult" HorizontalAlignment="Left" Margin="787,93,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="518" Height="205"/>
        <Button x:Name="btnretrieve" Content="Retrieve" HorizontalAlignment="Left" Margin="787,32,0,0" VerticalAlignment="Top" Click="btnretrieve_Click"/>
        <TextBox x:Name="retrieveid" HorizontalAlignment="Left" Margin="910,38,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="118"/>

    </Grid>

once you are done with creating the UI, open the APP.Xaml.Cs file 

Include a namespace 

using SQLite;

and create a public string variable as follows

public string dbpath { get; set; }

Next you need to search for onLaunched event in this file.

In that method at the end you will find 

Windows.Current.Activate();

above this line of code you need to write the following code

 this.dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path , "studentsdatabase.db");
            using (var db = new SQLite.SQLiteConnection(this.dbpath))
            {
                //creates tables if they dont exist.
                db.CreateTable<Student>();
              
            }


Here we are creating a table using the class file that we have created.

Next go to Mainpage.xaml.cs


Insert a namespace 
Using SQLite;

As we know that in our designer we have two buttons, One to Insert and Other to Retrieve, 

In the Insert Button event handler you need to write the following code

private async void btninsert_Click(object sender, RoutedEventArgs e)
        {
            try
            {
                var db = new SQLiteAsyncConnection((App.Current as App).dbpath);
                var data = new Student { StudentId = Convert.ToInt32(boxid.Text), Name = boxname.Text, Email = boxemail.Text };
                int x = await db.InsertAsync(data);
            }
            catch (Exception ee)
            {
                var dlg = new MessageDialog(ee.ToString());
                dlg.ShowAsync();
            }
            
        }


once inserted we can retrieve this values using the StudentId 

so in the retrieve button event handler write the following code.

private void btnretrieve_Click(object sender, RoutedEventArgs e)
        {
            //var db = new SQLiteConnection((App.Current as App).dbpath);
            using (var db = new SQLite.SQLiteConnection((App.Current as App).dbpath))
            {
                int id = Convert.ToInt32(retrieveid.Text);
                var retrievedstudent = (db.Table<Student>().Where(
                c => c.StudentId ==id )).Single();
                boxresult.Text = retrievedstudent.StudentId.ToString()+" " + retrievedstudent.Name.ToString()+" " + retrievedstudent.Email.ToString();
            }

        } 

once we are done with this run the sample and check weather it works.



So It works.

Thank you all for reading this post :)

If you would like to look at the sample follow the below link and feel free to download it


happy coding 


3 comments :

  1. hello, thakyou for sharing with us. i am facing error in btnInsert_click event when going to get the text from textbox like, boxid, boxname, boxemail it gives erroe: "doesn't exist in this current context". i am using visual studio 2013!! please give me solution..

    ReplyDelete
  2. while compiling i am getting error as The 'await' operator can only be used within an async method. Consider marking this method with the 'async' modifier and changing its return type to 'Task'. and another as Error 2 The type or namespace name 'MessageDialog' could not be found (are you missing a using directive or an assembly reference?).

    ReplyDelete
  3. please send me solution if you get it at itkartik.93@gmail.com

    ReplyDelete