SmartDate.MinValue

tkhan posted on Tuesday, September 09, 2008

Csla dudes!

One of the columns in our tables in our db is modifyDate. This is a nullable type in the database. I am using a SmartDate type in my BO and trying to insert a record and sqlserver does not like it. Digging through the code and doing some research, I realized that the SmartDate type uses the MinValue property of the DateTime type that equates to 00:00:00.0000000, January 1, 0001 - which sqlserver does not support.

I know that System.Data.SqlTypes.SqlDateTime.MinValue.Value equates to 01/01/1753 00:00 - the min value allowed in a sqlDateTime type. We could possibly set the MinValue of the SmartDate to be this, however, this does not really appear to be a good solution and is rather a hack.

Does the community have a better suggestion? Thanks a mil!

sergeyb replied on Tuesday, September 09, 2008

triplea replied on Tuesday, September 09, 2008

Not sure if this answers your question but did you make sure that when you pass the date value as a parameter to your SP/sql statement that you used the modifyDate.DBValue property? Also you might need to play with a different constructor for your SmartDate...

ajj3085 replied on Tuesday, September 09, 2008

Another poster already gave the answer.  My suggestion though is to always have values for Modifieddate / user.  It simplifies a lot of code (no need to check if there's a value).  I had started off having a nullable value, and am glad I got rid of it.

tkhan replied on Tuesday, September 09, 2008

Hi,

I have made the modifyDate non-nullable in the BOL and I am now managing the nullable types in the BOL. I am assigning DateTime.Now to the modifyDate property of the DAL on an insert like

using (var mgr = Csla.Data.ContextManager<CrmDatabaseDataContext>

.GetManager(Database.CrmDEV))

{

var data = new CodeReady.CRM.DAL.Email();

OnEmailMemberReading(data);

data.EmailAddress = ReadProperty<string>(EmailAddressProperty);

data.StatusId = ReadProperty<int?>(StatusIdProperty);

data.CreateUser = 482101;

//data.CreateUser = CodeReady.CRM.Framework.Application.CurrentContactID;

data.CreateDate = DateTime.Now;//ReadProperty<SmartDate>(CreateDateProperty);

data.ModifyUser = ReadProperty<int?>(ModifyUserProperty);

data.ModifyDate = DateTime.Now;// ReadProperty<SmartDate>(CreateDateProperty);

OnEmailMemberRead();

mgr.DataContext.Emails.InsertOnSubmit(data);

mgr.DataContext.SubmitChanges();

LoadProperty<int>(EmailIdProperty, data.EmailId);

_sysTime = data.SysTime.ToArray();

base.PopulateOriginalValues(this);

}//using

 

The app blows up with a System.SqlOverflowExcpetion saying that the value must be between 1753 and 9999. I just dont know what I am doing wrong and where. Thanks!!

JoeFallon1 replied on Tuesday, September 09, 2008

I made this change to SmartDate to support SQL Server dates. In my app I only expect dates like Today. It is a transactional app so there isn't any historical or future date stuff happening.

'Change to SmartDate class: 

  ''' <summary>
  ''' Converts a text date representation into a Date value.
  ''' </summary>
  ''' <remarks>
  ''' An empty string is assumed to represent an empty date. An empty date
  ''' is returned as the MinValue or MaxValue of the Date datatype depending
  ''' on the EmptyIsMin parameter.
  ''' </remarks>
  ''' <param name="value">The text representation of the date.</param>
  ''' <param name="emptyValue">Indicates whether an empty date is the min or max date value.</param>
  ''' <returns>A Date value.</returns>
  Public Shared Function StringToDate( _
    ByVal value As String, ByVal emptyValue As EmptyValue) As Date

    If Len(value) = 0 Then
      If emptyValue = SmartDate.EmptyValue.MinDate Then
        Return Date.MinValue

      Else
        Return Date.MaxValue
      End If

    ElseIf IsDate(value) Then
      'This is the change:
      If CDate(value) >= CDate("1/1/1900") AndAlso CDate(value) <= CDate("6/6/2079 11:59 PM") Then
        Return CDate(value)
      Else
        Return Date.MinValue
      End If

      'This is the original code:
      'Return CDate(value)

      'The SmartDate Structure cannot be inherited so we need to change CSLA itself.
      'If Value is not a date then set it to Date.MinValue so that "" will be returned.
      'Effectively this blanks out invalid values in the UI.
      'Also, ensure that Value is in the range 1/1/1900 to 6/6/2079 else SQL Server smalldatetime value is violated.

    Else
      Select Case LCase(Trim(value))
        Case My.Resources.SmartDateT, My.Resources.SmartDateToday, "."
          Return Now

        Case My.Resources.SmartDateY, My.Resources.SmartDateYesterday, "-"
          Return DateAdd(DateInterval.Day, -1, Now)

        Case My.Resources.SmartDateTom, My.Resources.SmartDateTomorrow, "+"
          Return DateAdd(DateInterval.Day, 1, Now)

        Case Else
          Throw New ArgumentException(My.Resources.StringToDateException)
      End Select
    End If

  End Function

Joe