none
SQLCE LastModifiedDate per row? RRS feed

  • Question

  • Hello guys,

    I am hitting a road block. I am using SQLCE and I wanted to use LastModifiedDate per row. To my understanding, SQLCE doesn't support trigger, so, I cannot do this. So, any suggestions? It is going to make a lot of nasty code if the value cannot be updated automatically. I will for sure, miss one or two update commands without updating the LastModifiedDate. That would be very bad. So, I really think I need something automatic.

    Thank you.

    Saturday, March 3, 2012 6:50 AM

Answers

  • Simple solution
    define property called LastModifiedDate
    whenever modifying entity set this column to datetime.Now

    :)


    .NET guy

    • Marked as answer by magicalclick Monday, March 5, 2012 5:41 AM
    Saturday, March 3, 2012 1:41 PM
  • Hi magicalclick,

    Welcome to MSDN Forum.

    I agree with tsadigov's solution, is it can solve your issue?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by magicalclick Monday, March 5, 2012 5:41 AM
    Monday, March 5, 2012 3:27 AM
    Moderator
  • I think I've found better way to solve this

    I've found this in a book  by Julia Lerman ISBN: 978-0-596-80726-9 page 609

    "One of the most useful places to take advantage of the ObjectStateManager is when

    saving changes, either by overriding the ObjectContext.SaveChanges method or in the
    ObjectContext.SavingChanges event handler."
    internal void FixupModifiedDates()
    {
    var entries =
    from ose in this.ObjectStateManager.GetObjectStateEntries
    (EntityState.Added | EntityState.Modified)
    where ose.Entity != null
    select ose;
    foreach (var entry in entries)
    {
    var fieldMetaData = entry.CurrentValues.DataRecordInfo.FieldMetadata;
    FieldMetadata modifiedField = fieldMetaData
    .Where(f => f.FieldType.Name == "ModifiedDate")
    .FirstOrDefault();
    if (modifiedField.FieldType != null)
    {
    string fieldTypeName = modifiedField.FieldType.TypeUsage.EdmType.Name;
    }
    if (fieldTypeName == PrimitiveTypeKind.DateTime.ToString())
    {
    entry.CurrentValues.SetDateTime(modifiedField.Ordinal,
    DateTime.Now);
    }
    }}
    


    .NET guy

    • Proposed as answer by tsadigov Monday, March 5, 2012 2:34 PM
    • Marked as answer by magicalclick Monday, March 5, 2012 6:07 PM
    Monday, March 5, 2012 2:34 PM

All replies

  • Simple solution
    define property called LastModifiedDate
    whenever modifying entity set this column to datetime.Now

    :)


    .NET guy

    • Marked as answer by magicalclick Monday, March 5, 2012 5:41 AM
    Saturday, March 3, 2012 1:41 PM
  • Hi magicalclick,

    Welcome to MSDN Forum.

    I agree with tsadigov's solution, is it can solve your issue?

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by magicalclick Monday, March 5, 2012 5:41 AM
    Monday, March 5, 2012 3:27 AM
    Moderator
  • It doesn't really help me, because that requires manually management, which is something I really wish to avoid. But I guess that's the only option for me. I just have to make sure there are very few access point to modify the rows, luckily my project is small enough for me to manually manage that. It is a very poor practice, but, that's the price to avoid extra fee to my hosting service.

    thank you for helping me out.

    Monday, March 5, 2012 5:55 AM
  • if you are using edmx and generating classes from it using t4, you can change t4 template to inject code that sets LastChangeDate into every other property

    so you will not have to manage this manually

    template based code generation gives us power to do interesting things


    .NET guy

    • Proposed as answer by tsadigov Monday, March 5, 2012 10:34 AM
    Monday, March 5, 2012 10:34 AM
  • I think I've found better way to solve this

    I've found this in a book  by Julia Lerman ISBN: 978-0-596-80726-9 page 609

    "One of the most useful places to take advantage of the ObjectStateManager is when

    saving changes, either by overriding the ObjectContext.SaveChanges method or in the
    ObjectContext.SavingChanges event handler."
    internal void FixupModifiedDates()
    {
    var entries =
    from ose in this.ObjectStateManager.GetObjectStateEntries
    (EntityState.Added | EntityState.Modified)
    where ose.Entity != null
    select ose;
    foreach (var entry in entries)
    {
    var fieldMetaData = entry.CurrentValues.DataRecordInfo.FieldMetadata;
    FieldMetadata modifiedField = fieldMetaData
    .Where(f => f.FieldType.Name == "ModifiedDate")
    .FirstOrDefault();
    if (modifiedField.FieldType != null)
    {
    string fieldTypeName = modifiedField.FieldType.TypeUsage.EdmType.Name;
    }
    if (fieldTypeName == PrimitiveTypeKind.DateTime.ToString())
    {
    entry.CurrentValues.SetDateTime(modifiedField.Ordinal,
    DateTime.Now);
    }
    }}
    


    .NET guy

    • Proposed as answer by tsadigov Monday, March 5, 2012 2:34 PM
    • Marked as answer by magicalclick Monday, March 5, 2012 6:07 PM
    Monday, March 5, 2012 2:34 PM