How to read null values out of a integer column in sql to C#?

Question

i am trying to make a project for my school. I am trying to read a table where some null values are in an integer column. I've made it an integer but i can't get the null values into the integer.

I've already searched in stackoverflow but none of the answers i could make at my project. Can someone provide me some help, tell me where i have to put the code to make it work again. I just started as programmer.

This is my database conn string + reader:

        public static List<Klant> GetAlleklanten()
        {
            var result = new List<Klant>();
            using (var conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                const string query = "select k.klantid, k.naam, k.adres, k.telefoonnummer, k.woonplaats, k.email, k.wachtwoord, kp.klantpasid from klant k left join klantpas kp on k.klantid = kp.klantid";
                SqlCommand selectKamers = new SqlCommand(query, conn);
                SqlDataReader reader = selectKamers.ExecuteReader();

                while (reader.Read())
                {
                    Klant klant = new Klant((int)reader["klantid"], (string)reader["naam"], (string)reader["adres"], (string)reader["telefoonnummer"], (string)reader["woonplaats"], (string)reader["email"], (string)reader["wachtwoord"], (int)reader["klantpasid"]);
                    result.Add(klant);
                }
                reader.Close();
            }
            return result;
        }

klantpasid is the one that also can return a null value instead of an integer. Here is the class where the klantpasid is in the construtor:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FontysHotel
{
    public class Klant
    {
            // instantie variabelen
            private int klantid;
            private string naam;
            private string adres;
            private string telefoonnummer;
            private string woonplaats;
            private string email;
            private string wachtwoord;
            private int? klantpasid;

            // properties
            public int Klantid
            {
                get
                {
                    return klantid;
                }
                set
                {
                    klantid = value;
                }
            }

            public string Naam
            {
                get
                {
                    return naam;
                }
                set
                {
                    naam = value;
                }
            }
            public string Adres
            {
                get
                {
                    return adres;
                }
                set
                {
                    adres = value;
                }
            }
            public string Telefoonnummer
            {
                get
                {
                return telefoonnummer;
                }
                set
                {
                telefoonnummer = value;
                }
            }
            public string Woonplaats
            {
                get
                {
                    return woonplaats;
                }
                set
                {
                    woonplaats = value;
                }
            }
            public string Email
             {

                get
                {
                    return email;
                }
                set
                {
                    email = value;
                }
            }
            public string Wachtwoord
            {
                get
                {
                    return wachtwoord;
                }
                set
                {
                    wachtwoord = value;
                }
            }

            public int? Klantpasid
            {
                get
                {
                    return klantpasid;
                }
                set
                {
                    klantpasid = value;

                }
            }
            // Constructor
            public Klant(int klantid, string naam, string adres, string telefoonnummer, string woonplaats, string email, string wachtwoord, int klantpasid)
            {
                Klantid = klantid;
                Naam = naam;
                Adres = adres;
                Telefoonnummer = telefoonnummer;
                Woonplaats = woonplaats;
                Email = email;
                Wachtwoord = wachtwoord;
                Klantpasid = klantpasid;
            }
     }
}

Please provide me some help, tell me where i have to place the right code so i can continue my school project. The error i am getting now is ''' The specified conversion is invalid '''

reader.IsDBNull(reader.GetOrdinal("klantid")) ? -1 : Conver.ToInt32(reader["klantid"]) — Dmitrii Bychenko
– Dmitrii Bychenko, Commented May 24, 2019 at 7:12
Not exactly sure what you meant ... 1) Is it (maybe) wrong, that Klantid has null values in your database? If it is your primary key, i guess it should be a unique value, NULL values wont help here. If so, you'll need to correct your data in your database: assign them values. 2) If it is correct, that your Klantid can be NULL, then you need to adapt your C# code to it - make Klantid a Nullable: public int? Klantid and private int? klantid; — nilsK
– nilsK, Commented May 24, 2019 at 7:24

Dmitrii Bychenko · Accepted Answer · 2019-05-24 07:19:58Z

3

You can check klantid for DBNull.Value, and if it is, assign corresponding special int value; so instead of

(int)reader["klantid"]

put

reader.IsDBNull(reader.GetOrdinal("klantid")) ? -1 : Conver.ToInt32(reader["klantid"])

a better way is to declare private int klantid; as private int? klantid; (nullable int):

private int? klantid; // it can accept null now

public int? Klantid
{
    get
    {
        return klantid;
    }
    set
    {
        klantid = value;
    }
}

then while reading from reader we can use turnary operator:

KlantId = !reader.IsDBNull(reader.GetOrdinal("klantid")) 
  ? Conver.ToInt32(reader["klantid"])
  : null;

edited May 24, 2019 at 7:19

answered May 24, 2019 at 7:14

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sefe · Accepted Answer · 2019-05-24 07:32:35Z

When you read your data with a DataReader, it will return DBNull.Value. Whe you want to fill your Klant.Klantpasid value (which is of type Nullable<int>), you will have to convert the value to Nullable<int> yourself. When ADO.NET was first implemented, there were no nullable values, so they introduced DBNull instead. More modern approaches like Entity Framework use nullable types instead.

You can write a helper method to help converting your read value to a nullable type:

static T? AsNullable<T>(object value) where T : struct {
    switch (value) {
        case T converted:
            return converted;
        case DBNull:
            return default(T?);
        default:
            throw new InvalidCastException();
    }
}

So instead of...

(int)reader["klantpasid"]

...you write:

AsNullable<int>(reader["klantpasid"])

And of course you change the type of your constructor parameter from int to int?.

H. Hakvoort · Accepted Answer · 2019-05-24 07:12:05Z

0

I know a fix for you, You change Klantpasid from int? to int If klantpasid is null in your database, we set the value to 0 instead of null

            public int Klantpasid
            {
                get
                {
                    return klantpasid;
                }
                set
                {
                if( value == null){
                  klantpasid = 0;
                  }else {
                    klantpasid = value;

                  }

                }
            }

answered May 24, 2019 at 7:12

H. Hakvoort

1611 gold badge2 silver badges14 bronze badges

1 Comment

Remco Bisschops Over a year ago

This does not work, the result of the if statements is always false because an integer can't be null

Adnand · Accepted Answer · 2019-05-24 07:18:54Z

0

You can declare your property as nullable

public int? Klantid
{
    get
    {
        return klantid;
    }
    set
    {
        klantid = value;
     }
}

Check out more on documentation.

answered May 24, 2019 at 7:18

Adnand

5721 gold badge10 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to read null values out of a integer column in sql to C#?

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related