How OrmLite Update a DateTime to getdate()

ServiceStack.OrmLite
1 
      Db.UpdateOnly(() => new QueueRecord
                {
                    CallTime = DateTime.Now,
                    State = 1,
                    WindowID = WindowID
                }, p => p.ID== currentItem.ID);

the above code is update the CallTime to Local Computer DateTime, I Want to update the CallTime to Sqlserver server datetime, If use the sql code is

update QueueRecord set CallTime = getdate()

How To Set the getdate() in Ormlite ?

2

You can’t with a Typed API so you’ll need to use db.ExecuteSql to execute a Custom Update statement.

For INSERT’s you can declare columns with the default value of RDBMS UTC Time, e.g:

public class Poco
{
    [Default(OrmLiteVariables.SystemUtc)]  //= {SYSTEM_UTC}
    public DateTime CreatedTimeUtc { get; set; }
}
3

Look at tests/ServiceStack.OrmLite.Tests/Shared/UpdateCommandFilter.cs for a filter which does what you want and tests/ServiceStack.OrmLite.Tests/DefaultValueTests.cs for how to use it. (Sorry, I don’t know how to generate a github url for those.)

4

Hi Bruce thanks for the pointer. FYI to add a link in Markdown you can use this syntax:

[Label](url)

Here’s link to UpdateCommandFilter.cs you can copy which I’ve just changed to use an extension method to make it a bit nicer to use. Here’s different examples showing its usage, e.g:

db.Update(cmd => cmd.SetUpdateDate<DefaultValues>(nameof(DefaultValues.UpdatedDateUtc)),
    new DefaultValues { Id = 1, DefaultInt = 45 }, new DefaultValues { Id = 2, DefaultInt = 72 });
5

If I Use

[Default(OrmLiteVariables.SystemUtc)]

In SqlServer,

this.Variables = new Dictionary<string, string>
            {
                { OrmLiteVariables.SystemUtc, "SYSUTCDATETIME()" },
            };

the return date is not my want date, I want return getdate() , How to override it , or added some OrmLiteVariables?

6

You can add a new variable with:

SqlServerOrmLiteDialectProvider.Instance.Variables["{GETDATE}"] = "GETDATE()";

Which you should then be able to use with:

[Default("{GETDATE}")]
7

I don’t get values set when using the Default attribute and the variable method as shown.

I have a table:

CREATE TABLE TestTable
(
RecID UniqueIdentifier NOT NULL,
LastSavedDateTime DATETIME NOT NULL
)

A poco for that table:

public partial class TestTable
{
[Required]
[PrimaryKey]
public string RecID { get; set; }        
[Required]
[Default("{GETDATE}")]
public DateTime LastSavedDateTime { get; set; }
}

And running this code yields an error

ServiceStack.OrmLite.SqlServer.SqlServer2016OrmLiteDialectProvider.Instance.Variables["{GETDATE}"] = "GETDATE()";
        var dbFactory = new OrmLiteConnectionFactory("Data Source=localhost;Initial Catalog=TestDB;User ID=Dev;Password=*******", ServiceStack.OrmLite.SqlServer2016Dialect.Provider);
        using (var db = dbFactory.Open())
        {
            TestTable testItem = new TestTable { RecID = System.Guid.NewGuid().ToString() };
            db.Insert(testItem);
        }

The error is:

System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'LastSavedDateTime', table 'TestDB.dbo.TestTable'; column does not allow nulls. INSERT fails.
The statement has been terminated.'

I’ve tried this on SS 5.0 from MyGet and also various different versions of the SQLDialect Providers.

What I really wanted was a subquery as the variable - e.g.:

ServiceStack.OrmLite.SqlServer.SqlServer2016OrmLiteDialectProvider.Instance.Variables["{NextSequenceNo}"] = "(SELECT COALESCE(MAX(SequenceNo), 0) + 1 FROM TestTable)";

I’ll have to resort to a custom insert, but it’d be nice if possible to have these variables working as I prefer the strong type philosophy.

8

The [Default] field creates a column with a default value when it creates the Table Schema, it has no effect on an already existing Table.