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
Thursday, July 31, 2014PrintSubscribe
Generating Controller from SQL Query

 Code On Time generator offers the ability to compose controllers from the results of custom SQL scripts.

The script can range in complexity from performing a simple “select *” from a table to any complicated SQL expression to combine fields from multiple tables.

Here is an example of a result set produced by an arbitrary SQL query.

The result of the SQL query can be viewed in the browser.

In this example, we use a script that displays fields from tables Customers, Orders, Order Details, and Products by using joins on the foreign keys CustomerID, OrderID, and ProductID. The query can be seen below.

SELECT CompanyName, OrderDate, ProductName, Quantity 
    FROM Customers 
        JOIN Orders 
            JOIN [Order Details] 
                JOIN Products 
                ON [Order Details].ProductID = Products.ProductID 
            ON Orders.OrderID = [Order Details].OrderID 
        ON Customers.CustomerID = Orders.CustomerID 

Example output can be seen in the following picture.

Results of the query.

Let’s generate a data controller for this query.

Start the web app generator and activate the Project Designer. In the Project Explorer, switch to the Controllers  tab. Click on the New Controller icon on the toolbar.

Creating a new controller in the Project Explorer.

Give the controller a name:

Property Value
Name ItemsOrderedByCustomer

Press OK to save the new controller. In the Project Explorer, right-click on the new controller and press “Generate From SQL…”.

Defining the new ItemsOrderedByCustomer controller

The Define Data Controller window will open. Paste in the script at the beginning of this article. Press Verify and the result will be seen in the data grid, as in the picture below:

Verifying the results of the query in the "Define Data Controller" window.

Select “Command Text” radio button. This will use the SQL script as a command and will take advantage of the application framework’s ability to compose SQL statements on the fly. SQL formulas will be defined for each field.

The “Base Table Name” property will be used in any dynamically created Insert and Update statements. Leave this property blank for now.

Keep default settings and press OK to define the data controller. The window will close and the Project Explorer will refresh with added fields, views, data fields, actions, as well as the command or business rules.

The data controller has been defined and now has a command.

Next, we will need to create a page and bind the controller to the page with a data view. Right-click on the controller and press Copy.

Copying the 'ItemsOrderedByCustomer' data controller.

Switch to the Pages tab in the Project Explorer. Click on the New Page icon.

Creating a new page in the web app.

Assign a name.

Property Value
Name ItemsOrderedByCustomer

Press OK to save the page. Drag the new page in the Project Explorer to the right of Home page node to place it second in the site menu.

Dropping the 'Items Ordered By Customer' page to right side of Home page node.     The 'Items Ordered by Customer' page is now second in the app menu.

Right-click on the new page and press Paste to bind the controller to the page.

Pasting into the 'Items Ordered by Customer' page.     The controller has been bound to the page with a data view.

On the toolbar, press Browse to generate the app and open it in the default browser.

The result of the query will be visible on the page. The user can sort, filter, and view data.

Thursday, July 31, 2014PrintSubscribe
Generating Controller with Business Rules

 Code On Time generator offers the ability to compose controllers from the results of custom SQL scripts.

If a stored procedure is the data source, business rules must be used to compose a result set. This allows the application framework to perform sorting and filtering operations that would otherwise be performed by the database server.

Here is an example of a result set produced by a stored procedure called from an SQL business rule.

Data from the Employee Sales by Country stored procedure can be viewed, sorted, and filtered.

In this example, we will be using the “Employee Sales by Country” stored procedure, available with the Northwind sample database. The procedure combines data from Employees, Orders, and gets the total price (from Order Details) for each order in between the Beginning Date and Ending Date parameters. The CREATE script for the stored procedure can be seen below.

CREATE procedure dbo.[Employee Sales by Country] 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT    Employees.Country, 
        Employees.LastName, 
        Employees.FirstName, 
        Orders.ShippedDate, 
        Orders.OrderID, 
        "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Example output can be seen in the following picture.

Example output from the EmployeeSalesByCountry stored procedure.

Let’s generate this data controller.

Start the web app generator and activate the Project Designer. In the Project Explorer, switch to the Controllers  tab. Click on the New Controller icon on the toolbar.

Creating a new controller in the Project Explorer.

Give the controller a name:

Property Value
Name EmployeeSalesByCountry

Press OK to save the new controller. In the Project Explorer, right-click on the new controller and press “Generate From SQL…”.

Defining the new controller.

The Define Data Controller window will open. Paste in the following script:

EXEC dbo.[Employee Sales by Country] 
    @Beginning_Date = '1970-01-01', 
    @Ending_Date = '2000-01-01'

