C# - SQL - NULL int problems

[Dan]

I'm fairly new to C# and I am having some problems
understanding int variables and null values.

I have created a class that populates the values of it's
fields from a DataReader in it's constructor. Those fields
are modified during various methods in the class. One
method updates the database with the values of the class
fields. I have everything working fine for string fields
but am running into problems with integers and null values.

The problem is that if I try to set a null int field from
the database to an int field in the class I get an
error.... so I've had to add the following check...
=================================================
if (dr["myID"] != System.DBNull.Value){
_myID = Convert.ToInt32(dr["myID"]);
}
=================================================
.... so now if the db value was null, then the class field
never gets initialized.


When I declare the variable I am not able to set the value
as null, or I get the error "Cannot convert null to int
because it is a value type".
==================================================
private int _myID = null;
==================================================


In the update method I want to be able to update the
database to whatever the value of the class field is (null
or otherwise).
==================================================
cmd.Parameters.Add("@myID", System.Data.SqlDbType.Int,
4).Value = _myID;
==================================================
....but the problem is that if the field never got
initialized, then this crashes. I want to be able to test
the value if it is null, but I can't set it to null.


-- How do I deal with this?
-- Is there a way to check to see if this int field has
been initialized?
-- Is there a better way to set an int field to a null
value?


Any help is greatly appreciated.

-Dan

 

[Kenton Smeltzer]

Dan this should work

try

{

//set your int here

}

catch(System.InvalidCastException){}

if(yourint != null) {

//its not null so you are OK

}

 

[Jmi]

Dan,

You pretty much have the right idea here in what you're
doing. What you need to realize (and perhaps you already
do) is that the values 0, "" (null string), and NULL are
not the same thing. Null is conceptually the absence of
anything. Therefore, for example an empty string is not
null, and null is not zero..... Confused? Good.

When you create your domain object, as you're already
donig, it should contain private members that represent
each field in the database, name, address, phone number,
salary, etc (m_name, m_address, m_salary).... and then
it should also contain Boolean private members for each
database field in which nulls are allowd
(m_phone_IsNull)...

Now, for each datafield, create read/write properties for
setting and obtaining its value. This will simply set or
return the value of the assocaited private m_ member.
Now, for the nullable columns, also create a read-only
property called ISNULL, and a function called SetNull() (
PhoneIsnull property and PhoneSetNull() fxn, for
example). Have the IsNull property return the value of
m_PhoneIsNull and have the set... fxn set it. This is the
proper way to handle this, and also makes a nice, clean
interface to the rest of your program.

BTW, on that whole note of this approach, yuo might also
want to consider, if you're not alrady doing so, tying
your program code to VIEW's in SQL server instead of
tables or direct querries generated by SQL statements.
This is in order to provie a level of abstraction between
the data your program requires, and the underlying table
structures in your database. After compiling and
submitting an application to production, it's a LOT easier
to move things around in a predefined SQL view than it is
to tear down your program and rebuild and resubmit it to
production if it becomes necessary to move table columns
around at some point in time.

I hope that helps!

JIM

-----Original Message-----
I'm fairly new to C# and I am having some problems
understanding int variables and null values.

I have created a class that populates the values of it's
fields from a DataReader in it's constructor. Those fields
are modified during various methods in the class. One
method updates the database with the values of the class
fields. I have everything working fine for string fields
but am running into problems with integers and null values.

The problem is that if I try to set a null int field from
the database to an int field in the class I get an
error.... so I've had to add the following check...
=================================================
if (dr["myID"] != System.DBNull.Value){
_myID = Convert.ToInt32(dr["myID"]);
}
=================================================
.... so now if the db value was null, then the class field
never gets initialized.


When I declare the variable I am not able to set the value
as null, or I get the error "Cannot convert null to int
because it is a value type".
==================================================
private int _myID = null;
==================================================


In the update method I want to be able to update the
database to whatever the value of the class field is (null
or otherwise).
==================================================
cmd.Parameters.Add("@myID", System.Data.SqlDbType.Int,
4).Value = _myID;
==================================================
....but the problem is that if the field never got
initialized, then this crashes. I want to be able to test
the value if it is null, but I can't set it to null.


-- How do I deal with this?
-- Is there a way to check to see if this int field has
been initialized?
-- Is there a better way to set an int field to a null
value?


Any help is greatly appreciated.

-Dan

.

 

