Tuesday, June 19, 2012
Capturing Current User Identity

Many applications require that the name and identity of the user be captured when a record is modified. Let’s create a ModifiedByID and ModifiedByName field in the Orders table that will be updated by an SQL Business Rule whenever an order is modified.

First, let’s add the columns to the table. Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Orders table node, and select Design.

Design the Orders table in the Northwind database.

Configure two new columns:

Column Name Data Type Allow Nulls
ModifiedByID uniqueidentifier True
ModifiedByName nvarchar(50) True

Save the table modifications. Switch to the web application generator, and refresh the Orders controller.

Refresh the Orders controller.

Regenerate the web application. Next, let’s remove the ModifiedBy fields from the presentation, and create a business rule to update these fields.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Views / editForm1 / c1 – Orders / ModifiedByUserID data field node, and select Delete option.

Delete ModifiedByUserID data fields from the edit form of Orders.

Confirm the operation. Right-click on Orders / Views / editForm1 / c1 – Orders / ModifiedByUserName data field node, and delete this data field as well.

Delete ModifiedByUserName data field from edit form of Orders controller.

Right-click on Orders / Business Rules node, and select New Business Rule.

New Business Rule for Orders controller.

Assign this business rule the following properties:

Property Value
Command Name Insert|Update
Name UpdatingModifiedBy
Type SQL
Phase After
Script
update Orders 
set ModifiedByUserID = @BusinessRules_UserId, 
    ModifiedByUserName = @BusinessRules_UserName
where OrderID = @OrderID

Press OK to save the business rule.

Make sure to spell the business rule properties correctly – for example, if the “@BusinessRules_UserId” function was capitalized as “@BusinessRules_UserID”, the function will not be found and an exception will be thrown.

On the toolbar, press Browse to regenerate the web application.

Navigate to the Orders page, edit a record, and save.

Edit an Order record and save changes.

View the record in SQL Server Management Studio. The relevant UserId and Name have been saved.

ModifiedByUserID and ModifiedByUserName columns have been populated by the business rule.