Blog: Posts from February, 2012

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Posts from February, 2012
Wednesday, February 29, 2012PrintSubscribe
Access Control Rules & NULL Value

Database servers use a special value to indicate that a column in a table row has no value. This special value is called NULL.

It may require a little consideration when configuring access control rules for your application if you need to filter out or filter in the empty values in a particular column.

The screen shot shows a list of customers with the “empty” Region column.

Only "empty" regions are accessible.

The following business rule makes it happen. The two calls of RestrictAccess method are demonstrating the passing of a NULL value to the application framework for filtering. You can simplify the method to use one call of RestrictAccess with either null or DBNull.Value passed as a parameter.

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", "Region")]
        public void NullValueInRegion()
        {
            RestrictAccess(null);
            RestrictAccess(DBNull.Value);
        }
    }
}

Visual Basic:

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

Namespace MyCompany.Rules

    Partial Public Class CustomersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <AccessControl("Customers", "Region")>
        Public Sub NullValueInRegion()
            RestrictAccess(DBNull.Value)
            RestrictAccess(Nothing)
        End Sub

     End Class
End Namespace

Next screen shot shows the effect of the permission property of the AccessControl attribute, which “hides” the rows with the empty Region from the end user.

Access to "empty" regions is denied.

This method definition will deny access to the customers with empty Region.

C#:

[AccessControl("Customers", "Region", AccessPermission.Deny)]
public void NullValueInRegion()
{
    RestrictAccess(null);
}

Visual Basic:

<AccessControl("Customers", "Region", AccessPermission.Deny)>
Public Sub NullValueInRegion()
    RestrictAccess(Nothing)
End Sub

You can also use an SQL expression referencing the name of the Region field with “is null” or “is not null” operand. You must call method RestrictAccess without parameters to activate the access control filter.

C#:

[AccessControl("Customers", "Region", "[Region] is null")]
public void NullValueInRegion()
{
    RestrictAccess();
}

Visual Basic:

<AccessControl("Customers", "Region", "[Region] is null")>
Public Sub NullValueInRegion()
    RestrictAccess()
End Sub
Wednesday, February 29, 2012PrintSubscribe
Automatic Data Filtering

Web applications created with Code On Time offer impressive built-in filtering capabilities available to the end user. Just a few mouse clicks and a list of products is custom tailored to match complex filtering requirements.

Complex filter defined by end user.

If the audience of your web application is composed of independent groups of users then the data must be segregated automatically. Each group of users will see only the data that meets certain criteria.

Consider the Orders table from the Northwind sample.

Northwind.dbo.Orders

Customers signing in the Northwind web application should only see their own orders.

A simple SELECT statement will filter the list of order by @CustomerID parameter.

select * from Orders where CustomerID = @CustomerID  

The snippet below shows the definition of the Orders data controller command from the Northwind sample.

  <commands>
    <command id="command1" type="Text">
      <text><![CDATA[
select
    "Orders"."OrderID" "OrderID"
    ,"Orders"."CustomerID" "CustomerID"
    ,"Customer"."CompanyName" "CustomerCompanyName"
    ,"Orders"."EmployeeID" "EmployeeID"
    ,"Employee"."LastName" "EmployeeLastName"
    ,"Orders"."OrderDate" "OrderDate"
    ,"Orders"."RequiredDate" "RequiredDate"
    ,"Orders"."ShippedDate" "ShippedDate"
    ,"Orders"."ShipVia" "ShipVia"
    ,"ShipVia"."CompanyName" "ShipViaCompanyName"
    ,"Orders"."Freight" "Freight"
    ,"Orders"."ShipName" "ShipName"
    ,"Orders"."ShipAddress" "ShipAddress"
    ,"Orders"."ShipCity" "ShipCity"
    ,"Orders"."ShipRegion" "ShipRegion"
    ,"Orders"."ShipPostalCode" "ShipPostalCode"
    ,"Orders"."ShipCountry" "ShipCountry"
from "dbo"."Orders" "Orders"
    left join "dbo"."Customers" "Customer" on "Orders"."CustomerID" = "Customer"."CustomerID"
    left join "dbo"."Employees" "Employee" on "Orders"."EmployeeID" = "Employee"."EmployeeID"
    left join "dbo"."Shippers" "ShipVia" on "Orders"."ShipVia" = "ShipVia"."ShipperID"
]]></text>
    </command>

