create Multiple tables using Sqlite-net-pcl

Question

I am using xamarin forms and Sqlite-net-pcl (nuget). I need help on creating multiple tables. I have set up the requirements as below. I need to do the following:

1) I need to create tables and database when the App launches. How to do this in App.cs?

Update Problem:

1) Tables are not created. Why?

---1--- in PCL : add these

-- classes for table

using SQLite;

namespace MyApp.Model
{
    [Table("TblCountry")]
    public class Country
    {
        public string Country { get; set; }
        public string CountryCode { get; set; }
        public string OfficialLanguage { get; set; }

    }


 [Table("TblEmployees")]
    public class Employee
    {
      [PrimaryKey, AutoIncrement]
      public string FirstName { get; set; }
      public string LastName { get; set; }
      public string Address { get; set; }
    }

}


--- interface class

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

namespace MyApp.DataAccessHelpers
{
   public interface ISQLite
    {
        SQLiteConnection GetConnection();
    }
}



---2---in Xamarin.Droid: I add this class

using SQLite;
using System.IO;
using Xamarin.Forms;
using MyApp.Droid.Implementation;
using MyApp.DataAccessHelpers;


[assembly: Xamarin.Forms.Dependency(typeof(AndroidSQLite))]
namespace MyApp.Droid.Implementation
{
    class AndroidSQLite : ISQLite
    {
        public SQLite.SQLiteConnection GetConnection()
        {
            string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.MyDocuments);
            var path = Path.Combine(documentsPath, DatabaseHelper.DbFileName);
            var conn = new SQLite.SQLiteConnection(path);
            return conn;
        }
    }
}

------- Update :

public class DatabaseHelper
 {
        static SQLiteConnection sqliteconnection;

        public const string DbFileName = "MyDb.db3";

        public DatabaseHelper()
        {
            try
            {
                sqliteconnection = DependencyService.Get<ISQLite>().GetConnection();
                sqliteconnection.CreateTable<CountryModel>();
                sqliteconnection.CreateTable<EmployeeModel>();

            }
            catch (Exception ex)
            {
                string strErr = ex.ToString();
            }
        }       

        public List<CountryModel> GetAllCountry()
        {
            return (from data in sqliteconnection.Table<CountryModel>()
                    select data).ToList();
        }


        public CountryModel GetCountryByHuNbr(string name)
        {
            return sqliteconnection.Table<CountryModel>().FirstOrDefault(c => c.Name == name);
        }


        public void DeleteAllCountry()
        {
            sqliteconnection.DeleteAll<CountryModel>();
        }


        public void DeleteCountryByid(int ID)
        {
            sqliteconnection.Delete<CountryModel>(ID);
        }


        public void InsertCountry(CountryModel country)
        {
            sqliteconnection.Insert(country);
        }

        public void UpdateCountry(CountryModel country)
        {
            sqliteconnection.Update(country);
        }

        //------- CRUD for employee

        public void InsertEmployee(EmployeeModel employee)
        {
            sqliteconnection.Insert(employee);

        }

       .....

       ... and all the CRUD for employee


    }
}

Thanks in advance.

dsalas dsalas · Accepted Answer · 2018-09-10T13:21:38

I created a helper class which contains all methods I need in order to interact with SQLite Database. I use the CreateTable() to create a table.

In App.xaml.cs file, I create an instance of my DataAccess helper class and I call the CreateLocalDbTables() method.

DataAccessHelper

 public class DataAccess : IDisposable
{
    private SQLiteConnection Connection;

    #region Constructor

    public DataAccess(ISQLitePlatform sQLitePlatform, string dbPath)
    {
        this.Connection = new SQLiteConnection(sQLitePlatform, dbPath);
    }
    #endregion

    #region Methods

    public void CreateLocaldbTables()
    {
        this.Connection.CreateTable<Registration>();
        this.Connection.CreateTable<TransmissionLog>();
        this.Connection.CreateTable<Parameters>();
        this.Connection.CreateTable<Guest>();
    }

In APP.xaml.cs

public partial class App : Application
{
    #region Properties

    public static DataAccess DBConnection { get; set; }

    #endregion

    public App(string localDbPath, ISQLitePlatform sqlitePlatform)
    {
        InitializeComponent();

        DBConnection = new DataAccess(sqlitePlatform,localDbPath);
        DBConnection.CreateLocaldbTables();

Model

    namespace AppRegistration.Models
    {
        using SQLite;
        using System;

        [Table("Activity")]
        public class Actividad
        {
            [Column("IdActivity")]
            [PrimaryKey, Autoincrement]
            public int IdActivity { get; set; }

            [Column("IdEvent")]
            [PrimaryKey]
            public int IdEvent { get; set; }

            [Column("ActivityDescription")]
            [NotNull]
            public string ActivityDescription { get; set; }

            [Column("Status")]
            [NotNull]
            public string Status { get; set; }


            [Column("UserId")]
            [NotNull]
            public int UserId { get; set; }

        }
    }

create Multiple tables using Sqlite-net-pcl

1 Answers