Press Verify and the result will be seen in the data grid, as in the picture below:

The script has been verified and the results can be seen in the data grid on the "Define Data Controller" window.

The Define Data Controller window also offers the option to define a Business Rule or the Command Text for the controller.

When “Command Text” is selected, the controller will have a command defined from the SQL script and will take advantage of the application framework’s ability to compose SQL statements on the fly. SQL formulas will be defined for each field. If specified, the “Base Table Name” property will be used in any dynamically created Insert and Update statements.

The “Business Rule” option will not generate a command for the controller. Instead, two SQL business rules will be created that prevent the default Select action from occurring and define a custom result set. In addition, three more business rules will be created to override the insert, update, and delete actions from occurring.

Keep default settings and press OK to define the data controller. The window will close and the Project Explorer will refresh with added fields, views, data fields, actions, as well as the command or business rules.

The EmployeeSalesByCountry controller has been defined.

Next, we will need to create a page and bind the controller to the page with a data view. Right-click on the controller and press Copy.

Copying the EmployeeSalesByCountry controller.

Switch to the Pages tab in the Project Explorer. Click on the New Page icon.

Creating a new page.

Assign a name.

Property Value
Name EmployeeSalesByCountry

Press OK to save the page. Drag the new page in the Project Explorer to the right of Home page node to place it second in the site menu.

Dropping the new page to the right of Home page node.     The Employee Sales By Country page has been placed after Home page node.

Right-click on the new page and press Paste to bind the controller to the page.

Using Paste command to bind the controller to the page.     A data view has been created for EmployeeSalesByCountry.

On the toolbar, press Browse to generate the app and open it in the default browser.

The result set will be visible on the page. The user can sort, filter, and view data. Note that additional programming will be required to allow the user to update, insert, or delete records.

Thursday, July 31, 2014PrintSubscribe
Creating Data Controller From Web Service

Code On Time web app generator automatically creates controllers for any specified tables and views from your database. In addition, you can define new controllers from any SQL query. One can also choose to display data from any data source using C# or Visual Basic business rules – you are only limited by your imagination.

In this example, let’s request a list of articles recently published on /blog. A sample application showing the blog posts can be seen below.

The list of posts retrieved from the web service is displayed in a list.

The URL that will be used to compose a REST request is the following:

http://www.blogger.com/feeds/2297698770491701674/posts/default/

You can see an example of the response with the essential items highlighted below.

<?xml version='1.0' encoding='UTF-8'?>
<?xml-stylesheet href="http://www.blogger.com/styles/atom.css" type="text/css"?>
<feed xmlns='http://www.w3.org/2005/Atom' ...>
  ...
  <entry>
    ...
    <published>2014-07-13T22:25:00.000-07:00</published>
    <updated>2014-07-15T00:03:46.566-07:00</updated>
    ...
    <title type='text'>
      Map View, Master-Detail Pages, 
      Custom Result Sets, Client-Side APIs
    </title>
    <content type='html'>
      &lt;p&gt;&lt;a title="Code On Time generator creates line-of-business Web Apps ...
    </content>
    ...
  </entry>
  <entry>
    ...
    <published>2014-07-13T10:00:00.000-07:00</published>
    <updated>2014-07-13T17:11:25.294-07:00</updated>
    ...
    <title type='text'>Assigning a Theme to a Page</title>
    <content type='html'>
      &lt;p&gt;&lt;a title="Code On Time Generator is a premier web application ...
    </content>
    ...
  </entry>
  ...
</feed>

The code business rule will need to accept this XML, create a data table and convert each “entry” element into a data row. The data rows will have four columns – Published, Updated, Title, and Content.

Defining the Controller

The first step is to define a controller that will handle the data table. One possible way of defining the controller would be to simply create it in the Project Designer. However, let’s take advantage of the automatic field, view, data field, and action generation provided by the Define Data Controller tool.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and press the New Controller icon.

Creating a new controller in the Project Explorer.

Give the controller a name:

Property Value
Name Posts

Press OK to save the new controller. Expand the new controller in the Project Explorer, and right-click on the Fields node. Select New Field option.

Creating a new field in the Posts controller.

Define the following properties:

Property Value
Name Title
Type String
Length 256

Press OK to save. Create another field with these properties:

Property Value
Name Content
Type String
Html Encoding False

Save the field, and add another:

Property Value
Name Published
Type DateTime

Save, and add the Updated field:

Property Value
Name Updated
Type DateTime

Save the last field. Right-click on the controller, and press Generate From Fields.

Generating the controller from the field definitions.

