Blog

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
Tuesday, March 26, 2013PrintSubscribe
Upload and Download: External Storage

Code On Time web applications perform automatic handling of binary large objects (BLOB). Thumbnails of images are displayed in grids and forms, such as in the screenshot below.

Automatic handling of pictures in BLOB fields.

Users can clear or replace the field with files of their own choosing. This functionality is available out-of-the-box and requires no extra programming when the data is stored in the database. It can be extended by adding utility fields to store the file name, size, and extension.

An alternative to database-stored data is use an external storage location. The data can be placed in online storage systems such as Amazon S3 or Microsoft Azure Storage. These systems provide many benefits, such as easy scalability and content delivery networks. The data can also be stored on a local Network Attached Storage (NAS).

Let’s implement a column in the Categories table of the sample Northwind database in order to store data on the local hard drive.

Adding Columns to the Categories Table

Start SQL Server Management Studio and connect to your database. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Categories and press Design.

Designing the Categories table in Northwind database.

Add the following columns:

Column Name Data Type Allow Nulls
ExternalDocFileName nvachar(150) yes
ExternalDocLength int yes
ExternalDocContentType nvarchar(150) yes

Save the changes to the table.

Refreshing the Project

Start Code On Time web application generator. Click on the project name, and select Refresh. Check the box next to Categories controller and continue to refresh the web app.

Refreshing the Categories controller.

Implementing ExternalDoc Field

Start the Project Designer. In the Project Explorer, right-click on Categories / Fields node, and press New Field.

Creating a new field in Categories controller.

Assign the following values:

Property Value
Name ExternalDoc
Type Byte[]
Allow null values. yes
The value of this field is computed at run-time by SQL expression. yes
Value is retrieved on demand yes
Source Fields CategoryID
On Demand Handler CategoriesExternalDoc
On Demand Style Thumbnail
Label External Doc

Save the field.

We will need to bind the field to grid1 and editForm1 views and prevent the user from editing the utility fields. In the Project Explorer, drag Categories / Fields  / ExternalDoc (Byte[]) field node onto Categories / Views / grid1.

Dropping ExternalDoc field onto view 'grid1'.     Data field instantiated in 'grid1' view.

Drag ExternalDoc (Byte[]) field node onto Categories / Views / editForm1 node.

Dropping ExternalDoc field onto view 'editForm1'.      Data field instantiated in 'editForm1' view.

Let’s prevent users from editing the utility fields in editForm1 or grid1. Right-click on Categories / Views node, and press List.

List context menu option for Views of 'Categories' controller.

Upload of binary content is currently only supported for existing records. Let’s remove the utility fields from createForm1. Click on Categories / Views / createForm1 / ExternalDocFileName data field node. Hold Shift key, and click on ExternalDocContentType. Right-click and press Delete.

Deleting blob fields from view 'createForm1'.

At the top of the Project Browser, switch to Data Fields tab. A grid of data fields will be displayed. Make the following changes to Format and Text Mode properties:

Field Name View Format Text Mode
ExternalDocFileName editForm1   Static
ExternalDocLength editForm1 n0 Static
ExternalDocContentType editForm1   Static
ExternalDocFileName grid1   Static
ExternalDocLength grid1 n0 Static
ExternalDocContentType grid1   Static

Implementing the Business Rules

If you try to upload files in the ExternalDoc field, an error will be displayed. File upload and download will have to be handled with custom business rules, which will do the following:

  • The first business rule will prevent the default upload behavior, and persist the file contents to the specified folder. The file will be named using the CategoryID and generic “.bin” extension.
  • When the binary content needs to be streamed to the client browser, another business rule will prevent the default behavior and use the CategoryID to find the file and pass it to the client.
  • The third method will update the value of the binary field with the primary key of the record if an external file exists. The client library will pass the ID to the server components and display a thumbnail.
    If the binary field is empty then the method will return the CategoryID preceded by the word null 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.

The folder that blobs will be saved to must exist before they will be saved. Navigate to your My Documents folder and create another folder with the name “My External Doc Files”.

Creating the 'My External Doc Files' folder in My Documents.

In the Project Explorer, right-click on Categories / Business Rules node, and press New Business Rule.

Creating a new business rule for Categories controller.

Assign the following properties:

Property Value
Type C# / Visual Basic
Command Name UploadFile
Command Argument ExternalDoc
Phase Execute

Save the business rule. Create another business rule with the following properties:

Property Value
Type C# / Visual Basic
Command Name DownloadFile
Command Argument ExternalDoc
Phase Execute

Create a third rule:

Property Value
Type C# / Visual Basic
Command Name Select
Phase Execute

On the toolbar, press Browse to generate the business rule files. When complete, right-click on Categories / Business Rules / UploadFile (Code / Execute) – r100 node, and press Edit Rule in Visual Studio.

Editing the UploadFile business rule in Visual Studio.

Replace the code base with the following. Note that a Stream parameter has been added to the method definition in order to handle the uploaded file.

C#:

