Code On Time: Tips and Tricks

Blog

Tips and Tricks

Thursday, August 25, 2011Print Subscribe

File Upload / Download (External Storage)

Our previous article has covered the subject of File Upload / Download. We have discussed storing and retrieving of files in binary database table columns. Web apps created with Code On Time can greatly benefit from automatic capturing of content type, file name, and length in the specially named table columns that compliment a binary column. This approach requires no programming and works great if the database is expected to store the binary content.

Recent innovations in data storage methods make it highly efficient and cost-effective to externalize binary content from the database. The database becomes much more compact and is easier to backup and manage.

Online storage systems such as Amazon S3 and Microsoft Azure Storage provide a robust and cheap storage for large data files. A web application can also benefit if the binary content does not leave the premises of the data center and is stored in Network Attached Storage.

Creating a Sample Web App

Let’s create a sample web application to illustrate the mechanism of externalizing binary data that remains associated with table rows in a database.

Start Code On Time, select “Create new web application task”, and choose Web Site Factory type for our web application.

Enter UploadDownload in Project Name and make sure to select the programming language that you are comfortable to use. Click Create button.

Leave the namespace and framework unchanged and click Next.

In this tutorial we will be using Microsoft SQL Server 2008. Other types of supported database will involve exactly the same implementation techniques.

Select SQL Server Data Provider and click the button located next to Connection String input field.

Enter the name of your local server or SQL Azure server and specify login credentials if needed.

Enter My Upload Download Demo in Database input field and click Create. Confirm that you want to create a database by press Yes button.

Select Northwind in Sample Tables drop down and click Install button. Wait for the installation script to finish. You will see a popup message indicating a successful installation.

The next step is optional. We suggest implementing a built-in membership system in your application. Click Add button shown in the picture. Wait for ASP.NET Membership installation script to finish.

Click OK button at the bottom of connection configuration screen to save the database connection string.

Click Next twice to reach Reporting section. Select “Enable dynamic and static reports in my application”.

Click Next button to get to Features page of the project wizard. Enter Upload / Download Demo (External) in Page Header box.

Continue clicking Next until you arrive to the summary page presenting a list of project data controllers. Proceed to generate a project by clicking Next. The web app will start in your default browser shortly thereafter.

Login using admin/admin123% user account. Select Categories tab to activate the list of product categories stored in the sample database.

Next we will change the structure of the database and create a virtual binary field that will have its content stored in the file system on your computer.

Preparing the Database

The following picture shows the structure of Categories table.

Table column Picture has been automatically processed by web app generator and implemented as a file upload field. The previous screen shot shows the thumbnails of the images stored in the database.

Our intent is to implement a virtual field that will coexist alongside the field Picture but will have its content stored outside of the application database. Think of it as if there in invisible column that looks exactly as Picture but is not actually present in the database table.

Let’s call this field ExternalDoc (short for External Document). The field implementation will require a few utility columns even though the binary field itself will not be present in the database .

These special utility columns must have their name start with the name of the binary column/field. Execute the following query in SQL Management Studio or SQL Azure Database Manager against My Upload Download Demo database.

alter table dbo.Categories
add ExternalDocFileName nvarchar(150) null
go

alter table dbo.Categories
add ExternalDocLength integer null
go 

alter table dbo.Categories
add ExternalDocContentType nvarchar(150) null
go

The scrip will add three utility columns to the structure of Categories table to allow capturing and storing of the uploaded file name, its length, and content type.

This is how the table structure will look now.

Implementing ExternalDoc Field in the Project

Let’s incorporate the new columns and the corresponding virtual field in our project.

Bring forward the web app generator window and click on the project name.

Our database has changed but the code generator is not aware of that. Database metadata (tables, columns, indexes, etc.) are cached in the project files. Click Refresh button to refresh the metadata. The code generator will bring you straight to Data Controller summary page.

Click Start Designer button to activate Project Designer. Select Categories data controller on All Controllers tab.

Enter CategoriesBusinessRules in Handler input and click OK button.

Select Categories data controller one more time. Activate Fields tab and select New | New Field on the action bar.

Enter ExternalDoc as Name. Change the type of the field to Byte[]. Select check box “Allow null values”.

Our field is virtual. We need to specify a SQL expression that will be evaluated by the database server when the field value is about to be retrieved. Select “The value of this field is computed at run-time by SQL expression” check box and enter NULL in SQL Formula text box.

Our field will store large binary objects (BLOB). This type of field requires special handling by the application. Select “Value is retrieved on demand” check box. On Demand Properties section will become visible in the designer.

Enter the name of the primary key column of the Categories table in the Source Fields input. The name of the column is CategoryID.

Your web application will also need a special construct that will help handling details of basic uploading/downloading just before/after you save the file to the external storage or have it retrieved.

Your application uses the term On Demand Handler to describe this construct in the source code. Any unique name will do. You can use the same on-demand handler name if a binary column is displayed in the views of other data controllers

We will call our handler CategoriesExtenalDoc, which combines the table name and the name of the virtual column. This is the default convention used for all on-demand handlers of your web application.

