Sql Server 2005 Default values

Hi there,

I'm building an MVC application against an existing SQL 2005 database. Most of the tables contain a few columns that have default values. For example the Product table has a WhenModified column that has a default value set to "getdate()" and is non nullable. So basically any time a change is made to a particular Product the WhenModified field is updated with the current date.

If I try to create a Product without setting WhenModified I get the error: "When Modified must be between 01/01/1753 00:00:00 and 12/31/9999 23:59:59". The Product will save fine if I set the WhenModified field (product.WhenModified = DateTime.Now).

Is there some setting in model designer that I can use that will force WhenModified to use the database default? Or is there some way to specify a default value for WhenModified?

It's not a massive issue but I would rather have a default value rather than having to specify the value each time a change is made.

Any help/suggestions greatly appreciated.

We can't do this for arbitrary columns. But we can do it for LightSpeed special date columns (CreatedOn, UpdatedOn and DeletedOn). See http://www.mindscapehq.com/forums/thread/291484 for details of how to do this (you will need a recent nightly build).

Your problem is then that WhenModified is an existing column and you can't change its name to conform to LightSpeed's special date field naming requirements. The solution to this is to call the LightSpeed field UpdatedOn (or just set Track Update Time to true), but use a naming strategy to tell LightSpeed that the UpdatedOn field should be mapped to the WhenModified column. See http://www.mindscapehq.com/documentation/lightspeed/Controlling-the-Database-Mapping/Overriding-the-Default-Mapping for information about naming strategies; in your case, you will just return defaultName for everything except GetUpdatedOnColumnName, which will return "WhenModified" (at least where appropriate).

If you have columns that need to be updated with default values that do not fit one of the LightSpeed special columns, let us know.

Ivan, thank you for the prompt and detailed response.

Overriding the default mapping will work well for us for a lot of our default values, however we do have additional columns that don't conform the Lightspeed's date field naming requirements. For example some columns have a string or an integer as the default value.

What would be the best approach to solving this? Thanks again for your time

It depends if those columns need to be updated on every, er, update. If the columns are defaulted when first set up, you can just mark them as Load Only and LightSpeed will not include them in INSERTs or UPDATEs.

If the columns need to be updated every time the entity is modified, I don't think that would currently be possible and we'd need to look at whether we can add the feature for you. If this is the case, could you give us a couple of examples so that we can understand the specific use cases we have to address? For example, what sort of default values do you need to be able to use? How does your scenario differ from a computed column? Do you also need to have the option to write nondefault values into these columns and if so how would you envisage this working in your .NET model (how would you signal 'let the database default this')? Is it okay that you won't be able to see the database-generated value even after the save is committed (you will need to reload the entity to see it)? Thanks!

The vast majority of our "default" columns are CreatedOn, UpdatedOn type scenarios so overridding the mapping will do the job for this purpose.

There are only a couple of other columns that use defaults and aren't really mission critical so setting these values in code is no big deal. The DB is a few years old, so it's probably time to reevaluate their relevance anyway.

Thanks for all your help. You guys are pretty fast in responding! Have a good weekend :)