Tuesday, 12 March 2013

How to Use SQlite local database in Windows 8 C# Applications

Hello all,

Today in this tutorial I am going to teach you about "How to use SQlite database in Windows 8 C#"

Before starting with the project, Open your visual studio 2012 and click on Tools and then select Extensions and Updates.

In that window go to online and search for SQlite for Windows Runtime. Now install that Extension.

It looks something like this

1

Now after that Create a new project and Name it "localdatabase"

After Creating the project we need to add one more component to our project.

Go to Tools and then select Library Package Manager and click on ManageNugetPackageForSolution,

after clicking that a box will open up , in that go online and search for sqlite-net . Add that to your project.

It looks something like this

2

Now we need to add some References to our project , so right click on the references in our project and click on add reference, and select Microsoft Visual C++ Windows Runtime package and Sqlite Windows Runtime.

It looks something like this

3

once you add these references and build your code you will get few errors something like this

err1

this is because, we are trying to add some Native C++ reference and this will not allow us to build our package for all the three Platforms X64, X86, ARM, so to overcome this we need to build our package only for one platform at once

to do that go to the Build Option , then click on the Configuration Manager and select only one platform lets say X86

the screenshot will tell you how to do that

err2

now Rebuild your application and all the errors are gone.

Now you are ready to write the code.

open the App.xaml.cs page and create two variables as follows


public string dbPath { get; set; }
public int currentCustomerId { get; set; }


here we use this dbPath variable to get the path to our database, we are going to create a new folder in our localfolder and that path we are going to assign to the dbpath variable.

the following code will do that

this.dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "customers.sqlite");

The above code should be written inside the OnLaunched event of app.xaml.cs before the Windows.Current.Active (); statement.

now in the above code, we are creating a folder named "customers.sqlite" inside the localfolder of the application

after that we need to create our tables inside that customers.sqlite.
in SQlite, we do not create the tables like the way we do in our General SQL databases, here table refers to the Class.
in this example lets create two tables named Customer and Proejcts. in order to do that
go to the solution explorer and create a new folder and name it as "Models".
To that Models folder add two classes with names Customer and Projects,
now open Customer.cs class and write the following code, this class holds the attributes of the table

it looks something like this

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


namespace localdatabase.Models
{
class Customer
{
[SQLite.PrimaryKey]
public int Id { get; set; }
public string Name { get; set; }
public string City { get; set; }
public string Contact { get; set; }


}
}

Now in the above code you can see how we defined our attributes of the customer table, and we declared our Id attribute to be our PRIMARY KEY .

so now in the similar way open the Projects.cs file and write the following code

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


