locked
Why int? doesn't accept DbNull.Value? RRS feed

  • Question

  • User-503402950 posted

    Hi ,

    I am very surprised to see that int? doesn't accept DbNull.Value. Although it accepts the null value but it never accepts the DBNull.Value.

    Thursday, June 2, 2011 10:06 AM

Answers

  • User187056398 posted

    Because a DBNull is not an int and it is not a null.  They are different types.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 2, 2011 10:10 AM
  • User-1011137159 posted

    mhbalti : "But then how can we pass a null parameter to db. through a property of class."

     

    You just need to check int variable is null or not  then pass dbnull to database.

     if (Month != null)
               objDBHelper.AddParameter("Month", Month);
    else
               objDBHelper.AddParameter("Month", DBNull.Value);

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 3, 2011 1:29 AM
  • User1288683547 posted

    Better you create stored procedure which accept null parameter values
    example

    create table testvalue ( 
    id int null, 
    [name] varchar(500) null ) 
    
    create proc InsertValue 
    ( @id int = null, @name varchar(500) = null)
    As
    Begin
          insert into testvalue values(@id, @name )
    End
    
    

    and create method for this, which accept null

        int InsertValue(Nullable<int> id, string name)
        {
            SqlConnection myCon = new SqlConnection(myConnectionString);
            myCon.Open();
            SqlCommand mycom = myCon.CreateCommand();
            mycom.CommandText = "InsertValue";
            mycom.CommandType = CommandType.StoredProcedure;
    
            SqlParameter myPar = mycom.CreateParameter();
            myPar.ParameterName = "@name";
            myPar.DbType = DbType.String;
            myPar.Direction = ParameterDirection.Input;
            myPar.Value = name;
            mycom.Parameters.Add(myPar);
    
            myPar = mycom.CreateParameter();
            myPar.ParameterName = "@id";
            myPar.DbType = DbType.Int32;
            myPar.Direction = ParameterDirection.Input;
            myPar.Value = id;
            mycom.Parameters.Add(myPar);
    
            return mycom.ExecuteNonQuery();
        }

    I had written above code for your requirement, don’t expect coding standard :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 3, 2011 9:38 AM

All replies

  • User187056398 posted

    Because a DBNull is not an int and it is not a null.  They are different types.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, June 2, 2011 10:10 AM
  • User-1011137159 posted

    NULL and DBNULL.Value both are differnt..

    NULL mean unassinged objects and but DBNULL.Value mean value is null in sql so either sql returns null value or we want to use give null value to sql then also we can assing value to dbnull value...

    So DBNULL.Value is oject of DBNULL is Represents a nonexistent value.

    So you can not assing int value to DBNULL.Value....

    Thursday, June 2, 2011 10:14 AM
  • User-503402950 posted

    Because a DBNull is not an int and it is not a null.  They are different types.

    Thanks Wellens

    But then how can we pass a null parameter to db. through a property of class.

    Thursday, June 2, 2011 10:21 AM
  • User-1011137159 posted

    mhbalti : "But then how can we pass a null parameter to db. through a property of class."

     

    You just need to check int variable is null or not  then pass dbnull to database.

     if (Month != null)
               objDBHelper.AddParameter("Month", Month);
    else
               objDBHelper.AddParameter("Month", DBNull.Value);

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 3, 2011 1:29 AM
  • User-503402950 posted

     if (Month != null)
               objDBHelper.AddParameter("Month", Month);
    else
               objDBHelper.AddParameter("Month", DBNull.Value);

     

    Thanks Amit,

    But this is our Desi(traditional) style, I was looking for a professional style.

    Friday, June 3, 2011 7:14 AM
  • User187056398 posted

    But this is our Desi(traditional) style, I was looking for a professional style.

    That is a professional style.  It's clear, simple and it works.

    Friday, June 3, 2011 9:06 AM
  • User1288683547 posted

    Better you create stored procedure which accept null parameter values
    example

    create table testvalue ( 
    id int null, 
    [name] varchar(500) null ) 
    
    create proc InsertValue 
    ( @id int = null, @name varchar(500) = null)
    As
    Begin
          insert into testvalue values(@id, @name )
    End
    
    

    and create method for this, which accept null

        int InsertValue(Nullable<int> id, string name)
        {
            SqlConnection myCon = new SqlConnection(myConnectionString);
            myCon.Open();
            SqlCommand mycom = myCon.CreateCommand();
            mycom.CommandText = "InsertValue";
            mycom.CommandType = CommandType.StoredProcedure;
    
            SqlParameter myPar = mycom.CreateParameter();
            myPar.ParameterName = "@name";
            myPar.DbType = DbType.String;
            myPar.Direction = ParameterDirection.Input;
            myPar.Value = name;
            mycom.Parameters.Add(myPar);
    
            myPar = mycom.CreateParameter();
            myPar.ParameterName = "@id";
            myPar.DbType = DbType.Int32;
            myPar.Direction = ParameterDirection.Input;
            myPar.Value = id;
            mycom.Parameters.Add(myPar);
    
            return mycom.ExecuteNonQuery();
        }

    I had written above code for your requirement, don’t expect coding standard :)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 3, 2011 9:38 AM
  • User-503402950 posted

    Better you create stored procedure which accept null parameter values

    Yes , I think it is also the right tectic , thanks to all of you.

    Monday, June 6, 2011 1:40 AM