SQLiteConnection Query

Question

I'm trying to select data from Database using SQLiteConnection. It's an UWP application.

public class ResumeModel
{
    public List<User> Users { get; set; } = new List<User>();

    public ResumeModel()
    {
        using (var connection = new SQLiteConnection(new SQLite.Net.Platform.WinRT.SQLitePlatformWinRT(), App.path))
        {
            try
            {
                object query = connection.Query<User>("Select * From User", null);
                if(query != null)
                {
                    Users = (List <User>) query;
                }
            } catch(Exception ex)
            {
                Debug.Write(ex.ToString());
            }
        }
    }
}

I'm getting the exception: "Object reference not set to an instance of an object"

Here is my User class:

public class User
{
    [SQLite.Net.Attributes.PrimaryKey, SQLite.Net.Attributes.AutoIncrement]
    public int userID { get; set; }
    public String username { get; set; }

    public User()
    { }

    public User(int userID, string name)
    {
        this.userID = userID;
        this.username = name;
    }
}

Does anyone know what I'm doing wrong?

Thanks

do a google search for the following `C# Initialize Class with a List property` — MethodMan, May 02 '17 at 20:07
I think the problem isn't in the List initialization because I get the exception before set the List User — user6824563, May 02 '17 at 20:16
Possible duplicate of [What is a NullReferenceException, and how do I fix it?](http://stackoverflow.com/questions/4660142/what-is-a-nullreferenceexception-and-how-do-i-fix-it) — mason, May 02 '17 at 20:43

score 2 · Accepted Answer · answered May 02 '17 at 20:31

You are not bringing single User. You are selecting "*".

Also you are not passing any parameters. So Instead of Null remove that altogether.

I also see you are checking if query != null you don't have to do that. If there is no data, you will receive count as 0

So your query should be

List<User> query = connection.Query<User>("Select * From User");

RH6 · Answer 2 · 2017-05-02T20:52:50.220

0

Try storing your userID entries and your username entries into seperate, but index related lists. Then loop through and initialize a User object for each of the entries of these two lists as your constructor parameters.

List<int> userIDs = connection.Query<int>("Select userID From User order by userIDs");
List<string> userNames = connection.Query<string>("Select userName From User order by userIDs");
List<User> Users = new List<User>();

for (int i = 0; i < userIDs.Count(); i++)
{
    User addition = new User(userIDs[i], userNames[i]);
    Users.add(addition);
}

The order by clause is to ensure the indexes of the two queries match.

edited May 02 '17 at 20:52

answered May 02 '17 at 20:18

RH6

144
8

@user6824563 do you know where this exception is being thrown? Also would you mind telling me what some of your locals are when debugging – RH6 May 02 '17 at 20:22
The exception is thrown in object query = connection.Query("Select * From User", null); – user6824563 May 02 '17 at 20:27
App.path is only the local of my SQLite database – user6824563 May 02 '17 at 20:27
Look at the other answer provided, I believe your best route is to store the userID and username for each row into seperate Lists, and then create your list of Users with each entry from the previous two lists as your initializations. This can be done with some not too complicated looping. – RH6 May 02 '17 at 20:37

SQLiteConnection Query

2 Answers2