CODEWITHSUNDEEP
c#.netpostgresql
Holly
Holly

Reputation: 333

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

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

  1. Use of unassigned local variable 'age'
  2. Argument 2: cannot convert from 'out int?' to 'out int'
  3. 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();
                 }
            }

Upvotes: 0

Views: 723

Answers (1)

Wojciech Iłowiecki
Wojciech Iłowiecki

Reputation: 267

I see two problems in your code:

  1. you are trying to use int.TryParse() with nullable int.
  2. 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();
                 }
            }

Upvotes: 1

Related Questions