SQLite Xamarin PCL

Question

I am developing an App in Xamarin type PCL, for my DB structure, I want to use SQLite, but I have the following doubts ...

1. When entering a record in my DB, it takes the ID = 0, in my data model use

    [PrimaryKey, AutoIncrement]
        Public int ViolationID {get; set; }
   

And still, I enter the registry at zero, I do not know I'm doing wrong ... or is this a bug in the SQLite.NET-PCL package?

2. How can I verify that these records are actually being entered? I have in my code

   public class DataAccess : IDisposable
   {
       private SQLiteConnection connection;
   
       public DataAccess()
       {
           var entity = DependencyService.Get<IEntity>();
           connection = new SQLiteConnection(entity.Plataforma, System.IO.Path.Combine(entity.DirectorioBD, "Infraccions.db3"));
           connection.CreateTable<Infraccion>();
       }
   
       public void InsertInfraccion(Infraccion infraccion)
       {
           connection.Insert(infraccion);
       }
   
       public void UpdateInfraccion(Infraccion infraccion)
       {
           connection.Update(infraccion);
       }
   
       public Infraccion GetInfraccion(int InfraccionID)
       {
           return connection.Table<Infraccion>().FirstOrDefault(c => c.InfraccionID == InfraccionID);
       }
   
       public List<Infraccion> GetInfraccions()
       {
           return connection.Table<Infraccion>().OrderBy(c => c.MotivoID).ToList();
       }
   
       public void DeleteInfraccion(Infraccion infraccion)
       {
           connection.Delete(infraccion);
       }
   
       public void Dispose()
       {
           connection.Dispose();
       }
   }
   

Should I create a table called Infraccions.db3 on my phone?

Thank you for your comments...

Answer 1

Are you saying that all new records have an ID of 0 and overwrite the existing record in the database?

If so, then this is how SQLite works - for int columns 0 is a valid value, so when the ID is 0 it will overwrite the record, instead of incrementing the value.

The correct way to use int primary keys is to define your primary key as a nullable int, that way the value for a new record is null, which will be updated to the next available id:

[PrimaryKey, AutoIncrement]
public int? ViolationID {get; set; }

Essentially change int to int?.

Answer 2

1) When entering a record in my DB, it takes the ID = 0, in my data model use

You are using primary key and auto increment in your model that's means you are not able to enter 0 manually in the primary field and it takes automatically next value in this field.

2) How can I verify that these records are actually being entered?

You can get DB file path when you create your database and you can able to open and see that data.

(System.IO.Path.Combine(entity.DirectorioBD, "Infraccions.db3") )

There are many browser plugins to access the sqllite database.