namespace localdatabase.Models
{
class Projects
{
[SQLite.PrimaryKey]
public int Id { get; set; }
public int CustomerId { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public DateTime DueDate { get; set; }


}
}
now come back to our App.xaml.cs page and add a namespace

using localdatabase.Models;


when we include the above namespace we will be able to access our class files which we just created, here localdatabase refers to our project name and Models refers to our folder where we placed our customer and projects classes

after adding the namespace go to the on launched event and write the following code before this Windows.Current.Active (); statement

using (var db = new SQLite.SQLiteConnection(this.dbPath))
{
//creates tables if they dont exist.
db.CreateTable<Customer>();
db.CreateTable<Projects>();
}

in the above code we are creating the tables using the Classes that we created.

now write the same above code in the constructor of the App.Xaml.cs page.

Now we are done with creation of the tables.
Next we need to write some methods which acts as queries to the database, which can be used to insert update or delete the data from the database.
To do that go to solution explorer and add a new folder and name it as "ViewModels".
to this ViewModels folder add two classes with names "CustomerViewModel.cs" and "ViewModelBase.cs"

now open the ViewModelBase.cs file and add the following code


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


namespace localdatabase.ViewModels
{
public class ViewModelBase
{
public event PropertyChangedEventHandler PropertyChanged;


protected virtual void RaisePropertyChanged(string propertyName)
{
var handler = this.PropertyChanged;
if (handler != null)
{
handler(this, new PropertyChangedEventArgs(propertyName));
}
}
}
}

once you go to the next step you will understand the reason for the above class

now open the CustomerViewModel.cs class file

and inherit the ViewModelBase class and also include these namespaces
Using localdatabase; //our project name
Using localdatabase.Models;
Using localdatabase.ViewModels;

in this class we are going to create the variables that we created in our Customer and Projects classes with get and set methods;
after that we will write few methods which can be used to View, Insert and Update our tables.
the code looks like this

using localdatabase;
using localdatabase.Models;
using localdatabase.ViewModels;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Windows.UI.Xaml;


namespace localdatabase.ViewModels
{


public class CustomerViewModel : ViewModelBase
{


private int id = 0;
public int Id
{
get
{ return id; }


set
{
if (id == value)
{ return; }


id = value;
RaisePropertyChanged("Id");
}
}


private string name = string.Empty;
public string Name
{
get
{ return name; }


set
{
if (name == value)
{ return; }


name = value;
RaisePropertyChanged("Name");
}
}


private string city = string.Empty;
public string City
{
get
{ return city; }

set
{
if (city == value)
{ return; }

city = value;
RaisePropertyChanged("City");
}
}

private string contact = string.Empty;
public string Contact
{
get
{ return contact; }

set
{
if (contact == value)
{ return; }

contact = value;
RaisePropertyChanged("Contact");
}
}

private localdatabase.App app = (Application.Current as App);

public CustomerViewModel GetCustomer(int customerId)
{
var customer = new CustomerViewModel();
using (var db = new SQLite.SQLiteConnection(app.dbPath))
{
var _customer = (db.Table<Customer>().Where(
c => c.Id == customerId)).Single();
customer.Id = _customer.Id;
customer.Name = _customer.Name;
customer.City = _customer.City;
customer.Contact = _customer.Contact;
}
return customer;
}

public string SaveCustomer(CustomerViewModel customer)
{
string result = string.Empty;
using (var db = new SQLite.SQLiteConnection(app.dbPath))
{
string change = string.Empty;
try
{
var existingCustomer = (db.Table<Customer>().Where(
c => c.Id == customer.Id)).SingleOrDefault();

if (existingCustomer != null)
{
existingCustomer.Name = customer.Name;
existingCustomer.City = customer.City;
existingCustomer.Contact = customer.Contact;
int success = db.Update(existingCustomer);
}
else
{
int success = db.Insert(new Customer()
{
Id = customer.id,
Name = customer.Name,
City = customer.City,
Contact = customer.Contact
});
}
result = "Success";
}
catch (Exception ex)
{
result = "This customer was not saved.";
}
}
return result;
}


public string DeleteCustomer(int customerId)
{
string result = string.Empty;
using (var db = new SQLite.SQLiteConnection(app.dbPath))
{
var projects = db.Table<Customer>().Where(
p => p.CustomerId == customerId);
foreach (Projects project in projects)
{
db.Delete(project);
}
var existingCustomer = (db.Table<Customer>().Where(
c => c.Id == customerId)).Single();

if (db.Delete(existingCustomer) > 0)
{
result = "Success";
}
else
{
result = "This customer was not removed";
}
}
return result;
}
}
}

In the above code we wrote some methods like getcustomer , savecustomer, and deletecustomer;
now to perform some operations on the database we can just call these methods form any where in the project.
Now go to MainPage.xaml and add few UI elements , lets say for our requirement, to add a customer to database we need 4 textboxes and a button for an event handler, to retrieve customer we need a textbox for Id and a button event handler and a textbox for the output.

after adding the UI elements it looks something like this

4ui
the xaml code for the above UI is as follows

<Grid Background="{StaticResource ApplicationPageBackgroundThemeBrush}">
<Button Content="get Customer" HorizontalAlignment="Left" Margin="248,197,0,0" VerticalAlignment="Top" Click="Button_Click_1"/>
<TextBox x:Name="txtid" HorizontalAlignment="Left" Margin="448,203,0,0" TextWrapping="Wrap" VerticalAlignment="Top"/>
<GridView x:Name="mygrid" HorizontalAlignment="Left" Margin="865,88,0,0" VerticalAlignment="Top" Width="360" Height="199"/>
<TextBox x:Name="saveid" HorizontalAlignment="Left" Margin="97,322,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="96"/>
<TextBox x:Name="savename" HorizontalAlignment="Left" Margin="236,322,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="96"/>
<TextBox x:Name="savecity" HorizontalAlignment="Left" Margin="376,322,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="96"/>
<TextBox x:Name="savecontact" HorizontalAlignment="Left" Margin="531,322,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Width="96"/>
<Button Content="insert" HorizontalAlignment="Left" Margin="287,413,0,0" VerticalAlignment="Top" Click="insertintocustomer"/>
<TextBox x:Name="outputbox" HorizontalAlignment="Left" Margin="730,88,0,0" TextWrapping="Wrap" VerticalAlignment="Top" Height="268" Width="603"/>

</Grid>

now generate the event handlers to the buttons and lets see how to use the methods that we created in the CustomerViewModel.cs file

now go to the MainPage.Xaml.Cs page

and add the following namespaces.

using localdatabase.Models;
using localdatabase.ViewModels;


now inside the event handler create an object for the CustomerViewModel class and set the properties of ID, Name, City and Contact on the object's reference and call the method SaveCustomer method by passing the object that we created.
the code looks like this

private void insertintocustomer(object sender, RoutedEventArgs e)
{
CustomerViewModel obj = new CustomerViewModel();
obj.Id = Convert.ToInt32(saveid.Text);
obj.Name = savename.Text;
obj.City = savecity.Text;
obj.Contact = savecontact.Text;
obj.SaveCustomer(obj);
}


now in the other button event handler we need to retrieve the data, to do that create an object for the CustomerViewModel Class and on that object reference call the GetCustomer method by passing the Id value , and this method returns a value of type var, so we create a variable customer of type var and we assign what ever we retrieved from the method.

the code for that looks something like this


private void Button_Click_1(object sender, RoutedEventArgs e)
{
CustomerViewModel obj = new CustomerViewModel();
var customer = obj.GetCustomer(Convert.ToInt32(txtid.Text));


outputbox.Text = customer.Id + " " +customer.Name + " " + customer.City +" " +customer.Contact;
}


now we are done with our creation of SQlite Database and UI to use that , now lets run the Application and see weather it works or not.

In our Output first we need to insert some details in to the table.

the following screenshot will illustrate it

op1

once you press the insert button . your data will be stored inside the database. now to retrieve that data, insert 5 in the above textbox and press get customer.

the following screenshot will show the result

op2

So we are all done :) :) :)

