C#•14mo ago

✅ System.Data.SQLiteDatabase .NET 7.0 Reading Items From Database

public static List<GameModel> GetAllGames()
{
    using SQLiteConnection? conn = new(dbFile);
    using SQLiteCommand? cmd = conn.CreateCommand();
    SQLiteDataReader? reader;

    List<GameModel> games = new();

    conn.Open();

    reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        GameModel game = new()
        {
            Id = Convert.ToInt32(reader["Id"].ToString()),
            Username = reader["User"].ToString(),
            Date = reader["Date"].ToString(),
            StartTime = reader["StartTime"].ToString(),
            EndTime = reader["EndTime"].ToString(),
            Duration = reader["Duration"].ToString(),
            Score = Convert.ToInt32(reader["Score"].ToString()),
            Total = Convert.ToInt32(reader["Total"].ToString()),
            Difficulty = reader["Difficulty"].ToString(),
            GameType = reader["GameType"].ToString()
        };

        games.Add(game);
    }

    return games;
}

public static List<GameModel> GetAllGames()
{
    using SQLiteConnection? conn = new(dbFile);
    using SQLiteCommand? cmd = conn.CreateCommand();
    SQLiteDataReader? reader;

    List<GameModel> games = new();

    conn.Open();

    reader = cmd.ExecuteReader();

    while (reader.Read())
    {
        GameModel game = new()
        {
            Id = Convert.ToInt32(reader["Id"].ToString()),
            Username = reader["User"].ToString(),
            Date = reader["Date"].ToString(),
            StartTime = reader["StartTime"].ToString(),
            EndTime = reader["EndTime"].ToString(),
            Duration = reader["Duration"].ToString(),
            Score = Convert.ToInt32(reader["Score"].ToString()),
            Total = Convert.ToInt32(reader["Total"].ToString()),
            Difficulty = reader["Difficulty"].ToString(),
            GameType = reader["GameType"].ToString()
        };

        games.Add(game);
    }

    return games;
}

I use this exact code (change a couple of variable names) in my other version of the math game I created and it keeps hollering at me object reference not set to an instance of an object when it comes to reader = cmd.ExecuteReader();. What is happening? There are 2 records in the database, but it just will not read.

39 Replies

SG97•14mo ago

debug to determine what is null most likely cmd assuming dbFile is correct

Mekasu0124OP•14mo ago

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="database" connectionString="Data Source=main.db" />
    </connectionStrings>
</configuration>

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <connectionStrings>
        <add name="database" connectionString="Data Source=main.db" />
    </connectionStrings>
</configuration>

this is my App.config file and at the top of my database file, I have the line

private static readonly string? dbFile = ConfigurationManager.ConnectionStrings["database"].ConnectionString;

private static readonly string? dbFile = ConfigurationManager.ConnectionStrings["database"].ConnectionString;

which I know is correct since it's saving the game records to the database, and creating the database if I delete it and re-run the program

SG97•14mo ago

debug on what's null no need to guess on a side note, dbFile is not a db file, it's a connection string. Naming is kinda confusing

Mekasu0124OP•14mo ago

it's the reader that's null

SG97•14mo ago

so in that case cmd.ExecuteReader() is returning null?

Mekasu0124OP•14mo ago

yes

SG97•14mo ago

what is the actual query you're running against the db what is the CommandText of the cmd

Mekasu0124OP•14mo ago

the code I have is in the initial post. I've never had to create CommandText when reading all items from the database.

I use this exact code (change a couple of variable names) in my other version of the math game I create...

It reads just fine and normal in my other math game version

SG97•14mo ago

I've no idea how you would read anything from a db without an actual query. Maybe someone else can help you, I'm out of suggestions

Mekasu0124OP•14mo ago

I'll try putting in a query for the CommandText and see what happens I appreciate your suggestions thus far