[Jon Skeet [C# MVP]]

Dan this should work

try

{

//set your int here

}

catch(System.InvalidCastException){}

Catching an InvalidCastException is almost always a bad idea - you
should be making sure that you don't attempt to cast it in the first
place.

if(yourint != null) {

//its not null so you are OK

}

An int variable can never be null, so this comparison won't even
compile.

 

[Kenton Smeltzer]

Jon you are correct, I was under the assuption that the compiler would
consider an object that has not been initilized as null. I tried compiling
it and it threw an error. So the compiler does not consider it null. Anyway
in the situation that Dan was asking the only way to leave the int null
would be via a try catch that is why I suggested he do it that way as I
assumed the compiler would treat it as a null value but if the compiler does
not reconize uninitilized varibles as null then it is useless. I assumed it
would compile like the following C++ code:

int test;

System::String *invalid;

invalid = "a";

try {

test = System::Convert::ToInt32(invalid);

Console::WriteLine(test.ToString());

}

catch (System::Exception()){}

if(test != NULL) {

Console::WriteLine(S"test is not null");

}

else {

Console::WriteLine(S"test is null");

}

Console::ReadLine();

which is valid. I appoligize for not trying it in the compiler as I assumed
the the runtime viewed uninitilized varibles as null as it does in C++.

Thanks,

Kenton

 

[mikeb]

I'm fairly new to C# and I am having some problems
understanding int variables and null values.

I have created a class that populates the values of it's
fields from a DataReader in it's constructor. Those fields
are modified during various methods in the class. One
method updates the database with the values of the class
fields. I have everything working fine for string fields
but am running into problems with integers and null values.

The problem is that if I try to set a null int field from
the database to an int field in the class I get an
error.... so I've had to add the following check...
=================================================
if (dr["myID"] != System.DBNull.Value){
_myID = Convert.ToInt32(dr["myID"]);
}
=================================================
... so now if the db value was null, then the class field
never gets initialized.


When I declare the variable I am not able to set the value
as null, or I get the error "Cannot convert null to int
because it is a value type".
==================================================
private int _myID = null;
==================================================


In the update method I want to be able to update the
database to whatever the value of the class field is (null
or otherwise).
==================================================
cmd.Parameters.Add("@myID", System.Data.SqlDbType.Int,
4).Value = _myID;
==================================================
...but the problem is that if the field never got
initialized, then this crashes. I want to be able to test
the value if it is null, but I can't set it to null.


-- How do I deal with this?
-- Is there a way to check to see if this int field has
been initialized?
-- Is there a better way to set an int field to a null
value?


Any help is greatly appreciated.

You might want to look at the types in the System.Data.SqlTypes
namespace (specifically SqlInt32 for the above example), and see if they
fit your needs. For the most part, they are essentially nullable
versions of various intrinsic .NET value types.

 

[Jon Skeet [C# MVP]]

which is valid. I appoligize for not trying it in the compiler as I assumed
the the runtime viewed uninitilized varibles as null as it does in C++.

It's not just a case of uninitialised variables having specific values.
It's a case of null not being a *possible* value for an int in C# - int
is a value type, and null is a reference type.

 

[Kenton Smeltzer]

Thanks Jon,
I am clear on the reference / value stuff as I said I made an assumption
that it was the same as C and I did not test it. It was a stupid mistake and
it wont happen again. I tend to think in one language and write in another
just be thankful I did not give Dan cobal logic in C or assembly in VB.
Anyway I apologize for the confusion.

Dan,
Jon is correct you should avoid the problem all together and not rely on a
try catch to resolve the problem. the solution I was trying to give you was
the quick way and I did not want to get in to DB design on the C# group. But
the absolute correct way to resolve the issue is to eliminate nulls from
your DB design as they violate the higher levels of normal form. If you have
values that are going to be null you should break them out into a separate
table and only put entries with there associated primary key in there. This
will allow you to join the table when you need the values while not having
to store the null values. This will provide you with a more reliable data
structure to build your code on and you will not have to worry about null
values creeping up.

The second solution that was suggested will also work by testing it against
the SQL null type. And finally the solution I provided will work and is
quick to implement if you are in a time crunch you will just need to modify
the logic a little. Instead of not initializing the int initialize it to a 0
or a -1 or some number that will never be used. use the try catch and if the
int is still 0,-1 or whatever then you can assume it is null. This is not
the best way to do it as Jon said and I should have asserted that when I
posted it (I should have tried to compile it too), but it will get you up
and running if you are in a hurry.

PS. I am assuming that you are familiar with DB normal forms if you are not
then I would be happy to help you offline if you chose that path, but it is
more a DB design topic rather than C#.

Thanks,
Kenton

my email is (e-mail address removed)