This will proceed to generate views, data fields, actions, and several code business rules to override CRUD operations. No command will be created.

The controller has been generated from the fields.

The first code business rule will provide an outline for defining the result set. The next three rules simply override the Insert, Update, and Delete actions and call PreventDefault() method. The developer must implement these rules in order for the respective actions to work.

On the toolbar, press Browse to regenerate the app and create the code files. When complete, right-click on Posts / Business Rules / Select (Code / Before) – GetData business rule, and press Edit Rule In Visual Studio.

Editing the rule in visual studio.

The file will open in Visual Studio. The business rule will create a DataTable object by calling to CreatePostsDataTable() method. The default implementation of this method will simply return a data table with no data.

[Rule("GetData")]
public void GetDataImplementation(
    string title, 
    string content, 
    DateTime? published, 
    DateTime? updated)
{
    ResultSet = CreatePostsDataTable();
}
        
private DataTable CreatePostsDataTable()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("Title", typeof(String));
    dt.Columns.Add("Content", typeof(String));
    dt.Columns.Add("Published", typeof(DateTime));
    dt.Columns.Add("Updated", typeof(DateTime));
    // 
    // Populate rows of table "dt" with data from any source 
    // (web service, file system, database, etc.)
    //
    return dt;
}

Let’s complete the implementation by providing the data for the data table. At the top of the file, add the following using/import directive:

C#:

using System.Xml;

Visual Basic:

Imports System.Xml

Then, replace the “Populate rows of table” comment after the data table is declared with the following code. The code will make a request to Blogger, read in each “entry” element and create a new data row in the table using the values of that element.

C#:

// get data into table
XmlReader reader = XmlReader.Create(
    "http://www.blogger.com/feeds/2297698770491701674/posts/default/");
reader.ReadToDescendant("entry");
while (reader.LocalName == "entry")
{
    DataRow r = dt.NewRow();
    XmlReader subtree = reader.ReadSubtree();

    if (subtree.ReadToDescendant("published"))
    {
        r["Published"] = subtree.ReadElementContentAsDateTime("published",
            "http://www.w3.org/2005/Atom");
        r["Updated"] = subtree.ReadElementContentAsDateTime("updated",
            "http://www.w3.org/2005/Atom");
        while (subtree.LocalName != "title")
            subtree.Read();
        r["Title"] = subtree.ReadElementContentAsString("title",
            "http://www.w3.org/2005/Atom");
        r["Content"] = subtree.ReadElementContentAsString("content",
            "http://www.w3.org/2005/Atom");

        dt.Rows.Add(r);
    }

    if (!reader.ReadToFollowing("entry") || reader.EOF)
        break;
}

Visual Basic:

Dim reader As XmlReader = XmlReader.Create(
                "http://www.blogger.com/feeds/2297698770491701674/posts/default/")
reader.ReadToDescendant("entry")
While reader.LocalName = "entry"
    Dim r As DataRow = dt.NewRow()
    Dim subtree As XmlReader = reader.ReadSubtree()

    If subtree.ReadToDescendant("published") Then
        r("Published") = subtree.ReadElementContentAsDateTime("published",
                                                              "http://www.w3.org/2005/Atom")
        r("Updated") = subtree.ReadElementContentAsDateTime("updated",
                                                            "http://www.w3.org/2005/Atom")
        While subtree.LocalName <> "title"
            subtree.Read()
        End While
        r("Title") = subtree.ReadElementContentAsString("title",
                                                        "http://www.w3.org/2005/Atom")
        r("Content") = subtree.ReadElementContentAsString("content",
                                                          "http://www.w3.org/2005/Atom")

        dt.Rows.Add(r)
    End If

    If Not reader.ReadToFollowing("entry") OrElse reader.EOF Then
        Exit While
    End If
End While

Make sure to save the file.

Adding the Page and Viewing the Results

Switch back to the Project Designer. Right-click on the Posts controller node, and press Copy.

Copying the 'Posts' controller.

Switch to the Pages tab in the Project Explorer. Click on the New Page icon.

Creating a new page from the Project Explorer.

Assign a name.

Property Value
Name Posts

Press OK to save the page. Drag the new page in the Project Explorer to the right of Home page node to place it second in the site menu.

Dropping the page on the left side of the Home page node.     The 'Posts' page has been placed second in the site menu.

Right-click on the new page and press Paste to bind the controller to the page.

Pasting onto the 'Posts' page.     The data controller has been bound with a data view.

On the toolbar, press Browse to generate and open the web app in the default browser. The list of posts retrieved from the web service is displayed on the page. Note that you must define a primary key before any of the items can be selected.