How to Establishing a backend connection to SQLite from Visual Studio 2012 with Example.

In this article I will explain How to Establish a backend connection to SQLite from Visual Studio 2012 with Example.

                                                                                                                                                               

Establishing a backend connection to SQLite from Visual Studio 2012

•                                                             Before starting a project got to tools and select “Extensions and Updates” options.                         
•         Under “Installed” options select “All”
•          Select SQLite for windows runtime

•     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

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

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

      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 let’s say X86 the screenshot will tell you how to do that

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 anywhere in the project.

Now go to MainPage.xaml and add few UI elements, let’s 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

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 whatever 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 let’s run the Application and see whether it works or not.

In our Output first we need to insert some details in to the table. The following screenshot will illustrate it

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