Tutorials

Microsoft Azure Storage is a low-cost option that allows storing binary content in the cloud. Let’s implement storing of product pictures in the Northwind sample database. We will capture name, size, and content type of the picture file in the Products table. The actual pictures will be saved in Microsoft Azure Storage.

Setting Up Azure Storage

Navigate to http://azure.com and click on the Portal link in the top right corner to log in to your Azure account. If you do not have an account, a free three month trial is offered.

When the Management tool loads, click on the New button in the bottom left corner. Select Data Services | Storage | Quick Create option. Assign a URL for your new storage and press Create Storage Account.

When complete, click on the new storage account name in the list to view details. On the action bar displayed at the bottom, press Manage Access Keys. Copy one of the keys into the clipboard.

At the top of the form, click on Containers tab. Press Add Container at the bottom of the screen.

Assign a name to the container. If you do not expect the product pictures to be accessible to anonymous users, then leave Access property as “Private”. Click on the Check icon to save.

Adding Utility Fields to the Database

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Products, and press Design.

Add the following columns:

Save the changes. Switch to the generator and click on the project name. Select Refresh, check the box next to Products controller, and proceed to refresh the web app.

Configuring BLOB Field

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Products / Fields node, and press New Field.

Assign the following properties:

Save the field. Drag the new field node and drop it onto Products / Views / editForm1 to instantiate the field as a data field in the editForm1 view.

Drag the field onto view grid1 to display the picture in the list of products.

Next, double-click on the Products controller node. Enter the following in BLOB Adapter Configuration.

Note that the values above are not functional. Replace the highlighted parts with your own settings.

Make sure to save the controller.

Viewing the Results

On the toolbar, press Browse. Navigate to the Products page, and edit any record. Note that there is a Picture field which accepts files. Click on the link to upload a file and select a picture from your hard drive. A bar at the top of the page will announce completion of upload and the thumbnail of the picture will be displayed. The utility fields will be updated with the picture file name, length, and content type. If you do not want users to change any utility field values, configure the data fields as Static.

The thumbnail will display the file extension if the file is not an image.

The uploaded files are visible in Azure Management screen.

To upload files larger than 4 MB, you will need to increase the application upload size limit.

Code On Time web applications perform automatic handling of binary large objects (BLOB). Thumbnails of images are displayed in grids and forms, such as in the screenshot below.

Users can clear or replace the field with files of their own choosing. This functionality is available out-of-the-box and requires no extra programming when the data is stored in the database. It can be extended by adding utility fields to store the file name, size, and extension.

An alternative to database-stored data is use an external storage location. The data can be placed in online storage systems such as Amazon S3 or Microsoft Azure Storage. These systems provide many benefits, such as easy scalability and content delivery networks. The data can also be stored on a local Network Attached Storage (NAS).

Let’s implement a column in the Categories table of the sample Northwind database in order to store data on the local hard drive.

Adding Columns to the Categories Table

Start SQL Server Management Studio and connect to your database. In the Object Explorer, right-click on Databases / Northwind / Tables / dbo.Categories and press Design.

Add the following columns:

Save the changes to the table.

Refreshing the Project

Start Code On Time web application generator. Click on the project name, and select Refresh. Check the box next to Categories controller and continue to refresh the web app.

Implementing ExternalDoc Field

Start the Project Designer. In the Project Explorer, right-click on Categories / Fields node, and press New Field.

Assign the following values:

Save the field.

We will need to bind the field to grid1 and editForm1 views and prevent the user from editing the utility fields. In the Project Explorer, drag Categories / Fields  / ExternalDoc (Byte[]) field node onto Categories / Views / grid1.

Drag ExternalDoc (Byte[]) field node onto Categories / Views / editForm1 node.

Let’s prevent users from editing the utility fields in editForm1 or grid1. Right-click on Categories / Views node, and press List.

Upload of binary content is currently only supported for existing records. Let’s remove the utility fields from createForm1. Click on Categories / Views / createForm1 / ExternalDocFileName data field node. Hold Shift key, and click on ExternalDocContentType. Right-click and press Delete.

At the top of the Project Browser, switch to Data Fields tab. A grid of data fields will be displayed. Make the following changes to Format and Text Mode properties:

Implementing the Business Rules

If you try to upload files in the ExternalDoc field, an error will be displayed. File upload and download will have to be handled with custom business rules, which will do the following:

• The first business rule will prevent the default upload behavior, and persist the file contents to the specified folder. The file will be named using the CategoryID and generic “.bin” extension.
  
• When the binary content needs to be streamed to the client browser, another business rule will prevent the default behavior and use the CategoryID to find the file and pass it to the client.
  
• The third method will update the value of the binary field with the primary key of the record if an external file exists. The client library will pass the ID to the server components and display a thumbnail.
  If the binary field is empty then the method will return the CategoryID preceded by the word null and the symbol “|”. For example, if the category ID is 7491 then the value stored in ExternalDoc will be null|7491. 
  We are not actually probing if the file exists in the designated folder. Instead we make an assumption that if ExternalDocFileName field value is not blank then the file has been previously uploaded without errors.

