Null value in an Int32? Workaround ideas please. 1

[gdrenfrew]

Hello,

I can store null values in my SQLServer database integer fields. However, I cannot set an Int32 variable to null in my C# application.

It's a 3-tier application, and I'd prefer to keep the bulk of it non-DB dependant. I'd prefer to use a standard integer type, not SQL types (SQLInt32 etc) as they are not serializable.

What are other developers doing to get around this problem? I'd happily write a little function in the DAL to make my "empty" variables equal to DBNull.value (so I can pass them to my stored procedures), but how should I deal with integers that can be null in the main body of my program? Are you writing your own types because the C# aren't all the useful in the real world?

Thanks

 

[B00gyeMan]

What exactly do you want to know?
If you are reading the data with a SqlDataReader you could do like this:

SqlDataReader anSqlDataReader;

anSqlDataReader = aConnection.ExecuteReader( aCommand);
...
anIntValue = anSqlDataReader.IsDBNull( aColNo) ? 0 : anSqlDataReader.GetInt32( aColNo);
...


This code places (zero) in that anIntValue variable if the field is empty, where aColNo is the column no.
Also, if a value never gets used (and therefore never inserted in the DB) i.e. -1, you can use that value to signal that the field was null in the database.

anIntValue = anSqlDataReader.IsDBNull( aColNo) ? -1 : anSqlDataReader.GetInt32( aColNo);

P.S.: aColNo is the column no. in the order returned by the executed SQL statement and starting from zero.

 

[chiph]

As you've found out, you can't store a DB null in an int.

What I do is store int.MinValue to indicate this.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[gdrenfrew]

thanks for both posts.
Chip, can you elaborate a little bit please?
thanks,

 

[chiph]

When reading from the database, when I see a null value (column is equal to DBNull.Value), I use the MinValue property of the int class (this is pseudo-code, I'm working from memory):

if (mycolumn.value == DBNull.Value)
   myIntValue = int.MinValue;
else
   myIntValue = int.Parse(mycolumn.value);

Going the other way, when storing values in the database, I assign the DBNull to the ADO.NET parameter like this:

SqlParameter p = new SqlParameter("@count", SqlDbType.Int)
if (myIntValue == int.MinValue)
   p.Value = DBNull.Value;
else
   p.Value = myIntValue;

Obviously, this won't work if your application considers -2,147,483,648 (what int.MinValue equals) to be a valid value.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first