Converting Nullable Integers

Question

_{Friday, October 1, 2010 10:53 AM}

In the following example, the program breaks during run-time when the PayeeID is null. What is the best way to deal with nullable values being returned from the database query?

    public class Site
    {
        private int? _PayeeID;

        public int? PayeeID
        {
            get { return _PayeeID; }
            set { _PayeeID = value; }
        }

        public void GetSite(int siteID)
        {
            string sqlCommand = "usp_Sites_Select";

            Database db = new Database();
            db.AddSQLCommand(sqlCommand);
            db.AddParameterWithValue("SiteID", siteID);

            using (IDataReader dataReader = db.GetDataReader())
            {
                while (dataReader.Read())
                {
                    this.PayeeID = Convert.ToInt32(dataReader["PayeeID"]);
                }
            }
        }
    }

 

 

All replies (4)

_{Friday, October 1, 2010 12:46 PM ✅Answered}

If you want to preserve the nulls, I think you'll need something like:

if (dataReader["PayeeID"] == null)
    PayeeID == null;
else
    PayeeID = Convert.ToInt32(dataReader["PayeeID"]);

 

---

_{Friday, October 1, 2010 1:01 PM}

If you want to preserve the nulls, I think you'll need something like:

if (dataReader["PayeeID"] == null)
    PayeeID == null;
else
    PayeeID = Convert.ToInt32(dataReader["PayeeID"]);

 

 

Does the following code do the same thing as yours?

if (dataReader["PayeeID"] != DBNull.Value)
                        this.PayeeID = Convert.ToInt32(dataReader["PayeeID"]);
                    

 

---

_{Friday, October 1, 2010 3:11 PM}

Two things:

First- make your public int a nullable type

private Nullable<int> _PayeeID;
public Nullable<int> PayeeID { get; set; }

Second, use a ternary operator when assigning your value:

this.PayeeID = dataReader.IsDBNull(dataReader.GetOrdinal("PayeeID")) ? 0 : reader.GetInt32(dataReader.GetOrdinal("PayeeID"));

Second option is the best. The statement says "If payeeID is null, make it 0. If it's not null get the number". The first option won't do anything other than accept null as an assignment value.

---

_{Friday, October 1, 2010 4:35 PM}

Does the following code do the same thing as yours?

 

No, mine explicity sets the value to null.

But you correctly check for DBNull instead of just null (but you can't use the == and != with DBNull).   So it could something more like:

        int? Status;
        while (Reader.Read() == true)
        {
            if (Reader["Status"] is DBNull)
                Status = null;
            else
                Status = Convert.ToInt32(Reader["Status"]);
        }