Hope you all liked the tutorial.

Thank you all for reading :)

will be back with some other tutorial

:)

subscribe to get updates :)

Download the Code Sample from here:

http://sdrv.ms/11gcEOB

26 comments :

  1. Awesome Work bro.. \m/
    Helpful for lots of students :)

    ReplyDelete
  2. Great Article to get started with databases. thanks

    ReplyDelete
  3. its well but one exception is raises at customerViewModel.cs
    argumentNullException was not handled by the user
    the variable ex is declared but never used

    ReplyDelete
  4. There is an error "inconsistent accessibility: base class ViewModelBase is less accessible than CustomerViewModel class .
    In the file CustomerViewModel.cs
    public class CustomerViewModel : ViewModelBase //error
    {
    ----
    ----
    ----
    }

    How to resolve this error

    ReplyDelete
  5. var existingCustomer = (db.Table().Where(
    c => c.Id == customer.Id)).SingleOrDefault();
    This is the error i'm getting where ever "Where" is used in the CustomerViewModel.cs page:-
    Error 1 'object' does not contain a definition for 'Where' and no extension method 'Where' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
    How to tackle this error

    ReplyDelete
  6. Check the ViewModelBase.cs , give the access specifier of that class to be public so that you can inherit in the other class

    ReplyDelete
  7. so that error is because you did not call the table there, main mistake here was that, my editor didnt allow some code, now i have placed the right code to create the table

    it goes something like this

    db.CreateTable();

    where Customer is ur Model class.


    do the same thing in the other cases too. i mean in the Getcustomer , InsertCustomer and DeleteCustomer,

    any ways i edited the blog, now you can find the right code

    ReplyDelete
  8. can you post the error, so that i can understand it clearly ?

    ReplyDelete
  9. any ways, i have uploaded a sample you can check from that :)
    http://sdrv.ms/11gcEOB

    ReplyDelete
  10. I was able to figure it out the solutions for my above errors. Well thanks a lot for sharing your code , it is very useful for me :)

    ReplyDelete
  11. Hey could you please share your mail id with me,my mail id is gupta.prakul@gmail.com, actually i'm building one windows 8 app that requires database connectivity. I would be needing your little bit of your help!

    ReplyDelete
  12. Thats a great tutorial but I have little Problem.
    When I try to "Play" the app I get an error :

    In System.ArgumentNullException ist eine Ausnahme vom Typ "mscorlib.dll" aufgetreten, doch wurde diese im Benutzercode nicht verarbeitet.

    Zusätzliche Informationen: Value cannot be null.

    Falls ein Handler für diese Ausnahme vorhanden ist, kann das Programm möglicherweise weiterhin sicher ausgeführt werden.

    So the System say there is a Problem in the SQLite.cs

    It marks

    var utf8Length = System.Text.Encoding.UTF8.GetByteCount (s);

    from

    static byte[] GetNullTerminatedUtf8 (string s)
    {
    var utf8Length = System.Text.Encoding.UTF8.GetByteCount (s);
    var bytes = new byte [utf8Length + 1];
    utf8Length = System.Text.Encoding.UTF8.GetBytes(s, 0, s.Length, bytes, 0);
    return bytes;
    }

    I hope you can help me to fix the Problem.

    Sorry for the bad english ;)

    ReplyDelete
  13. Hi I followed all your steps but I am getting 2 errors in the CustomerViewModel.cs class
    Error 1
    'localdatabase.Models.Customer' does not contain a definition for 'CustomerId' and no extension method 'CustomerId' accepting a first argument of type 'localdatabase.Models.Customer' could be found (are you missing a using directive or an assembly reference?)
    Error 2
    Error 2 Cannot convert type 'localdatabase.Models.Customer' to 'localdatabase.Models.Projects'

    plz help

    ReplyDelete
  14. Hi,may i know how to retrieve the data to an object instead of publish it in the ListBox? THx

    ReplyDelete
  15. Hi,may i know how to query the summation of the particular item? my code is as follow:
    var projects = dbConn.Table().Where(
    p => p.spent_category == listPicker.SelectedItem.ToString());
    what i wish to do is to sum up all the matched spent_category by using the above query..Please help me please,it's so urgent as my deadline for my Final Year Project is going to end very soon T^T

    ReplyDelete
  16. Prakul, I am also getting the same error, could you tell me how u solved that?

    ReplyDelete
  17. Thank you for the auspicious writeup. It in fact was a amusement account it. Look advanced to more added agreeable from you! Nevertheless, how can we communicate?

    ReplyDelete
  18. Rithesh Baradi9 July 2013 01:04

    in the DeleteCustomer method

    var projects = db.Table().Where(
    p => p.CustomerId == customerId);

    i guess it shoud be

    var projects = db.Table().Where(
    p => p.CustomerId == customerId);


    let me know if i am mistaken.

    ReplyDelete
  19. http://www.devart.com/linqconnect/demo.html

    ReplyDelete
  20. It is very helpful...can you please say me how to insert an image into SQLite database and also How to retrieve it....

    ReplyDelete
  21. Not so far I have found new cool tool to work with SQLite on win - Valentina Studio. Its free edition can do things more than many commercial tools!!
    I very recommend check it. http://www.valentina-db.com/en/valentina-studio-overview

    ReplyDelete