Forum Cross Platform with Xamarin

Announcement:

The Xamarin Forums have officially moved to the new Microsoft Q&A experience. Microsoft Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

To create new threads and ask questions head over to Microsoft Q&A for .NET and get involved today.

SQlite with multiple model

i found a sample here https://developer.xamarin.com/samples/xamarin-forms/Todo/ to save data locally using sqlite. But how can we create multiple table .Example i want save item , customer , sales order . After that i can retrieve data and update data for all the table . Any sample code to operate with multiple table.

Answers

  • wsmong2wsmong2 USMember ✭✭

    it possible to link two table using foreign key?

  • YkshLeoYkshLeo USMember ✭✭✭✭

    It is critical to note that SQLite.net does not support foreign keys

  • YkshLeoYkshLeo USMember ✭✭✭✭

    By using SQLite-Net Extensions, i think you can manage to use foreign key in an easy way. :smile:

    Refer :

  • wsmong2wsmong2 USMember ✭✭

    using System;
    using SQLite.Net.Attributes;
    using SQLiteNetExtensions.Attributes;
    using System.Collections.Generic;

    namespace ImSalesAssist2
    {
    public class SalesOrder
    {
    public SalesOrder ()
    {
    }

        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string SoNo { get; set; }
        public string SoDate { get; set; }
        public string Debtor { get; set; }
        public string Term { get; set; }
        public string SoLine{ get; set; }
        public double Total{ get; set; }
        public double TotalItem{ get; set; }
        public double SubTotal{ get; set; }
        public double Discount{ get; set; }
        public double Taxable{ get; set; }
        public string Attention{ get; set; }
        public string Remark{ get; set; }
    
        [OneToMany]
        public List<SoLine> SoLineDetail{ get; set;}
    
    }
    
    public class SoLine
    {
        public SoLine ()
        {
        }
    
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string ItemCode { get; set; }
        public string Description { get; set; }
        public decimal Price{ get; set; }
        public double Qty{ get; set; }
        public double Foc{ get; set; }
        public string BaseUOM { get; set; }
        public string SalesUOM { get; set; }
        public decimal Discount{ get; set; }
        public string Project { get; set; }
        public decimal Total { get; set; }
    
        [ForeignKey(typeof(SalesOrder))]
        public int SalesOrderID{ get; set; }
    
    
    }
    

    }

    i declare my model like this , one sales order will have many soLine. Now how can retrieve data from this two table at the same time ?

    public IEnumerable GetSalesOrder ()
    {
    lock (locker) {
    return (from i in database.Table() select i).ToList();
    }
    }

    This only will return data in salesOrder table , now i need to retrieve the data at the SoLine table together. How to do like that?

  • wsmong2wsmong2 USMember ✭✭

    is possible to insert data from list to sqlite?

  • YkshLeoYkshLeo USMember ✭✭✭✭
  • wsmong2wsmong2 USMember ✭✭

    can i know how to calculate the total datarow in sqlite?

  • YkshLeoYkshLeo USMember ✭✭✭✭

    You can use something like this to get total count of rows :

        public int GetCount()
        {
            lock(locker)
            {
                SQLiteConnection _sqlconnection;
                var count =  _sqlconnection.ExecuteScalar<int>("SELECT Count(*) FROM Table");
                return count ;
            }
        }
    
  • mongmong USMember

    It say use of unassigned local variable "_sqlconnection " any thing missing?

  • YkshLeoYkshLeo USMember ✭✭✭✭

    Verify namespace : using SQLite.Net;

  • wsmong2wsmong2 USMember ✭✭

    can i know sqlite file save at where after debug in ios and android emulator?

  • YkshLeoYkshLeo USMember ✭✭✭✭
    edited December 2015

    @wsmong2,

    Android Emulator

    The filesystem of the emulator doesn't map to a directory on your hard drive. The emulator's disk image is stored as an image file, which you can manage through the emulator binary itself (run "emulator -help" for a description of options).

    You're best off using adb from the command line to jack into a running emulator. If you can get the specific directory and filename, you can do an "adb pull" to get the database file off of the emulator and onto your regular hard drive.

    NB : This works for unrooted devices too - it only works for emulators, and devices where you are operating adb as root.

    Refer : http://www.rogcg.com/blog/2013/12/18/android-emulator-sqlite-database-location

  • YkshLeoYkshLeo USMember ✭✭✭✭
    edited December 2015

    @wsmong2,

    iOS Simulator

    For SDK 3.2 - SDK 4 replaces the /User folder in that path with a number for each of the legacy iPhone OS/iOS versions it can simulate, so the path becomes:

    ~/Library/Application Support/iPhone Simulator/[OS version]/Applications/[appGUID]/

    if you have the previous SDK installed alongside, its 3.1.x simulator will continue saving its data in:

    ~/Library/Application Support/iPhone Simulator/User/Applications/[appGUID]/

    For Xcode6 and iOS8

    ~/Library/Developer/CoreSimulator/Devices/[DeviceID]/data/Containers/Data/Application/[AppID]/

  • wsmong2wsmong2 USMember ✭✭

    i discover that sqlite do not have datetime dataype.Now i using string as a datatype , in my model, i can declare datetime datatype , when the data populated from sqlite file , it show 0001-01-01 as datatime .Any way to solve this?

  • wsmong2wsmong2 USMember ✭✭

    can i know how to delete sqlite date more than 1 month?

    return database.Query("Delete from SalesOrder where SoDate < date('now','-1 month')");

    my query seen do not work ,anything i miss out? SoDate is datetime with format dd/mm/yyyy HH:MM:SS

  • YkshLeoYkshLeo USMember ✭✭✭✭

    @wsmong2,

    Try something like this.

    Code to delete data older than two days :

    String query = "DELETE FROM myTable WHERE SaveDate <= date('now','-2 day')";
    sqlConnection.Execute(query);
    
  • wsmong2wsmong2 USMember ✭✭

    public list DeleteRecord
    {
    lock(locker)
    {
    return database.Query("Delete from SalesOrder where SoDate < date('now','-1 month')");
    }
    }

    i write like this , but still cannot working .My SoDate is datetime with format dd/mm/yyyy HH:MM:SS

Sign In or Register to comment.