Automatic Row Tracking with Visual Studio 2013 (Amr Altahlawi)

Automatic Row Tracking with Visual Studio 2013 (Amr Altahlawi)

Rate This
  • Comments 13

As databases are used by many applications and many users within those applications, it’s a common practice to log when data was created or last modified. In addition to knowing when the data was changed, knowing who changed the data is a common requirement in business applications. LightSwitch in Visual Studio 2013 introduced a new feature to track the creation and modification of data automatically for you by adding a new set of properties to all entities defined within the intrinsic database (i.e., data tables created as part of the app, not attached data). Those properties are: Created, CreatedBy, Modified and ModifiedBy.

Name

Type

Display Name

Created DateTimeOffset Created
CreatedBy Person Created By
Modified DateTimeOffset Modified
ModifiedBy Person Modified by

As you can see from the table above, these new fields make use of the new data types that have been introduced in Visual Studio 2013, DateTimeOffset and the Person type. While these fields are not intended to implement full database auditing, they are a huge productivity boost when you need to display, query, or correlate user activity in your application. Let’s go through an example to see how this works.

Assume you are building an order tracking system. You have 3 entities (Products, Orders, OrderDetails) and you have the requirement to track who created and modified the order and its details. Before the release of Visual Studio 2013, you had to manually add properties to each table and write special code to track who created/modified the data. Now this is built in! You have the option to turn this feature on or off for any internal entities you have modeled in the Data Designer.

When you model your entities using the Data Designer, you will now see that there is a new option in the property window under the General category called “Enable Created/Modified properties” which is checked by default.

When this option is selected, it indicates that the application will track the creation/modification of data for this entity by adding the above properties. Those properties are hidden in the Data Designer but can be seen in the Screen designer so you can optionally display these fields on screens.

With these new fields, it is very easy to filter records that were created by a user. For example, say we want to only display data on a screen that was created by the logged in user. Simply create a query using the Query Designer and add a filter where the CreatedBy field equals the new global value Current User.

Screenshot2

When we run the application and insert some order details, you’ll see that the application saves who created/modified the data and the time as well.

screenshot

If you are upgrading your project from a previous release to Visual Studio 2013, the Enable Created/Modified properties option is not turned on by default, however, you may enable them as long as there are no existing properties with the same names. Enabling these properties will validate these properties don’t currently exist. If any do exist, a message will be displayed and you will need to remove (or rename) them if you want to make use of this feature.

Under the covers, LightSwitch determines what identity to store in the CreatedBy and ModifiedBy fields based on the authentication type of the application. This mechanism is built into the new Person Type. Furthermore, LightSwitch populates the values for CreatedBy and ModifiedBy fields before the save pipeline process starts, so you can use them in your business logic if you need to. For more information please read: Using the Person Business type.

This is another one of those features we added in Visual Studio 2013 in response to customer feedback. We hope that this proves useful in your applications. Please install the Visual Studio 2013, try it and let us know what you think by leaving a comment below or visiting the forum.

Thanks,

Amr Altahlawi, Developer, LightSwitch Team

Leave a Comment
  • Please add 6 and 7 and type the answer here:
  • Post
  • I really like this as its one of those time saving features that really pays off in several ways, such as business auditing and debugging issues.

    Thank you very much!

  • Will it modify the existing database to include those fields?

  • Marcelo, Yes it will. after you upgrade, you need to enable the option for the existing entities as it is turned off by default for existing ones. Then you re-publish your app and it should add the fields to the existing database.

  • Howdy! Is there any simple way of merging an existing users database into LS and using these fields? We currently have a solution that uses a hybrid of the built in users and a custom user table that are linked using the usernames.

  • This is a nice, but what if I am using an external designed database and more than one front-end?

    It look to my I have to create it all myself.

  • Hi Wouter,

    when you said that you are using an external database, you mean that you are attaching to external DB, right?

    This feature exists only for the intrinsic DB. So if you are using external one, you either have to do it through your code or through the DB provider you are using.

  • Hi Richard,

    I'm afraid there is no easy way to do that.

    If I understand you correctly, you're saying that you have a link between the LightSwitch users and your custom user tables (a foreign key from your custom table to the LightSwitch users), right? if this is correct, I guess you can custom your screens and add another query to the screen (a query from your custom table) and join it to the screen entity to match the createdBy/modifiedby with the custom table. Then you can show your custom user name instead of the CreatedBy, ModifiedBy.

  • Can the data (original and new ) be also logged, or any changes made was also recorded aside from the date created, modified, by etc?

  • If I have existing SQL Server Tables, that already have Created, CreatedBy, Modified, ModifiedBy columns, will they be detected and used when I add an existing table to a LightSwitch App? If not, that would be a great feature to add. Most applications I've worked on over the years use a very similar set of columns, and it would be nice to be able to just map them in to their LS equivalents.

  • Can anybody tell me or direct me to a tutorial on how row tracking for current.user was done manually? I'm trying to do it without using lightswitch. Thanks to all.  

  • Hello,

    nice idea. But in the real worl databases already exist. So nice looking - but practically not useful.

    Hansjörg

  • I really like this.  Helps on new projects.  Good work!

  • Good addition.

    Are there any plans to put in a hook for external databases? LS is a great tool, but my client's needs almost always require connecting to an existing SQL db.

    Thanks,

    Bob

Page 1 of 1 (13 items)