working with Sqlite database in xamarian


APP Developement, Vs2017 and Xamarian

Introduction


This article explains the following topic.

1. What are the essentials to look for connectiong to sqlite database in an app

2. Where does the sqlite database exists

3. what is the connection string

4. how to start wrting the code

Description


1.Creating a Data Model to work with. For xeample below is the datamodel student.cs. This can be created in a seperate folder such as model folder or in the project node itself.

using SQLite;
 
namespace SIMS.Model
{
    public class StudentData
    {
        [PrimaryKey, AutoIncrement]
        public int id { get; set; }
        public string StudentName { get; set; }
        public datetime StudentDob{ get; set; }
        public decimalFeesPaid{ get; set; }
    }
}
 

 2. Create an interface in shared project folder

The following is the code.

using SQLite;
 
namespace PasswordKeeper.Interface
{
    public interface ISQLite
    {
        SQLiteConnection GetConnection();
    }
}
 
3. In the student.Android project folder create or write a new class and call it iSqllitteDataAccess.cs and write the following code
 
using PasswordKeeper.Droid.DataAccess;
using Xamarin.Forms;
using PasswordKeeper.Interface;
using SQLite;
using System.IO;
 
[assembly: Dependency(typeof(SQLiteDatabase))]
namespace SIMS.Droid.DataAccess
{
    public class SQLiteDatabase : ISQLite
    {
        public SQLiteConnection GetConnection()
        {
            var dbase = "PKeeperDB";
            var dbpath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData);
            var path = Path.Combine(dbpath, dbase);
            var connection = new SQLiteConnection(path);
            return connection;
 
        }
    }
}

In the above code we have included the dependency class of sqlitedatabase. We have used an attribute such as [assembly:Dependency(typeof(SqLiteDatabase))].

This referece to the installed sqlite instance. in the next line we have created a class by name sqliteconnection which derives from the isqlite interface and implements a Get connection method to get connection instance to the sqlite database.Next we have defined variables to hold the following:

var Dbase= name of the database

var dbpath=name of the database path where it resides on your android mobile. ususally this will be in the System.Environment.SpecialFolder.ApplicationData folder which we can get by using System.Environment.GetFolderpath enumerator of C#.

then var path=Path.combine(dbpath,dbase) combine the database and gives access  to the complete path.

and the connection object will return the actual connection.

 

Now to consolidate the above activity Firstly we installed the Sqlite database, then Write an interface class  with a method in the shared folder , then write a domain model class, then write a dataaccess class to implement the interface to get a connection access to sqlite database.

4. The next step will be to access this connection and make actual connection to the database , retrive the data and show it on a XAML form.

   To this we need to design a XAML form. create a new XAML page and paste the following code:

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:local="clr-namespace:PasswordKeeper"
             x:Class="PasswordKeeper.MainPage"
             NavigationPage.HasBackButton="False"
              Title="Login">
    <ContentPage.Content>
        <StackLayout VerticalOptions="CenterAndExpand" Padding="5">
            <Entry x:Name="UserName" Placeholder="User Name"></Entry>
            <Entry x:Name="Password" Placeholder="Password" IsPassword="True"></Entry>
            <StackLayout Orientation="Horizontal">
                <Button Text="Log In" x:Name="btnlogin" Clicked="btnlogin_Clicked"></Button>
                <!--<Button Text="show" Clicked="Show_Clicked"></Button>-->
            </StackLayout>
        </StackLayout>
    </ContentPage.Content>
</ContentPage>

The above code  create a username and password form which will act as an entry point to the android app. the rendered XAML view might look as below