CODEWITHSUNDEEP
c#sql-serverormlite-servicestack
Learner
Learner

Reputation: 786

Saving TimeSpan into SQL Server 2012 as a Time column with ServiceStack OrmLite and C#

I have a Time(7) column in a database table, and I want to save a TimeSpan using C# and ORMLite as Object Relational Mapper. While performing this action I get this exception

Operand type clash: bigint is incompatible with time

This is my server stack trace;

[UpdateJobFrequency: 11/07/2015 5:43:39 AM]:
[REQUEST: {JobFrequency:{Id:0,JobId:3,FrequencyId:2,InstanceOfFrequency:1,Time:PT1H,WeekNumber:0}}]
System.Data.SqlClient.SqlException (0x80131904): Operand type clash: bigint is incompatible with time
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at ServiceStack.OrmLite.OrmLiteCommand.ExecuteScalar()
   at ServiceStack.OrmLite.OrmLiteReadCommandExtensions.LongScalar(IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteResultsFilterExtensions.ExecLongScalar(IDbCommand dbCmd, String sql)
   at ServiceStack.OrmLite.OrmLiteDialectProviderBase`1.InsertAndGetLastInsertId[T](IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.Insert[T](IDbCommand dbCmd, T obj, Boolean selectIdentity)
   at ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass4`1.<Insert>b__3(IDbCommand dbCmd)
   at ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)
   at ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec[T](IDbConnection dbConn, Func`2 filter)
   at ServiceStack.OrmLite.OrmLiteWriteApi.Insert[T](IDbConnection dbConn, T obj, Boolean selectIdentity)
   at JobRepositoryBase.<Create>d__a`1.MoveNext() in c:\dev\RepositoryBase.cs:line 89
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at Job.WebAppHost.Service.JobFrequencyService.<Post>d__6.MoveNext() in c:\dev\JobFrequencyService.cs:line 30

I am passing 01:00:00 as TimeSpan value from the interface but it is showing as Time: PT1H in the object at server side.

Also, I have read in one of the stack overflow questionnaire that ADO.Net TimeSpan is mapped to SQL Server Time(7). And I believe that servicestack ORMLite is built upon ADO.Net. However, in this case I am facing this exception.

Any help would be highly appreciated.

Upvotes: 1

Views: 492

Answers (2)

Kon Rad
Kon Rad

Reputation: 315

For future reference, there is now a working solution for utilizing MS SQL's 'Time' column with ORMLite, mapped to TimeSpan type in C#. Simply place the code below anywhere within your C# application:

    //Enable ORMLite to suppot MS SQL 'Time' column
    //Based on https://github.com/ServiceStack/ServiceStack.OrmLite/wiki/OrmLite-Type-Converters
    SqlServerDialect.Provider.RegisterConverter<TimeSpan>(new ServiceStack.OrmLite.SqlServer.Converters.SqlServerTimeConverter
    {
        Precision = 7
    });

Upvotes: 4

Learner
Learner

Reputation: 786

Due to urgency, I had to do a workaround for the problem, I had to change the data type of my table to BigInt. So, what ORMLite is doing is mapping the BigInt as TimeSpan and vice versa - Github, now I can store and retrieve this field without any problem.

But still I believe this is not a perfect solution as in my case I am developing database from scratch, but in a case where someone has to use the existing database, this workaround fails.

Upvotes: 1

Related Questions