C#•10mo ago

✅ Catching different errors using System.Data.SQLite

public void CreateSomeTable()
{
  Using SQLiteConnection conn = new(_dbFile);
  Using SQLiteCommand cmd = conn.CreateCommand();

  conn.Open();

  cmd.CommandText = @"CREATE TABLE IF NOT EXISTS
                      [login] (
                      [Username] VARCHAR(15),
                      [Password] VARCHAR(30),
                      UNIQUE(Username)
                      )";
  try
  {
      cmd.ExecuteNonQuery();
  }
  catch (SQLiteException ex)
  {
      throw new SQLiteExecption ex;
  }
}

public void InsertNewUser(UserModel user)
{
  Using SQLiteConnection conn = new(_dbFile);
  ...

  cmd.CommandText = @"INSERT INTO login(Username, Password) VALUES ($un, $pw)";
  cmd.Parameters.AddWithValue("$un", user.Username);
  cmd.Parameters.AddWithValue("$pw", user.Password);

  try
  {
      cmd.ExecuteNonQuery();
  }
  catch (SQLiteException ex)
  {
      throw new SQLiteException(ex.Message);
  }
}

public void CreateSomeTable()
{
  Using SQLiteConnection conn = new(_dbFile);
  Using SQLiteCommand cmd = conn.CreateCommand();

  conn.Open();

  cmd.CommandText = @"CREATE TABLE IF NOT EXISTS
                      [login] (
                      [Username] VARCHAR(15),
                      [Password] VARCHAR(30),
                      UNIQUE(Username)
                      )";
  try
  {
      cmd.ExecuteNonQuery();
  }
  catch (SQLiteException ex)
  {
      throw new SQLiteExecption ex;
  }
}

public void InsertNewUser(UserModel user)
{
  Using SQLiteConnection conn = new(_dbFile);
  ...

  cmd.CommandText = @"INSERT INTO login(Username, Password) VALUES ($un, $pw)";
  cmd.Parameters.AddWithValue("$un", user.Username);
  cmd.Parameters.AddWithValue("$pw", user.Password);

  try
  {
      cmd.ExecuteNonQuery();
  }
  catch (SQLiteException ex)
  {
      throw new SQLiteException(ex.Message);
  }
}

so I'm wanting to write a custom class that error handles my database. For example:

public class DatabaseErrorHandler()
{
  public void HandleUniqueEntryError()
  {
      // some code here
  }
}

public class DatabaseErrorHandler()
{
  public void HandleUniqueEntryError()
  {
      // some code here
  }
}

I would somehow (idk how) use the class within the code above for adding a new user to the database. In the create database function, the Username field is unique. A user tries to create a new account with a username that already exists. I would like for the process to be something like 1. user clicks create user button call database function 2. database tries to save -> send error to custom error handler class 3. function for unique errors sends back some type of value to the View for the user to tell them that the username already exists and to try again How would I go about doing this? I wanted to do a custom class so that I could call the error class anywhere I needed throughout my database file and be able to still have the program act accordingly.

20 Replies

lycian•10mo ago

You can add a when clause to your exception message handling.

catch (SQLiteException ex) when (DatabaseErrorHandler.HandleError(ex))
{
  // anything else you would wnat to do
}

catch (SQLiteException ex) when (DatabaseErrorHandler.HandleError(ex))
{
  // anything else you would wnat to do
}

where the handler would be

public static class DatabaseErrorHandler
{
  public static bool HandleError(Exception ex)
  {
  }
}

public static class DatabaseErrorHandler
{
  public static bool HandleError(Exception ex)
  {
  }
}

Mekasu0124OP•10mo ago

ok so with

catch (SQLiteException ex) when (Database.ErrorHandler.HandleError(ex))
{
  // anything here
}

catch (SQLiteException ex) when (Database.ErrorHandler.HandleError(ex))
{
  // anything here
}

how would the when clause know that it's a UNIQUE error versus when it's a Table/Column/Row Does Not Exist, or when I try to query info from the database, and get an error of the username not found?

lycian•10mo ago

The SQLiteException itself has an error code https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqliteexception.sqliteerrorcode?view=msdata-sqlite-7.0.0#microsoft-data-sqlite-sqliteexception-sqliteerrorcode Those map to documented codes. For example, if there's a constraint error you would find https://www.sqlite.org/rescode.html#constraint then check the extended error code if you wanted to know more about the constraint

