none
LightSwitch and SQL Server Column Default Values

    Question

  • I just started working with LightSwitch and was creating an interface to an existing SQL Server database table.  The table has two columns (InsertionDate and DateLasteUpdated) that have a default value defined in the database (i.e. GetDate()).  However, when I insert a row using the LightSwitch interface, the values inserted into those columns are both NULL.  This tells me that the LightSwitch interface screen is actually passing NULL for both of those columns. 

    Since those columns are not presented on the screen, why are any values being pssed for those columns?  (If no values were passed for those columns, they would automatically be set to the current datetime.)

    Saturday, August 06, 2011 6:33 PM

All replies

  • If the column is datetime or number and required, it should be set to zero for number and Now for datetime.  This happens at entity construction and can be changed on screen.  If column is nullable, then value default is not set and remains null.  You can also set defaults in the Created handler for the entity. That should give you same effect of what your looking for, but have not tested that in your use case.

        partial void Task_Created()
        {
          this.StartTime = DateTime.Now;
          this.TargetTime = DateTime.Now.AddDays(1);
          this.Priority = 3;
        }

    Saturday, August 06, 2011 6:42 PM
  • EntityName_Created is the place to *initialise* your entity's data, not Inserting/Updating, because the initialise values will then be visible in the screen when you're adding it, which is usually the whole purpose of initialising your data.

    If you set values in Inserting/Updating you're effectively altering your entity just as it's about to be saved (ie the user has clicked the "save" button). You might then well be overwriting a value that the user has entered. Not good, lol.

    So, for initialising, use EntityName_Created.

    For adding related records on save (like history/auditing info), or for updating a status value (or something similar) based on what the user has entered, use Inserting/Updating.

    "Since those columns are not presented on the screen, why are any values being pssed for those columns?  (If no values were passed for those columns, they would automatically be set to the current datetime" - this is just the way that Entity Framework, which LS relies on, works.

    I hope that makes it all a bit clearer.


    Yann

    (plus ça change, plus c'est la même chose!)

    Sunday, August 07, 2011 12:40 AM
  • @WilliamStacey/Yann,

    The purpose of the default value being in the InsertionDate column is precisely to preclude any action being required on the part of any applications.  Similarly, by having the default set in the DateLastUpdated, no action is required during the INSERT statement.  I am looking to have the current system datetime as the InsertionDate so as to preclude any opportunity for a user to predate or postdate an insertion.  (I am a DBA first and a developer/programmer and concerned with truth in data; however, I also preferred this approach as a developer . . . partially out of laziness. ;-)

    "Since those columns are not presented on the screen, why are any values being pssed for those columns? (If no values were passed for those columns, they would automatically be set to the current datetime" - this is just the way that Entity Framework, which LS relies on, works.

    Actually, those columns were not presented on the screen; therefore, I assumed that no values were passed for them.  However, in looking at the data, I discovered that the default values had not been entered.  I then thought that, perhaps, I had failed to set them but, upon checking, I found I had set them.  I then entered a row of data in Management Studio and the defaults were set.

    Since there is absolutely no intent to ever present the InsertionDate other than in a Read-Only mode, there is no worry about overwriting any user inserted data.  I get the impression you have never made use of default values set in a database.  WHile I do not hold with always having a default value, there are times when default values are not only quite useful but even the best option.  InsertionDate columns are a very typical place where one finds them being used.  It typically provides a means for ascertaining things like the actual process date, the actual date of entry, or the actual datetime for a log entry . . . as opposed to one that a user may or may not have entered (thus providing either a potentially false/misleading or a totally missing datetime-stamp.

     

    Sunday, August 07, 2011 3:05 AM
  • So, essentially, what y'all are telling me is that:

    • Unless I code for the value to be provided, it will be passed as NULL;
    • If I want a default value, I have to write code so that the default value is set in the process of creating a new record/entity/whatever;
    • No database-level defaults will have any meaning.

    This is only the second language/development environment that I have encountered with this constraint . . . the other was MS Access. :-/

    Sunday, August 07, 2011 3:10 AM
  • Wow, if you're aiming those remarks at *me*, it looks like you've misunderstood everything I said.

    If you were addressing someone else, disregard what I'm about to say. Though it might be best in future, when several people have responded, that you indicate WHO you're making your comments to, to avoid any misunderstanding.

    1. I was in fact *telling* you to put the code in Creating, not Inserting/Updating, for *exactly* the reason you just stated.
    2. When I wrote "Actually, those columns were not presented on the screen; therefore, I assumed that no values were passed for them", I was quoting what you had said, not disagreeing with you, & then *explaining* that it's the way that EF works & therefore why it was different from what you expected.
    3. Again quoting you, "Since there is absolutely no intent to ever present the InsertionDate other than in a Read-Only mode, there is no worry about overwriting any user inserted data.", I was explaining that IF the code was placed in inserting/updating that there was a RISK of overwriting values that a user had entered, and that was why the code *should* go in Creating. This is best practise, whether you "intend" your data to be read-only or not.
    4. And I *certainly* hope that "I get the impression you have never made use of default values set in a database" was not for me, as everything I said was absolutely correct, & I have been dealing with default values in databases since Access 2.0.

    Yann

    (plus ça change, plus c'est la même chose!)

    Sunday, August 07, 2011 4:53 AM
  • I ran into that very same thing today as well. I guess what we are asking is a checkbox on the table fields that says "Don't send me!"
    Sunday, August 07, 2011 4:59 AM
  • Hello Yann,

    I think RDW2 is just frustrated at the way LS behaves in this matter. I didn't read it as being directed at you. We all really appreciate your help.

    Alex

    Sunday, August 07, 2011 5:10 AM
  • Hehe, OK, thanks for that..

    Hmm, maybe I should have *asked* first, instead of ASSuMING. :-P (well I sort of did)


    Yann

    (plus ça change, plus c'est la même chose!)

    Sunday, August 07, 2011 5:14 AM
  • @RDW2:

    Here's what I do:

    Bit and numeric values are set to Not Allow Nulls in the database and the default value is set to 0 for a number.  The default bit values are set to a default of true or false depending on the application usage.  Generally our users will NOT ever need a 3 state boolean value - except on a Search layout group.

    There is NO code required to set default values.  SQL does it for you.

    Yes, it would be insane to have to test everywhere for a null value on numerics within a biz app.  Thank goodness this isn't the case.

     

    Tuesday, August 09, 2011 12:27 AM
  • Hello,

    Today I have exactly the same problem as you and found this thread. Did you ever find an answer to the question?

    One possible 'cunning' plan I can think of is adding an insert trigger to the table , that intercepts any value from the LS app and replaces them by good old GetDate(). So no messing with wrong dates on user PC's.

    Also I have set my table definition for the InsertDate to not accept nulls, therefore, in LS app I need to use the entity_created event and set the values beforehand to the user system date, then when the data is saved , the trigger kicks in and writes the SQL server date.

    But it is a bypass.

    Another cunningly cunning plan would be to request GetDate from the sql server connection from within the Entity_Created code and use the result as default for the entity. It just generates a bit more traffic of course.


    Jan D'Hondt - Database and .NET development

    Thursday, March 01, 2012 1:27 PM
  • Hey Jan,

    I set default dates in the Entity _Created method, not in SQL Server.

    I do this because there are multiple fields in the Entity that need to be set. 

    As a methodology for LS development, I only use default values for bits, numbers, and GUIDs in SQL.   I'm not saying that this is the best solution  - just that this keeps things simple and always works.


    Garth Henderson - Vanguard Business Technology

    Tuesday, March 06, 2012 7:26 PM
  • Hi

    I went through the whole pain of working with Lightswitch and SQL defaults, I did find a solution from Huy Nguyen (see my q's here) and I blogged on how I got round all the finicky pieces here:
    Using SQL Server Defaults with Lightswitch

    I hope this helps!


    Saturday, May 11, 2013 6:54 PM