question

DellBoy-0798 avatar image
0 Votes"
DellBoy-0798 asked LeonLu-MSFT commented

Create table in SQLite with generic class 'Quantity' as a picker - ERROR not a valid type for SQLite DB

Morning All,

xamarin forms - populate picker in MVVM from SQLite DB issue

So...Ive used the following post

https://www.c-sharpcorner.com/article/populate-picker-using-mvvm/
to help me include a populated picker using MVVM in my project, using a picker to allowing the user to select a 'Quantity' to order for each product (which all works fine as its populated from code, but now refactoring to load from SQLite..please see current code below)..

//ProductModel

      public class ProductModel : INotifyPropertyChanged
     {
         public event PropertyChangedEventHandler PropertyChanged;
    
         private int _ProductId;
         private string _BrandName;
         public ObservableCollection<Quantity> _ListQuantites;
         private Quantity _selectedQuantity;
 //ETC have removed the other properties for sake of this Q
    
    
         //Constructor
         public ProductModel()
         {
             //Subscription
             this.PropertyChanged += OnPropertyChanged;
         }
    
         [PrimaryKey, AutoIncrement]
         public int ProductId
         {
             get { return _ProductId; }
             set
             {
                 if (_ProductId == value) return;
                 _ProductId = value;
                 OnPropertyChanged();
             }
         }
    
      
         public ObservableCollection<Quantity> ListQuantites
         {
             get
             {
                 return _ListQuantites;
             }
             set
             {
                 _ListQuantites = value;
                 OnPropertyChanged();
             }
         }
    
         public Quantity SelectedQuantity
         {
             get
             {
                 return _selectedQuantity;
             }
             set
             {
                 if (value == null)
                 {
                     _selectedQuantity = _selectedQuantity;
                 }
                 else
                 {
                     _selectedQuantity = value;
                     OnPropertyChanged();
                 }
             }
         }
    
       
         //OnPropertyChanged
         private void OnPropertyChanged(object sender, PropertyChangedEventArgs e)
         {
             if (e.PropertyName == nameof(SelectedQuantity))
             {
                 //test quantity amount
             }
         }
    
         // [NotifyPropertyChangedInvocator]
         protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
         {
             PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
         }
     }

Up Until now I was populating the ProductModel with its picker, from the constructor of the ProductViewModel page with:


          WineList = new ObservableCollection<ProductModel>();
                     WineList.Add(new ProductModel { ProductId = 1, BrandName = "Mc guigans", Grape = "Red", ListQuantites = List_Quantites, Image = "W.png", Description = "Fruity Flav", Size="700ml", Price = 10.00M, SubTotalForItem = 0.00M, Genre = "Wine" });
     //etc...and so on
    
 //then
  List_Quantites = PickerService.GetQuantitiesForProductPage();
    
 //Picker service
      public static ObservableCollection<QuantityModel> GetQuantitiesForProductPage()
         {
             var quantities = new ObservableCollection<QuantityModel>()
             {
                 new QuantityModel() {Key=1, Value="0"},
                 new QuantityModel() {Key=2, Value="1"},
                 new QuantityModel() {Key=3, Value="2"},
                 new QuantityModel() {Key=4, Value="3"}
 //etc
             };
             return quantities;
         }
    
    
 //XAML
    
     <Picker Grid.Column="3" Grid.Row="0" Title="     " VerticalOptions="Center" x:Name="productPicker" VerticalTextAlignment="Center" HorizontalOptions="EndAndExpand"  ItemsSource="{Binding ListQuantites}" ItemDisplayBinding="{Binding Value}" SelectedIndexChanged="QuantityChanged" SelectedItem ="{Binding SelectedQuantity}"/>

So...yeah as I said all works fine and dandy...But now I would like to load the ProductModel list from a table in SQLite....I have already used SQLite basic CRUD operations to create, load, view, update, edit...etc for orders made...so this is also working...the problem I seem to be having is creating the table, it is failing when I try to create an entry in the table with 'Quantity'....obviously...so I have changed the code...change 'Quantity' to object, then the plan being to populate this on the VM...but this also didnt work...

found another post relating to this:

https://forums.xamarin.com/discussion/2546/create-table-in-xamarin-throws-exception-because-of-generic-list
So 'Quantity' class is not a valid type for SQLite DB value...but turns out neither is object...has anyone idea for a work around for this...or some advice on how to refactor to resolve this?

any help or point in the right direction is appreciated thank Y


UPDATE

  public class Quantity
     {
         public int Key { get; set; }
         public string Value { get; set; }
     }



 //Create table
 SQLiteConnection database;
  public ProductsDatabaseController()
         {
             database = DependencyService.Get<ISQLite>().GetConnection();
             SQLiteFunctionality SQLite = new SQLiteFunctionality(); 
    
 database.CreateTable<ProductModel>();




dotnet-xamarindotnet-sqlite
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you share code about Quantity.cs? And creating the table code. Do you use following way to create a table?

// create the tables
if (conn != null) {
  await conn.CreateTableAsync<Quantity>();

}
0 Votes 0 ·

Hi Leon thanks for reply please see update

0 Votes 0 ·

When you create a table use database.CreateTable<ProductModel>();, all type of properties should be sqlite support types like following link.

https://www.sqlite.org/datatype3.html

Obviously, Quantity is not INTEGER, REAL, TEXT. or BLOB.

0 Votes 0 ·

0 Answers