DateTime not nullable problem

[Kevin Yu]

hi all

since the DateTime can't be assign null, the min value is set to 1901 or
something, if in a application design, the date field can be null, so in
between the UI and the DB, the business logic has to take care of the
conversion?? e.g when inserting DateTime.minvalue into db, (especially when
using store precedure, it seems that pass null parameter to a store
precedure cause problem.), the store procedure must translate the minvalue
into null??


Kevin

 

[Ollie Riches]

as far as I am aware the next version of the framework will allow the
assignment of null to the type DateTime

HTH

Ollie Riches

 

[Kevin Spencer]

Just use business logic to determine whether the value is DateTime.MinValue,
and assign a null value to your parameter if it is.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Neither a follower nor a lender be.

 

[Michael]

in the meantime, I typically just handle it like this at the data
access layer (with "p" being the business object):

INSERT / UPDATE
DateTime emptyDate = DateTime.Parse("1/1/0001");

if(p.StartDate == emptyDate)
cmd.Parameters["@StartDate"].Value=DBNull.Value;
else
cmd.Parameters["@StartDate"].Value=p.StartDate;
GET
if (!dr.IsDBNull(12)) p.StartDate = dr.GetDateTime(12);

 

[Kevin Yu]

I guess it's a typical problem for non nullable valuetype, unlike ref type,
they can't be assigned null.

it's a same problem with int too. I guess taking care of the translation in
the business logic is the way to go.
it's tedious since the translation will be done when binding to/retrieving
value from the UI control and passing it
to the store procedure. one thing I notice is Oracle.NET seems to throw
exception when passing null value into
the parameter, if building the sql statement in the .net app instead of
calling store procedure in the db, it will work.

well, hope the problem will go away in the future version of .NET



Kevin

 

[MWells]

Take a look at System.Data.SqlTypes. .NET has versions of essentially every
data type that is nullable, designed specifically for SQL use. You use them
like this;

using System.Data;

SqlTypes.SqlInt32 i = new SqlTypes.SqlInt32 ();

i = 32; // set value to integer 32
i = SqlTypes.SqlInt32.Null; // set value to null

i.Value == 32; // compare value to integer 32
i.IsNull; // compare value to null

Also note that normally when you're dealing with Params, you need to use
DBNull.Value rather than null.