Habit Logger v2

HabitLoggerv2

---

Parameterized Queries in ADO.NET

---

SQLite Downloads
"SQLite Tutorial For Beginners - Make A Database In No Time
Setup SQLite Database in Visual Studio (14/23)

---

Git/GitHub
https://www.theserverside.com/blog/Coffee-Talk-Java-News-Stories-and-Opinions/How-to-push-an-existing-project-to-GitHub
https://kbroman.org/github_tutorial/pages/init.html
Create a .gitignore file first and add .vs/

---

There's a less verbose way of declaring an array:
int[] menuNumbers = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9];
as opposed to
int[] menuNumbers = new int { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
I was able to quickly switch this syntax by using the intellisense action 'use collection expression'

---

Clean up unused stuff (e.g., unnecessary using statements at the top of the code) by clicking the broom icon at the bottom of VS.

---

When creating a table that uses a foreign key, use ON DELETE CASCADE at the end of the foreign key declaration. This will delete all the records in the table that used that foreign key id. That way you don't have orphan records in the table that no longer relate to anything. For example:

static void CreateTables(SQLiteConnection connection)
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"
            CREATE TABLE IF NOT EXISTS Habits (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                Habit TEXT NOT NULL,
                Unit TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS HabitInstances (
                Id INTEGER PRIMARY KEY AUTOINCREMENT,
                HabitId INTEGER NOT NULL,
                Date TEXT NOT NULL,
                Quantity INTEGER NOT NULL,
                FOREIGN KEY (HabitId) REFERENCES Habits(Id) ON DELETE CASCADE
            );";

        command.ExecuteNonQuery();
    }
}

When the DeleteHabit() method is run, it will delete the selected habit and also all HabitInstances records related to that habit.

---

select, ctrl k, ctrl f - fix indentation

---

Don't use the same db connection for the whole app (uses resources)

using (var connection = new SQLiteConnection($"Data Source={dbPath}"))
{

}

using is syntactical sugar that creates a try/catch block behind the scenes...

try
{
    //new connection
    //do something
}
catch (Exception e)
{
    //handle error here
}
finally
{
    connection.Close()
}

Example:

void AddHabit()
{
    using (var connection = new SQLiteConnection($"Data Source={dbPath}"))
    {
        connection.Open();

        Console.Clear();
        PrintHabits("Add Habit");

        Console.WriteLine("\nEnter the new habit name:");
        string? habitName = Console.ReadLine();
        Console.WriteLine("\nEnter the unit of measurement (e.g., miles, pages, minutes, etc.)");
        string? unit = Console.ReadLine();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = "INSERT INTO Habits (Habit, Unit) VALUES (@habit, @unit)";
            command.Parameters.AddWithValue("@habit", habitName);
            command.Parameters.AddWithValue("@unit", unit);
            command.ExecuteNonQuery();
        }

        Console.WriteLine("\nHabit added successfully!");
    }
}

Though I suppose I may still be opening the using statement too early here and it may be better after the user prompts and variable declarations, before actually doing something db-related.