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 · Accepted Answer · 2018-09-12 12:48:42Z

1

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

        }
    }

edited Sep 12, 2018 at 12:48

answered Sep 10, 2018 at 13:21

dsalas

1591 gold badge2 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dsalas Over a year ago

I updated my answer. Check the structure of the Model. I see you are missing some tags needed for table creation. Also, it is recommended to use a plugin like SQLite/SQL Server Compact Toolbox to see what is inside your database. Here is the link: marketplace.visualstudio.com/… .@MilkBottle

Collectives™ on Stack Overflow

create Multiple tables using Sqlite-net-pcl

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related