Set On Demand Style to Thumbnail to have a preview thumbnail displayed when the field is rendered.

Finally enter the field label as External Doc.

The project designer property page will look as follows. Click OK button to save the field.

Changing Visual Properties of ExternalDoc and Utility Fields

Select Data Fields tab to activate bindings of data controller fields to presentation views. Data fields allow controlling many presentation aspects of your application.

Let’s bind the virtual field External Doc to editForm1 and grid1 views.

The first binding will insert the field ExternalDoc in the view grid1. Select New | New Data Field on action bar. Also make sure to enter 15 in the columns property of the data field. This will limit the space taken by the field on automatically produced reports if you choose an option to print the list of categories when running the application.

Next bind our virtual field to the view editForm1. Make sure to select a category for the field. Category selection is mandatory if the view is a form.

Save the second binding and enter External in the Quick Find area on the action bar.

Delete all bindings of utility fields to the view createForm1. Uploading of binary content is only supported for existing records. Select each binding and click Delete button or select Delete option from the row context menu in the list of bindings.

Change Text Mode property of all utility fields from Text to Static. This will make the field values read-only from the user standpoint but will allow application to make changes to the values when the files are uploaded. Optionally assign n0 as a data format string for both bindings of ExternalDocLength to improve readability of the field value for very long files.

The list of ExternalDocXXX bindings will looks as follows when you finish making changes.

Click Exit button to exit the project designer and proceed to generate the web application.

Navigate to Categories tab. The screen will look similar to the screen shot.

Select any category and observe that we now have the virtual field and utility fields presented at the bottom of the form view.

Implementing Upload / Download Business Rules

If you try to upload a file in External Doc field then you will see that utility fields are acquiring the properties of the file but the actual data is no saved and an error message is displayed stating “Error: failed to upload categories external doc. Invalid column name \u0027ExternalDoc\u0027.”

You can edit the record again and this time push Clear button instead of performing an upload.

The same error message will be displayed at the top of the page but the utility fields will get cleared.

You have probably guessed already that we need to get involved in the process and save the file when the browser has finished uploading the file.

Bring up the code generator and click develop link under Actions column next to the name of your project. Visual Studio or Visual Web Developer will start.

Press Ctrl+Alt+L to activate Solution Explorer and find CategoriesBusinessRules.cs(vb) in the project tree. Double-click the file to open the text editor.

The business rules class will require three methods to support an externally stored binary content.

The first method will need to intercept the event of uploading, prevent the application framework from trying to save the file, and persist the file contents to the file system.

We accomplish that by implementing a method with ControllerAction attribute. The parameters of the attribute indicate the data controller, the name of the action, and the name of the field.

Method arguments include CategoryID and ExternalDoc stream to let us interact with the uploaded content. You can also include optional arguments with names ExternalDocFileName, ExternalDocLength, and ExternalDocContentType if you need this information to correctly externalize the file.

Our implementation is simply storing the file in the predefined folder in My Documents. We use the category ID and generic “.bin” extension to persist the file
The second method will be invoked when the binary content needs to be streamed to the client browser. Make sure to prevent the default processing logic from being executed by calling PreventDefault method.

Again we are using the category ID to find the location of the previously saved file and this time we write the contents of the file to the stream passed as externalDoc argument. The application framework will automatically take care of supplying the file name and content type to the browser.
The purpose of the third method is t0 let the application know if there is a value in a given binary column of a given row.

If an external file exists then the method will update the value of the binary field with the primary key of the record. The client library will use this ID to communicate with the server components of your application and to display a thumbnail if needed.

If the binary field is empty then the method will return the same category ID converted to a string and preceded by the word null in lower case and the symbol “|”. For example, if the category ID is 7491 then the value stored in ExternalDoc will be null|7491.

We are not actually probing if the file exists in the designated folder. Instead we make an assumption that if ExternalDocFileName field value is not blank then the file has been previously uploaded without errors. Your own implementation may do whatever it takes including possible communicating with external systems to verify that the file does exist.

Note that method names are absolutely arbitrary and play no role in application execution. ControllerAction and RowBuilder attributes turn these methods in the special sauce of your business rules class.

Before you proceed any further make sure to fire up Windows Explorer. Go to Documents folder and create My External Doc Files folder referenced in the implementation below. The folder must exist by the time you try uploading the content.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [ControllerAction("Categories", "UploadFile", "ExternalDoc")]
        protected void SaveExternalDocToFileSystem(int categoryID, Stream externalDoc)
        {
            PreventDefault();
            string fileName = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream output = File.Create(fileName);
            externalDoc.CopyTo(output);
            output.Close();
        }

        [ControllerAction("Categories", "DownloadFile", "ExternalDoc")]
        protected void LoadExternalDocFromFileSystem(int categoryID, Stream externalDoc)
        {
            PreventDefault();
            string fileName = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream input = File.OpenRead(fileName);
            input.CopyTo(externalDoc);
            input.Close();
        }

        [RowBuilder("Categories", RowKind.Existing)]
        public void VerifyPresenceOfExternalFile()
        {
            int categoryID = Convert.ToInt32(SelectFieldValue("CategoryID"));
            string externalDocFileName = Convert.ToString(
                SelectFieldValue("ExternalDocFileName"));
            // update ExternalDoc field to reflect existence of a file
            if (!String.IsNullOrEmpty(externalDocFileName))
                UpdateFieldValue("ExternalDoc", categoryID);
            else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryID));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.IO