public static int GetNumber(string gameType, string difficulty)
{
    Random rng = new();
    int randomNumber = 0;

    if (gameType == "Addition" || gameType == "Subtraction")
    {
        if (difficulty == "Easy")
        {
            randomNumber = rng.Next(0, 300);
        }
        else if (difficulty == "Medium")
        {
            randomNumber = rng.Next(0, 600);
        }
        else if (difficulty == "Hard")
        {
            randomNumber = rng.Next(0, 900);
        }
    }
    else if (gameType == "Multiplication" || gameType == "Division")
    {
        if (difficulty == "Easy")
        {
            randomNumber = rng.Next(0, 100);
        }
        else if (difficulty == "Medium")
        {
            randomNumber = rng.Next(0, 200);
        }
        else if (difficulty == "Hard")
        {
            randomNumber = rng.Next(0, 300);
        }
    }

    return randomNumber;
}

public static int GetNumber(string gameType, string difficulty)
{
    Random rng = new();
    int randomNumber = 0;

    if (gameType == "Addition" || gameType == "Subtraction")
    {
        if (difficulty == "Easy")
        {
            randomNumber = rng.Next(0, 300);
        }
        else if (difficulty == "Medium")
        {
            randomNumber = rng.Next(0, 600);
        }
        else if (difficulty == "Hard")
        {
            randomNumber = rng.Next(0, 900);
        }
    }
    else if (gameType == "Multiplication" || gameType == "Division")
    {
        if (difficulty == "Easy")
        {
            randomNumber = rng.Next(0, 100);
        }
        else if (difficulty == "Medium")
        {
            randomNumber = rng.Next(0, 200);
        }
        else if (difficulty == "Hard")
        {
            randomNumber = rng.Next(0, 300);
        }
    }

    return randomNumber;
}

before I try that though, I do have a question for you. What is a better way to write this?

SG97•14mo ago

generally I don't do strings on predefined "types", I use enums

Mekasu0124OP•14mo ago

I tried doing

public static int GetNumber(...)
{
  Random rng = new();

  if (gameType == "Addition" || gameType == "Subtraction")
  {
      return difficulty switch
      {
          "Easy" => rng.Next(0,300),
          "Medium" => rng.Next(0,600),
          "Hard" => rng.Next(0,900)
      };
  }
  else if (gameType == "Multiplication" || gameType == "Division")
  {
      return difficulty switch
      {
          "Easy" => rng.Next(0,100),
          "Medium" => rng.Next(0,200),
          "Hard" => rng.Next(0,300)
      };
  }
}

public static int GetNumber(...)
{
  Random rng = new();

  if (gameType == "Addition" || gameType == "Subtraction")
  {
      return difficulty switch
      {
          "Easy" => rng.Next(0,300),
          "Medium" => rng.Next(0,600),
          "Hard" => rng.Next(0,900)
      };
  }
  else if (gameType == "Multiplication" || gameType == "Division")
  {
      return difficulty switch
      {
          "Easy" => rng.Next(0,100),
          "Medium" => rng.Next(0,200),
          "Hard" => rng.Next(0,300)
      };
  }
}

but it told me that not all code paths returned a value... wdym?

SG97•14mo ago

I don't like matching strings anywhere, especially when you know what gametypes and difficulties you have

Mekasu0124OP•14mo ago

so what do you do instead?

SG97•14mo ago

        public enum GameType
        {
            Addition,
            Substraction
        }

        public enum Difficulty
        {
            Easy,
            Medium,
            Hard
        }

        public enum GameType
        {
            Addition,
            Substraction
        }

        public enum Difficulty
        {
            Easy,
            Medium,
            Hard
        }

Mekasu0124OP•14mo ago

public Enum GameTypes
{
  Addition,
  Subtraction,
  Multiplication,
  Division
}

public Enum Difficulties
{
  Easy,
  Medium,
  Hard
}

public Enum GameTypes
{
  Addition,
  Subtraction,
  Multiplication,
  Division
}

public Enum Difficulties
{
  Easy,
  Medium,
  Hard
}