If you paste this query in SQL Server Management Studio and click execute button then the list of orders will come up in the output window.

Your first natural instinct is to stick the “WHERE Orders.CustomerID = @CustomerID” right in the command text. All you need to know is how to provide the parameter value. Isn’t it that sample? The answer is “yes” and “no”.

Code On Time web applications do not use the command text “as-is”.  The application framework uses the command text as a developer-friendly dictionary. The text of the command allows your application locating the “from..” clause, the name of  the “main” table, the SQL expressions hidden behind the field aliases. The application framework uses the command text snippets to put together SELECT, UPDATE, INSERT, and DELET statements at runtime. Notice that SELECT statements are also automatically enhanced with a complex “WHERE …” clause to incorporate the user-defined filters when needed.

Filtering shall be accomplished with the help of the business rules. Your custom filters will be injected by the application framework in the right spot of dynamically created SQL statements and will co-exist with the user-defined criteria.

Let’s consider what it takes to create a programmatic filter.

Click on the name of the Northwind sample project on the start page of the web application generator and select Design project action.

Activate Controllers tab in the Project Explorer and double-click the name of your data controller.

Enter OrdersBusinessRules in the Handler property of the data controller Orders and click OK button.

"Handler" property of data controller

Click Exit button on the tool bar and proceed to generate the application.

Select the project name on the start page of the generator one more time and  choose Develop project action. Visual Studio of Visual Web Developer will start up.

Press Ctrl+Shift+F when the project loads in the development environment and search for OrdersBuinessRules. Double-click OrdersBusinessRules.cs(vb) in the Find Results window to open the business rules file.

This file is created if it does not exist already. Subsequent code generation will not overwrite your changes.

Enter the method ShowOrdersPlacedByCustomer in the definition of the class.

C#:

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

namespace MyCompany.Rules
{
    public partial class OrdersBusinessRules : MyCompany.Data.BusinessRules
    {
        [AccessControl("Orders", "CustomerID", "[CustomerID] = @CustomerID")]
        public void FilterOrdersPlacedByCustomer()
        {
            if (!UserIsInRole("Administrators"))
                RestrictAccess("@CustomerID", "AROUT");
        }
    }
}

Visual Basic:

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

Namespace MyCompany.Rules

    Partial Public Class OrdersBusinessRules
        Inherits MyCompany.Data.BusinessRules

        <AccessControl("Orders", "CustomerID", "[CustomerID] = @CustomerID")>
        Public Sub FilterOrdersPlacedByCustomer()
            If (Not UserIsInRole("Administrators")) Then
                RestrictAccess("@CustomerID", "AROUT")
            End If
        End Sub
    End Class
End Namespace

If you sign in with user account user/user123% then you should see the following list of orders.

Orders filtered by Customer ID

Administrative user account admin/admin123% will have an unrestricted view of orders stored in the database.

The definition of access control attribute placed above the method name activates the method at runtime. The parameters of the access control attribute determine how the access control is performed.

AccessControl("Orders", "CustomerID", "[CustomerID] = @CustomerID")

The first parameter indicates the name of the data controller. The application framework treats this parameter as a regular expression that is matched against the name of the data controller. You can enter  a blank string as an alternative.  The blank string or “partial match” regular expression works well in shared business rules when the same access control rule is applied to multiple data controllers.

The second parameter is the name of the field that must exist in the data view for the access control rule to be triggered. In our example any of the fields defined in view grid1 can be used as the second parameter of AccessControl attribute.

View of the data controller Orders in Project Explorer

The third parameter defines the SQL snippet that will be embedded in the SELECT statement produced by application framework at runtime. The application framework replaces any references to the data fields placed in square brackets with the actual expressions defined in data controller command.

For example, the alternative definition of the filtering expression will work exactly the same way if you enter the AccessControl attribute as follows.

AccessControl("Orders", "CustomerID", "Orders.CustomerID = @CustomerID")

The SQL snippet may have any complexity. You can explicitly reference any fields inferred from the “FROM…” clause of the data controller command text. You can reference any data fields defined in the view using square brackets around their names. You can reference any functions supported by your database server. SQL snippet is physically embedded in the SELECT statement composed by the app.