Namespace MyCompany.Rules

    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <ControllerAction("Categories", "UploadFile", "ExternalDoc")> _
        Protected Sub SaveExternalDocToFileSystem(ByVal categoryID As Int32, _
                                                  ByRef externalDoc As Stream)
            PreventDefault()
            Dim fileName As String = Path.Combine( _
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
                String.Format("My External Doc Files\\{0}.bin", categoryID))
            Dim output As Stream = File.Create(fileName)
            externalDoc.CopyTo(output)
            output.Close()
        End Sub

        <ControllerAction("Categories", "DownloadFile", "ExternalDoc")> _
        Protected Sub LoadExternalDocFromFileSystem(ByVal categoryID As Int32, _
                                                  ByRef externalDoc As Stream)
            PreventDefault()
            Dim fileName As String = Path.Combine( _
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
                String.Format("My External Doc Files\\{0}.bin", categoryID))
            Dim input As Stream = File.OpenRead(fileName)
            input.CopyTo(externalDoc)
            input.Close()
        End Sub

        <RowBuilder("Categories", RowKind.Existing)> _
        Protected Sub VerifyPresenceOfExternalFile()
            Dim categoryId As Integer = Convert.ToInt32(SelectFieldValue("CategoryID"))
            Dim externalDocFileName As String = Convert.ToString( _
                SelectFieldValue("ExternalDocFileName"))
            ' update ExternalDoc field to reflect existence of a file
            If Not String.IsNullOrEmpty(externalDocFileName) Then
                UpdateFieldValue("ExternalDoc", categoryId)
            Else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryId))
            End If

        End Sub
    End Class
End Namespace

Upload a few files to the ExternalDoc column. You should be able to see the file thumbnail if the upload was successful or the file type displayed in a white box if the file is not an image.

Here is how the content of the folder that we use to store the uploaded content may look like.

Your externalized content will also print on reports. For example, select Categories tab and choose Report|PDF Document option on the action bar.

A prompt to download a PDF document will be displayed. Open the file and you should be able to see the uploaded content on it.

Conclusion

Code On Time offers an excellent mechanism of implementing storage of a binary content outside of the database.

We expect that future updates to the framework will allow file uploading not only when users are editing existing records but also when a new record is being created.

Upcoming releases will also support code-free uploading of binary content through built-in business rules to external file system and Microsoft Azure Storage.

Project Azure Factory available with Code On Time will support popular annotation feature as well.

Labels: Application Factory, ASP.NET Code Generator, ASP.NET Membership, BLOB, Data Aquarium Framework, Tips and Tricks, Web App Generator

Tuesday, July 26, 2011Print Subscribe

Access Control Rules

Web app generator Code On Time introduces Access Control Rules, the first component of EASE (Enterprise Application Services Engine). EASE components simplify implementation of enterprise-class features in line-of-business web applications created with Code On Time and require little or no programming at all.

Introduction

Why do we need access control rules?

Many line-of-business applications start with a simple spreadsheet that allows performing data analysis or calculations to satisfy a specific business requirement. The spreadsheet turns in an application as soon as business users realize that the spreadsheet must accept data input from multiple users. A database is set up and application user interface is built on top of it.

In a few instances the entire database contents may be available to all authorized application end users. In most situations there is a need to create a set of restrictions that would separate slices of data that are available to individual users or users in a given business role.

For example, a web application administrator may be authorized to see the entire list of customers. A sales person will likely be allowed to see only the customers that he or she has a relationship with.

Another example is a line-of-business web application integrated with a content management system such as DotNetNuke or Microsoft SharePoint. The application data must be isolated by a portal or site name. Data created by all CMS users is stored in the same database tables but under no circumstances shall the users see each other’s records.

Preparing Database for Access Control

A common approach to facilitate access control implementation is to add columns reflecting ownership of data. For example, you can implement User ID column in each table of your database.

If more than one user has a relationship with a data record then developers opt to implement a dedicated table linking a a data record with multiple users.

A typical relational database structure provides certain natural means of establishing access control based on database table relationships. It may be sufficient to implement just one ownership column. For example, consider the following snippet from the Northwind Sample.

Table Orders has a reference to an Employee. If a web application user is in the role of Sales then one can make the following assumptions:

an employee shall see only his or her orders
an employee shall see only customers that have matching orders placed by the employee
a subset of order details are naturally visible to an employee when an order is selected
if an employee is viewing the global list of order details then only orders of her customers shall be accessible.

The described access control rules can be implemented if we associate each Employees table record with a User ID.

