none
How to cast DBNull to int RRS feed

  • Question

  • Hi all,

    How can I assign the DBNull value to an integer table field ?  

    i.e.

    Field1 is an integer column and I have to assigne to it myvalye:

    Mytable.Field1 = (myobject == null ) ? Convert.toineteger32(DBNull) : myvalue;

    Is it correct ?

    Regards

    Wednesday, February 8, 2012 11:21 AM

Answers

  • It really depends on what you're trying to accomplish. It's  not clear from your question. However, let me add one thing that hasn't been mentioned in any of the replies so far. In a DataRow, the data in the column is treated as an object. If you reference via the Typed DataSet syntax, then you cannot set it to DBNull.Value, because in the Typed DataSet, the field is expected to be of type int. However, if you don't use the Typed DataSet syntax, then you can set it to DBNull.Value if you wish, like this:

    Mytable[0]["Field1"] = (myobject == null ) ? DBNull.Value : myvalue;

    The display of code here sucks ... it didn't used to look this bad. Somebody made some "improvement" to the forum that made code blocks look worse. Good job.

    (BTW, your original statement had incorrect syntax, you forgot the row position, it should have been mytable[0].Field1)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com





    Monday, February 20, 2012 2:28 AM
  • Hi

    You can't convert but you can manage your sql query by using the isnull(field,0) (select inull(field,0) from table ) so the values retrieved will have 0 and not null 

    Hope it helps


    Best Regards...Please mark as answer if my post is helpful http://yosr-jemili.blogspot.com


    • Edited by YosrJ Saturday, February 18, 2012 6:47 PM
    • Proposed as answer by horngsh Monday, February 20, 2012 11:49 PM
    • Marked as answer by Alan_chenModerator Friday, February 24, 2012 6:13 AM
    Saturday, February 18, 2012 6:41 PM
  • AFAIK, the DBNull.Value in the database table means neither 0 nor empty string.  It is often used when users are adding a new record filling many fields at that moment and forget or mean to omit the input of some fields in terms of they don't have default value being preset(to leave it uninputted).  Often these kinds of fields are referded to as DB Null value in them.

    Just my two cents.


    My blog: http://soho-hsh.blogspot.com



    • Edited by horngsh Monday, February 20, 2012 11:57 PM
    • Marked as answer by Alan_chenModerator Friday, February 24, 2012 6:13 AM
    Monday, February 20, 2012 11:56 PM
  • since the original poster asked he want insert an integer value into some field.

    Actually no, it was the other way around. He said:

    How can I assign the DBNull value to an integer table field ?

    And I showed him how to do that. Put another way:

    if (INeedToSetThisToDBNull)
        Mytable[0]["Field1"] = DBNull.Value;
    This will work just fine and is, in fact, the only way to set Field1 to a NULL value. I probably wouldn't actually code anything that way though, because typically one shouldn't be explicitly setting something to DBNull.Value because it kind of goes against what the meaning of a NULL field should be. But, we don't really know what the OP has in mind, because he hasn't been back to this thread to elaborate.

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, February 21, 2012 6:07 AM
  • Bonnie,

    I don't know how you created your message but I cannot quote it in the forum way and I am to lazy to do it in HTML.

    However.

    We are humans, something like apes but then a little bit smarter. In our thinking we expect many things. But an DBNull object is not an integer, it represents a non existing item in a DataBase which has in Net gotten the type DBNull. 

    If it is used and exist and the type is int than it becomes an object with a type of int. 

    In C# because of the so many meanings of the words Null it gives always confusions. Be aware a DBNull is not a nullable valuetype and also not a placeholder for an object which does not contain a reference;  a confusion which is often made. 

    And therefore you cannot cast it (using a base class of the object), but only create a new one by converting it (and use it of course as a result of an conversion without creating a real item in Net).


    Success
    Cor


    Tuesday, February 21, 2012 3:47 PM

