Blog: Posts from August, 2011

AJAX(112) App Studio(2) Apple(1) Application Builder(244) 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(100) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(4) 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(17) OAuth(8) OAuth Scopes(1) OAuth2(10) Offline(19) Offline Apps(4) Offline Sync(5) Oracle(10) PKCE(2) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(171) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(80) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) Stored Procedure(4) Teamwork(15) Tips and Tricks(85) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(335) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Posts from August, 2011
Thursday, August 25, 2011PrintSubscribe
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

alter table dbo.Categories
add ExternalDocLength integer null

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

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.


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)
            string fileName = Path.Combine(
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream output = File.Create(fileName);

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

        [RowBuilder("Categories", RowKind.Existing)]
        public void VerifyPresenceOfExternalFile()
            int categoryID = Convert.ToInt32(SelectFieldValue("CategoryID"));
            string externalDocFileName = Convert.ToString(
            // update ExternalDoc field to reflect existence of a file
            if (!String.IsNullOrEmpty(externalDocFileName))
                UpdateFieldValue("ExternalDoc", categoryID);
                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)
            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)
        End Sub

        <ControllerAction("Categories", "DownloadFile", "ExternalDoc")> _
        Protected Sub LoadExternalDocFromFileSystem(ByVal categoryID As Int32, _
                                                  ByRef externalDoc As Stream)
            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)
        End Sub

        <RowBuilder("Categories", RowKind.Existing)> _
        Protected Sub VerifyPresenceOfExternalFile()
            Dim categoryId As Integer = Convert.ToInt32(SelectFieldValue("CategoryID"))
            Dim externalDocFileName As String = Convert.ToString( _
            ' update ExternalDoc field to reflect existence of a file
            If Not String.IsNullOrEmpty(externalDocFileName) Then
                UpdateFieldValue("ExternalDoc", categoryId)
                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.



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.

Wednesday, August 24, 2011PrintSubscribe
Printing of Pictures, Upload/Download, Bug Fixes

The latest release of Code On Time  has introduced the following improvements and bug fixes:

  • Only one BLOB handler is registered even if more than one reference exists in the project fields.
  • Clearing of a blob will now clear the special fields <FieldName>ContentType, <FieldName>Length, <FieldName>FileName.
  • If a picture field is defined in a grid / data sheet view then it will be fitted proportionally to the available space. If multiple picture fields are present then all columns will fit in the dimensions of the page. This applies to ASP.NET 4.0 web apps only.
  • Upload/Download mechanism has been enhanced to support externalized storage of binary content. It is now possible to declare a placeholder field in a data controller and respond to UploadFile and DownloadFile commands in business rule methods to store the binary content outside of the database.
    We are expecting to publish the second part of Upload/Download tutorial later today.
    Code On Time will be offering two standard upload modules that will store any content in the file system or in Windows Azure Storage. The goal is to offer out of the box solution that requires zero coding.
  • An enhancement has been added to the business object builder to prefix aliases in automatically produced SQL statements embedded in the data controllers to prevent naming conflicts that may occur if a table has a column with the same name. We have further refined the enhancement to narrow its impact on existing projects.
  • Logging of messages produced during code generation has been changed to further increase speed of the process.
  • The ability to customize labels has been restored. You can enter FieldName=Label Text pairs on the Business Logic Layer page under Text Label Construction section.
  • Correct filter details are presented if you apply a filter to a Boolean field.
Tuesday, August 23, 2011PrintSubscribe
Enhanced Charts, Sample Databases

New release of Code On Time web app generator includes a collection of bug fixes and enhancements.

  • Chart support was enhanced to allow X axes with grouping for data fields of type Date and DateTime. For example, create an ASP.NET 4.0 project with the sample  Northwind database and activate Project Designer. Find Orders data controller on All Controllers tab and create a new view of type Chart. Configure the data fields of the chart as shown in the picture.
    Generate the sample app and activate the new view. You will see a chart of orders groups by quarter. You can now print the chart by clicking on  print icon in the pager area of the chart view.
  • It is now possible to create a database straight from the web app generator database connection configuration string.
  • Sample databases are support for MS SQL Server. For example, you can create Northwind sample with just a few clicks.

  • User interface of the Project Wizard has been changed to present Business Logic Layer, Membership and Authentication, and Features pages as tabbed views.
  • URL Hashing is the new EASE feature available in Unlimited edition. The purpose of this feature is to encrypt any hyperlinks produced by Navigate action, Hyperlink Format String, and History. URL Hashing will also ensure that any attempts to specify URL parameters other than _link or ReturnUrl  will result in Access Denied message. This allows to provide assurance that only authorized commands are passed to the application. The tutorial will be available shortly. 
  • New projects will not be trying to treat table/view names with “_” in them as the source of grouping schema. This property can be enabled on Features page if needed. Most developers will not want this feature to be enabled.
  • Repeating patterns of words in labels of the baseline app are now detected. The code generator will reduce any patters with up to four words.
  • The issue with master detail navigation and switching between grid and form views has been resolved. 
  • Multiple selection in grid views with a single primary key will affect Report and Actions items on the action bar. Only selected records are included in the output of reports and export actions if a multi-row selection is detected.
  • SQL Server’s hierarchyid and geography fields are now correctly processed. It is now possible to create an application from Adventure Works 2008 R2 without any errors.
  • The bug with "View Details" arrow in Project Designer views has been fixed.
  • Check Box List now fully supports context fields, which allows implementing cascading dependencies on many-to-many fields.
  • Memory management in the code generator has been improved to further increase the speed of code generation. The benefits will be visible in Premium and Unlimited editions on quad-core machines.
  • Hyperlink Format String with lookups is working correctly with NULL values when Relationship Explorer or URL Hashing is enabled.
  • Rendering of an automatic report from the view with multiple image fields will result in a correctly generated report.