CODEWITHSUNDEEP
c#sqlitesystem.data.sqlite
Emir Sürmen
Emir Sürmen

Reputation: 950

C# SQLite3 Select if null

I want the code to do something if the thing its looking for doesn't exist in the DB. This is what I am working with currently:

const string cs = @"Data Source= ..\..\..\database.sqlite3";

await using var con = new SQLiteConnection(cs);
con.Open();
            
await using var cmd = new SQLiteCommand(con);

member ??= ctx.Member;
            
cmd.CommandText = @"SELECT iq FROM users WHERE id = $user_id";
cmd.Parameters.AddWithValue("$user_id", member.Id);
var r = cmd.ExecuteReader();

while (r.Read())
{
    if (r["iq"] == null)
    {
        var random = new Random();
        var iq = random.Next(1, 200);
                
        await cmd.DisposeAsync();
                
        cmd.CommandText = "INSERT INTO users(id, iq) VALUES($member_id, $id)";
        cmd.Parameters.AddWithValue("$iq", iq);
        cmd.Parameters.AddWithValue("$member_id", member.Id);
                
        cmd.ExecuteNonQuery();
                
        var embed = new DiscordEmbedBuilder
        {
            Title = $"IQ of {member.Username} is {iq} :brain:",
            Color = DiscordColor.Cyan
        };

        await ctx.Channel.SendMessageAsync(embed);  
    }
    else
    {
        var iqFinal = r["iq"];

        var embed = new DiscordEmbedBuilder 
        {
            Title = $"IQ of {member.Username} is {iqFinal} :brain:", 
            Color = DiscordColor.Cyan
        };
                        
        await ctx.Channel.SendMessageAsync(embed);   
    }
}

I really don't know how do make that happen, I've tried try catch blocks but they didn't seem to work either. How can I do this?

Upvotes: 0

Views: 306

Answers (1)

forpas
forpas

Reputation: 164099

I assume that id is the primary key of the table, so you don't expect more than 1 rows from the statement:

SELECT iq FROM users WHERE id = $user_id

The problem with this statement is that it may not return any rows if $user_id does not exist in the table, so in this case r.Read() will return false and the code inside the while loop will never be executed.

One way to do what you want is to use aggregation:

SELECT MAX(iq) AS iq FROM users WHERE id = $user_id

which always returns a row: either the row with the iq where id = $user_id or null if $user_id does not exist in the table.

Also you should change the while loop to just:

if (r.Read()) {
    .....................
}

or better just:

r.Read();

because the aggregation query is guaranteed that will return exactly 1 row.

Upvotes: 1

Related Questions