SqlDataReader Convert Int to Int?

Question

I need to return a NULL or Blank value for Apvl_Lvl_Id if the SQL result returns NULL as the integration this passes the Apvl_Lvl_Id value to can't handle a value if it doesn't match an existing value for that instance.

I can't figure out what I'm missing, hoping someone can offer some pointers. Hope the below code is enough for an example.

   while (dr.Read())
      {
        if(dr.IsDBNull(Apvl_Lvl_Id))
         {
           int? Apvl_Lvl_IdNULL;
           Apvl_Lvl_Id = Apvl_Lvl_IdNULL;
         }
         else
         {
            Apvl_Lvl_Id = dr.GetInt32(0);
         }
      }

@Christian.K Some background; I'm getting the Apvl_Lvl_Id based on a system ID. The value returned is then passed to another class that builds a txt file which is then run by an integration. IF there is no value for the Apvl_Lvl_Id the SQL returns NULL, so I need to not pass a value to the class building the file otherwise it breaks the integration. Is this possible? From what you've said I can kinda see where I'm going wrong. — WHoward
– WHoward, Commented Feb 8, 2019 at 11:43

Christian.K · Accepted Answer · 2019-02-08 12:04:42Z

Sorry, but that code makes little sense. Apparently, Apvl_Lvl_Id is supposed to be the column ordinal - effectively the zero-based count of the column in a row - that you check for being DBNull. Then you are trying to assign the actual column's value to that ordinal? Apart from that, there is just no way you can assign null to an int. Introduce a separate variable, say, Apvl_Lvl_Value, make it an Nullable<int> (or int?) and assign the the column's value as required (actual value or null).

In "short", the code should more like this:

  while (dr.Read())
  {
     int? Apvl_Lvl_Value;
     // Check if the value of the column with the ordinal Apvl_Lvl_Id is DBNull
     if(dr.IsDBNull(Apvl_Lvl_Id))
     {
       // If so, use .NET "null" for the rest of the logic.
       Apvl_Lvl_Value = null;
     }
     else
     {
        // Use the actual columns (non-NULL) value.
        Apvl_Lvl_Value = dr.GetInt32(Apvl_Lvl_Id);
     }

     // Do something with the column's value, stored in Apvl_Lvl_Value

  }

Now, some things here are guesswork which are not really clear otherwise from your question, but generally, the above is a "pattern" by which one would use IsDBNull() and a Get*() method together.

Mike · Accepted Answer · 2019-02-08 11:29:59Z

1

And why don't you just set

Apvl_lvl_Id = null;

answered Feb 8, 2019 at 11:29

Mike

1991 silver badge11 bronze badges

2 Comments

WHoward Over a year ago

I can't convert Apvl_lvl_Id to null because it's an int, I need it as an int for when a value is returned. I only need it to be converted to a nullable int when a null value or 0 is returned.

Erik T. Over a year ago

@WHoward As long as you don't change the type of Apvi_Lvl_Id to int? there is no chance that Apvi_Lvl_Id will ever be null.

Collectives™ on Stack Overflow

SqlDataReader Convert Int to Int?

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related