1
votes

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);
        }
    }
}
1
didn't you ask the same question yesterday? stackoverflow.com/questions/51452558/… - Jason
@Jason this time I want just to create a database and multiple tables - user10117348
Is there any particular reason you're trying to do this on the mobile devices? My usual way of doing this is to create a library for my data model classes (Client, Order etc) and then create a Windows console app that uses SQLite.CodeFirst to automatically generate the .db file from them. Then in your mobile apps you just include that .db file as a resource, copy into place and reference the library with the model classes so that you can use those in your mobile code as well. The way you're doing it seems like an awful lot of work for something that's a pretty straightforward process. - Mark Feldman

1 Answers

2
votes

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; }
}