C#•10mo ago

✅ System.Data.SQLite Data Types

I'm wanting to save a float value to a relative database through the package System.Data.SQLite. I've not had to do this yet, and the docs for that package aren't much help.

55 Replies

oke•10mo ago

so are you looking for an example...? there isnt really a question

Angius•10mo ago

You'd do it like with any other variable Do you want to use EF or just plain SQL?

Mekasu0124OP•10mo ago

apologies

public class DatabaseEngine
{
    private static readonly string _dbFile = "Data Source=main.db";

    public void CreateDb()
    {
        using SQLiteConnection conn = new(_dbFile);
        using SQLiteCommand cmd = conn.CreateCommand();

        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS
                            [emplyees] (
                            [Id] INTEGER,
                            [FirstName] VARCHAR(2048),
                            [LastName] VARCHAR(2048),
                            [Username] VARCHAR(2048),
                            [Password] VARCHAR(2048),
                            [PayRate] ";

        try
        {
            conn.Open();
        }
    }
}

public class DatabaseEngine
{
    private static readonly string _dbFile = "Data Source=main.db";

    public void CreateDb()
    {
        using SQLiteConnection conn = new(_dbFile);
        using SQLiteCommand cmd = conn.CreateCommand();

        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS
                            [emplyees] (
                            [Id] INTEGER,
                            [FirstName] VARCHAR(2048),
                            [LastName] VARCHAR(2048),
                            [Username] VARCHAR(2048),
                            [Password] VARCHAR(2048),
                            [PayRate] ";

        try
        {
            conn.Open();
        }
    }
}

this is my code so far PayRate should be a float/double I'm used to saving them as REAL in python sqlite3 markup

Angius•10mo ago

So... do SQLite is SQLite SQL is SQL It doesn't magically gain new types just because you use it with C#

Mekasu0124OP•10mo ago

ok so then it would be a REAL then

Angius•10mo ago

Yes

Mekasu0124OP•10mo ago

oh ok. thank you very much 🙂

oke•10mo ago

im pretty sure SQLite doesnt have the same datatypes for storage i may be wrong

Angius•10mo ago

The same as what?

oke•10mo ago

SQL

Angius•10mo ago

SQL is the language SQLite is the database SQL supports whatever types a given database has

oke•10mo ago

i just felt my brain get bigger

Angius•10mo ago

Or rather, every database uses it's own flavour of SQL

Jimmacle•10mo ago

[Password] VARCHAR(2048) :pepesus:

Mekasu0124OP•10mo ago

O_o what?

oke•10mo ago

im sure its encrypted

Mekasu0124OP•10mo ago

haha ok wait

oke•10mo ago

right?

Angius•10mo ago

It better not be encrypted It better be hashed

oke•10mo ago

hashed* totally knew that

Mekasu0124OP•10mo ago

I haven't learned encryption yet in C# and besides it's a locally hosted application. Like the database and files and such are local. Nothing backups online. Not even passwords

Jimmacle•10mo ago

plaintext passwords are always bad

Mekasu0124OP•10mo ago

I like this dude 😂 ok so then how would I ~~encrypt~~ hash a password? in python, we use salt

Jimmacle•10mo ago

consider someone gets access to your PC and happens to find your database, and in your testing you used the same password you used for online accounts

Angius•10mo ago

SHA256 is supported in .NET by default

oke•10mo ago

nah, screw hashing them, just use a SecureString

Angius•10mo ago

And is... sufficient

Mekasu0124OP•10mo ago

I 100% understand your viewpoint, but my dev database passwords are always Test123! lmao SecureString is sufficient?

Angius•10mo ago

SecureString is obsolete

Jimmacle•10mo ago

securestring is ancient and not recommended

Angius•10mo ago

Deprecated, even

oke•10mo ago

but just store it in a json file by serializing the instance

Angius•10mo ago

SHA256 is sufficient

oke•10mo ago

we should use KWIC for passwords instead

Angius•10mo ago

Alternatively, use a BCrypt or Argon2id package if you want real security

Mekasu0124OP•10mo ago

ok I'm looking pulling up some sites on it now and I'll look into it when I get to that part

oke•10mo ago

compress the password, base 64 it, then hash it

Angius•10mo ago

why None of that is remotely necessary Salt and hash

oke•10mo ago

really confuse hackers, and open up more possibilities

Angius•10mo ago

Yeah nah It's just useless work

oke•10mo ago

my intentions are far beyond your understanding

Angius•10mo ago

If you're here just to meme then your intention better be to remove yourself from the conversation

oke•10mo ago

ouch

Mekasu0124OP•10mo ago

side question for you if you don't mind.

public string CreateVersionFile()
{
  try
  {
    Dictionary<string, bool> setupDict = JsonSerializer.Deserialize<Dictionary<string, bool>>(_setupPath);

    Dictionary<string, string> versionDict = new()
    {
        { "version", "1.0.0" }
    }

    setupDict.Add(versionDict);

    var newJsonData = JsonSerializer.Serialize(_setupPath, setupDict);
    return versionDict["version"];
  }
  catch (Exception ex)
  {
      Console.WriteLine(ex.Message);
  }
}

public string CreateVersionFile()
{
  try
  {
    Dictionary<string, bool> setupDict = JsonSerializer.Deserialize<Dictionary<string, bool>>(_setupPath);

    Dictionary<string, string> versionDict = new()
    {
        { "version", "1.0.0" }
    }

    setupDict.Add(versionDict);

    var newJsonData = JsonSerializer.Serialize(_setupPath, setupDict);
    return versionDict["version"];
  }
  catch (Exception ex)
  {
      Console.WriteLine(ex.Message);
  }
}

my goal here is to add a new key value pair to the json file if it doesn't exist (check happens in previous function). I'm still not that familiar on working with a json file, but figured it better for this use case as i didn't see a use in creating a config table in the database folder.

Angius•10mo ago

You want a dictionary of dictionaries? setupDict is a Dictionary<string, bool>

Mekasu0124OP•10mo ago

well no

Angius•10mo ago

You can't just add a Dictionary<string, string> to it

Mekasu0124OP•10mo ago

{
  "setup": false,
  "version": "1.0.0"
}

{
  "setup": false,
  "version": "1.0.0"
}

is the intended goal

Angius•10mo ago

That is not a dictionary

Mekasu0124OP•10mo ago

oh so ould it be

Angius•10mo ago

That is an object

Mekasu0124OP•10mo ago

setupDict["version"] = "1.0.0";
File.WriteAllText(_setupPath, setupDict);

setupDict["version"] = "1.0.0";
File.WriteAllText(_setupPath, setupDict);

Angius•10mo ago

If you want to throw away type safety and use Dictionary<string, object>, sure But I'd recommend an object

class Thingamajig
{
    public required bool Setup { get; set; }
    public required string Version { get; set; }
}

class Thingamajig
{
    public required bool Setup { get; set; }
    public required string Version { get; set; }
}

MODiX•10mo ago

Angius

REPL Result: Success

using System.Text.Json;

class Thingamajig
{
    public required bool Setup { get; set; }
    public required string Version { get; set; }
}

var json = """
    {
      "Setup": false,
      "Version": "1.0.0"
    }
    """;

var data = JsonSerializer.Deserialize<Thingamajig>(json);
data.Setup = true;
data.Version = "1.0.1+123";

Console.WriteLine(JsonSerializer.Serialize(data));

using System.Text.Json;

class Thingamajig
{
    public required bool Setup { get; set; }
    public required string Version { get; set; }
}

var json = """
    {
      "Setup": false,
      "Version": "1.0.0"
    }
    """;

var data = JsonSerializer.Deserialize<Thingamajig>(json);
data.Setup = true;
data.Version = "1.0.1+123";

Console.WriteLine(JsonSerializer.Serialize(data));

Console Output

{"Setup":true,"Version":"1.0.1\u002B123"}

{"Setup":true,"Version":"1.0.1\u002B123"}

Compile: 497.158ms | Execution: 44.993ms | React with ❌ to remove this embed.

Mekasu0124OP•10mo ago

ok cool. brb

Gaming

Programming

✅ System.Data.SQLite Data Types