How to save a list of objects in a SQLite database and read it to retrieve it?

Question

I am currently making an application in C #. I am trying to save a user's data in a SQLite database. This user has a name, a first name, a username, a password, a balance and a list of programs. I can save everything in the database except the program list. Here is the code that I have now to record all this :

 SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionString);

                try
                {
                    sqliteCon.Open();
                    string Query = "Update tblUser Set Balance ='"+CurrentUser.Balance+"',ProgramList='"+CurrentUser.ProgramList+"' Where UserName='"+CurrentUser.UserName+"'";
                    SQLiteCommand createCommand = new SQLiteCommand(Query, sqliteCon);
                    createCommand.ExecuteNonQuery();
                    sqliteCon.Close();
                    this.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

The problem is when I want to recover the data I have an error message saying: Can not cast an object of type "System.String" type "System.Collection.Generic.List [ClassLibrary1. Program]

Here is how my table is structured:

CREATE TABLE "tblUser" (
    "ID"    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    "FirstName" TEXT NOT NULL,
    "LastName"  TEXT NOT NULL,
    "Username"  TEXT NOT NULL UNIQUE,
    "Password"  TEXT NOT NULL,
    "Balance"   INT NOT NULL,
    "ProgramList"   LIST
);

Here is the code when I try to recover the data :

  SQLiteConnection sqliteCon = new SQLiteConnection(dbConnectionstring);
            try
            {
                sqliteCon.Open();
                string Query = "select * from tblUser where UserName='" + this.txtUsername.Text + "' and PassWord='" + this.txtPassword.Password + "'";
                SQLiteCommand createCommand = new SQLiteCommand(Query, sqliteCon);

                createCommand.ExecuteNonQuery();
                SQLiteDataReader dr = createCommand.ExecuteReader();

                int count = 0;
                while (dr.Read())
                {
                    User user = new User();
                    User.Username = (string)dr[3];
                    User.FirstName = (string)dr[1];
                    User.LastName = (string)dr[2];
                    User.Balance = (int)dr[5];
                    User.ProgramList = (List<Programme>)dr[6] ;
                    CurrentUser = User;
                }
...

I can recover everything except the list ... I know that the mistake comes from : User.ProgramList = (List)dr[6] ; and from "ProgramList" LIST

To answer Ben :

public class User
    {

        public string UserName { get; set; }
        public string FirstName{ get; set; }
        public string LastName { get; set; }
        public int Balance { get; set; }
        public List<Program> ProgramList { get; set; }


        public User() { }
        public User(string username, string firstname, string lastname, int balance,List<Program> programlist)
        {
            this.UserName = username;
            this.FirstName = firstname;
            this.LastName = lastname;
            this.Balance = balance;
            this.ProgramList = programlist;
        }

If you change User.ProgramList to string instead of list what is the result? — pagratios
– pagratios, Commented Jun 2, 2019 at 22:32
I get a string then. That's not what I want, I want a list of programs ... — Ush_63100
– Ush_63100, Commented Jun 2, 2019 at 22:41
That insert...shouldn't work. What exactly is the definition of your User class? In particular User.ProgramList. The C# class, not the SQL table definition. — Ben
– Ben, Commented Jun 2, 2019 at 22:55

Ben · Accepted Answer · 2019-06-03 00:03:28Z

There's a few things here. First be aware that when you write:

string Query = "Update tblUser Set Balance ='"+CurrentUser.Balance+"',ProgramList='"+CurrentUser.ProgramList+"' Where UserName='"+CurrentUser.UserName+"'";

You're not inserting the ProgramList, you're invoking the ToString() method of the list to concatenate it to the rest of the string you're creating, which will come out something like "System.Collection.Generic.List [ClassLibrary1. Program]", so you're actually inserting a string into that column. Which brings up another issue, your table is not configured with "ProgramList" LIST and saying it is just confuses people trying to help understand your question. My guess is it's also a TEXT field, but it would be more helpful to include the actual definition rather than what you think it should be.

To the issue. You can't store a LIST in a database column. Think of it this way, each field (each column in a row) is a single piece of data. You don't have a single piece of data, you have a collection of data. As such, you need a new table to store that data. There are two main ways to handle this. One is to record the data for each program a user has, something like:

CREATE TABLE "tblProgramsForUser" (
    "ID"    INTEGER NOT NULL,
    "UserID" INTEGER NOT NULL,
    "ProgramName" TEXT NOT NULL,
    "ProgramType"  TEXT NOT NULL,
    "OtherProgramField"  TEXT NOT NULL
);

Or if you expect to reuse programs a lot, store them separately and then join them:

CREATE TABLE "tblPrograms" (
    "ID"    INTEGER NOT NULL,
    "ProgramName" TEXT NOT NULL,
    "ProgramType"  TEXT NOT NULL,
    "OtherProgramField"  TEXT NOT NULL
);

CREATE TABLE "tblUserPrograms" (
    "UserID"    INTEGER NOT NULL,
    "ProgramID" INTEGER NOT NULL
);

So now what you would do in your program is update the user specific information, and then loop through your list of programs for the user and insert them into the new table (or update if applicable).

Now all that said, you should look into using SQL Parameters: https://csharp-station.com/Tutorial/AdoDotNet/Lesson06 to make your program more secure. It might also pay to look into an ORM (like Entity Framework) which would tend to make all of this logic much easier.

Hope this helps.

Ok I understand better my mistake. It is therefore not possible to directly put a list in a column of a table. The first solution seems exploitable. I will try, thanks for the time you have granted!

Collectives™ on Stack Overflow

How to save a list of objects in a SQLite database and read it to retrieve it?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related