Web 2.0

Code On Time applications support direct uploading of external files into the database.

Basics

Consider the following database schema based on the Northwind sample available with Microsoft SQL Server. Table Categories features Picture field of type image. This data type allows capturing binary content. The purpose of this particular field is to capture a picture representing a category as suggested by the field name.

Generate a Web Site Factory web application with this three tables and navigate to Categories page. The screen shot below shows an automatically created page of categories stored in the database. Field Picture is provided with an automatic preview thumbnail.

Users can create new categories and upload category pictures as shown next.

Create a new category record in createForm1 view by selecting New Categories button on action bar.

Save and edit the new category and indicate that you want to upload a file.

Click Browse button and select the image file. File uploading will start immediately.

The file is being uploaded in editForm1.

The file has been uploaded as indicated by the message at the top of the page.

Note that file uploading works only with existing records. The Picture field is available only in editForm1 and grid1 view.

Uploading of binary content is executed asynchronously. If a record does not exist yet then it is not possible to save a file to the database.  Create a new record first, save it, and only at that time upload any files that must be associated with a record.

Code On Time applications will be offering a delayed uploading option that will work with new records by postponing operation until a new record is successfully stored in the database. Creation of  a new record and file uploading will become one smooth operation.

Capturing Extended File Properties

Generated applications do not limit the type of content submitted by end users. If an Adobe PDF file or Microsoft Word document is uploaded instead of an image then the file content will be saved in the database. The thumbnail will not display a preview in that case. If you were to download the file from the application to your hard drive by right-clicking on the thumbnail and selecting Save Target As option then you will notice the following message.

The name of the file is represented as generic CategoriesPicture_27, the file type is unknown, the content type is octet-stream.

The binary file stores the file contents only. The file name and content type are not known.

You can easily remedy the situation by introducing utility fields to capture the file name, content type, and length of the uploaded file. The latter is not strictly needed since the application can determine the size of the file by inspecting the database. File length can be useful if you users need to known the file size prior to downloading the file on their computer.

Change Categories table as shown below. Three new columns complement field Picture in table Categories. We have added columns PictureFileName, PictureContentType, and PictureLength. Make sure to allocate enough space for Content Type field to accommodate very long content types introduced in the latest versions of Microsoft Office.

Refresh the meta data of your project and regenerate the application.

Create a new category, select the category in the grid view and start editing category properties. New utility fields are displayed right under the Picture field.

Upload any document or image.

Notice that the thumbnail of the uploaded file shows the file extension. The utility fields have captured their corresponding properties.

Right-click  the thumbnail, select Save Target As and observe the prompt that may look as follows.

The name of the file is correctly suggested in “Save As” dialog. The type of the file is also correct.

If you click on the thumbnail directly then the file will open in the application associated with the file type on your computer.

We suggest that your further customize the data controller in the project Designer as shown in the next screen shot. 

All utility fields are marked as hidden in createForm1.

Field PictureContentType is “hidden” in editForm1 and grid1.

Field PictureFileName has its Text Mode property set to Static in editForm1 and grid1.

Field PictureLength is “hidden” in editForm1 and Static in view grid1.

These changes will ensure that utility information is captured and only the file name and file size are displayed as read-only values.

Implementing Virtual Fields to Store BLOB/ FILE Content

The new architecture of the framework in Code On Time applications allows full control of the upload / download processing of binary fields with the data controller business rules.

Let’s create a virtual “Picture” field in Products data controller and store the uploaded files in the external file system folder. Such approach may help to reduce the size of the database and provides developers with total control of the process.

To be continued.

Code On Time announces support for SQL Anywhere 12.  Generate powerful user-friendly line-of-business web applications straight from SQL Anywhere 12 databases with Microsoft Office look and feel. The following instructions are written with an assumption that you have installed SQL Anywhere software on your computer.

INSTALLATION

Download the code generator at http://codeontime.com/download.aspx. The installation program will also install Microsoft.NET 4.0, IIS Express 7.5, and Microsoft Report Viewer 2010. These components are available at no cost and fully supported by Microsoft.

The code generator will use IIS Express to run generated web applications on your computer. It can be installed alongside the production version of IIS available in several versions of Microsoft Windows.