The folder that blobs will be saved to must exist before they will be saved. Navigate to your My Documents folder and create another folder with the name “My External Doc Files”.

In the Project Explorer, right-click on Categories / Business Rules node, and press New Business Rule.

Assign the following properties:

Save the business rule. Create another business rule with the following properties:

Create a third rule:

On the toolbar, press Browse to generate the business rule files. When complete, right-click on Categories / Business Rules / UploadFile (Code / Execute) – r100 node, and press Edit Rule in Visual Studio.

Replace the code base with the following. Note that a Stream parameter has been added to the method definition in order to handle the uploaded file.

C#:

using System;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r100")]
        public void r100Implementation(int? categoryID, string categoryName, 
            string description, string externalDocFileName, int? externalDocLength, 
            string externalDocContentType, Stream externalDoc)
        {
            PreventDefault();
            string fileName = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream output = File.Create(fileName);
            externalDoc.CopyTo(output);
            output.Close();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.IO

Namespace MyCompany.Rules

    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r100")> _
        Public Sub r100Implementation(ByVal categoryID As Nullable(Of Integer),
                                      ByVal categoryName As String,
                                      ByVal description As String,
                                      ByVal externalDocFileName As String,
                                      ByVal externalDocLength As Nullable(Of Integer),
                                      ByVal externalDocContentType As String,
                                      ByVal externalDoc As Stream)
            PreventDefault()
            Dim fileName As String = Path.Combine( _
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
                String.Format("My External Doc Files\\{0}.bin", categoryID))
            Dim output As Stream = File.Create(fileName)
            externalDoc.CopyTo(output)
            output.Close()
        End Sub
    End Class
End Namespace

Save the file. In the Project Explorer, right-click on Categories / Business Rules / DownloadFile (Code / Execute) – r101 node and press Edit Rule in Visual Studio.

Replace the code:

C#:

using System;
using MyCompany.Data;
using System.IO;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r101")]
        public void r101Implementation(int? categoryID, string categoryName, string description, 
            string externalDocFileName, int? externalDocLength, string externalDocContentType, 
            Stream externalDoc)
        {
            PreventDefault();
            string fileName = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
                String.Format(@"My External Doc Files\{0}.bin", categoryID));
            Stream input = File.OpenRead(fileName);
            input.CopyTo(externalDoc);
            input.Close();
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System
Imports System.IO

Namespace MyCompany.Rules

    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r101")> _
        Public Sub r101Implementation(ByVal categoryID As Nullable(Of Integer),
                                      ByVal categoryName As String,
                                      ByVal description As String,
                                      ByVal externalDocFileName As String,
                                      ByVal externalDocLength As Nullable(Of Integer),
                                      ByVal externalDocContentType As String,
                                      ByVal externalDoc As Stream)
            PreventDefault()
            Dim fileName As String = Path.Combine( _
                Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
                String.Format("My External Doc Files\\{0}.bin", categoryID))
            Dim input As Stream = File.OpenRead(fileName)
            input.CopyTo(externalDoc)
            input.Close()
        End Sub
    End Class
End Namespace

Save the file. Finally, right-click on Categories / Business Rules / Select (Code / Execute) – r101 node and press Edit Rule in Visual Studio.

Replace the code with the following:

C#:

using System;
using MyCompany.Data;

namespace MyCompany.Rules
{
    public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
    {
        [Rule("r102")]
        public void r102Implementation(int? categoryID, string categoryName, 
            string description, string externalDocFileName, int? externalDocLength,
            string externalDocContentType)
        {
            if (!String.IsNullOrEmpty(externalDocFileName))
                UpdateFieldValue("ExternalDoc", categoryID);
            else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryID));
        }
    }
}

Visual Basic:

Imports MyCompany.Data
Imports System

Namespace MyCompany.Rules
    
    Partial Public Class CategoriesBusinessRules
        Inherits MyCompany.Data.BusinessRules
        <Rule("r102")> _
        Public Sub r102Implementation(ByVal categoryID As Nullable(Of Integer),
                                      ByVal categoryName As String,
                                      ByVal description As String,
                                      ByVal externalDocFileName As String,
                                      ByVal externalDocLength As Nullable(Of Integer),
                                      ByVal externalDocContentType As String)
            If Not String.IsNullOrEmpty(externalDocFileName) Then
                UpdateFieldValue("ExternalDoc", categoryID)
            Else
                UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryID))
            End If
        End Sub
    End Class
End Namespace

Save the file.

Viewing the Results

On the Project Designer toolbar, press Browse. When complete, navigate to the Categories page. The utility fields will be present in the grid.

Start editing a record, and upload a file to External Doc field. The file will be uploaded successfully, and the utility fields will be updated with the correct information.

Clicking on the thumbnail will download or open the file with the correct format. The file is stored in the folder My Documents/My External Doc Files with a file name of the CategoryID and extension of “.bin”.