LLBLGen Pro version + buildnr - 2.6 Final (October 6th, 2008 ) Runtime library version - 2.6.08.1013

I have this entity field with the new SQL 2008 Time data type that is automatically mapped to a TimeSpan .NET type. Getting the value from the database doesn't seem to be the problem but when it's saved I get the following error:

Server Error in '/Admin' Application.
Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Object must implement IConvertible.

Source Error:

Line 453:       {
Line 454:           OfficeScheduleDAO dao = (OfficeScheduleDAO)CreateDAOInstance();
Line 455:           return dao.AddNew(base.Fields, base.Transaction);
Line 456:       }
Line 457:       


Source File: C:\Documents and Settings\User\My Documents\App\Source\DAL\App.DAL\EntityBaseClasses\OfficeScheduleEntityBase.cs   Line: 455

Stack Trace:

[InvalidCastException: Object must implement IConvertible.]
   System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) +7596825
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +4870818

[InvalidCastException: Failed to convert parameter value from a TimeSpan to a DateTime.]
   System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType) +4870015
   System.Data.SqlClient.SqlParameter.GetCoercedValue() +32
   System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc) +100
   System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters) +203
   System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc) +237
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137
   SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() +743

[ORMQueryExecutionException: An exception was caught during the execution of an action query: Failed to convert parameter value from a TimeSpan to a DateTime.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.]
   SD.LLBLGen.Pro.ORMSupportClasses.ActionQuery.Execute() +1139
   SD.LLBLGen.Pro.ORMSupportClasses.BatchActionQuery.Execute() +126
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.ExecuteActionQuery(IActionQuery queryToExecute, ITransaction containingTransaction) +143
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.AddNew(IEntityFields fields, ITransaction containingTransaction) +396
   App.DAL.EntityClasses.OfficeScheduleEntityBase.InsertEntity() in C:\Documents and Settings\User\My Documents\App\Source\DAL\App.DAL\EntityBaseClasses\OfficeScheduleEntityBase.cs:455
   SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.CallInsertEntity() +33
   SD.LLBLGen.Pro.ORMSupportClasses.DaoBase.PersistQueue(List`1 queueToPersist, Boolean insertActions, ITransaction transactionToUse, Int32& totalAmountSaved) +1165
   SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save(IPredicate updateRestriction, Boolean recurse) +1341
   SD.LLBLGen.Pro.ORMSupportClasses.EntityBase.Save() +152
   App.UI.Web.Admin.WebForm14.Page_Load(Object sender, EventArgs e) in C:\Documents and Settings\User\My Documents\App\Source\UI\Web\App.UI.Web.Admin\WebForm14.aspx.cs:25
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +14
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +35
   System.Web.UI.Control.OnLoad(EventArgs e) +99
   App.UI.Web.Admin.Components.AdminBasePage.OnLoad(EventArgs e) in C:\Documents and Settings\User\My Documents\App\Source\UI\Web\App.UI.Web.Admin\Components\AdminBasePage.cs:144
   System.Web.UI.Adapters.ControlAdapter.OnLoad(EventArgs e) +15
   System.Web.UI.Control.LoadRecursive() +8679693
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +627


Version Information: Microsoft .NET Framework Version:2.0.50727.3053; ASP.NET Version:2.0.50727.3053 

Reproduced.

RTL 2.6.08.1013

Test

[TestMethod]
public void SaveTimeFieldOnSS2K8()
{
    MyTableEntity entity = new MyTableEntity();
    entity.TheTime = new TimeSpan(8, 0, 0);

    using (DataAccessAdapter adapter = new DataAccessAdapter())
    {
        adapter.SaveEntity(entity);
    }

    Assert.IsFalse(entity.IsNew);
    Assert.AreEqual(0, new TimeSpan(8, 0, 0).CompareTo(entity.TheTime));
}

Exception Same as above

Seems to be a SqlClient problem We will look into it.

On .NET 2.x, latest sp, it doesn't work indeed.

Will try on .NET 3.5

Bug in System.Data.SqlClient.SqlParameter, DbType set property -> GetMetaTypeFromDbType ->

case DbType.Time:
                        return MetaDateTime;

The problem is: the code is written with the ADO.NET provider factory system so the code can be re-used with both the .NET sql client AND the CE client which is in a different assembly. This is required as the parameter is of type DbParameter, due to the factory, which doesn't have a property 'SqlDbType', it only has a property 'DbType'.

So (and there are many other examples) this is again an example how bad this whole ADO.NET db factory design really is: it's sole purpose is to be able to write db agnostic code, but in practise that doesn't work, there's always a tiny detail which ruins it and where you have to branch out to special case 'if version == XYZ' code.

I do see a workaround for this bug, which is fortunate, as MS doesn't fix their code with proper intervals. I'll try that workaround (which is: if type is time, don't set dbtype, just set the value).

Please see the attached dll for a workaround fix for this issue.

Related connect: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=381934

(not that it will help, but you'll never know! )

Thank you David and Frans.

Your workaround worked like a charm.

Accidentally I found a workaround myself and I'll post it here in case somebody runs into the same problems.

Because I work with a third party UI control that doesn't play nice with TimeSpan two way binding I was forced to create a DateTimeConverter. With a small hack this also this fixed the .NET problem with the time parameter.

After MS fixes the issue or if you use Frans' workaround all you have to do to make it work is comment line 40 and uncomment line 39. This way you can still take advantage of the new sql time data type and use it in UI as a friendly datetime.

Thank you,

Val

Here's the converter:

using System;
using System.ComponentModel;

namespace YourCompany.TypeConverters
{
    [Description("Converter with as core type System.DateTime, for mapping a field with a .NET type System.DateTime onto a time database field")]
    public class DateTimeToTimeConvertor : TypeConverter
    {
        public override bool CanConvertFrom(ITypeDescriptorContext context, Type sourceType)
        {
            return (sourceType == typeof(TimeSpan));
        }

        public override bool CanConvertTo(ITypeDescriptorContext context, Type destinationType)
        {
            return (destinationType == typeof(TimeSpan));
        }

        public override object ConvertFrom(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value)
        {
            if (value is TimeSpan)
            {
                TimeSpan t = (TimeSpan)value;
                return new DateTime(2000, 1, 1, t.Hours, t.Minutes, 0);
            }

            throw new NotSupportedException("Conversion from a value of type '" + value.GetType().ToString() + "' to System.DateTime isn't supported");
        }

        public override object ConvertTo(ITypeDescriptorContext context, System.Globalization.CultureInfo culture, object value, Type destinationType)
        {
            if (value == null)
                throw new ArgumentNullException("value", "Value can't be null");
            if (!(value is DateTime))
                throw new ArgumentException("Value isn't of type DateTime", "value");

            DateTime d = (DateTime) value;
            TimeSpan t = new TimeSpan(d.Hour, d.Minute, 0);
            //return t;
            return d;
        }

        public override object CreateInstance(ITypeDescriptorContext context, System.Collections.IDictionary propertyValues)
        {
            return new DateTime();
        }
    }
}

Thanks for sharing!