Sunday 25 May 2008

DynamicData - Automatic Column Update

Articles in this Series

Automatic Column Update

The problem I'm trying to address is lets say you have the same columns on each table for simple auditing to check:

  1. Who created the record and when
  2. Who updated the record last and when

We will use the Northwind database for this test with a little tweaking.

We will use the Customers table in Northwind and add four columns:

  1. CreatedBy String
  2. CreatedOn DateTime
  3. UpdatedBy String
  4. UpdatedOn DateTime

These columns don’t need to be shown on any pages so scaffolding needs to be turned off see Listing 1

[MetadataType(typeof(CustomerMetadata))]
public partial class Customer
{
}

public class CustomerMetadata
{
    [ScaffoldColumn(false)]
    public Object UpdatedBy { get; set; }
    [ScaffoldColumn(false)]
    public Object UpdatedOn { get; set; }
    [ScaffoldColumn(false)]
    public Object CreatedBy { get; set; }
    [ScaffoldColumn(false)]
    public Object CreatedOn { get; set; }

}
Listing 1

To automatically update this table each time a row is created or update the DataContext partial class will need to be extended see Listing 2 in the DataContext each table has a partial method that can be implemented. The two needed for adding auditing data are InsertTableName and UpdateTableName where TableName is the name of the table that is to have auditing added.

public partial class NorthwindDataContext : System.Data.Linq.DataContext
{
    partial void InsertCustomer(Customer instance)
    {
        var user = HttpContext.Current.User;
        instance.CreatedBy = user.Identity.Name;
        instance.CreatedOn = DateTime.Now;
        instance.UpdatedBy = user.Identity.Name;
        instance.UpdatedOn = DateTime.Now;

        // finally send this to the DB
        this.ExecuteDynamicInsert(instance);
    }

    partial void UpdateCustomer(Customer instance)
    {
        var user = HttpContext.Current.User;
        instance.UpdatedBy = user.Identity.Name;
        instance.UpdatedOn = DateTime.Now;

        // finally send this to the DB
        this.ExecuteDynamicUpdate(instance);
    }
}
Listing 2

Now when a new record is created in the Customer table the four fields will be updated automatically and because the scaffolding metadata the columns will not be show in the DynamicData app.

Note: This will also work with Linq to SQL.

9 comments:

saxon said...

Great Sharing, but following up the
auto column updating(auditing column),
I 'm sure you should have it
turned on for more than 1 table

Could you advise how to apply it in a generic way for all table with this set of column?

most probably a base class that override the OnSubmitchange or OnPropertyChange?

but I'm not too sure and skillful to add that base class to be inherited.

Anonymous said...

I am using Dyanmic Data while connecting to a oracle database using Entity Data model. I have a table which has created By, Created Date , Updated By and Update Date. I would like to know how can I assign values to these fields when a new row is added to the grid or an existing row is updated.

Stephen J. Naughton said...

See the Entity Framework msdn docs here:http://msdn.microsoft.com/en-us/library/cc716714.aspx that should be a good start. in the future you would get the most from the new DomainService see David Ebbo's session at Mix here:http://videos.visitmix.com/MIX09/T47F there are also several others but this one cover ASP.Net and DD.

Steve :D

Anonymous said...

ok but what if you wanted to show the fields as readonly???

Stephen J. Naughton said...

Hi there look at this article here Column Generator

And also have a look at my latest stuff
Securing Dynamic Data Preview 4 Refresh – Part 2 where I use a class that inherits from MetaModel and MetaTable to create read only fields.

Steve :D

Unknown said...

I am in a similar situation using LINQ to SQL. I have the createdBy, createdOn, etc on every table in my database and I would like to add in the busines logic to save that information and tell DD not to show those columns on the UI. I know I can go into each partial class and do this, but it seems like there should be a way to do this globally. Any ideas?

Stephen J. Naughton said...

have a look at this post A Method to Handle Audit Fields in LINQ to SQL by Steve Sheldon.

Steve

Paul Harris said...

Brilliant. So simple when you know how.

Unknown said...

Thanks a lot Mr. Steve, for sharing the update logic automatically.