1

I am new to Xamarin and SQLite so I'm asking myself what is a good practise to keep the database structure up to date, when a new column is added or so. To use the SQLite DB I am using SQLite-net-pcl.

I have read about some solutions that use a version field stored somewhere and that is used to alter the database structure manually when the version changes.

But from what I saw is that calling CreateTable on a SQLiteConnection does not only create the table, it also updates the table in the database when the underlying class changes.

So, is it a good practise just to call 

SQLiteConnection db = new SQLiteConnection(dbPath);
db.CreateTable<ClassA>();

everytime the system initializes, to keep the database up to date? Every change to ClassA will then be applied to the database table without any data loss.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
CactusJack
  • 93
  • 1
  • 13

1 Answers1

0

I test this operation, I got the following result.

First of all, My model like following code.

public class Prijem
{
    [PrimaryKey, AutoIncrement, Unique]
    public int BCode { get; set; }
    public string Name { get; set; }
    public string FirmName { get; set; }
    public string ItemCode { get; set; }
    public string Count { get; set; }
 }

I use following code to insert data.

await App.Pdatabase.SavePrijemAsync(new Prijem() {Name="New",FirmName="55Fame" ,ItemCode="dg",Count="15"});

My SavePrijemAsync method like following code.

  public Task<int> SavePrijemAsync(Prijem prijem)
    {
        if (IsExisted(prijem))
        {
             return _database.UpdateAsync(prijem);
        }
        else
        {
             return _database.InsertAsync(prijem);

        }
    }

I got the record like following sceenshot in the sqlite database. enter image description here

Then I just add a property called MyImage 

   public class Prijem
{
    [PrimaryKey, AutoIncrement, Unique]
    public int BCode { get; set; }
    public string Name { get; set; }
    public string FirmName { get; set; }
    public string ItemCode { get; set; }
    public string Count { get; set; }

    string image = " Image";
    public string MyImage
    {
        set
        {
            if (image != value)
            {
                image = value;

            }
        }
        get
        {
            return image;
        }
    }

}

I used update operation like following code.

 await App.Pdatabase.SavePrijemAsync(new Prijem() {Name="New",FirmName="55Fame" ,ItemCode="dg",Count="15" });

And insert operation like following code.

   await App.Pdatabase.SavePrijemAsync(new Prijem() { Name = "New11", FirmName = "55Fame", ItemCode = "dg", Count = "15" });

I got the result in the database like following screenshot. enter image description here

In the end, We can get the result, If we add a property to the Model. this column will be added in the sqlite database, but default value we must update it manually for existing data, if we insert the new value to the database, the default value will be added.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leon
  • 8,404
  • 2
  • 9
  • 52
  • How do you check, if the entry is already in the database, as the primary key (the only unique property) is created by the database? From what I see, the given example does not use the CreateTable Function at all, and that's what I especially asked for. – CactusJack Jan 06 '20 at 08:13
  • I export the DB file by DDMS, I used DB Browser to open the db file to check the collum in sqlite database. – Leon Jan 06 '20 at 08:44
  • When I extend my Model with one Property, like you did, and just say "Insert" on the DB connection, I get an Exception: SQLite.SQLiteException: 'table Prijem has no column named MyImage'. I always have to call "CreateTable" for the class with the new property, otherwise it does not work. – CactusJack Jan 06 '20 at 09:04
  • You can test my demo by this thread:https://github.com/851265601/Xamarin.Android_ListviewSelect/blob/master/DataBasedemo.zip – Leon Jan 08 '20 at 09:09