SQL Server

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
SQL Server
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.

Sunday, June 16, 2013PrintSubscribe
Multiple Databases

Multiple database services are common in the modern business environment. Code On Time generator has the capability to tie this data together in a single web application.

In this example, DB1 holds the Categories and Products tables of an web-based order management system. DB2 holds the Suppliers table used by the purchasing department. You can see the database schemas in the picture below.

The schemas for databases DB1 and DB2.

Suppose that business requirements demand displaying vendor information next to the product name. We would like to add fields in Products to capture information from the Supplier, as well as allow access to information about Suppliers, all in the same application.

How do we overcome the physical separation of DB1 and DB2?

Take a look at the default web app generated from DB1 below. You will see that two pages have been created for Categories and Products, respectively. We will need to add Suppliers to this web app as well.

Categories and Products are available in the DB1 database.

Code On Time Generator does not support generation from multiple databases. Creating an application that handles different data sources will require a combined sample database that you can generate from. After the web app is created, you can change the connection strings to connect the remote data. In this example, it would be easiest to add the Suppliers table to DB1. Also, add SupplierID and SupplierCompanyName to the Products table. These fields, while not foreign keys in the database, will capture information from DB2 when we set them up as lookups.

Adding Suppliers Table to DB1

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / DB1 / Tables node, and press New Table.

Adding a new table to database DB1.

Assign the following values:

Column Name Data Type Allow Nulls
SupplierID int no
CompanyName nvarchar(50) no
ContactName nvarchar(50) yes
ContactTitle nvarchar(50) yes
Address nvarchar(50) yes
City nvarchar(50) yes
Region nvarchar(50) yes
PostalCode nvarchar(50) yes
Country nvarchar(50) yes
Phone nvarchar(50) yes
Fax nvarchar(50) yes
HomePage ntext yes

Press Ctrl+S to save the table. Assign the table a name of “Suppliers”.

Entering the name for 'Suppliers' table.

In the Object Explorer, right-click on Databases / DB1 / Tables / dbo.Products table node, and press Design.

Designing the Products table of database DB1.

Add the following columns:

Column Name Data Type Allow Nulls
SupplierID int yes
SupplierCompanyName nvarchar(50) yes

Save the table. Switch back to the app generator, click on the project name, and press Refresh.

Check the boxes next to Products controller and dbo.Suppliers table, and click Refresh.

Refreshing Products and Suppliers tables.

Proceed to regenerate the application.

When complete, notice that the Suppliers page has been added to the web app. However, there are no suppliers to be found. We will need to change the connection string for the Suppliers controller.

No suppliers are present in DB1 database.

Adding a Second Connection String

Switch back to the app generator. Click on the project name, and press Settings. Click on Web Server Configuration.

In the Web.Config modification instructions textbox, add the following:

AppendChild: /configuration/connectionStrings
<add name="DB2" connectionString="Data Source=.;Initial Catalog=DB2;Integrated Security=True;" providerName="System.Data.SqlClient" />

Press Finish to skip to the Summary page. Click Design to activate the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Suppliers controller node.

The Suppliers controller selected in the Project Explorer.

Change the Connection String Name property:

Property Value
Connection String Name DB2

Press OK to save.

Configuring Cross-Database Lookups

Double-click on Products / Fields / SupplierID node.

SupplierID field in the Products controller.

Make the following changes:

Property Value
Items Style Lookup
Items Data Controller Suppliers
Data Value Field SupplierID
Data Text Field CompanyName
New Data View createForm1
Copy SupplierCompanyName=CompanyName

Press OK to save. At the top of the Project Browser window, switch to the Data Fields tab. Assign an Alias of “SupplierCompanyName” to all SupplierID data fields.

Setting the Alias of SupplierID data fields to 'SupplierCompanyName'.

In the Project Explorer, double-click on Products / Fields / SupplierCompanyName (String(50)) node.

SupplierCompanyName field selected from the Products controller.

Switch to the Data Fields tab. Mark all data fields as hidden.

Marking the SupplierCompanyName data fields as hidden.

Viewing the Results

On the toolbar, press Browse. Navigate to the Products page, and start editing a record. Click on the SupplierCompanyName lookup – a list of suppliers will be displayed.

The lookup displays a list of suppliers.

Select a supplier. The ID will be inserted into the field, but the name will be displayed.

The Supplier lookup displays the Company Name.

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.