Create SQLite Table Xamarin Forms

Question

I want to create a Multiple SQLite tables but I don't know how to proceed.

The flow of my project:
- When I click the login button it should create the SQLite Database and tables if the username and password is filled.

Tables needed to be created:
Users Table (Fields: UserID(string), UsrPassword(string), ContactId(int), Status(string))
Retailer (Fields: Retailer_Name(string), Retailer_Handler(int))

What I already finished doing:
1. I already added the SQLite Nuget Package
2. I added SQLiteAsyncConnection GetConnection(); to my interface
3. I added Database creator for each project (Android and UWP)
4. I already bind the my LoginPage form to my ViewModel

My codes are below:

ISQLiteDB.cs

using SQLite;
using System;
using System.Collections.Generic;
using System.Text;

namespace TBSMobileApplication.Data
{
    public interface ISQLiteDB
    {
        SQLiteAsyncConnection GetConnection();
    }
}

LoginPage.xaml

<?xml version="1.0" encoding="utf-8" ?>
<ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             x:Class="TBSMobileApplication.View.LoginPage"
             BackgroundColor="#ecf0f1">
    <ContentPage.Content>
        <StackLayout 
            VerticalOptions="StartAndExpand">
            <StackLayout.Padding>
                <OnPlatform 
                    x:TypeArguments="Thickness"
                    iOS="20"
                    Android="20,100,20,0">
                </OnPlatform>
            </StackLayout.Padding>

            <Label 
                Text="Username"
                TextColor="#34495e"
                Font="Arial,10"/>
            <Entry
                Placeholder="Username"
                PlaceholderColor="#95a5a6"
                FontSize="12"
                FontFamily="Arial"
                x:Name="entUsername"
                Text="{Binding Username}"/>
            <Label 
                Text="Password"
                TextColor="#34495e"
                Font="Arial,10"/>
            <Entry
                Placeholder="Password"
                PlaceholderColor="#95a5a6"
                FontSize="12"
                FontFamily="Arial"
                IsPassword="True"
                x:Name="entPassword"
                Text="{Binding Password}"/>
            <Button 
                Text="Login"
                FontSize="12"
                HorizontalOptions="Start"
                BackgroundColor="#3498db"
                Command="{Binding LoginCommand}"/>
        </StackLayout>
    </ContentPage.Content>
</ContentPage>

LoginPage.xaml.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TBSMobileApplication.ViewModel;
using Xamarin.Forms;
using Xamarin.Forms.Xaml;

namespace TBSMobileApplication.View
{
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class LoginPage : ContentPage
    {
        public LoginPage ()
        {
            InitializeComponent ();
            BindingContext = new LoginPageViewModel();
            MessagingCenter.Subscribe<LoginPageViewModel,string>(this, "Login Alert",(sender,Username) =>
            {
                DisplayAlert("Login Alert", "Please fill-up the form", "Ok");
            });
        }
    }
}

LoginPageViewModel.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Text;
using System.Windows.Input;
using TBSMobileApplication.View;
using Xamarin.Forms;

namespace TBSMobileApplication.ViewModel
{
    public class LoginPageViewModel : INotifyPropertyChanged
    {
        void OnProperyChanged(string PropertyName)
        {
            PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(PropertyName));
        }

        public string username;
        public string password;

        public string Username
        {
            get { return username; }
            set
            {
                username = value;
                OnProperyChanged(nameof(Username));
            }
        }

        public string Password
        {
            get { return password; }
            set
            {
                password = value;
                OnProperyChanged(nameof(Password));
            }
        }

        public ICommand LoginCommand { get; set; }

        public LoginPageViewModel()
        {
            LoginCommand = new Command(OnLogin);
        }

        public void OnLogin()
        {
            if (string.IsNullOrEmpty(Username) || string.IsNullOrEmpty(Password))
            {
                MessagingCenter.Send(this, "Login Alert", Username);
            }
            else
            {

            }
        }

        public event PropertyChangedEventHandler PropertyChanged;
    }
}

AndroidSQLiteDB.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

using Android.App;
using Android.Content;
using Android.OS;
using Android.Runtime;
using Android.Views;
using Android.Widget;
using SQLite;
using TBSMobileApplication.Data;
using TBSMobileApplication.Droid.Data;
using Xamarin.Forms;

[assembly: Dependency(typeof(AndroidSQLiteDb))]

namespace TBSMobileApplication.Droid.Data
{
    public class AndroidSQLiteDb : ISQLiteDB
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var dbFileName = "backend.db3";
            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, dbFileName);

            return new SQLiteAsyncConnection(path);
        }
    }
}

WindowsSQLiteDB.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite;
using TBSMobileApplication.Data;
using TBSMobileApplication.UWP.Data;
using Windows.Storage;
using Xamarin.Forms;

[assembly: Dependency(typeof(WindowsSQLiteDb))]

namespace TBSMobileApplication.UWP.Data
{
    public class WindowsSQLiteDb : ISQLiteDB
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var dbFileName = "backend.db3";
            var documentsPath = ApplicationData.Current.LocalFolder.Path;
            var path = Path.Combine(documentsPath, dbFileName);
            return new SQLiteAsyncConnection(path);
        }
    }
}

Answer 1

You don't want to create tables when the user clicks a button - the db and tables should really be created when your app starts the first time on a given device. But the procedure is the same

// get the connection
var db = DependencyService.Get< ISQLiteDB>();
var conn = db.GetConnection();

// create the tables
if (conn != null) {
  await conn.CreateTableAsync<Users>();
  await conn.CreateTableAsync<Retailer>();
}

and you will need a class for each DB model

public class Retailer {
  public string Retailer_Name { get; set; }
  public int Retailer_Handler { get; set; }
}