For example, the definition of the access control rule below will limit customer orders to those shipped by United Package.

AccessControl("Orders", "CustomerID", 
            "[CustomerID] = @CustomerID and [ShipViaCompanyName]='United Package'")

The screen shot shows the end result.

Orders filtered  by CustomerID and Ship Via Company name

The definition of the access control rule below uses SQL expression that starts with the word “SELECT…”.

AccessControl("Orders", "CustomerID", 
            "select CustomerID from Customers where CustomerID = @CustomerID")

If the SQL parameter of the attribute starts with “SELECT” then the application framework will assume that a single column is returned in the output data set and the values of the field CustomerID must be contains in the data set.

The end result composed by the application framework will be equivalent to the following filtering expression.

select * from Orders 
where Orders.CustomerID in (
    select CustomerID from Orders where @CustomerID
)

In this particular example there is little value in using this syntax. Utilize this form of access control rules when you need to match customer ID with more than one value.

If only one field has to be filtered by a programmatic value then you can omit the SQL property in the access control rule definition.

C#:

[AccessControl("Orders", "CustomerID")]
public void FilterOrdersPlacedByCustomer2()
{
    if (!UserIsInRole("Administrators"))
        RestrictAccess("AROUT");
}

Visual Basic:

<AccessControl("Orders", "CustomerID")>
Public Sub FilterOrdersPlacedByCustomer2()
    If (Not UserIsInRole("Administrators")) Then
        RestrictAccess("AROUT")
    End If
End Sub

Notice that you can call the method RestrictAccess multiple times to filter the customer ID by more than one value.

You can define multiple methods with arbitrary names adorned with one or several AccessControl attributes. The method is called by the application framework if the attributes were matched to the runtime conditions. Actual filtering will only take place if you invoke the RestrictAccess method at least once. This allows for programmatically controlled filtering under any imaginable conditions.

Programmatic filters cannot be removed the end users. The single access control rule will impact all pages of your application that reference the same data controller. For example, the following screen shot shows the Orders lookup with the user-defined filter impacted by our access control rule.

image

Tuesday, February 28, 2012PrintSubscribe
What is Code On Time and Why You Need It?

Code On Time web application generator creates modern web apps straight from your database with lightning speed. In a matter of minutes, your data can be available on the web with sophisticated search, manipulation, and reporting capabilities that would otherwise take many months to build.

Your web applications can be deployed to a dedicated or shared server. You can choose to deploy your app to Windows Azure Cloud or package the app for deployment to popular web content management systems SharePoint or DotNetNuke.

The value of organized data is proven every day by companies such as Google and Facebook. These giants of software industry have managed to turn mundane information into goldmines.

Many businesses own vast datasets of valuable information trapped in spreadsheets or on-premise databases. Exposing this data to potential consumers means web application development, which always comes with tremendous costs. Application deployment also involves hard-to-predict expenses.

Code On Time makes it possible to build an app on top of SQL Azure database and have it deployed to Microsoft’s state-of-the-art computing cloud Windows Azure. In about fifteen minutes, you can have all your data displayed in an advanced web application running in a multi-server configuration protected by firewall, enhanced with load balancing, and on-demand performance. Scale your application up and down, turn it on and off. None of it requires maintenance personnel or special knowledge. The pay-as-you-go model allows precise cost control.

If you are a resident expert in configuring network servers and databases, or have such an expert on board, then Code On Time will accelerate web application development for your team.

All generated web applications include an advanced application framework with complete source code. The framework makes it possible to have light-weight web applications with high-end user interface with a little or no effort. The framework also allows elegant extension of customized apps with business rules that can take full advantage of vast Microsoft.NET API and custom components.

Code On Time is perfect for database administrators who need to control data under their management. Instant production of web applications on top of your databases without modifying the database structure makes our product a perfect companion for daily data management tasks.

Code On Time works great for project managers and application architects as a way to visualize the project database without writing a single line of code. You can use the generated apps as makeshift data management tools that will help populate your data, making it easy to spot any inconsistencies or undue complexity. The apps will even run on your laptop with a local database and do not require actual deployment to a server. The high quality user interface of  generated web applications can complement custom projects and allow your team to concentrate on essential custom code.