CODEWITHSUNDEEP
c#sqlitexamarinsqlite-net-pcl
CactusJack
CactusJack

Reputation: 127

Is calling 'Create Table' to keep database up to date a good practise?

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.

Upvotes: 1

Views: 460

Answers (1)

Leon Lu
Leon Lu

Reputation: 9224

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.

Upvotes: 0

Related Questions