SqlDateTime overflow using NHibernate and stored procedure

Question

I'm new to NHibirnate. I've create mapping files, model and that work's. But now I'm trying to use stored procedure for insert, where I'm using current date and it is throws an exception.

My maping:

  <class name="MyClass" table="table_name">
    <id name="Id" column="id">
      <generator class="identity"/>
    </id>
    <property name="Sum" column="sum"/>
    <property name="Direction" column="direction"/>
    <property name="Date" column="datetime"/>
    <property name="Number" column="number"/>
    <sql-insert xml:space="preserve">
      EXECUTE dbo.pr_AddNew @sum = ?, @direction = ?
    </sql-insert>
  </class>

My Class:

public class MyClass
{
    public virtual int Id { get; set; }
    public virtual DateTime Date { get; set; }
    public virtual decimal Sum { get; set; }
    public virtual byte Direction { get; set; }
    public virtual int Number { get; set; }
}

And my stored procedure:

CREATE PROCEDURE pr_AddNew
    @sum decimal(19, 2),
    @direction int
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO dbo.table_name([datetime], sum, direction, number)
        VALUES(GETDATE(), @sum, @direction, dbo.fn_GetNextNumber())
END
GO

As you can see I'm using GETDATE() function to get current day on db side.

And here is my Test method:

    [TestMethod]
    public void AddTest()
    {
        var myClass= new MyClass{ Direction=0, Sum=150};
        IRepository repository = new MyClassRepository();
        repository.Add(myClass);

        using (ISession session = _sessionFactory.OpenSession())
        {
            var fromDb = session.Get<MyClass>(myClass.Id);
            Assert.IsNotNull(fromDb);
            Assert.AreNotSame(myClass, fromDb);
        }
    }

This code gives me an exception: "SqlDateTime overflow."

So If I create myClass instance as:

var myClass= new MyClass{ Direction=0, Sum=150, Date=DateTime.Now()};

everything is ok. As I understand the problem is in the difference between min possible DateTime value in SQL and C#. But I don't need to pass datetime value to sql. What can I do with this?

You have to tell NH that the Date column is automatically generated, otherwise it will try to save System.DateTime.MinValue, ie '0001-01-01'. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Jul 4, 2016 at 12:45
@PanagiotisKanavos I'v understand that, but how can I do this? — Gleb
– Gleb, Commented Jul 4, 2016 at 12:56

Stefan Steinegger · Accepted Answer · 2016-07-05 11:46:29Z

1

You have to map it as a generated column. It tells NH to not send it to the database for insert or update and also tells NH that the values is loaded after insert or update (which may be a performance problem).

generated="never|insert|always"

In your case:

<property name="Date" column="datetime" generated="insert"/>

By the way, there is a completely different approach to get the same. You can set the Date in the constructor of the class and map it normally. It's much less painful in contrast to cope with stored procedures.

answered Jul 5, 2016 at 11:46

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SqlDateTime overflow using NHibernate and stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related