so let's say I did this in my Helpers.cs file, how would I go about using that for what I'm wanting to do?

SG97•14mo ago

I think you need a discard for your switch expression oh

Mekasu0124OP•14mo ago

the discard plus changing if else if to an else fixed that problem are Enums more efficient?

SG97•14mo ago

you're not handling the case where gametype is not addition/substraction/multiplication/division ah, you changed to else all good

Mekasu0124OP•14mo ago

I switched it from an if/else if to an if/else and that fixed that so let's say theoretically I created the two enums shown here. How would I use them in place of my strings and such?

SG97•14mo ago

instead of passing in "Addition" as the gameType you pass in GameType.Addition same goes for the difficulty

Mekasu0124OP•14mo ago

oh ok. I'm trying to set one up to learn how to use them, but I'm not sure what I'm doing wrong

Mekasu0124OP•14mo ago

do they getters/setters?

SG97•14mo ago

Mekasu0124OP•14mo ago

SG97•14mo ago

are you using top level statements

Mekasu0124OP•14mo ago

so it's enum not Enum

SG97•14mo ago

ah yes

Mekasu0124OP•14mo ago

I don't know what a top level statement is and I've never used enums like this before so I think I'll keep the strings if it's not going to hurt anything

SG97•14mo ago

the idea is that the developer cannot pass in gameType as "asdf" and difficulty "fdas" as they are predefined as enums and generally matching magic strings is a code smell but yes, one problem at a time

Mekasu0124OP•14mo ago

public static void ShowMenu(string username, string date)
{
    string choice = GetMenuChoice();

    switch (choice)
    {
        case "Addition":
        case "Subtraction":
        case "Multiplication":
        case "Division":
        case "Random":
            string difficulty = GetGameDifficulty();
            int maxQuestions = GetNumberOfQuestions();

            Game.StartGame(username, date, choice, difficulty, maxQuestions);
            break;

        case "Previous Games":
            Game.ShowPreviousGames(username, date);
            break;

        case "Quit Game":
            ColorCon.WriteLine("Exiting Application", ConsoleColor.Red);
            Environment.Exit(0);
            break;
    }
}

