C#•9mo ago

✅ Using SQLite with C#

The next project of the C# Academy is creating a habit tracker. I'm a little confused on the SQLite aspect of it as I can't seem to get the statement right. I've watched a basic tutorial video https://www.youtube.com/watch?v=oeuTw00F1as&t=725s and I'm using various stack overflows (that make sense) to get my sql working. I have some SQLite knowledge from working with Python, it just seems to be a smidge different.

private static readonly string dbFile = "habits.db";
public static void CreateDatabase()
{
  SQLiteConnection.CreateFile(dbFile);
  var sqlite = new SQLConnection("Data Source=" + dbFile);
  sqlite.Open();
  string sql = @"CREATE TABLE IF NOT EXISTS habits(
                  Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                  Name TEXT NOT NULL,
                  TrackType TEXT NOT NULL,
                  Description TEXT NOT NULL
                ";
  SQLiteCommand command = new SQLiteCommand(sql, sqlite);
  command.ExecuteNonQuery();
}
public static void SaveEntry(Habit newHabit)
{
  var slite = new SQLiteConnection("Data Source=" + dbFile);
  sqlite.Open();
  SQLiteCommand insertSQL = new SQLiteCommand(
    "INSERT INTO habits(Name, TrackType, Description) VALUES (?,?,?)",
    sqlite
  );
  insertSQL.Parameters.Add(newHabit.Name);
  insertSQL.Parameters.Add(newHabit.TrackType);
  insertSQL.Parameters.Add(newHabit.Description);

  try
  {
      insertSQL.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
      throw new Exception(ex.Message);
}

private static readonly string dbFile = "habits.db";
public static void CreateDatabase()
{
  SQLiteConnection.CreateFile(dbFile);
  var sqlite = new SQLConnection("Data Source=" + dbFile);
  sqlite.Open();
  string sql = @"CREATE TABLE IF NOT EXISTS habits(
                  Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
                  Name TEXT NOT NULL,
                  TrackType TEXT NOT NULL,
                  Description TEXT NOT NULL
                ";
  SQLiteCommand command = new SQLiteCommand(sql, sqlite);
  command.ExecuteNonQuery();
}
public static void SaveEntry(Habit newHabit)
{
  var slite = new SQLiteConnection("Data Source=" + dbFile);
  sqlite.Open();
  SQLiteCommand insertSQL = new SQLiteCommand(
    "INSERT INTO habits(Name, TrackType, Description) VALUES (?,?,?)",
    sqlite
  );
  insertSQL.Parameters.Add(newHabit.Name);
  insertSQL.Parameters.Add(newHabit.TrackType);
  insertSQL.Parameters.Add(newHabit.Description);

  try
  {
      insertSQL.ExecuteNonQuery();
  }
  catch (Exception ex)
  {
      throw new Exception(ex.Message);
}

I got some of this code from https://stackoverflow.com/questions/19479166/sqlite-simple-insert-query and https://learn.microsoft.com/en-us/dotnet/standard/data/sqlite/?tabs=visual-studio however when I run my code

public class Program
{
    public static void Main(string[] args)
    {
        string filePath = "habits.db";
        if (!File.Exists(filePath))
        {
            Database.CreateDatabase();
        }
        Habit newHabit = new()
        {
            Name = "Test",
            TrackType = "Count",
            Description = "Cigarettes Smoked"
        };
        Database.SaveEntry(newHabit);
     }
}

public class Program
{
    public static void Main(string[] args)
    {
        string filePath = "habits.db";
        if (!File.Exists(filePath))
        {
            Database.CreateDatabase();
        }
        Habit newHabit = new()
        {
            Name = "Test",
            TrackType = "Count",
            Description = "Cigarettes Smoked"
        };
        Database.SaveEntry(newHabit);
     }
}

I get the problem on my SaveEntry() > insertSQL.Parameters.Add(newHabit.Name); => Unable to cast object of type 'System.String' to type 'System.Data.SQLiteParameter' and I'm not entirely sure why? thanks

1 Reply

Mekasu0124•9mo ago

public static void SaveEntry(Habit newHabit)
{
    Console.WriteLine("Creating Connection To Database File.");
    var sqlite = new SQLiteConnection("Data Source=" + dbFile);

    Console.WriteLine("Connection Created. Opening Database File.");
    sqlite.Open();

    Console.WriteLine("Database File Opened. Creating SQL Statement as new command");
    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);

    Console.WriteLine("SQL Statment Created. Executing Command");
    try
    {
        insertSQL.ExecuteNonQuery();
        Console.WriteLine("Command Executed Successfully. New Habit Saved");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

public static void SaveEntry(Habit newHabit)
{
    Console.WriteLine("Creating Connection To Database File.");
    var sqlite = new SQLiteConnection("Data Source=" + dbFile);

    Console.WriteLine("Connection Created. Opening Database File.");
    sqlite.Open();

    Console.WriteLine("Database File Opened. Creating SQL Statement as new command");
    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);

    Console.WriteLine("SQL Statment Created. Executing Command");
    try
    {
        insertSQL.ExecuteNonQuery();
        Console.WriteLine("Command Executed Successfully. New Habit Saved");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

I was able to fix the issue by using AddWithValue instead of just Add. Never mind.

Gaming

Programming

✅ Using SQLite with C#