All replies

  • Hi skuanet,

    I don't think we can convert DBNull to any other types, you can try to like this:

    Mytable.Field1 = (myobject == null ) ? 0 : myobject.value;
    Have a nice day.

    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 9, 2012 5:51 AM
    Moderator
  • You can't convert DBNull to int

    so change your code to set to '0' if it is DBNull

    Thursday, February 9, 2012 8:59 AM
  • Don't confuse DB columns with .Net datavalues.

    DBNull means the special "not used item" setting of a database column, it has nothing to do with nullable value types or with non yet instanced adresses in .Net.


    Success
    Cor

    Thursday, February 9, 2012 11:51 AM
  • Hi,

    I am writing to check the status of the issue on your side. Would you mind letting us know the result of the suggestions?
    If you need further assistance, please feel free to let me know. I will be more than happy to be of assistance.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 15, 2012 8:19 AM
    Moderator
  • Hi

    You can't convert but you can manage your sql query by using the isnull(field,0) (select inull(field,0) from table ) so the values retrieved will have 0 and not null 

    Hope it helps


    Best Regards...Please mark as answer if my post is helpful http://yosr-jemili.blogspot.com


    • Edited by YosrJ Saturday, February 18, 2012 6:47 PM
    • Proposed as answer by horngsh Monday, February 20, 2012 11:49 PM
    • Marked as answer by Alan_chenModerator Friday, February 24, 2012 6:13 AM
    Saturday, February 18, 2012 6:41 PM
  • It really depends on what you're trying to accomplish. It's  not clear from your question. However, let me add one thing that hasn't been mentioned in any of the replies so far. In a DataRow, the data in the column is treated as an object. If you reference via the Typed DataSet syntax, then you cannot set it to DBNull.Value, because in the Typed DataSet, the field is expected to be of type int. However, if you don't use the Typed DataSet syntax, then you can set it to DBNull.Value if you wish, like this:

    Mytable[0]["Field1"] = (myobject == null ) ? DBNull.Value : myvalue;

    The display of code here sucks ... it didn't used to look this bad. Somebody made some "improvement" to the forum that made code blocks look worse. Good job.

    (BTW, your original statement had incorrect syntax, you forgot the row position, it should have been mytable[0].Field1)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com





    Monday, February 20, 2012 2:28 AM
  •  However, let me add one thing that hasn't been mentioned in any of the replies so far. In a DataRow, the data in the column is treated as an object. If you reference via the Typed DataSet syntax, then you cannot set it to DBNull.Value, because in the Typed DataSet, the field is expected to be of type int. However, if you don't use the Typed DataSet syntax, then you can set it to DBNull.Value if you wish, like this:

    
    

    I thought I did, be aware the DBNull.Value columns are not set to int or object, they are set to properties like IsDBNullValue in typed dataset.

    To handle the objects non strongly typed in a typed dataset you have to instance a typed dataset as non typed dataset (which we see often done in samples, but in fact wrong).


    Success
    Cor




    Monday, February 20, 2012 8:22 AM
  • Cor,

    I thought I did, be aware the DBNull.Value columns are not set to int or object, they are set to properties like IsDBNullValue in typed dataset.

    The columns are objects and can be set to anything. That is why you can use the non-typed syntax to set a column to DBNull.Value, as I showed above. Try it if you don't believe me. ;0)

    To handle the objects non strongly typed in a typed dataset you have to instance a typed dataset as non typed dataset (which we see often done in samples, but in fact wrong).

    You can access the columns of a Typed DataSet in a non-typed manner without having to instance it as a non-typed DataSet. I do it frequently. Again, try it yourself ... ;0)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Monday, February 20, 2012 3:12 PM
  • AFAIK, the DBNull.Value in the database table means neither 0 nor empty string.  It is often used when users are adding a new record filling many fields at that moment and forget or mean to omit the input of some fields in terms of they don't have default value being preset(to leave it uninputted).  Often these kinds of fields are referded to as DB Null value in them.

    Just my two cents.


    My blog: http://soho-hsh.blogspot.com



    • Edited by horngsh Monday, February 20, 2012 11:57 PM
    • Marked as answer by Alan_chenModerator Friday, February 24, 2012 6:13 AM
    Monday, February 20, 2012 11:56 PM
  • AFAIK, the DBNull.Value in the database table means neither 0 nor empty string.

    Exactly right, it simply means that no data has been entered, intentionally or not ... and that is why I wonder why you marked @YosrJ's reply as a Proposed Answer when he clearly advocates just returning a 0 from the query when the database value is NULL. This is clearly NOT a correct answer, IMHO. Agreed?


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, February 21, 2012 12:30 AM
  • IMHO, the correct way to do your job is redesign your table schema and specify the field you want to be Allow Null. Secifically designating your field as allow null is just the right way to do this common database programming.  You must first understand what the DB Null means and go further into the right direction.  

    I guess you might be going the wrong way to do database programming.  We DON'T HAVE TO CAST a DBNull into an Integer or any other data type.

    just my two cents.


    My blog: http://soho-hsh.blogspot.com

    Tuesday, February 21, 2012 3:50 AM
  • Well,  since the original poster asked he want insert an integer value into some field.  That post marked as proposed answer is the closest and not good but acceptable answer. Besides,  I have provided my opinion to the poster.  Please take a look.

    My blog: http://soho-hsh.blogspot.com

    Tuesday, February 21, 2012 3:53 AM
  • since the original poster asked he want insert an integer value into some field.

    Actually no, it was the other way around. He said:

    How can I assign the DBNull value to an integer table field ?

    And I showed him how to do that. Put another way:

    if (INeedToSetThisToDBNull)
        Mytable[0]["Field1"] = DBNull.Value;
    This will work just fine and is, in fact, the only way to set Field1 to a NULL value. I probably wouldn't actually code anything that way though, because typically one shouldn't be explicitly setting something to DBNull.Value because it kind of goes against what the meaning of a NULL field should be. But, we don't really know what the OP has in mind, because he hasn't been back to this thread to elaborate.

    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, February 21, 2012 6:07 AM
  • Yea, you are exactly right. 

    I am sorry that I mixed this thread with another thread asking to convert DBNull.Value to integer value.  I am a bit of careless with the context.


    My blog: http://soho-hsh.blogspot.com

    Tuesday, February 21, 2012 9:31 AM

  • The columns are objects and can be set to anything.


    Bonnie, objects cannot be set to anything (in the meaning of everything). A house is a house and never becomes a plane.

    That rules follows programming in .Net as well. A character is a character and some characters (those from zero to nine) can be converted to an integer. So can a DbNull object be converted in a way to an integer by telling that DBNull.Value is zero or null. But it can not be set itself to something else or be casted like that. (I know that for many C# programmers the difference between casting and converting is not always clear). 



    Success
    Cor


    Tuesday, February 21, 2012 9:59 AM
  • objects cannot be set to anything (in the meaning of everything). A house is a house and never becomes a plane.

    Cor, of course you're right and I didn't actually mean it literally. If a column is defined as an int type, it can obviously only contain integers. But, it can also contain DBNull.Value and that was my point, of course.

    So can a DbNull object be converted in a way to an integer by telling that DBNull.Value is zero or null.

    But an integer value of 0 may represent a valid number for that particular field. Depending on how any given field in a DataSet is being used, picking an arbitrary 0 value as representing NULL, may not be a good idea. Consider a temperature column. Say it's in Celsius. A temperature of 0 means the freezing point. A temperature of DBNull.Value means no one has set the temperature yet. Two very different meanings.

    But it can not be set itself to something else or be casted like that. (I know that for many C# programmers the difference between casting and converting is not always clear). 

    Cast like how? Like I did setting the column to DBNull.Value? That works fine, I'm not sure what you're getting at ...


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, February 21, 2012 3:02 PM
  • Bonnie,

    I don't know how you created your message but I cannot quote it in the forum way and I am to lazy to do it in HTML.

    However.

    We are humans, something like apes but then a little bit smarter. In our thinking we expect many things. But an DBNull object is not an integer, it represents a non existing item in a DataBase which has in Net gotten the type DBNull. 

    If it is used and exist and the type is int than it becomes an object with a type of int. 

    In C# because of the so many meanings of the words Null it gives always confusions. Be aware a DBNull is not a nullable valuetype and also not a placeholder for an object which does not contain a reference;  a confusion which is often made. 

    And therefore you cannot cast it (using a base class of the object), but only create a new one by converting it (and use it of course as a result of an conversion without creating a real item in Net).


    Success
    Cor


    Tuesday, February 21, 2012 3:47 PM
  • Dear Cor and Bonnie:

    Please take a look at this on MSDN:

    DBNull Class

    .NET Framework 4
    This topic has not yet been rated Rate this topic

    Represents a nonexistent value. This class cannot be inherited.

    System.Object
    System.DBNull 

    DBNull is actually a class. A .Net class or in C/C++ class is also called an user-defined data type.  As you can see, DBNull class inherits System.Object class, in Object Oriented field, we can cast or convert any sub class to its base class.  So undoubtedly DBNull object can of course cast to an Object Data Type without any problem.  But please also look at this Int32 class in MSDN page:

    Int32 Structure

    .NET Framework 2.0
    3 out of 19 rated this helpful Rate this topic

    Represents a 32-bit signed integer.

    Namespace: System
    Assembly: mscorlib (in mscorlib.dll)

    'Declaration
    <SerializableAttribute> _
    <ComVisibleAttribute(True)> _
    Public Structure Int32
    	Implements IComparable, IFormattable, IConvertible, IComparable(Of Integer), _
    	IEquatable(Of Integer)
    'Usage
    Dim instance As Integer
    /** @attribute SerializableAttribute() */ 
    /** @attribute ComVisibleAttribute(true) */ 
    public final class Int32 extends ValueType implements IComparable, IFormattable, 
    	IConvertible, IComparable<int>, IEquatable<int>

    It is a System.Int32 Structure, belongs to a value type data type(We can see it extends ValueType class from the J# Int32 class definition).  So please look at ValueType class definition on MSDN:

    ValueType Class

    .NET Framework 4
    2 out of 2 rated this helpful Rate this topic

    Updated: August 2011

    Provides the base class for value types.

    System.Object 
      System.ValueType
        System.Enum

    Bingo! ValueType actually inherits System.Object class.  So from the class hierarchy, System.Int32 is in the 3rd level of the class hierarchy(root level--Object, then ValueType, Then itself--Int32), Whereas DBNull is at the 2nd level of class hierarchy, so DBNull and Int32 are not sibling classes. To summarize it, both DBNull and Int32 can be cast and converted to System.Object data type without any problems.  In object oriented field, the public inheritance relationship(C++ terms) makes us be able to assign sub class object to its base class variable or pointer or so called Reference Variable in .Net.

    But NOT in the reverse direction,  we can call a cat(sub class) is a kind of animal(base class) so that we can be sure of casting sub class to base class.  But an animal is not necessarily a cat!  This animal could be a dog or a lion.  So we can't cast base class into sub class in this public inheritance relationship.

    So my conclusion is any .Net data type all inherits from System.Object(Even an Integer Constant such as 100 can also be converted to System.Object data type).


    My blog: http://soho-hsh.blogspot.com


    • Edited by horngsh Tuesday, February 21, 2012 4:17 PM
    Tuesday, February 21, 2012 4:14 PM
  • Sueh,

    I think that both Bonnie and I know this very well, but the main thing in your reply is, you can cast a cat to an animal (mostly we take for this sample mammal), but you cannot let that animal meow (and if it is a mammal and is a dog he barks).

    :-))

     


    Success
    Cor

    Wednesday, February 22, 2012 10:10 AM