C#•14mo ago

✅ Learning SQLite for C# Academy Project

public class Database
{
    private static readonly string dbFile = "habits.db";

    public static bool CreateDatabase()
    {
        SQLiteConnection.CreateFile(dbFile);

        var sqlite = new SQLiteConnection("Data Source=" + dbFile);

        sqlite.Open();

        string sql = @"
                        CREATE TABLE IF NOT EXISTS habits(
                            Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                            Name TEXT,
                            TrackType TEXT,
                            Description TEXT)
                      ";

        SQLiteCommand command = new SQLiteCommand(sql, sqlite);

        try
        {
            command.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }

    public static bool SaveEntry(Habit newHabit)
    {
        var sqlite = new SQLiteConnection("Data Source=" + dbFile);
        sqlite.Open();

        SQLiteCommand insertSQL = new SQLiteCommand(
            "INSERT INTO habits(Name, TrackType, Description) VALUES (?,?,?)",
            sqlite
        );
        insertSQL.Parameters.AddWithValue("Name", newHabit.Name);
        insertSQL.Parameters.AddWithValue("TrackType", newHabit.TrackType);
        insertSQL.Parameters.AddWithValue("Description", newHabit.Description);

        try
        {
            insertSQL.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }

    public static bool UpdateEntry(Habit newHabit, int num)
    {
        var sqlite = new SQLiteConnection("Data Source=" + dbFile);
        sqlite.Open();

        SQLiteCommand updateSQL = new SQLiteCommand(
            "UPDATE habits SET Name=?, TrackType=?, Description=? WHERE Id=?",
            sqlite
        );

        updateSQL.Parameters.AddWithValue("Id", num);
        updateSQL.Parameters.AddWithValue("Name", newHabit.Name);
        updateSQL.Parameters.AddWithValue("TrackType", newHabit.TrackType);
        updateSQL.Parameters.AddWithValue("Description", newHabit.Description);

        try
        {
            updateSQL.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }
}

public class Database
{
    private static readonly string dbFile = "habits.db";

    public static bool CreateDatabase()
    {
        SQLiteConnection.CreateFile(dbFile);

        var sqlite = new SQLiteConnection("Data Source=" + dbFile);

        sqlite.Open();

        string sql = @"
                        CREATE TABLE IF NOT EXISTS habits(
                            Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                            Name TEXT,
                            TrackType TEXT,
                            Description TEXT)
                      ";

        SQLiteCommand command = new SQLiteCommand(sql, sqlite);

        try
        {
            command.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }

    public static bool SaveEntry(Habit newHabit)
    {
        var sqlite = new SQLiteConnection("Data Source=" + dbFile);
        sqlite.Open();

        SQLiteCommand insertSQL = new SQLiteCommand(
            "INSERT INTO habits(Name, TrackType, Description) VALUES (?,?,?)",
            sqlite
        );
        insertSQL.Parameters.AddWithValue("Name", newHabit.Name);
        insertSQL.Parameters.AddWithValue("TrackType", newHabit.TrackType);
        insertSQL.Parameters.AddWithValue("Description", newHabit.Description);

        try
        {
            insertSQL.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }

    public static bool UpdateEntry(Habit newHabit, int num)
    {
        var sqlite = new SQLiteConnection("Data Source=" + dbFile);
        sqlite.Open();

        SQLiteCommand updateSQL = new SQLiteCommand(
            "UPDATE habits SET Name=?, TrackType=?, Description=? WHERE Id=?",
            sqlite
        );

        updateSQL.Parameters.AddWithValue("Id", num);
        updateSQL.Parameters.AddWithValue("Name", newHabit.Name);
        updateSQL.Parameters.AddWithValue("TrackType", newHabit.TrackType);
        updateSQL.Parameters.AddWithValue("Description", newHabit.Description);

        try
        {
            updateSQL.ExecuteNonQuery();
            return true;
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
            return false;
        }
    }
}

The project is to build a habit tracker. I'm trying to get my Database file to be used universally throughout my code with a Create if not exists, SaveEntry, and Update Entry functions. So far, everything but my UpdateEntry function works and I can't figure out why. I have tried various things from stack overflow and I'm not quite understanding. I have a good knowledge point on SQLite3 from Python, but it's a tad different with c#. When I put in break points in my test code, the UpdateEntry function gets the correct values from the newHabit being passed to it, but when it tries to update, it just changes the Name from "Test" to 0. Thanks in advance.

15 Replies

Mekasu0124OP•14mo ago

am I supposed to close the database somewhere, I'm missing doing that? or do I need to find the current habit in the database, get it's ID, then update it?

SG97•14mo ago

you wrap the SQLiteConnection with a using

SG97•14mo ago

https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=netcore-cli

Overview - Microsoft.Data.Sqlite

An overview of Microsoft.Data.Sqlite

SG97•14mo ago

with that the connection gets disposed (and closed) automatically I assume you can also use the using without braces and it gets automatically disposed at the end of scope (method) and to make sure, how are you calling UpdateEntry UPDATE habits SET Name=?, TrackType=?, Description=? WHERE Id=? also the SaveEntry has questionmarks as values

Mekasu0124OP•14mo ago

how would I use the using statement without the braces?

public class Program
{
    public static void Main(string[] args)
    {
        Console.WriteLine("Checking If Database Exists.");
        string filePath = "habits.db";

        if (!File.Exists(filePath))
        {
            Console.WriteLine("Database Does Not Exists. Creating New Database.");
            Database.CreateDatabase();
        }

        Console.WriteLine("Database Exists. Saving New Habit.");
        Habit newHabit = new()
        {
            Name = "Test",
            TrackType = "Count",
            Description = "Cigarettes Smoked"
        };

        bool entrySaved = Database.SaveEntry(newHabit);

        if (entrySaved)
        {
            Habit newHabit2 = new()
            {
                Name = "Test2",
                TrackType = "Count",
                Description = "Cigarettes Smoked In A Day"
            };

            bool entryUpdated = Database.UpdateEntry(newHabit2, 1);

            if (entryUpdated)
            {
                Console.WriteLine("Habit Saved. Closing Application");
            }
            else
            {
                Console.WriteLine("Issue Updating Entry.Exiting Application");
                Environment.Exit(0);
            }
        }
        else
        {
            Console.WriteLine("Issue Saving New Entry.Exiting Application");
            Environment.Exit(0);
        }
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        Console.WriteLine("Checking If Database Exists.");
        string filePath = "habits.db";

        if (!File.Exists(filePath))
        {
            Console.WriteLine("Database Does Not Exists. Creating New Database.");
            Database.CreateDatabase();
        }

        Console.WriteLine("Database Exists. Saving New Habit.");
        Habit newHabit = new()
        {
            Name = "Test",
            TrackType = "Count",
            Description = "Cigarettes Smoked"
        };

        bool entrySaved = Database.SaveEntry(newHabit);

        if (entrySaved)
        {
            Habit newHabit2 = new()
            {
                Name = "Test2",
                TrackType = "Count",
                Description = "Cigarettes Smoked In A Day"
            };

            bool entryUpdated = Database.UpdateEntry(newHabit2, 1);

            if (entryUpdated)
            {
                Console.WriteLine("Habit Saved. Closing Application");
            }
            else
            {
                Console.WriteLine("Issue Updating Entry.Exiting Application");
                Environment.Exit(0);
            }
        }
        else
        {
            Console.WriteLine("Issue Saving New Entry.Exiting Application");
            Environment.Exit(0);
        }
    }
}

this code is not permanent. I'm just trying to get the database file working properly first before I start building the actual code. This is how I'm currently calling those functions

SG97•14mo ago

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/statements/using

Mekasu0124OP•14mo ago

do I have something wrong here?

SG97•14mo ago

the questionmarks?

Mekasu0124OP•14mo ago

is this wrong as well?

SG97•14mo ago

I've not done sqlite at all, but having ? as value placeholders sound odd

Mekasu0124OP•14mo ago

ok. I'll look more into it later today. I have to get to bed. Work in the AM : ( thanks for your help so far 🙂

SG97•14mo ago

https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=netcore-cli

Mekasu0124OP•14mo ago

https://pastebin.com/005wjqAF what do you think about this so far?

SG97•14mo ago

better, you could use instead of: cmd.Parameters.Add(new SQLiteParameter(oldHabit.Name)); -> cmd.Parameters.AddWithValue("@CurrentName", oldHabit.Name); UpdateEntry has double SqlCommand oh I see I think you don't need the sqlite.Close(); on every method, as the using should dispose/close the connection have you tried your solution? as for the new style of usings:

public static void SaveEntry(Habit newHabit)
{
    using SQLiteConnection sqlite = new SQLiteConnection(@"Data Source=habits.db");
    using SQLiteCommand cmd = new SQLiteCommand();
    sqlite.Open();

    cmd.CommandText = "INSERT INTO habits(Name, Date, TrackType, Description) VALUES ($Name, $Date, $TrackType, $Description)";
    cmd.Parameters.Add(new SQLiteParameter("$Name", newHabit.Name));
    cmd.Parameters.Add(new SQLiteParameter("$Date", newHabit.Date.ToString()));
    cmd.Parameters.Add(new SQLiteParameter("$TrackType", newHabit.TrackType));
    cmd.Parameters.Add(new SQLiteParameter("$Description", newHabit.Description));

    cmd.ExecuteNonQuery();
}

public static void SaveEntry(Habit newHabit)
{
    using SQLiteConnection sqlite = new SQLiteConnection(@"Data Source=habits.db");
    using SQLiteCommand cmd = new SQLiteCommand();
    sqlite.Open();

    cmd.CommandText = "INSERT INTO habits(Name, Date, TrackType, Description) VALUES ($Name, $Date, $TrackType, $Description)";
    cmd.Parameters.Add(new SQLiteParameter("$Name", newHabit.Name));
    cmd.Parameters.Add(new SQLiteParameter("$Date", newHabit.Date.ToString()));
    cmd.Parameters.Add(new SQLiteParameter("$TrackType", newHabit.TrackType));
    cmd.Parameters.Add(new SQLiteParameter("$Description", newHabit.Description));

    cmd.ExecuteNonQuery();
}

makes it a tad cleaner

Mekasu0124OP•14mo ago

have you tried your solution?

I'm actually finished with the project (in my opinion). https://github.com/mekasu0124/CodeReviews.Console.HabitTracker/tree/master/HabitTracker.mekasu0124 you're welcome to view it here. It's fully function and works as intended.

better, you could use instead of: cmd.Parameters.Add(new SQLiteParameter(oldHabit.Name)); -> cmd.Parameters.AddWithValue("@CurrentName", oldHabit.Name);

you're not wrong here. I may make that change at a later date when I go back to this project to add other ways of tracking habits than just by count.

as for the new style of usings:

you're not wrong here. It would definitely achieve a better cleaner readability. I'll incorporate that at a later date as well. ty ❤️ I do appreciate all of your assistance 🙂 if you give my app a try, and come into any problems, you're welcome to dm me and let me know 🙂 /solved

Gaming

Programming

✅ Learning SQLite for C# Academy Project