using System;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r100")]
        public void r100Implementation(int? categoryID, string categoryName, 
            string description, string externalDocFileName, int? externalDocLength, 
            string externalDocContentType, 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();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.IO

Namespace MyCompany.Rules

    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r100")> _
        Public Sub r100Implementation(ByVal categoryID As Nullable(Of Integer),
                                      ByVal categoryName As String,
                                      ByVal description As String,
                                      ByVal externalDocFileName As String,
                                      ByVal externalDocLength As Nullable(Of Integer),
                                      ByVal externalDocContentType As String,
                                      ByVal 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
    End Class
End Namespace

Save the file. In the Project Explorer, right-click on Categories / Business Rules / DownloadFile (Code / Execute) – r101 node and press Edit Rule in Visual Studio.

Editing the DownloadFile business rule in Visual Studio.

Replace the code:

C#:

using System;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r101")]
        public void r101Implementation(int? categoryID, string categoryName, string description, 
            string externalDocFileName, int? externalDocLength, string externalDocContentType, 
            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();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.IO

Namespace MyCompany.Rules

    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r101")> _
        Public Sub r101Implementation(ByVal categoryID As Nullable(Of Integer),
                                      ByVal categoryName As String,
                                      ByVal description As String,
                                      ByVal externalDocFileName As String,
                                      ByVal externalDocLength As Nullable(Of Integer),
                                      ByVal externalDocContentType As String,
                                      ByVal 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
    End Class
End Namespace

Save the file. Finally, right-click on Categories / Business Rules / Select (Code / Execute) – r101 node and press Edit Rule in Visual Studio.

Editing the Select business rule in Visual Studio.

Replace the code with the following:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r102")]
        public void r102Implementation(int? categoryID, string categoryName, 
            string description, string externalDocFileName, int? externalDocLength,
            string externalDocContentType)
        {
            if (!String.IsNullOrEmpty(externalDocFileName))
                UpdateFieldValue("ExternalDoc", categoryID);
            else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryID));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules
    
    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r102")> _
        Public Sub r102Implementation(ByVal categoryID As Nullable(Of Integer),
                                      ByVal categoryName As String,
                                      ByVal description As String,
                                      ByVal externalDocFileName As String,
                                      ByVal externalDocLength As Nullable(Of Integer),
                                      ByVal externalDocContentType As String)
            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

Save the file.

Viewing the Results

On the Project Designer toolbar, press Browse. When complete, navigate to the Categories page. The utility fields will be present in the grid.

image

Start editing a record, and upload a file to External Doc field. The file will be uploaded successfully, and the utility fields will be updated with the correct information.

image

Clicking on the thumbnail will download or open the file with the correct format. The file is stored in the folder My Documents/My External Doc Files with a file name of the CategoryID and extension of “.bin”.

image

Monday, March 18, 2013PrintSubscribe
Upload and Download: Internal Storage

Code On Time web applications automatically handle storage of binary large objects (BLOB). For example, the Northwind sample database contains pictures of each record in the Categories table. The web app displays the pictures in the grid and form views. Clicking on the thumbnail will download the picture.

Pictures displayed from the BLOB field in Categories table.

Open the edit form for a category and edit the record. The control under the Picture thumbnail allows replacing or clearing the blob stored in the field.

Control under the Picture field allows changing or clearing the blob.

Any type of file may be uploaded to the field. However, the file name and type is not stored by the database. When the user tries to download the file again, the user will receive a message similar to the one below.

When a file is downloaded, the file name and type are not preserved.

The generic file name is “CategoriesPicture_1” and the content type is “.octet-stream”.

In order to preserve the file name and type, utility fields will be added to the Categories table.

Adding Utility Fields

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Categories and press Design.

Designing the Categories table in SQL Server Management Studio.

Add the following columns:

Column Name Data Type Allow Nulls
PictureFileName nvarchar(100) true
PictureContentType nvarchar(100) true
PictureLength int true

Please note that these columns are case-sensitive. If the name of your field is “XXX”, the application framework will attempt to locate utility fields “XXXFileName”, “XXXContentType”, and “XXXLength”.

Save the changes to the table.

Refreshing the Project

Start the web application generator. Click on the project name and press Refresh. Select Categories controller and continue to refresh the project.

Refreshing the Categories controller.

Viewing the Results

Continue to regenerate the web app. When complete, navigate to the Categories page. Select a record, and upload any non-image file.

File Name, Content Type, and Length fields have been automatically updated when a file is uploaded.

Note that the three utility fields have automatically been updated. Click on the thumbnail to download the file. Note that the file name and type have been correctly added.

When attempting to download the file, the name and type are correctly assigned.

Friday, March 1, 2013PrintSubscribe
Geo Tagging, “OR” in Quick Find, DB2, PostgreSQL, Data Sheet Preview

Code On Time release 7.0.7.0 introduces the following features and enhancements:

  • Quick Find supports "OR" if comma “,” or semicolon ";" are entered by the user. Expressions, such as "40 biscuit, beverages 10" are now supported. Quick Find will also skip "aliased" fields when composing a 'where' clause.
     
  • Geo Tagging has been introduced in the client library to allow tagging data with latitude/longitude of the geographical location of the user at the time of data entry.
     
  • Preview of 2nd generation data sheet view is integrated in the release. The new implementation supports horizontal scrolling and freezing of columns, vertical scrolling of data rows, and rendering of hierarchies in projects based on SQL Server and Oracle. 
     
  • PostreSQL database engine is now supported.
     
  • DB2 database engine is now supported.
     
  • Virtualization node set plugin CreateBusinessRule assigns “id” attribute to enable dynamic registration of business rules.
     
  • Designer correctly saves data fields when "Code Filter" property is changed.
     
  • Navigation button is displayed correctly in IE10.
     
  • Grid view templates are compatible with JavaScript business rules.
     
  • Rich Text fields in modal form views will not raise an exception when a field value is changed and the form is closed.
     
  • Data field support new "Tag" property to support column freezing, hierarchies, geo-location, and other future client library enhancements.
     
  • Projects with custom membership correctly integrate an authentication module used to authenticate export and REST requests.
     
  • Batch edit will correctly retain the selection of "update" checkboxes in the views with large number of fields.