The report viewer component will render will render dynamically created reports in PDF, Word, Excel, and TIFF formats.

Follow installation instructions and click on Code OnTime Generator shortcut created on the desktop after installation.

GENERATING AN APPLICATION

Start new Web Site Factory project and enter SQL Anywhere Demo as the project name.

Click Next until your reach Data Connection page in the project wizard.

Select SQL Anywhere option in data provider and enter the following connection string.

userid=dba;password=sql;

Enable dynamic and static reporting in your project.

Click next and you will see Authentication and Membership page of the project wizard.

Follow instructions at /blog/2011/05/sql-anywhere-membership-configuration.html to configure ASP.NET Membership in your database.

Select the check box titled “Enable support for ASP.NET Membership with membership bar user manager”.

Select the check box “Membership will use a standalone database that already exist”. Select SQL Anywhere option in Provider Name.

Enter the same connection string

userid=dba;password=sql;

You can also point the connection string to another SQL Anywhere database to store ASP.NET membership data that can be shared between multiple projects. Make sure that configuration of the database has been performed prior to generating the application.

Click Next several times until your see a list of data controllers created from your database.

Now you are ready to generate an application and see it in action.

Click next and wait for the application to be displayed in your web browser. If the browser page comes out blank then simply give it a few moments and hit Refresh button. This may happen if your computer is busy and IIS Express is still getting ready to start the generated web application.

You should see the screen similar to the one in the picture.

USING GENERATED WEB APPLICATION

Make sure to keep the code generator running. Code On Time will automatically shut down the started IIS Express instances if you close the web application generator window.

Sign-in using one of the user accounts automatically created by application.  We suggest that you sign in as admin / admin123% . This user account is authorized to see the membership manager.

Click around and play with the generated pages.

Here is the screenshot of the Customers page if you select a customer record. Master record is shown in edit mode with the detail records tabbed at the bottom. Several other standard page layout are available. You can use the project Designer to create custom layouts. Click on the link to see an example of a custom Order Form.

Note that detail records can be edited in modal forms if you are using a commercial edition of the web application generator.

Here is the screen shot of the Products page display in Data Sheet view that allows spreadsheet-style editing of data. Data Sheet view does not require any external components and is available in Premium and Unlimited editions of Code On Time.

Select Report | Adobe PDF option from the action bar of any grid or data sheet view and take a look at PDF printout of your data with custom filters displayed in the report header.

Select Actions | View RSS Feed and subscribe to the feed to be notified by your RSS feed reader when new movies with the filtering criteria become available. How needs email anymore?

Try some cool data analysis features that allow extending your application and data to the end-user desktops enabling safe and efficient data delivery to business users.

Select Actions | Export to Spreadsheet to export data for analysis.

A prompt will show up to warn you that some data is being downloaded. Internet Explorer 9 and other major browser display a prompt at the bottom of the screen.

Press Open button to open the file. This will start Microsoft Excel. You will see a warning about potential security concern.

We are downloading data from our own application – there is no risk involved. Press Enable button to continue.

The data will be downloaded into a new Worksheet. You may be asked to identify yourself. Enter admin/admin123% or any other valid user account registered in the application membership database.

Select Insert on the ribbon and click PivotTable button.

Confirm the creation of a data range.

Now you are good to go. Here is the pivot view of the sales order items that shows distribution of product sales by employees. With a few clicks add a Pivot Chart for added impact.

Within minutes business users can make sense of their data and have amazing dashboards built in the tool they know best –Microsoft Excel.

The data feed embedded into the spreadsheet is live. Users can save the spreadsheet on the hard drive.

To refresh the data users can open the spreadsheet and select Refresh button on the Data tab of the ribbon.

Users will be prompted to re-enter the user name and password. The user’s identity will be verified against the database and the data feed will be refreshed.

Conclusion

Within minutes you can turn your SQL Anywhere 12 database into interactive treasure trove of data that can be placed on Internet or Intranet server.

Other available database connectivity options are MySQL, Microsoft SQL Server, and Oracle.

Review code customization techniques at http://codeontime.com/tutorials.aspx to learn how to use application designer to enhance the application and how to write custom business rules if the standard features of the generated application need to be enhanced.