Nullable DateTimes in C#.Net 2.0 and Sql Server

.NET
1

Hi All

I’ve checked though the forums and seen one or two threads that deal with nullable types and I have a problem with DateTime value types that I need to solve.

I have a business object with various DateTime fields. In effect these are measuring the date and time that certain processes in the application are completed. All are mapped to a Sql Server 2000 database. I am working in .net 2.0 in c#.

The problem lies with the age old problem of the DataTime field in the database allowing null values. The dates will be gathered from the UI and passed to the business object. Where there is a private field and public property declared for the DateTime value type fields there are declared correctly as

private DateTime? dateTimeProperty;

and

public DateTime? DateTimeProperty
{get:set}

and work fine.

when retrieving values from the database and mapping to the fields in the business object I handle them as follows (using a SqlDataReader to loop through the records and instantiate a List<T> of the business objects):

if (reader[“DBFIELD”] == DBNull.Value)

{

businessObject.DateTimeProperty = null;

}

[color=#0000ff]else

[/color]{

businessObject.DateTimeProperty = Convert.ToDateTime(reader[“DBFIELD”]);

}

So, after this preamble, I am encountering a problem when mapping the business object data back to Sql Server. I iterate through the fields and seek to map them to parameters added to a SqlDataSource control which is declared in a data access class.

When I come to set the parameter I have tried:

Parameter pDateTimeField = new Parameter(“DATETIME”, TypeCode.DateTime, “”);

and

Parameter pDateTimeField = new Parameter(“DATETIME”, TypeCode.DateTime, businessObject.DateTimeField.ToString());

and

if (businessObject.DateTimeField == null)
{
Parameter pDateTimeField = new Parameter(“DATETIME”, TypeCode.DateTime, DbNull.Value.ToString());
}

all to no avail.

Can anyone help me solve this???

All the best

Peter Goddard

2

How about

Parameter pDateTimeField = new Parameter(“DATETIME”, TypeCode.DateTime, businessObject.DateTimeField);

3

Thanks but that doesn’t work because the third argument passed to the Parameter Constructor is expecting a string.

So this is either DbNull.Value.ToString() (in which case the value breaks the DateTime rule)

or

businessObject.DateTimeField.ToString()

which works as long as the object’s field isn’t null and it’s the null I’m having trouble with.

Any other ideas??

Peter

4

Try DbNull.Value; its the equivalent of NULL.

5

Then use the Parameters collection to add the parameter and the value:

parms.AddWithValue(“DATETIME”, businessObject.DateTimeField);

6

What type of value are you passing in to the method that saves date to db? DateTime or String?
Try this approach


public void AddMyRecord(DateTime myRecord) {
using(SqlConnection connection = new SqlConnection("my_connection_string")) {
                SqlCommand cmd = new SqlCommand("uspMyStoredProcedure", connection);
                // Sets command type
                cmd.CommandType = CommandType.StoredProcedure;
                // Adds required parameters
                // Here SqlParameter expects parameter name as string and a parameter value as object
                cmd.Parameters.Add(new SqlParameter("@MyParameter", this.ConvertToDbDateTime(myRecord)));
                // Opens connection to database
                connection.Open();
                // Executes command
                cmd.ExecuteNonQuery();
            }
}

private Object ConvertToDbDateTime(DateTime val) {
        return val != DateTime.MinValue ? (Object)val : (Object)DBNull.Value;
}
7

I’m passing in the BusinessObject.dateTime value, not a string.

I’ve tried the following:

//extract –>
command.Parameters.Add(new SqlParameter(“@DATETIME”, ConvertToDbDateTime(businessObject.DateTimeField)));


private static object ConvertToDbDateTime(DateTime? dateTime)

{

return dateTime != null ? (Object)dateTime : DBNull.Value;

}

But this yields the following message:

No mapping exists from object type System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] to a known managed provider native type.

I really think Microsoft has slipped up here, if a database table column is nullable, so the SqlCommand Parameters collection should be able to set the parameter to null (or in this case DbNull.Value as earlier pointed out).

8

Ok Everyone

I seem to have solved this using the following method

I add a parameter for each nullable DateTime field and pass to the conversion function as follows

Parameter pDateTime = newParameter(“DATETIME”, TypeCode.DateTime, ConvertToDbDateTime(businessObject.DateTimeProperty).ToString());

src.InsertParameters.Add(pDateTime);

with the function as follows:

private static object ConvertToDbDateTime(DateTime? dateTime)

{

return dateTime != null ? (Object)dateTime : DBNull.Value;

}

This works exactly as I had wanted. Thank you everyone for your help!

Kind Regards

Peter

9