public static string GetMenuChoice()
{
    Console.Clear();
    string menuOptions = """
                            What Would You Like To Do?

                            A - Addition
                            S - Subtraction
                            M - Multiplication
                            D - Division
                            R - Random
                            P - Previous Games
                            Q - Quit Game
                             
                             
                            Your Selection: 
                            """;
    List<string> choices = new() { "a", "s", "m", "d", "r", "p", "q" };
    string errorText = "Invalid Input. Choice Must Be: \"A\", \"S\", \"M\", \"D\", \"R\", \"P\", or \"Q\"";

    string choice = ColorCon.GetStringFromConsole(
        prompt: menuOptions,
        color: ConsoleColor.Cyan,
        validator: x => choices.Contains(x.ToLower()),
        errorMessage: errorText);

    return choice switch
    {
        "a" or "A" => "Addition",
        "s" or "S" => "Subtraction",
        "m" or "M" => "Multiplication",
        "d" or "D" => "Division",
        "r" or "R" => "Random",
        "p" or "P" => "Previous Games",

public static void ShowMenu(string username, string date)
{
    string choice = GetMenuChoice();

    switch (choice)
    {
        case "Addition":
        case "Subtraction":
        case "Multiplication":
        case "Division":
        case "Random":
            string difficulty = GetGameDifficulty();
            int maxQuestions = GetNumberOfQuestions();

            Game.StartGame(username, date, choice, difficulty, maxQuestions);
            break;

        case "Previous Games":
            Game.ShowPreviousGames(username, date);
            break;

        case "Quit Game":
            ColorCon.WriteLine("Exiting Application", ConsoleColor.Red);
            Environment.Exit(0);
            break;
    }
}

public static string GetMenuChoice()
{
    Console.Clear();
    string menuOptions = """
                            What Would You Like To Do?

                            A - Addition
                            S - Subtraction
                            M - Multiplication
                            D - Division
                            R - Random
                            P - Previous Games
                            Q - Quit Game
                             
                             
                            Your Selection: 
                            """;
    List<string> choices = new() { "a", "s", "m", "d", "r", "p", "q" };
    string errorText = "Invalid Input. Choice Must Be: \"A\", \"S\", \"M\", \"D\", \"R\", \"P\", or \"Q\"";

    string choice = ColorCon.GetStringFromConsole(
        prompt: menuOptions,
        color: ConsoleColor.Cyan,
        validator: x => choices.Contains(x.ToLower()),
        errorMessage: errorText);

    return choice switch
    {
        "a" or "A" => "Addition",
        "s" or "S" => "Subtraction",
        "m" or "M" => "Multiplication",
        "d" or "D" => "Division",
        "r" or "R" => "Random",
        "p" or "P" => "Previous Games",

        "q" or "Q" => "Quit Game",
        _ => throw new Exception("Error Determining Game Type In Helper Function")
    };
}

public static string GetGameDifficulty()
{
    string difficultyOptions = """

                                Select A Difficulty

                                E - Easy
                                M - Medium
                                H - Hard

                                Your Selection: 
                                """;

    List<string> choices = new() { "e", "m", "h" };
    string errorText = "Invalid Input. Choice Must Be: \"E\", \"M\", or \"H\"";

    string difficulty = ColorCon.GetStringFromConsole(
        prompt: difficultyOptions,
        color: ConsoleColor.Magenta,
        validator: x => choices.Contains(x.ToLower()),
        errorMessage: errorText);

    return difficulty switch
    {
        "e" => "Easy",
        "m" => "Medium",
        "h" => "Hard"
    };
}

        "q" or "Q" => "Quit Game",
        _ => throw new Exception("Error Determining Game Type In Helper Function")
    };
}

public static string GetGameDifficulty()
{
    string difficultyOptions = """

                                Select A Difficulty

                                E - Easy
                                M - Medium
                                H - Hard

                                Your Selection: 
                                """;

    List<string> choices = new() { "e", "m", "h" };
    string errorText = "Invalid Input. Choice Must Be: \"E\", \"M\", or \"H\"";

    string difficulty = ColorCon.GetStringFromConsole(
        prompt: difficultyOptions,
        color: ConsoleColor.Magenta,
        validator: x => choices.Contains(x.ToLower()),
        errorMessage: errorText);

    return difficulty switch
    {
        "e" => "Easy",
        "m" => "Medium",
        "h" => "Hard"
    };
}

well if I use those enums, then these functions will have to update accordingly and I don't know how to do that

SG97•14mo ago

you can always improve the codebase later ofc you do

Mekasu0124OP•14mo ago

so what. List<string> choices = new() { "e", "m", "h" }; would become....what....List<enum> choices = new() { Difficulties.Easy, Difficulties.Medium, Difficulties.Hard };?

SG97•14mo ago

no, those are user inputs you map them to gametype/difficulty later on

Mekasu0124OP•14mo ago

so then should it be

public enum GameTypes
{
  Addition = "Addition",
  ...
}

public enum GameTypes
{
  Addition = "Addition",
  ...
}

? yea I'm lost. I'm just not going to worry about it. Thanks anyways though

SG97•14mo ago

always make it work first, then improve

Mekasu0124OP•14mo ago

the game is done. It's complete. Everything works now and works as it should. I'm pushing a release to my github as we speak. I just wanted to learn about the enums and how to use them is all

SG97•14mo ago

you figured out the db problem?

Mekasu0124OP•14mo ago

yep. I thought for some odd ball reason that I didn't need to set a cmd.CommandText = "SELECT * FROM [table]"; and that turned out to be my problem

Gaming

Programming

✅ System.Data.SQLiteDatabase .NET 7.0 Reading Items From Database