SqliteException.SqliteErrorCode Property (Microsoft.Data.Sqlite)

Gets the SQLite error code.

lycian•10mo ago

and to be clear, I don't think the when clause has to be a static class in case you need to keep instance data to do something with. I just normally like it that way because evaluation can be a bit tricky to understand if that's used for flow control and data states

Mekasu0124OP•10mo ago

nah it's fine. I prefere static functions when needed as it's often better to have Class.Method() instead of Class myClass = new Class(); myClass.Method(); DatabaseErrorHandler.cs: https://pastebin.com/jK5gVjM6 Database.cd: https://pastebin.com/2wriVmhb How does this look to you?

lycian•10mo ago

That looks reasonable to me

Mekasu0124OP•10mo ago

ok cool. Another question. How would I do something (idk what it's called) where I can say like

var errorMessage = ErrorMessages.2

var errorMessage = ErrorMessages.2

or something like that so I can have my various error messages stored in their own thing, and then call them where needed?

lycian•10mo ago

not sure I fully follow your DatabaseErrorHandler could have out parameters for the error message, might make it cleaner

catch (Exception e) when (ErrorHandler.IsUserError(out var message))
{
  return message;
}

catch (Exception e) when (ErrorHandler.IsUserError(out var message))
{
  return message;
}

Mekasu0124OP•10mo ago

ok I'll give that a try, but I mean something like. Hold on let me look through my projects to see if I can find it

public enum SupportedOperatingSystem
{
    Windows,
    Linux,
    Mac
}

public enum SupportedOperatingSystem
{
    Windows,
    Linux,
    Mac
}

it's like this and id be able to call it or something. I'm not sure, but I have to get on to bed. I'll be back tomorrow or wednesday. Thanks for your help

Pobiega•10mo ago

Enums can't be strings in C#, but you could just make a static class with public const strings, if you just want "a list" of errors. It would give you a similar developer experience

lycian•10mo ago

If you prefer an enum for other reasons, you can convert to a string with Enum.ToString<SupportedOperatingSystem>(SupportedOperatingSystem.Windows)

Mekasu0124OP•10mo ago

that doesn't sound like a bad idea. I just like having all of my stuff in one spot, and then just call what I need when I need it. Something like when the password in the wrong format

public class ErrorStrings
{
  internal static string InvalidUsernameFormat() => "my error message here";
}

public class ErrorStrings
{
  internal static string InvalidUsernameFormat() => "my error message here";
}

type thing I'll keep that in mind, but it seems a bit extensive for this specific use case unless it's the better route

lycian•10mo ago

if there's no other real reason for an enum than just to map to a string then yea just do statics

internal static readonly string InvalidUsernameFormat = "my error message here"

internal static readonly string InvalidUsernameFormat = "my error message here"

Mekasu0124OP•10mo ago

ok bet. I have another question as well. I'm trying to find a list of all the System.Data.SQLite error code numbers to use in my DatabaseErrorHandler class so that I can have a sort of filter system, but the only thing I'm finding is this https://learn.microsoft.com/en-us/dotnet/api/microsoft.data.sqlite.sqliteexception.sqliteerrorcode?view=msdata-sqlite-7.0.0 because like I know I can use the property SqliteErrorCode and it has the integer value of the error code, but without being able to see all of the error codes, I don't know what codes to check for until I run into them I'll put a pin in the custom error handler for now. I want to get the application functional first

Pobiega•9mo ago

Why not just internal const string?

lycian•9mo ago

it depends. Short answer is const copies the string into every location at compilation, and static readonly will always just be a reference. and it's just habit

Mekasu0124OP•9mo ago

Does using internal const string cause the program to be more efficient?

lycian•9mo ago

I actually haven't looked too much, but I'm going to guess the difference is minimal

Mekasu0124OP•9mo ago

oh ok so safe to assume it's just user preference

lycian•9mo ago

The only legitimate impact I've seen is because in a large project (especially with strings in testing) you can run out of const space in compilation. I believe there is an upper limit. It's such a random and nuanced thing that if you don't hit it, do whatever feels good to you JaredPar definitely would know, but I dare not invoke his wrath on this subject matter on a Friday afternoon

Gaming

Programming

✅ Catching different errors using System.Data.SQLite