SubSonic 3.0 Simple Repository Adding a DateTime Property To An Object

Question

I am trying out SubSonic to see if it is viable to use on production projects. I seem to have stumbled upon an issue whith regards to updating the database with default values (String and DateTime) when a new column is created.

If a new property of DateTime or String is added to an object.

public class Bug
{
    public int BugId { get; set; }
    public string Title { get; set; }
    public string Overview { get; set; }
    public DateTime TrackedDate { get; set; }
    public DateTime RemovedDate { get; set; }
}

When the code to add that type of object to the database is run

var repository = new SimpleRepository(SimpleRepositoryOptions.RunMigrations);           

repository.Add(new Bug()
{
    Title = "A Bug",
    Overview = "An Overview",
    TrackedDate = DateTime.Now
});

it creates the following sql:

UPDATE Bugs SET RemovedDate=''01/01/1900 00:00:00''

For some reason it is adding double 2 single quotes to each end of the string or DateTime.

This is causing the following error:

System.Data.SqlClient.SqlException - Incorrect syntax near '01'

I am connecting to SQL Server 2005

Any help would be appreicated as apart from this issue i am finding SubSonic to be a great product.

---

I have created a screen cast of my error here:

Answer 1

Try changing the type of RemovedDate from DateTime to DateTime?

public class Bug
{
    public int BugId { get; set; }
    public string Title { get; set; }
    public string Overview { get; set; }
    public DateTime TrackedDate { get; set; }
    public DateTime? RemovedDate { get; set; }
}

Answer 2

OK this looks like it's definitely a bug, you don't say whether there's a WHERE clause on that UPDATE statement but if there isn't it's even more of one. Please report it as an issue to github with a test case to replicate. In the meantime you can get around this by setting the value of RemovedDate in code e.g.

repository.Add(new Bug()
{
  Title = "A Bug",
  Overview = "An Overview",
  TrackedDate = DateTime.Now
  RemovedDate = DateTime.Now // Set it as required here Now's just an example
});

Answer 3

It is a bug and the dual quotes was introduced a few revs back. I'm fairly certain this has been fixed > 3.0.0.3 so if you want to grab the current drop it should be dealt with.

Sorry for the confusion - but as you have found out you can set the value on create and this will sort the problem. In addition you can do what andymeadows suggests and this will make the field nullable (which is probably more accurate).

Anyway - we'll have this fixed soon :). I'm trying to find a job and don't have too many cycles to spend merging branches :(

Answer 4

Hi everyone,

was there ever a fix introduced for this? I'm running 3.0.0.3 and I'm having the same error while running migrations and I'm setting the value in the constructor.

CreatedDate = DateTime.Now;

the SQL that is running in the background for the migrations is

UPDATE Messages SET CreatedDate=''1/1/1900 12:00:00 AM''

Is this being run with the 'default' date so existing records will have some value?