I'm working on an application to record points scored during a board game. This is a project for portfolio. The game is represented by object:

    public class GameEntries
{
    [PrimaryKey,AutoIncrement]
    public int Id { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public ObservableCollection<PlayerEntries> Players { get; set; }
    public bool ScoringMode { get; set; }
    public string Date { get; set; }
}

where the Player object is:

    public class PlayerEntries
{
    [PrimaryKey, AutoIncrement]
    public int Id { get; set; }
    public List<int> points { get; set; }
    public string Name { get; set; }
}

I want to save the gameobject in to the SQLite database but its work only without the ObservableCollection in it.

There is screenshot of error

How can I store the GameEntries objects?

CodePudding user response：

This SQLite error is causing because you can't store data of ObservableCollection type.

Storage Classes could be used to define the format that SQLite uses to store data on disk. SQLite provides five primary data types which are mentioned below:

NULL – It is a NULL value. INTEGER – It is an integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value. REAL – It is a floating point value, stored as an 8-byte floating number. TEXT – It is a string, stored using the database encoding (UTF). BLOB – It is a group of data, stored exactly as it was entered.

Type Affinity concept is supported by SQLite on columns. The considered storage class for any column is called its affinity. Every table’s column in SQLite database is assigned one of the following type affinities:

TEXT – This column captures all data for NULL, TEXT or BLOB. NUMERIC – This column capture values for all storage classes. INTEGER – It has an exception in a CAST expression and behaves in similar way as a column with NUMERIC affinity. REAL – It forces integer values into floating representation. and behaves like a column with NUMERIC affinity. NONE – A column having NONE affinity do not choose one storage class above other and do not change data from one storage class to other.

TIP: In this case you can save your GameEntries table (without ObservableCollection) and Players, separated, but using id to know of what "player" is your "gameEntries", like a pair.

CodePudding user response：

ObservableCollection<PlayerEntries> is not a valid type for a SQLite database value. Your type of ObservableCollection<PlayerEntries> does not match any of the clrType == typeof(XXXX) statements, so you get that exception. You will need to rethink the class structure a little to be able to use SQLite-net like that.

If you want to use ObservableCollection<PlayerEntries>, you need to use the SQLite Extension with OneToMany relationship.

The code below is the class types.

 public class GameEntries
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }

        [OneToMany(CascadeOperations = CascadeOperation.All)]
        public ObservableCollection<PlayerEntries> Players { get; set; }
        public bool ScoringMode { get; set; }
        public string Date { get; set; }
    }
    public class PlayerEntries
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        [ForeignKey(typeof(GameEntries))]
        public int GameID { get; set; }

        [TextBlob(nameof(Points))]
        public List<int> points { get; set; }
        public string Points { get; set; }
        public string Name { get; set; }
    }

Connection and save operation for the database.

string path;
    SQLiteConnection db;
    int i = 0;
    private void Connect_Clicked(object sender, EventArgs e)
    {
        var dbName = "OneToManyDb.db3";
        path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), dbName);

        db = new SQLiteConnection(path);
        db.CreateTable<GameEntries>();
        db.CreateTable<PlayerEntries>();


    }

    private void Save_Clicked(object sender, EventArgs e)
    {
        var game = new GameEntries()
        {
            Title = "A"   i,
            Description = "B"   i
        };
        db.Insert(game);
        var player = new PlayerEntries()
        {
            Name = "a"   i,
            points = new List<int> { 1, 2, 3 }
        };
        db.Insert(player);

        game.Players = new ObservableCollection<PlayerEntries> { player };


        db.UpdateWithChildren(game);
        db.UpdateWithChildren(player);

        //var S = db.GetWithChildren<PlayerEntries>(player.Id);

    }