Code On Time allows integrating Microsoft ASP.NET Membership in the generated applications. User accounts and roles are stored in dedicated tables and user and role management APIs are already built in ASP.NET. Let’s incorporate ASP.NET Membership plumbing in the application database.

Change Employees table to implement two additional columns UserID and UserName. Notice that if you are developing with a database other than Microsoft SQL Server then you may need to choose a different types for UserID and UserName columns. For example, MySQL implementation of ASP.NET Membership uses int as the type of the membership user ID.

Start Code On Time and begin creating a new Web Site Factory project . On the Database Connection page of project wizard press on a button next to connection string input field.

Set the database connection string and press the Add button to create membership tables and stored procedures in the application database.

Continue application configuration until you reach the Data Controllers summary page in the project wizard.

Creating a Business Rules Class

Access Control Rules in Code On Time web applications are implemented as methods in business rules classes. These methods can be shared by all data controllers of your application or designed to address the needs of a specific data controller.

We will consider both situations and will show the real-live examples of custom and shared access control rules.

We will start by creating a business rules class associated with Customers data controller.

Click on Start Designer button and select Customers data controller on All Controllers tab of Project Designer.

Enter CustomersBusinessRules in Handler field under Business Rules section and click OK button to persist changes.

Click Exit button at the top of the screen and click Next to generate the project.

The implementation of business rules will require source code text editing and can be done in Notepad or a development tool such as Visual Studio or Visual Web Developer.

If you do not have a development tool on your computer then click “open” under Actions column of the Code On Time start page.

Select App_Code\Rules\CustomersBusinessRules.cs(vb) file in Windows Explorer and open the file in Notepad.

If you do have a development tool listed above then simply click “develop” link to activate the development tool and open the same file in Solution Explorer.

The extension of the file depends on your programming language. Double click the file name to open the file in the editor.

Next we will show examples in both C# and Visual Basic demonstrating various way of implementing access control rules.

Challenges in Implementing Access Control Rules

Modern database software make it exceptionally easy to select data. The application developer writes a data selection statement in declarative language SQL. Such statements typically list the source tables, table columns, and table join instructions.

Data selection statements are frequently enhanced with filters to present the data that users would like to see.

There is this pesky requirement to segment the data based on user identity or business role. A developer will have to incorporate access control filters in every single data selection statement and foresee all sorts of business requirements that may call for exceptions to some of the filters.

Because of that, developers end up writing their data selection statement as stored procedures persisted in the database. These stored procedures are fundamentally basic selection statements enhanced with numerous checks and conditions to ensure proper access control.

Code On Time applications create SQL selection statements on-the-fly and incorporate user-defined adaptive filters, search criteria, and sort order. Selection statements are enhanced with parameters to prevent any possibility of an injection attack that plagues many applications with hand-written SQL.

It is impossible to write a stored procedure that accepts an unknown number of filtering and sorting parameters to match on-the-fly SQL statements created by your Code On Time web application. Access Control Rules are designed specifically to address the need to apply access control restrictions to dynamic SQL statements.

Next we will show you several examples of Access Control Rules and will implement filtering based on user identity and business role.

Restricting Access by a Single Value

Consider the list of customers presented in the following screenshot. We can see a list of customers from many countries.

Let’s limit this list of customers to USA only and have this rule apply to all application users.

