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
Sunday, June 2, 2013PrintSubscribe
Complex Queries

Suppose that you have a complex dataset that must be displayed in your web application. The application framework parses the command text in order to find expressions to correspond to data controller fields. These expressions are simple enough to used when dynamically constructing SQL SELECT statements at runtime.

However, the SQL parser in Code On Time has some limitations and may be unable to handle complex queries. In this situation, it would make more sense to offload the parsing to the database server and simply display the data as a view.

For example, suppose that it is necessary to display all phone numbers for shippers, suppliers, and customers grouped by CompanyName, Phone, and ContactName in a sample Northwind web app. Several UNIONs must be used in order to aggregate all the data. The SQL query is displayed below.

SELECT CompanyName, Phone, null as "ContactName"
FROM Shippers
    UNION
SELECT CompanyName, Phone, ContactName
FROM Customers
    UNION
SELECT CompanyName, Phone, ContactName
FROM Suppliers
GROUP BY CompanyName, Phone, ContactName

If one were to create a controller and use the query above as a command, they will receive an error message displayed below.

Error received when the command is too complex to be parsed.

Instead, let’s save the query as a view and add the view to the project.

Creating the View

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Views node, and press New View.

Adding a new view to the Northwind database.

In the SQL Pane, paste in the SQL script displayed below.

SELECT CompanyName, Phone, null as "ContactName"
FROM Shippers
    UNION
SELECT CompanyName, Phone, ContactName
FROM Customers
    UNION
SELECT CompanyName, Phone, ContactName
FROM Suppliers
GROUP BY CompanyName, Phone, ContactName

Save the view with the name “PhoneNumbers”.

Adding View to Project

Start the web app generator. Click on the project name, and press Refresh. Check the box next to PhoneNumbers, and continue to refresh the project.

Adding the PhoneNumbers view to the project via Refresh.

When refresh is complete, continue to regenerate the project.

Viewing the Results

When the web app opens in the default browser, navigate to New Pages | Phone Numbers page. The new view will be displayed without errors.

The Phone Numbers view is displayed properly in the generated web app.

Tuesday, May 7, 2013PrintSubscribe
Tools for Excel - Pages and Commands Hot Fix

Tools for Excel release 1.0.8.1 includes a hot fix that allows working with pages and commands on computers with regional settings that use “,” as a decimal separator.

The fix is available at http://codeontime.com/releases/tools-for-excel.

Sunday, May 5, 2013PrintSubscribe
Assigning Access Key With Code

Both Azure Storage and Amazon S3 require a couple of security parameters and repository name in order to upload or download a file. The data controller configurations of both adapters require explicit definition of these parameters.

Let’s consider the configuration of an Azure Blob Adapter.

Field: Picture
Storage-System: Azure
Account: northwindproductpictures
Container: pics
Key: e2Wl668b6fEGauS6cOTAHaj7Ut6QfwKdbGY4Vd8yngz40y2f54M5EfZtSNNHYoXW7i7+kZAfFJrg==
Path-Template: Products/{ProductID}-{PictureFileName}

The application framework allows providing default values for Account, Container, and Key.

Start the app generator. Select your project and click Develop. The project will be opened in Visual Studio. In the Solution Explorer, right-click on ~\App_Code\Data, and press Add | Add New Item…

Adding a new item to the Data folder in a Code On Time Web Site Factory project.

Select Class from the list, specify a name, and press OK to add the new file.

Adding a class file to the project.

Replace the code base with the following. Use your own key instead of the highlighted text.

C#:

using System;

namespace MyCompany.Data
{
    public partial class AzureBlobAdapter
    {
        public override string Key
        {
            get
            {
                return "ENTER KEY HERE";
            }
        }
    }
}

Visual Basic:

Imports Microsoft.VisualBasic

Namespace MyCompany.Data
    Partial Public Class AzureBlobAdapter
        Public Overrides Property Key As String
            Get
                Return "ENTER KEY HERE"
            End Get
            Set(value As String)
                MyBase.Key = value
            End Set
        End Property
    End Class
End Namespace
Save the file, and activate the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Products. Change the Blob Adapter Configuration as shown below.

Field: Picture
Storage-System: Azure
Account: northwindproductpictures
Container: pics
Path-Template: Products/{ProductID}-{PictureFileName}

The application framework will now use the key explicitly returned by the AzureBlobAdapter.Key property. 
The image thumbnails are loaded from Azure Storage using the new key.

The Account and Container properties may also be overridden in a similar fashion to further simplify the adapter configuration. Multiple virtual binary fields may share the same Account, Container, and Key. The implementation of the properties may optionally read values from the database depending on the user identity, role, or other conditions.

The Amazon S3 Adapter allows overriding properties AccessKeyID, SecretAccessKey, and Bucket.