Automatic Row Tracking with Visual Studio 2013 (Amr Altahlawi)

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