Code On Time: SQL Server

Sunday, June 16, 2013Print Subscribe

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.

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.

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.

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”.

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

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.

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.

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.

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.

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.

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

Switch to the Data Fields tab. Mark all 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.

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

Labels: Application Builder, SQL Server, Web Application Generator

Sunday, June 2, 2013Print Subscribe

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.

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.

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.

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.

Labels: Application Builder, SQL Server, Web Application Generator

Monday, March 18, 2013Print Subscribe

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.

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.

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.

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.

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.

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.

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.

Labels: Application Builder, SQL Server, Tutorials, Web Application Generator