Enter the following method in the CustomersBusinessRules.cs(vb) and save the file.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class CustomersBusinessRules : MyCompany.Data.BusinessRules
    {
        [AccessControl("Customers", "Country")]
        public void CountryFilterThatAppliesToEverybody()
        {
            RestrictAccess("USA");
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class CustomersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <AccessControl("Customers", "Country")> _
        Public Sub CountryFilterThatAppliesToEverybody()
            RestrictAccess("USA")
        End Sub
    End Class
End Namespace

The name of the method plays no role. Use you imagination to ensure that it will be easy to understand the purpose of the method in the future. The method is decorated with AccessControl attribute .

The first parameter of the attribute constructor indicates the data controller that will use this access control method. The second parameter indicates the field that will be filtered by the access control method.

If the data controller is requested to retrieve data then it will scan the list of methods of the associated business rules class and find those that are decorated with the matching AccessControl attribute. By default the data controller will assume that the method is designed to allow access to data. The data controller will invoke the method and if any calls to RestrictAccess are executed then every restriction will be incorporated in the access control filter of SQL data selection statement. In this instance the method will ensure that all users are restricted to see customers from USA regardless of their identity or business role.

Notice that the list of options available in the adaptive filter is considerably shorter. Any user-defined filters will be applied on top of the filters produced by Access Control Rules. Access control filter in the example is activated in all instances of requests to retrieve a list of customers.

Access control methods can utilize any sort of logic to decide if a restriction is applicable at any given moment. For example, a filter can prevent access to the list of customers during non-working hours. The following access control method will allow access to the list of customer from 9 AM though 5:30 PM. If the data is being accessed outside of this time period then a restriction by a non-exiting country is activated.

C#:

[AccessControl("Customers", "Country")]
private void CountryFilterThatAppliesToEverybody()
{
    DateTime today = DateTime.Now;
    DateTime workDayBegins = new DateTime(today.Year, today.Month, today.Day, 9, 00, 00);
    DateTime workDayEnds = new DateTime(today.Year, today.Month, today.Day, 17, 30, 00);
    if (workDayBegins <= today && today <= workDayEnds)
        RestrictAccess("USA");
    else
        RestrictAccess("*****");

}

Visual Basic:

<AccessControl("Customers", "Country")> _
Private Sub CountryFilterThatAppliesToEverybody()
    Dim today As DateTime = DateTime.Now
    Dim workdayBegins As DateTime = New DateTime(today.Year, today.Month, today.Day, 9, 0, 0)
    Dim workdayEnds As DateTime = New DateTime(today.Year, today.Month, today.Day, 17, 30, 0)
    If (workdayBegins <= today And today <= workdayEnds) Then
        RestrictAccess("USA")
    Else
        RestrictAccess("*****")
    End If
End Sub

Multiple AccessControl attributes can be applied to the same method.

If several methods with AccessControl attribute are discovered then the data controller will incorporate SQL data selection statement restrictions by concentrating them with “and” logic.

Restricting Access by Multiple Values

If more than one value must be used to filter out the data then simply call RestrictAccess method multiple times and pass the values that are compatible with the data type of the field specified in AccessControl attribute.

The following method will limit the list of customers to three specific IDs when presenting the list to users with roles other than Administrators.

C#:

[AccessControl("CustomerID", AccessPermission.Allow)]
protected void NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
{
    if (UserIsInRole("Administrators"))
        UnrestrictedAccess();
    else
    {
        RestrictAccess("GREAL");
        RestrictAccess("OLDWO");
        RestrictAccess("THEBI");
    }
}

Visual Basic:

<AccessControl("CustomerID", AccessPermission.Allow)> _
Protected Sub NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
    If UserIsInRole("Administrators") Then
        UnrestrictedAccess()
    Else
        RestrictAccess("GREAL")
        RestrictAccess("OLDWO")
        RestrictAccess("THEBI")
    End If
End Sub

This example is using UnrestrictedAccess method to indicate that the restriction does not apply to Administrators. Access control methods will only result in data filtering if at least one call to RestrictAccess has been made. You can use UnrestrictedAccess method to negate the result of the access restriction previously applied within the same method execution path.

Here is the slightly shorter version of the method restricting access to specific customer accounts for non-administrative users. If a user does not belong to the role Administrators, then no restrictions will be imposed on the the list of customers.

C#:

[AccessControl("CustomerID", AccessPermission.Allow)]
protected void NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
{
    if (!UserIsInRole("Administrators"))
    {
        RestrictAccess("GREAL");
        RestrictAccess("OLDWO");
        RestrictAccess("THEBI");
    }
}

Visual Basic:

<AccessControl("CustomerID", AccessPermission.Allow)> _
Protected Sub NonAdministrativeUsersAreAuthorizedToSeeOnlyThreeCustomers()
    If Not UserIsInRole("Administrators") Then
        RestrictAccess("GREAL")
        RestrictAccess("OLDWO")
        RestrictAccess("THEBI")
    End If
End Sub

The screenshot shows the list of customers presented to a user with role Users.

Denying Access

The previous examples of access control rules are specifying AccessPermission.Allow value passed as a parameter of AccessControl attribute. This is default permission of access control rules and can be omitted.

There is always a possibility that a data access exception must be implemented. For example, you may need to prevent administrators from being able to access The Big Cheese customer from USA. The following method does just that.

C#:

[AccessControl("CustomerID", AccessPermission.Deny)]
public void ExceptionForAdministrators()
{
    if (!UserIsInRole("Administrators"))
        UnrestrictedAccess();
    else
        RestrictAccess("THEBI");
}

Visual Basic:

<AccessControl("CustomerID", AccessPermission.Deny)> _
Public Sub ExceptionForAdministrators()
     If (Not UserIsInRole("Administrators")) Then
        UnrestrictedAccess()
    Else
        RestrictAccess("THEBI")
    End If
End Sub

If both “allow” and “deny” access control rules are imposing restrictions at runtime, then the data controller will compose an access control filter such as the one below.

(List of “Allow” restrictions) and Not (List of “Deny” restrictions)

Restricting Access via Parameterized SQL

Our examples are using static values to compose restrictions. In real world situations you may need to examine a user identity and figure the appropriate restriction value on the fly.

It may be impractical to invoke RestrictAccess method for each restriction due to a very large number of such restrictions.

You can solve both problems by incorporating SQL statements in the definition of AccessControl attribute.

For example, you may want to consider restricting non-administrative accounts to see only those customers that they have a relationship with. The following method makes an assumption that the user with User Name = ‘user’ is a sales person and shall see only customers matching Employee ID = 1.

The total number of records in the Customers table of Northwind database is 91. If you implement the method presented next then the user with the name “user” will only see 65 records. Make sure to comment out or delete any previously defined access control methods described above to prevent the cumulative effect of access control restrictions.

C#:

[AccessControl("CustomerID", Sql = 
    "select distinct CustomerID from Orders where EmployeeID = @EmployeeID")]
public void LimitUserToSeeOnlyHerCustomers()
{
    if (!UserIsInRole("Administrators"))
        if (Context.User.Identity.Name == "user")
            RestrictAccess("@EmployeeID", 1);
        else
            UnrestrictedAccess();
}

Visual Basic:

<AccessControl("Customers", "CustomerID", _
    "select distinct CustomerID from Orders where EmployeeID = @EmployeeID", _
    AccessPermission.Allow)> _
Public Sub LimitUserToSeeOnlyHerCustomers()
    If (Not UserIsInRole("Administrators")) Then
        If Context.User.Identity.Name = "user" Then
            RestrictAccess("@EmployeeID", 1)
        Else
            UnrestrictedAccess()
        End If
    End If
End Sub

The data controller will inject SQL statement defined in AccessControl attribute in the data selection statement.

The value of parameter @EmployeeID is assigned by the code in the access control method.

If you call method RestrictAccess with two arguments then the first argument is the name of the parameter in Sql statement of the attribute and the second argument is its value.

The data controller will compose a restriction that looks as follows.

“Customers.CustomerID” in (select distinct CustomerID from Orders where EmployeeID = @EmployeeID)

The expression in AccessControl attribute is inserted as-is. It is up to you to ensure that the expression is valid and will return a correct list of values.

Implementing Restrictions for Northwind Sample

Let’s implement access control rules in the Northwind sample that rely on columns User ID and User Name, implemented previously in this article.

We will make a few changes to the web application design.

Run Code On Time web application generator and select your project.

Click the Next button twice to reach Business Logic Layer page in the project wizard. Select “Generate a shared business rules class …” check box and continue pressing Next until you reach the summary of Data Controllers in your project.

Click Start Designer button to activate the project designer.

Select Employees data controller and activate Fields tab.

Select UserID field, activate Field tab, and change its Items Style to User ID Lookup under Lookup section.

Enter UserName=UserName in the Copy field right under the Items Style. This instruction will ensure that the value of UserName field from Membership Manager will be copied into Employees.UserName field.

Scroll down and modify the Security section if you want to ensure that only Administrators are able to assign/create users while editing the Employees record. The following configuration will put no restrictions on who can see the user name and will enable business users with role Administrators to change the user account associated with the employee.

Click OK and select this field one more time. Activate Data Fields tab.

Change both instances of the UserID data field to reference UserName field as an alias of UserID. Select each binding (data field) and edit Alias property under General section.

Bind UserID field to grid1 view to see the User Name associated with each record in Employees table when viewing the list of employees. Click New|New Data Field and proceed to create a data field (binding) linking field UserID to view grid1. Make sure to leave the field Category blank and choose UserName as the field’s Alias. Press OK.

Your list of data fields associated with field UserID will look as follows.

Select Controller:Employees link in the path of the Designer and select UserName field on the Fields tab.

Delete both bindings of UserName field to views editForm1 and createForm1. These bindings are shown prior to deletion in the next screenshot.

Exit the Project Designer and generate the project.

Sign-in as admin/admin123% and associate user account user with the employee with the last name of Davolio.

Open the CustomersBusinessRules.cs(vb) file in the text editor and delete any previously defined access control rules. Also, replace the base class with MyCompany.Rules.SharedBusinessRules. This will ensure that any global shared access control rules will be inherited in custom business rules associated with Customers data controller.

The new version of CustomersBusinessRules is presented next.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class CustomersBusinessRules :
       MyCompany.Rules.SharedBusinessRules
    {
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules

    Partial Public Class CustomersBusinessRules
        Inherits MyCompany.Rules.SharedBusinessRules

    End Class

End Namespace

Refresh the project tree in Solution Explorer if you are using Visual Studio or Visual Web Developer.

Open file ~/App_Code/Rules/SharedBusinessRules.cs(vb) in the editor.

This file implements MyCompany.Rules.SharedBusinessRules class. The data controller implementation of your web application will create an instance of this class when preparing to process requests from any data controllers of your project if a dedicated data controller is not available.

We will implement a collection of access control rules to perform consistent data selection restrictions based on user identity.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class SharedBusinessRules : MyCompany.Data.BusinessRules
    {

        public object UserID
        {
            get
            {
                return System.Web.Security.Membership.GetUser().ProviderUserKey;
            }
        }

        [AccessControl("EmployeeID", Sql =
            "select EmployeeID from Employees where UserID = @UserId")]
        [AccessControl("CustomerID", Sql =
            @"select distinct CustomerID from Orders 
            inner join Employees 
            on Orders.EmployeeID = Employees.EmployeeID 
            where Employees.UserID = @UserID")]
        public void RestrictByEmployeeIdAndCustomerId()
        {
            if (!UserIsInRole("Administrators"))
                RestrictAccess("@UserID", UserID);
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules
    
    Partial Public Class SharedBusinessRules
        Inherits MyCompany.Data.BusinessRules

        Public ReadOnly Property UserID As Object
            Get
                Return System.Web.Security.Membership.GetUser().ProviderUserKey
            End Get
        End Property

        <AccessControl(Nothing, "EmployeeID", _
            "select EmployeeID from Employees where UserID = @UserID")> _
        <AccessControl(Nothing, "CustomerID", _
            "select distinct CustomerID from Orders " & _
            "inner join Employees " & _
            "on Orders.EmployeeID = Employees.EmployeeID " & _
            "where Employees.UserID = @UserID")> _
        Public Sub RestrictByEmployeeIdAndCustomerId()
            If Not UserIsInRole("Administrators") Then
                RestrictAccess("@UserID", UserID)
            End If
        End Sub

    End Class
End Namespace

Paste the source code defined above in the ~/App_Code/Rules/SharedBusinessRules.cs(vb), save it and try it in action by signing in as user/user123%.

You will notice that this simple restriction affects uniformly Customers, Order Details, Employees, Employee Territories, and Customer Demographics.

Any data view that has either CustomerID or EmployeeID field will be automatically restricted if the current is not a member of Administrators role.

You can widen the reach of shared restrictions to Order Details if you simply add EmployeeID field to this data controller and mark it as Hidden.

The command text of Order Details data controller is defined as follows. The command is a simple SQL selection statement joining relevant tables with main table dbo.[Order Details].

select
    "OrderDetails"."OrderID" "OrderID"
    ,"Order"."CustomerID" "OrderCustomerID"
    ,"OrderCustomer"."CompanyName" "OrderCustomerCompanyName"
    ,"OrderEmployee"."LastName" "OrderEmployeeLastName"
    ,"OrderShipVia"."CompanyName" "OrderShipViaCompanyName"
    ,"OrderDetails"."ProductID" "ProductID"
    ,"Product"."ProductName" "ProductProductName"
    ,"ProductCategory"."CategoryName" "ProductCategoryCategoryName"
    ,"ProductSupplier"."CompanyName" "ProductSupplierCompanyName"
    ,"OrderDetails"."UnitPrice" "UnitPrice"
    ,"OrderDetails"."Quantity" "Quantity"
    ,"OrderDetails"."Discount" "Discount"
from "dbo"."Order Details" "OrderDetails"
    left join "dbo"."Orders" "Order" on "OrderDetails"."OrderID" = "Order"."OrderID"
    left join "dbo"."Customers" "OrderCustomer" on "Order"."CustomerID" = "OrderCustomer"."CustomerID"
    left join "dbo"."Employees" "OrderEmployee" on "Order"."EmployeeID" = "OrderEmployee"."EmployeeID"
    left join "dbo"."Shippers" "OrderShipVia" on "Order"."ShipVia" = "OrderShipVia"."ShipperID"
    left join "dbo"."Products" "Product" on "OrderDetails"."ProductID" = "Product"."ProductID"
    left join "dbo"."Categories" "ProductCategory" on "Product"."CategoryID" = "ProductCategory"."CategoryID"
    left join "dbo"."Suppliers" "ProductSupplier" on "Product"."SupplierID" = "ProductSupplier"."SupplierID"

We will create a field that uses Order alias of table dbo.Orders to reference Orders.EmployeeID column in SQL Formula.

Start the Project Designer and select Order Details data controller. Activate Fields tab and and choose New|New Field option on the action bar. Enter the following properties for the new field under New Field section and save its settings by pressing OK button.

Notice that we have wrapped the word Order with double quotes to make sure that it will not be misinterpreted by the database server as SQL keyword “order” .

The field will be automatically available in all data views of controller Order Details but remain hidden from end users. The presence of the field will allow it to participate in the access control rules.

Generate the application and observe that shared business rules now extend to Order Details as well. Non-administrative users will only see the order details of orders that they have placed in the system.

Availability

The new mechanism of access control rules greatly simplifies creation of a consistent data segmentation in multi-tenant applications that require isolation of database content created by users.

Access Control Rules are available in the Premium and Unlimited editions of Code On Time web application generator. Owners of Free and Standard editions can use Filter Expressions as discussed later.

What’s Next?

Unlimited edition of Code On Time generator will soon be offering Dynamic Access Control List, another powerful component of EASE (Enterprise Application Services Engine).

Dynamic Access Control List is designed to complement Access Control Rules and allow luxury of defining precise access control at runtime. The real world business processes make it difficult to foresee all possible access control restrictions, and most importantly exceptions to the rules at design time.

Dynamic Access Control List will maintain access control rules in the application database. Administrative user interface will enable dynamic creation of access control rules to respond to the business requirements at runtime without making any changes to the application code.

Filter Expressions

Developers working with Free and Standard editions can use Filter Expressions to implement access control rules in their applications.

Filter expressions are defined on a level of a view and can reference properties of business rules classes as parameters.

For example, if you make changes to dbo.Employees table as described at the top of the article and add UserID and UserName columns then you can filter Orders by EmployeeID. You will have to enable shared business rules and define filter expressions on grid1 and editForm1, followed by implementation of EmployeeID property in the SharedBusinessRules class.

Select your project on the start page of Code On Time web application generator, click Design button, select Orders data controller on All Controllers tab, and activate Views tab.

Change both grid1 and editForm1 views to have the following Filter Expression under Sort and Filter section of the property page.

Generate your project and change ~/App_Code/Rules/SharedBusinessRules.cs(vb) file to have the following code.

C#:

using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class SharedBusinessRules : MyCompany.Data.BusinessRules
    {

        public object UserID
        {
            get
            {
                return System.Web.Security.Membership.GetUser().ProviderUserKey;
            }
        }

        public int EmployeeID
        {
            get
            {
                using (SqlText findEmployee = new SqlText(
                    "select EmployeeID from Employees where UserID = @UserId"))
                {
                    findEmployee.AddParameter("@UserId", UserID);
                    object id = findEmployee.ExecuteScalar();
                    if (DBNull.Value.Equals(id))
                        return -1;
                    else
                        return Convert.ToInt32(id);
                }
            }
        }

    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace MyCompany.Rules
    
    Partial Public Class SharedBusinessRules
        Inherits MyCompany.Data.BusinessRules

        Public ReadOnly Property UserID As Object
            Get
                Return System.Web.Security.Membership.GetUser().ProviderUserKey
            End Get
        End Property

        Public ReadOnly Property EmployeeID As Integer
            Get
                Using findEmployee As SqlText = New SqlText( _
                    "select EmployeeID from Employees where UserID = @UserId")
                    findEmployee.AddParameter("@UserId", UserID)
                    Dim id As Object = findEmployee.ExecuteScalar()
                    If DBNull.Value.Equals(id) Then
                        Return -1
                    Else
                        Return Convert.ToInt32(id)
                    End If
                End Using
            End Get
        End Property

    End Class
End Namespace

The value of property EmployeeID will be automatically evaluated and passed as a parameter in SQL statements created to render data for presentation in grid1 and editForm1 views of Orders data controller. Here is how a user associated with employee Davolio may see a list of Orders.

Class SharedBusinessRules is created to handle requests for all data controllers. This allows referencing EmployeeID as a parameter in other data controllers including EmployeeTerritories, Employees, and Order Details.

Filter expressions lack the flexibility of conditional restrictions available with Access Control Rules and Dynamic Access Control List. You will need to compose filter expressions that use more than one parameter to accomplish an effect of conditional filtering.

If you need to return more than one value for filtering purposes, then change the type of property to be a list of values or an array. Also refer to the property values as shown in the example below.

EmployeeID in @EmployeeID

Filter expressions can be used with Access Control Rules when needed if a presentation of data in a specific view requires additional filtering.

Labels: Application Factory, ASP.NET Membership, Business Rules/Logic, EASE, Security, Tips and Tricks, Web App Generator

Tuesday, June 28, 2011Print Subscribe

Highlighting “Past Due” and “Within” Data Rows

Code On Time generates applications that can be styled with CSS rules to adjust logo, background, and provide conditional formatting of the data views.

The latest enhancements to the client library allow creating complex rules that depend on current date and time.

The screenshot below shows a list of orders from the Nortwhind database sample presented in a Data Sheet view. Conditional styling rules are applied to the view.

The second row from the top has a pink “FiveDays” background since the required date for this order is 7/2/2011 and the date in the screen shot is 6/28/2011, which places the order within five days of the required date.

There are four rows with red background. The “PastDue” background is applied to these rows since the first row has been shipped after the required date and the other three were not shipped.

Here is how you can set this up in your own application.

Start the code generator, select your project, click Design button, activate All Controllers tab and locate your controller.

Select the controller and activate Views tab.

Select view grid1 and activate Styles tab.

Enter two styling rules shown in the picture

The test formulas shown below are referring to the Orders table from the Northwind sample.

Css Class	Test
FiveDays	[ShippedDate] == null && Date.$within([RequiredDate], 5)
Past Due	Date.$pastDue([ShippedDate], [RequiredDate])

Tests are written in JavaScript. Data controller field names are specified in square brackets. The client library will substitute references to the fields names with calls to a function that will return the field values. The expressions will be evaluated for each data row to see if a custom CSS class must be assigned to the table row.

Client Library extensions to the Date class are being used to test the dates. The extensions are Date.$within and $Date.pastDue.

Exist the designer and generate your application. Open your project in Visual Studio via File | Open Website option if your app is a Web Site Factory project or File | Open Project if your app is a Web App Factory project.

Add new CSS style sheet in your project under the ~/App_Themes folder.

Double click your new style sheet and enter the following CSS rule.

.FiveDays td.Cell
{
    background-color: #FFCCCC!important;
    color: Black !important;
}

.PastDue td.Cell
{
    background-color: #CC3300!important;
    color: Black !important;
}

It is very important to use “!important” after the CSS property values to override the default style sheets of your application.

Run your application in a web browser and make sure to hit the refresh button to ensure that the styles are taking effect.

Labels: AJAX, Application Factory, ASP.NET, Tips and Tricks, User Interface, Web 2.0, Web App Generator