c#: How do create a null INT to use as an SQL Parameter

Question

I'm declaring some variables then

I'm looping through some data using switch command if an attribute exists it gets assigned to the relevant variable It is possible age will not be found the PostgreSQL Table reflects this

CREATE my_table(
    id SERIAL PRIMARY KEY,
    name varchar,
    age INTEGER

);

The code snippet is giving me errors

Use of unassigned local variable 'age'
Argument 2: cannot convert from 'out int?' to 'out int'
Cannot convert type 'System.DBNull' to 'int'

How do I declare a null int and maybe assign a value if not pass it to the database as null?

IN pseudo code to show the gist of what I'm doing

// declared at the same level
    string name = string.Empty;
    int? age;
    
    foreach (var p in Feature.Properties)
        {
            var Key = p.Key;
            var Value = p.Value;
            switch (Key.ToLower())
            {
                
            case "name":
                {
                    name = Value;
                    break;
                }
            
            case "age":
                {
                // May not exist
               // Err 2
                    int.TryParse(Value, out age);
                    break;
                }
            }
        }    
        

          // Err 1 name is OK 
          Console.WriteLine(name + age);
        using (var DB_con = new NpgsqlConnection(cs))
            {
                var sql = "INSERT INTO my_table (name,age )VALUES "+
                    "(@p_name, @p_age  RETURNING id;";
                using (var cmd = new NpgsqlCommand(sql, DB_con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@p_name", name);   
               // Err 3
                    cmd.Parameters.AddWithValue("@p_age", age ?? (int)DBNull.Value  );

                    
                    DB_con.Open();
                    var res = cmd.ExecuteScalar();
                    DB_con.Close();
                 }
            }

3) A Parameter.Value has a type `object` so that should be `(object) age ?? DBNull.Value`. Do not try to cast DBNull.Value to int as that is not valid. — Igor, Jun 14 '21 at 13:16
2) see [How to use int.TryParse with nullable int](https://stackoverflow.com/questions/3390750/how-to-use-int-tryparse-with-nullable-int) — stuartd, Jun 14 '21 at 13:17
I'd strongly suggest using something like Dapper and letting it handle the `null` mapping for you, to avoid having to deal with `DBNull.Value`. — mjwills, Jun 14 '21 at 13:29

score 1 · Accepted Answer · answered Jun 14 '21 at 13:42

I see two problems in your code:

you are trying to use int.TryParse() with nullable int.
you are trying to cast DBNull.Value into int.

please try something like this:

// declared at the same level
    string name = string.Empty;
    int? age;
    
    foreach (var p in Feature.Properties)
        {
            var Key = p.Key;
            var Value = p.Value;
            switch (Key.ToLower())
            {
                
            case "name":
                {
                    name = Value;
                    break;
                }
            
            case "age":
                {
                // May not exist
               // Err 2
               int parsedAge;
                       //try parsing into int, when sucessfull then assing value
                    if(int.TryParse(Value, out parsedAge))
                    {
                          age = parsedAge;
                    }
                    break;
                }
            }
        }    
        

          // Err 1 name is OK 
          Console.WriteLine(name + age);
        using (var DB_con = new NpgsqlConnection(cs))
            {
                var sql = "INSERT INTO my_table (name,age )VALUES "+
                    "(@p_name, @p_age  RETURNING id;";
                using (var cmd = new NpgsqlCommand(sql, DB_con))
                {
                    cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Parameters.AddWithValue("@p_name", name);   
               // Err 3
               //remove casting into int
                    cmd.Parameters.AddWithValue("@p_age", age ?? DBNull.Value  );

                    
                    DB_con.Open();
                    var res = cmd.ExecuteScalar();
                    DB_con.Close();
                 }
            }

Thanx I had come up with similar based on previous comments – Holly Jun 15 '21 at 09:33 — Holly, Jun 15 '21 at 09:33

c#: How do create a null INT to use as an SQL Parameter

1 Answers1